Old versions, cleanup, and long-running readers

Start with the guided lesson. These sections develop the mechanisms and tradeoffs beyond that first example.

Version retention and reclamation

Old versions remain necessary while active readers, backups, replication, or other supported features can still need them. A retention horizon summarizes the oldest required state under the engine’s model. Reclaiming before that horizon violates reads; retaining indefinitely consumes memory and storage.

For illustrative 10,000 updates per second, 200 bytes of retained version data per update, and a 600-second horizon, version payload is 10000 × 200 × 600 = 1,200,000,000 bytes, or 1.2 GB. This excludes indexes, allocation overhead, compression, and additional maintenance structures.

A transaction left open for hours can therefore impose substantial costs even when it executes no current queries. The operational system needs timeouts, visibility into old snapshots, and a policy for workloads requiring long historical reads. Cancelling a reader may release retention, but only after the engine confirms that no other reference needs the versions.

Vacuum and maintenance

PostgreSQL’s vacuum documentation provides a concrete example of the operational work associated with versioned storage. The precise storage layout and maintenance behavior are implementation-specific. The general responsibility is to reclaim no-longer-needed state, maintain useful metadata, and prevent old transaction state from accumulating indefinitely.

Maintenance competes with foreground work. Too little can produce bloat and longer searches. Too much can consume I/O and CPU needed by current transactions. Scheduling should consider both resource headroom and debt age. A system that postpones all cleanup to achieve a short benchmark’s best result is not demonstrating a sustainable operating point.

Retention debt is a workload dimension

The earlier 1.2 GB estimate counted payload for ten minutes of retained updates. If each version additionally requires forty-eight bytes of metadata and sixteen bytes of allocation overhead, the illustrative per-update total becomes 264 bytes. At ten thousand updates per second over six hundred seconds, the total becomes 1.584 GB before index effects. A small overhead multiplied by a long horizon becomes operationally important.

A single old snapshot can hold back reclamation of versions for many rows even if it reads only a few. Some designs track finer dependencies; others retain a broad horizon for simplicity. Choosing a separate historical-read service or limiting transactional snapshot duration can reduce interference, but those are explicit architecture and product decisions.

Version debt can increase work after the old reader finishes. Vacuum or compaction must scan metadata, remove obsolete entries, and return reusable space. Freeing logical versions does not necessarily shrink files immediately or return memory to the operating system. Resource dashboards should distinguish logically dead state, reusable internal space, and physically released capacity.

Maintenance requires a safe relationship with replication and backups. A standby applying old log records, a logical decoding consumer, or a point-in-time restore process may depend on information beyond ordinary reader snapshots. The engine defines separate retention mechanisms and their interaction. A stalled consumer can therefore create disk pressure without any slow interactive transaction.

Long reads and fresh writes have different needs

A report that scans a billion rows may need a stable snapshot but not the freshest possible value. A purchase needs a fresh decision over a small state. Combining them in one undifferentiated resource pool can make the report retain versions and consume I/O while purchases encounter longer chains and less cache space.

A read replica or analytical copy can separate some costs, provided freshness and consistency are explicit. Running the report elsewhere does not automatically preserve a cross-table snapshot unless the replication and query model provide it. Copying each table independently at different times can create a report that never corresponded to any actual database state.

Conversely, an application should not retain a transaction snapshot simply to render a user interface for several minutes. It can often fetch a result, finish the transaction, and display that result as a historical observation. The interface then needs a refresh or conflict policy for subsequent writes. Database snapshot lifetime should follow correctness needs rather than user think time.

What an MVCC performance claim should include

A statement that readers do not block writers is incomplete without specifying lock modes, schema changes, uniqueness checks, writer conflicts, and maintenance behavior. MVCC removes some direct read/write blocking by retaining versions. It does not imply that all read operations are free of waits or that the engine has no internal latches.

Useful measurements include version-chain lengths, old-snapshot age, serialization-abort rate, time waiting for conflicting writers, dead-tuple or obsolete-version volume, and maintenance throughput. Measuring only committed transactions per second can hide a growing reclamation backlog. A sustainable operating point keeps both foreground latency and version debt bounded over time.

When comparing products, use their documented isolation guarantees rather than a generic MVCC label. PostgreSQL’s repeatable-read and serializable modes provide a concrete example of different anomaly contracts over a versioned storage system. The lesson’s interval diagrams and doctor scenario explain mechanisms; they are not claims that every versioned engine implements the same algorithm.

A concrete version-retention trace

An item begins with owner Alice at commit timestamp 10. A transaction transfers it to Bob at 20, and another deletes the item at 30. A snapshot at 15 still needs Alice’s version. A snapshot at 25 needs Bob’s. A current read after 30 must observe absence. The deletion cannot be interpreted as “continue searching for any older value,” because that would resurrect Bob’s ownership.

snapshot 15 -> [Alice, visible 10..20)
snapshot 25 -> [Bob,   visible 20..30)
snapshot 35 -> [deleted, visible from 30)

If the oldest registered snapshot advances from 15 to 25, Alice’s version may become reclaimable under this simplified model, provided no backup, replica, or other retention owner still needs it. Bob’s remains necessary for the snapshot at 25. Advancing to 35 can make both value versions reclaimable, while the deletion’s distributed or index-retention rules may still require metadata.

A garbage collector must not infer this horizon from a periodically sampled list with a registration race. A reader starting at 15 must register before reclamation can pass its required state. The same physical node can also remain protected by an active traversal after its logical version becomes obsolete. Logical eligibility and memory-safe reclamation are related but separate conditions.