I recently moved my scattered notes from Notion, Logseq, and Heptabase into Obsidian, and started revisiting books I read before. This series is called Old School: reading old books, asking new questions. This post is about The Internals of PostgreSQL.
My reason for reading it was very specific — not some vague "building fundamentals." For a while at work I was writing SELECT ... FOR UPDATE all the time, and at some point I realized I couldn't explain what it actually locks. The whole table? Those rows? Locked until when? And underneath that, an even more basic question: how does UPDATE actually "update" a row?
I thought those were two questions. After the book, I found out they are the same one. Because the first answer the book gave me flipped my intuition:
UPDATE never updates anything.
PostgreSQL does not modify the row in place. It writes a new version and marks the old one as expired. The old version does not disappear right away — some other transaction might still be looking at it. This is MVCC: the same row exists in several versions at once, and each transaction uses its own snapshot to decide which version it can see. Roughly, it is row version + txid + snapshot visibility.
The diagram below is how I currently think about visibility:
T1 creates snapshot S1 first, then T2 updates the same row and commits. When T1 reads again, which version does it see? Depends on the isolation level. Under Repeatable Read, T1 keeps its original snapshot, so it sees the old version; under Read Committed, the second select gets a fresh snapshot and may see T2's new value.
I had memorized the definitions of these two isolation levels before, but it was always memorization. Once you know "data is versions, and snapshots decide visibility," there is nothing left to memorize — they are just two answers to "when do you take a new snapshot."
So what does FOR UPDATE lock? The answer is tied to versions: it is a row-level lock that stamps its transaction ID onto the current version of that row. Any other transaction that wants to modify the row has to wait — until the lock holder commits or rolls back. So it does not lock the table, and not even "that piece of data" in the abstract. It locks that version.
Under high concurrency this gets messier. When many transactions hold locks on the same hot row at once (foreign key checks, shared locks), PostgreSQL needs a structure called MultiXact just to record "which group of transactions currently holds this lock." Lock contention on hot rows presses all the way down into machinery you normally never know exists. These days, when I look at lock problems in a live system, my first question is: is everyone fighting over the same rows?
The version mechanism has another cost, and anyone who runs PostgreSQL long enough will hit it. UPDATE keeps writing new versions; once no snapshot needs an old version anymore, it becomes a dead tuple — still taking up space, wanted by no one. If dead tuples never get cleaned up, the table swells. That is bloat: the live data has not grown, but the physical size keeps climbing, scans slow down, and cache hit rates drop with it.
Cleanup is vacuum's job, and PostgreSQL runs autovacuum in the background. But there is an easy trap: as long as one long transaction stays open, its old snapshot keeps those dead tuples "possibly still visible to someone," so vacuum will not reclaim them. A lot of PostgreSQL performance problems, chased to the end, turn out to be one idle in transaction nobody closed.
Listed out like this, the design sounds full of flaws. Yet the company that criticizes MVCC the hardest happens to be the company that pushes PostgreSQL the furthest.
OpenAI shared how they keep ChatGPT running. The post bluntly calls out MVCC as unfriendly to write-heavy workloads: changing one field copies the whole row, constant updates keep spawning new versions, and reads have to skip past piles of old ones. The engineer who wrote it even co-authored a more famous critique with a CMU professor, titled "The Part of PostgreSQL We Hate the Most." But the other half of the same post: they run a single primary with nearly 50 read replicas, serving 800 million users and millions of queries per second — without sharding yet.
The people who understand its flaws best still choose to push it to the limit.
Someone else looks at the same thing from the other end. The author of a video series has written Postgres for close to ten years, and he argues we have not actually touched PostgreSQL's performance ceiling — there are over four hundred tunable parameters, and most systems complain the database is slow without ever changing the defaults. One is concrete in its criticism, the other concrete in its praise, and I think they are saying the same thing: every layer of this system is a tradeoff, and tradeoffs can be understood.
The book is full of these tradeoffs. My favorite is the victim buffer. Shared buffers are finite; when a new page comes in, an old buffer has to give up its seat. Intuition says this is just LRU — evict whoever has been unused the longest. But a true LRU has to maintain exact usage order, updating a page's position every time it is touched — and in a highly concurrent database, that precise bookkeeping is itself a cost. So PostgreSQL uses something closer to clock-sweep: each buffer gets a usage count, and only one swept down to 0 can become the victim. Not the theoretically prettiest version — the version that holds up under concurrency.
Same flavor with the planner. It picks the path with the lowest estimated cost, but that only works if the statistics are fresh — which is why unglamorous background machinery like ANALYZE and autovacuum directly decides the quality of the planner's judgment. However smart the planner is, stale statistics send it down the wrong path.
Finally, WAL. If you have read DDIA, you have seen the replication models — master-slave, multi-master, leaderless. But after looking at PostgreSQL's WAL, I started caring about a more basic question: what exactly counts as a successful write?
When the leader finishes writing?
When the follower receives it?
When WAL reaches the OS page cache?
Only when it is flushed to disk?
Or only when the follower has replayed it and you can actually read it back?
These semantics are very different. Before this book, it never occurred to me that "write succeeded" was a phrase that needed to be taken apart.
Back to my original question. If someone asks me now what FOR UPDATE locks, I can walk from versions to snapshots to dead tuples to WAL. Not because I memorized more terms — I finally know they are different faces of the same design.
At the end of that video, the author asks a question that made me pause: AI is taking over "operational" tasks — so between someone who can only operate a database and someone who understands how it works underneath, who keeps their job? His answer is one line: the secret is in the know-why.
I mostly agree. Running commands, tuning parameters, doing migrations — AI can do all of that, better than I can. But why the system grew into this shape, where it made its tradeoffs, which direction to look when things break — that know-why, you still have to read for yourself.
That is why I am doing the Old School series. Books do not just hand you answers. They let you see why a system grew into the shape it has today.