←back to thread

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

(www.cs.cmu.edu)
366 points virtualwhys | 2 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 #
halayli ◴[21 Oct 24 08:10 UTC] No.41901808[source]▶
I am referring to physical pages in an SSD disk. The 8k pg page maps to 2 pages in a typical SSD disk. Your comment proves my initial point, which is write amplification cannot be discussed without talking about the disk types and their behavior.
replies(2): >>41902116 #>>41903957 #
emptiestplace ◴[21 Oct 24 09:09 UTC] No.41902116[source]▶
Huh? It seems you've forgotten that you were just saying that a single bit change would result in a 4096 byte write.
replies(1): >>41906755 #
1. Tostino ◴[21 Oct 24 18:11 UTC] No.41906755[source]▶
> a single bit change would result in a 4096 byte write

On (most) SSD hardware, regardless of what software you are using to do the writes.

At least that's how I read their comment.

replies(1): >>41907050 #
2. emptiestplace ◴[21 Oct 24 18:40 UTC] No.41907050[source]▶
Right, and if pg writes 8192 bytes every time, this is no longer relevant.