The problem: readers and writers at once
Snip's stats workers update clicks_daily constantly. At the same moment, an owner's dashboard reads those rows, and
a nightly report reads the whole table for an hour. If readers had to wait for writers (and writers for readers), a
long report would freeze counting, and busy counting would stall dashboards.
Most modern databases avoid this with MVCC, multi-version concurrency control: never overwrite a row in place; write a new version and keep the old one for whoever still needs it.
Versions, step by step
In Postgres, every row version records which transaction created it and which (if any) replaced or deleted it. Here's
one row of clicks_daily as three transactions run:
| Time | What happens | Versions stored for (x7Kp, today) |
|---|---|---|
| 1 | Transaction 100 inserts the row with 10 clicks | v1: clicks = 10, created by 100 |
| 2 | Report transaction 105 starts and takes its snapshot | v1 |
| 3 | Worker transaction 110 adds 5 clicks and commits | v1: 10, replaced by 110 · v2: 15, created by 110 |
| 4 | Dashboard transaction 115 starts and reads the row | v1, v2 |
| 5 | Report 105 reads the row | v1, v2 |
- The dashboard (115) started after 110 committed, so it sees v2: 15.
- The report (105) took its snapshot before 110 committed, so it still sees v1: 10, even though v1 has been replaced. Its view of the whole database stays consistent for its entire hour.
- Nobody waited for anybody.
The rule each reader applies is simple: show me the version created by a transaction that had committed when my snapshot was taken, and not yet replaced by one that had.
Writers still conflict with writers: two transactions updating the same row at once means one waits for the other, or (at stricter isolation levels) one is aborted.
The bill: cleaning up
Old versions don't disappear by themselves. Every update leaves a dead version behind, and something has to remove it once no running transaction could still need it. In Postgres that's vacuum, which usually runs automatically.
That brings costs you'll meet in practice:
- Bloat. A row updated thousands of times a day (like a counter) leaves thousands of dead versions. If vacuum falls behind, tables and indexes grow and queries slow down.
- Long transactions hold everything back. Vacuum can't remove any version a running transaction might still see. One forgotten transaction left open for hours stops cleanup across the whole database.
- Write amplification. In Postgres, a new row version can also mean new entries in the table's indexes, so an update to one column can cost writes to every index (04.02 Indexes and B-trees).
This is part of why Snip's workers batch click counts (02.06 Async workers and queues): one update per link per batch leaves far fewer dead versions than one per click.
On replicas
A read replica (04.07 Replication: leaders, followers and lag) applies the primary's changes, including vacuum's cleanup. If a long query on the replica still needs a version the primary has cleaned up, the replica must either pause applying changes (and fall behind) or cancel the query. Long analytics queries on replicas and fresh replicas pull against each other.
Seen in production
Uber
2016Schemaless on Postgres
Uber's article, based on Postgres 9.2, describes write amplification: an update to a field covered by a single index still had to be propagated into all 12 indexes on the table. It also describes how, on a replica, Postgres pauses applying the write-ahead log while a conflicting query runs, and kills the query if it blocks replication for too long. These were among the reasons Uber moved to MySQL.
What we foundPostgres's multi-version storage made updates expensive on heavily indexed tables, and long replica queries conflicted with replication.
Why Uber Engineering Switched from Postgres to MySQLuber.com ↗A Postgres database's tables keep growing even though row counts are flat, and queries are slowing. Someone finds a transaction that has been open for 30 hours. What's the link?
Recap
Interview questions
What is MVCC, and what problems can it cause?