3
votes
Accepted
Understanding the Impact of ORDER BY in Window Functions on other Partitions in PostgreSQL
Think of your data as a pile of clothes, where the odd column stores the id of a type of clothing (shirt, pants, socks, etc). The PARTITION clause on odd is the equivalent of organizing the clothes ...
- 35.1k
3
votes
Accepted
potgresql uses only one worker for index creation
No. No one has implemented parallel build for hash indexes.
See the docs:
For index methods that support building indexes in parallel (currently, only B-tree)
So you would need to implement it ...
- 38.6k
2
votes
PostgreSQL's unaccent function is unable to remove accents (diacritic signs) from Japanese characters like 'ド'
Strings can be decomposed with Unicode rules by normalize(text, NFD) since PostgreSQL 13. In the case of the mentioned string, it produces the following code points:
SELECT
codepoint,
to_hex(...
- 30.8k
2
votes
Understanding the Impact of ORDER BY in Window Functions on other Partitions in PostgreSQL
Without the ORDER BY, the expression is underdetermined. There are multiple possible answers, of which it returns the most convenient to compute. Once one expression gains an ORDER BY, then it ...
- 38.6k
2
votes
PostgreSQL's unaccent function is unable to remove accents (diacritic signs) from Japanese characters like 'ド'
Like Vérace mentioned in comment, this symbol doesn't have diacritic signs. This is a syllable as Japanese use syllabary instead of alphabet.
That means that while "ト" is "to" in ...
- 721
2
votes
Accepted
Shared buffers, WAL buffers and Checkpointers
Data like table pages and index pages are always accessed through shared_buffers. The only time they are accessed directly on disk is when they are being copied into shared_buffers, or being cleaned ...
- 38.6k
1
vote
Possible options for SQL and NoSQL combination for social media data
We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we have a mix of structured and unstructured data...
Having some data examples would ...
- 35.1k
1
vote
How do I find missing sub-partitions of an index to make main index complete?
While I was unable to actually figure out a way to get Postgres to tell me which partition was missing, I did come across the relispartition flag on the pg_class table that I was able to query on and ...
- 111
1
vote
Accepted
Postgres sorting order not respecting default C collation?
C.UTF8 is not a valid ICU collation. If you give ICU a garbage collation name like that, it ignores what it cannot parse, and you probably end up with the und collation instead. That would explain ...
- 47.8k
1
vote
Accepted
PostgreSQL : is max_worker_processes cpu bound?
I think that you misunderstand max_worker_processes. This parameter does not limit the number of PostgreSQL backend processes (the server processes that belong to a database session), bu the number of ...
- 47.8k
1
vote
Accepted
Creating VIEW using EXECUTE ignores USING clause parameter?
The USING clause of EXECUTE is for passing values to DML commands ("utility commands"). The manual:
Another restriction on parameter symbols is that they only work in
optimizable SQL ...
- 172k
Only top scored, non community-wiki answers of a minimum length are eligible