All Questions
Tagged with database-internals postgresql
39 questions
0
votes
1
answer
275
views
Query running successfully but much longer due to wait_event MessageQueueSend
I have a long running bug where some larger queries, sometimes run much much longer due to being stuck on wait_even MessageQueueSend. The difference can be anything from <100% to 1000s percent when ...
0
votes
1
answer
78
views
Why does PostgreSQL not add padding for column alignment at the end of tuple?
In the example here: https://www.percona.com/blog/postgresql-column-alignment-and-padding-how-to-improve-performance-with-smarter-table-design/
Why doesn't PostgreSQL add 2 bytes padding after the ...
- 103
2
votes
1
answer
74
views
How do you make sure a data page can only have 3 rows
For a table with a setting of (fillfactor = 75) I am trying to make sure that each data page can only have 3 rows.
select ((8192 * 0.75 - 24) / 3)- 4 - 24; -- returns 2012.
First 24 is page header ...
- 487
3
votes
1
answer
148
views
Change the definition of an Index with Expression without dropping/recreating the index
I'm in a peculiar situation:
I have multiple indexes with expressions that use a function. I want to switch them to a different function, where I can guarantee the function behaves the same (so the ...
- 279
1
vote
1
answer
780
views
What is difference between checkpoint_timeout and checkpoint_completion_target in PostgreSQL?
I am MSSQL guy and I find it a bit difficult to understand the main purpose of checkpoint_completion_target. I cannot find a comprehensive resource that would explain more clearly the difference ...
- 1,313
4
votes
1
answer
834
views
Suggestion for nearly gap-less sequences in postgres
In PostgreSQL sequences are designed to have gaps for reasons mentioned in this post https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences. My question is: could sequences not be ...
- 247
1
vote
2
answers
394
views
Tracing Postgres frontend and backend messages
As per my understanding when we execute a command or query, the Postgres client sends a frontend message, and in return gets the response in the backend message format.
How do I capture and check ...
- 135
0
votes
0
answers
292
views
B-tree indexing example
I am trying to understand "how" Postgres creates the indexes using b-trees with an example.
To be specific, I am looking to understand how the b-tree will look like when the ("Name"...
- 109
0
votes
0
answers
17
views
Array of chars? [duplicate]
Can we create an array of chars in postgresql i.e (Column_name char[5]) and does it take only 5 bytes in disk ? since type char takes only one byte
- 55
3
votes
2
answers
9k
views
varchar(n) size?
When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
- 55
0
votes
0
answers
1k
views
What means execute S_2, S_3 and S_4: COMMIT in Postgresql logs?
I'd noticed different commit types in Postgres log:
2019-12-20 12:00:00 [99999]: [12-1] host=...,user=...,db=... LOG: duration: 1000.000 ms execute S_2: COMMIT
2019-12-20 12:00:00 [99999]: [12-1] ...
- 461
0
votes
0
answers
152
views
Is there a way to make postgres "COUNT WHERE" query log(N)?
Afaik, now this request SELECT COUNT(*) FROM user WHERE rating > 50 looks like this:
find an item with the value of rating 50(or greater) in our b-tree index, pretty fast.
Iterate though all items ...
- 121
8
votes
3
answers
4k
views
Postgres heap vs SQL Server clustered index
I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres.
Can someone share their ...
- 93
3
votes
2
answers
164
views
PostgreSQL GiST compress skeleton when no compression required
I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of ...
- 133
3
votes
1
answer
364
views
Performance difference in accessing differrent columns in a Postgres Table
Created a table with 200 bigint column, 200 varchar column. (Postgres 10.4)
create table i200c200 ( pk bigint primary key, int1 bigint, int2 bigint,....., int200 bigint, char1 varchar(255),......, ...
- 664