This is the chapter that finally connected a bunch of things I'd been treating as separate facts — why UPDATE doesn't edit rows in place, why table files keep growing even after you delete data, why VACUUM exists at all. It turns out all of it comes back to one mechanism, MVCC, and once that clicked, a handful of previously mysterious Postgres behaviors stopped being mysterious.
What transactions actually give you
BEGIN;
UPDATE venues SET is_closed = TRUE WHERE id = 5;
UPDATE closures SET closed_on = NOW() WHERE venue_id = 5;
COMMIT; -- or ROLLBACK;
Everything between BEGIN and COMMIT either all happens, or none of it does. If something goes wrong partway through — an error, a constraint violation, or you just decide to ROLLBACK yourself — neither update takes effect. This matters here specifically because marking a venue closed and logging when it closed are really two halves of one action. Without a transaction wrapping both, you could end up with a venue marked closed but no closure record, or a closure record for a venue that's still marked open, if something failed between the two statements.
ACID, without the textbook phrasing
- Atomicity — the whole transaction is one unit. No partial, half-applied state where some of it went through and some didn't.
- Consistency — the database moves from one valid state to another. Constraints never get violated in the committed result, even if intermediate steps inside the transaction technically would have violated them on their own.
- Isolation — concurrent transactions don't see each other's uncommitted changes. What "see" actually means here depends on the isolation level, which is worth its own section.
- Durability — once committed, it survives a crash. It's written to disk, specifically to something called the write-ahead log (WAL), before Postgres considers the commit successful.
These four letters get recited a lot without much explanation of what they're actually protecting against. Atomicity is what stops the venue/closure scenario above from going half-done. Durability is what means a COMMIT isn't a lie — if the server crashes one second after it returns success, that data is still there when it comes back up.
Isolation levels
| Level | Prevents | Postgres default? |
|---|---|---|
| Read Uncommitted | (Postgres treats this the same as Read Committed — it doesn't actually implement dirty reads) | no |
| Read Committed | dirty reads | yes, this is the default |
| Repeatable Read | dirty + non-repeatable reads | no |
| Serializable | all anomalies, including phantom reads | no |
Read Committed being the default means each individual statement inside a transaction sees whatever was committed at the moment that specific statement started — not the moment the whole transaction began. That distinction matters more than it sounds like it should: two statements in the same transaction, run a few seconds apart, could see different data if something else committed in between, because each statement takes its own fresh look at what's currently committed.
Serializable is the strongest guarantee — it behaves as though transactions ran one at a time, in some order, with no interleaving at all. The trade-off is that Postgres has to detect conflicts between concurrent Serializable transactions and abort one of them if it can't guarantee that "as if run one at a time" property, which means application code using Serializable transactions generally needs retry logic built in for when that happens.
For anything genuinely financial-grade, Serializable is the safer default despite that extra complexity. For most everyday application logic, Read Committed is fine, and it's what you get unless you explicitly ask for something stronger.
MVCC — the actual mechanism underneath all of this
Here's the part that reframed a bunch of things for me. Postgres doesn't lock rows for reads the way you might picture from a simpler mental model of "one person edits, everyone else waits." Instead, it keeps multiple versions of a row.
When you run an UPDATE, Postgres doesn't modify the existing row in place. It writes an entirely new row version with the updated values, and marks the old version as expired — not deleted yet, just no longer the current version. Readers who started their query before the update still see the old version, consistently, without being blocked by the write happening concurrently. Writers, likewise, aren't blocked by ongoing reads. This is why Postgres handles a mix of concurrent reads and writes well without needing to lock everything constantly.
This also connects directly back to something from the CRUD article earlier in this series — every INSERT creates a new row version, and now the reason that phrasing matters is clearer: Postgres genuinely never edits a row's storage in place. It's always writing new versions and marking old ones as no longer current.
Why VACUUM exists
Those old, expired row versions don't just disappear the moment they stop being current — they sit there, taking up space, until something cleans them up. That's what VACUUM does.
VACUUM ANALYZE venues;
If you've ever deleted a bunch of rows from a table and noticed the file on disk didn't shrink the way you'd expect, this is the explanation — the dead row versions are still physically there until VACUUM reclaims that space. AUTOVACUUM runs by default in the background specifically so you don't normally have to think about this, but it's worth knowing it exists and understanding why, rather than just trusting it silently.
This is a genuinely good question to know the answer to, mostly because a lot of people who've used Postgres casually have never actually encountered it — they've never had a table grow large enough, or gone deep enough into how updates work, to run into it. Knowing why VACUUM exists is less about the command itself and more a decent signal that you've thought about what's actually happening underneath an UPDATE, not just what syntax makes one run.
Putting it together with the venue/closure example
Back to the transaction from the start of this article:
BEGIN;
UPDATE venues SET is_closed = TRUE WHERE id = 5;
UPDATE closures SET closed_on = NOW() WHERE venue_id = 5;
COMMIT;
Under the hood, both UPDATEs create new row versions rather than editing in place. Until COMMIT runs, another transaction reading venues at Read Committed isolation still sees the old, pre-update version of that row — the change genuinely isn't visible yet. Once COMMIT happens, the new versions become the current ones, and the old versions become eligible for cleanup, eventually, by VACUUM. If something failed between the two UPDATE statements and the transaction rolled back instead, both new row versions would just be discarded, and the original rows would remain the current version, as if none of this had happened at all.
Where this leaves you
MVCC is the reason Postgres handles concurrent reads and writes gracefully without heavy locking, and it's also the reason a handful of things that might otherwise seem unrelated — why updates don't happen in place, why deleted data doesn't immediately shrink a table, why VACUUM exists — actually connect to one underlying idea. Transactions and isolation levels sit on top of this, controlling exactly what a given piece of code is allowed to see while other things are happening concurrently.
Next up: views and materialized views — the difference between a query that re-runs live every time versus one that's actually stored and needs to be refreshed.
Top comments (0)