Lesson 15 · Transactions · Module 2

Snapshots Without Locks

A report runs for ninety seconds. During those ninety seconds, thousands of rows it is reading are being updated by other people. It returns one consistent answer, it never waits, and nobody waits for it. That is not politeness — it is a specific mechanism with a specific bill, and the bill arrives somewhere you would not think to look.

The win in this lesson: you will be able to explain why "readers don't block writers" is possible at all — not as a slogan but as a mechanism — and why the price of it shows up later as vacuum work rather than as lock waits.

1. The price we refuse to pay

There is an obvious way to make a long read consistent: lock everything it touches until it finishes. It works. It is also unusable. A ninety-second report would hold a ninety-second gate in front of every writer behind it, and on a busy table that gate becomes the system's throughput ceiling. Worse, the failure is invisible in testing — it only appears when reads and writes overlap, which is to say in production.

So the requirement is stricter than "make reads correct". It is: make reads correct without letting a read's duration become anybody else's latency. Postgres names the thing that does this, and states the advantage in one sentence:

"The main advantage of using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading."

PostgreSQL 18 documentation, Concurrency Control · Introduction

Read that as an engineering claim rather than a feature bullet. Two operations that would obviously conflict — one reading a row, one rewriting it — are declared not to conflict. The only way that can be true is if they are not touching the same thing.

2. The update that is not an update

They are not. The mechanism is one sentence long, and everything else in this lesson falls out of it:

"In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row."

PostgreSQL 18 documentation, Routine Vacuuming

An UPDATE in Postgres is not an edit. It is an insert of a new row version, plus a note on the old one saying which transaction superseded it. The old version stays exactly where it was, byte for byte. A reader that was already looking at it is not interrupted, because nothing was taken away from it — the writer built its change alongside, not on top.

Every row version carries two stamps: the transaction id that created it, and the transaction id that deleted or superseded it (empty while it is current). You can see them:

SELECT xmin, xmax, balance FROM accounts WHERE id = 7;

 xmin  | xmax | balance
-------+------+---------
 41820 |    0 |     100

A snapshot is then just a rule for reading those stamps: a record of which transactions had committed at a particular instant. Given a snapshot and a row version, visibility is a decision, not a wait — was xmin committed as of my snapshot, and was xmax not? If yes, this version is mine. Otherwise, follow the chain to another version, or ignore the row entirely.

"This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data."

PostgreSQL 18, Concurrency Control · Introduction

3. Who sees what

Here is the mechanism running. One row, two versions, three transactions — a writer and two readers at different isolation levels — and the only question worth asking at each point on the line: which version does this transaction resolve to?

4. Two snapshots, two clocks

An isolation level, in Postgres, is mostly an answer to one question: how often do I take a fresh snapshot? The default takes one per statement:

"Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed by concurrent transactions during the query's execution. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run."

PostgreSQL 18 documentation, Transaction Isolation

The consequence is the thing people are surprised by, and the docs say it outright:

"Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts."

PostgreSQL 18, Transaction Isolation

So BEGIN on its own buys you atomicity, not a stable view. If your code reads a total, then reads the rows that make up that total, a default-isolation transaction can return a total that no set of rows ever produced. The fix is to move the snapshot from the statement to the transaction:

"This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction… Thus, successive SELECT commands within a single transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started."

PostgreSQL 18, Transaction Isolation

Note the precision of "the first non-transaction-control statement". The snapshot is not taken at BEGIN. It is taken at your first real statement — so a connection that opens a transaction and then sits idle has not yet frozen anything.

Seen from a session, the difference is unmissable:

-- session A                                 -- session B
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 7;   --  100
                                             UPDATE accounts
                                               SET balance = 250 WHERE id = 7;
                                             COMMIT;
SELECT balance FROM accounts WHERE id = 7;   --  100  (same snapshot)
COMMIT;

SELECT balance FROM accounts WHERE id = 7;   --  250  (new statement, new snapshot)

5. The bill: dead tuples and bloat

Nothing was free. Every superseded version is still sitting in its page, and Postgres is explicit about who has to clean up and why:

"But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements."

PostgreSQL 18, Routine Vacuuming

That is the job of VACUUM: "The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse."

Read the phrase no longer of interest to any transaction slowly, because it is the hinge of this whole lesson. A dead version cannot be reclaimed while any open snapshot might still need it. So the question "how much garbage is my database carrying?" has an answer that depends on the oldest transaction currently open — not on how much you write, and not on how long ago you wrote it.

The expensive mistake is a long transaction, not a big one

A transaction that started an hour ago and has done nothing since is still holding a snapshot, and that snapshot pins every row version superseded in the last hour. Vacuum runs, finds it is not allowed to remove anything, and achieves nothing. The table grows. The indexes on it grow with it, because each live version needs its own index entries. Queries slow down in proportion, because — as Lesson 08 put it — the currency is pages touched, and bloat means more pages holding the same amount of real data.

Which reframes a habit most codebases have. An idle-in-transaction connection is not merely an idle connection. It is a brake on garbage collection for the entire cluster, and the damage scales with your write rate, not with anything that transaction did.

The number to watch is not CPU. It is this:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables
 ORDER BY n_dead_tup DESC
 LIMIT 10;

-- and the transaction that is stopping you:
SELECT pid, state, now() - xact_start AS open_for, query
  FROM pg_stat_activity
 WHERE state = 'idle in transaction'
 ORDER BY xact_start;

6. Residual risk

The cost is deferred, which means it is easy to miss

Lock contention announces itself: queries wait, you see it on a latency graph, you find the blocker. Bloat does not announce itself. It presents as everything getting gradually slower, a disk filling at a rate nobody ordered, and an autovacuum worker that has been running on one table since Tuesday. The mechanism that removed your lock waits also removed your warning signal.

Vacuum is not optional, and the deadline is not negotiable

Reclaiming space is only half of what vacuum is for. The other half is a hard constraint:

"But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss."

PostgreSQL 18, Routine Vacuuming

"To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions." This is the same wall Lesson 07 watched a company run at during a live migration. The connection is worth stating plainly: the visibility stamps that let readers avoid locks are the same 32-bit counter that will stop your writes if vacuum falls far enough behind. One mechanism, both consequences.

MVCC does not resolve write conflicts

Readers and writers are decoupled. Two writers are not. Snapshots decide what you see; they say nothing about what happens when two transactions both decide to change the same row. That is still settled by row locks — and Postgres keeps them available for exactly this: "Table- and row-level locking facilities are also available in PostgreSQL for applications which don't generally need full transaction isolation and prefer to explicitly manage particular points of conflict." A read-modify-write done under READ COMMITTED with no lock and no conditional update is a lost update, and no isolation level short of asking for one will save you.

7. Check yourself

8. Back to your world

Two things to go and check on any system you own. First: does any report, export or batch job read more than once inside a transaction at the default isolation level? If so, it can produce an internally inconsistent answer, and it will do so rarely enough that nobody trusts the bug report. Second: run the pg_stat_activity query above in business hours and look for sessions sitting idle in transaction. Those are usually not deliberate — a connection pool, an ORM that opened a transaction early, or an HTTP handler that called an external API mid-transaction. Each one is quietly holding your database's garbage collection hostage.