←back to thread

MVCC – the part of PostgreSQL we hate the most (2023)

(www.cs.cmu.edu)
366 points virtualwhys | 1 comments | 20 Oct 24 15:30 UTC | HN request time: 0s | source
Show context
halayli ◴[21 Oct 24 00:57 UTC] No.41899794[source]▶
This topic cannot be discussed alone without talking about disks. SSDs write 4k page at a time. Meaning if you're going to update 1 bit, the disk will read 4k, you update the bit, and it writes back a 4k page in a new slot. So the penalty for copying varies depending on the disk type.
replies(2): >>41900275 #>>41904085 #
srcreigh ◴[21 Oct 24 02:54 UTC] No.41900275[source]▶
Postgres pages are 8kb so the point is moot.
replies(2): >>41901535 #>>41901808 #
1. olavgg ◴[21 Oct 24 07:19 UTC] No.41901535[source]▶
The default is 8kb, but it can be recompiled for 4kb-32kb, I actually prefer 32kb because with ZSTD compression, it will most likey only use 8kb after being compressed. Average compress ratio with ZSTD, is usually between 4x-6x. But depending on how your compressable you data is, you may also get a lot less. Note that changing this block size, will require initialization of a new data file system for your Postgres database.