A dashboard query runs while a background job updates ten thousand rows. In a lock-based engine, that SELECT waits—sometimes for seconds—until every UPDATE releases its row locks. PostgreSQL's MVCC sidesteps this entirely: readers operate on a snapshot taken at query start, so they never block writers and writers never block readers.
The mechanism is straightforward. When a row changes, PostgreSQL writes a new tuple version instead of overwriting the old one. The previous version remains visible to any transaction whose snapshot predates the commit. Under steady-state conditions with adequate vacuuming, read latency tends to remain stable regardless of write throughput, but it can degrade if vacuum falls behind or bloat accumulates.
The trade-off is storage. Dead tuple versions accumulate until vacuum reclaims them. That maintenance overhead—bloat, autovacuum tuning, the occasional VACUUM FULL—is the only real cost of MVCC. The rest of this article shows why, for read-dominated systems, that cost is worth paying and how to keep it bounded.
Readers never block writers: the snapshot mechanics
PostgreSQL ships with Read Committed as the default isolation level. When a transaction runs at this level, every SELECT (without FOR UPDATE/SHARE) takes a snapshot at query start. That snapshot contains only rows committed before the query began; it never sees uncommitted data or changes committed by concurrent transactions during the query's execution.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE id = 42;
The snapshot is per-query, not per-transaction. Two successive SELECT commands in the same transaction can return different results if other transactions commit between them. This is the mechanism that lets readers proceed without ever blocking on writers.
Writers behave symmetrically: UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE search for target rows using a snapshot taken at command start. They lock only the specific rows they modify, not the entire table or index range.
Reader takes snapshot at T1, writer commits new version at T2, reader's next query at T3 sees the committed change without ever waiting for a lock.
Snapshot overhead is low—essentially a transaction ID threshold—so read throughput can scale well with cores, though actual scaling depends on many factors (I/O, cache hit rate, query complexity, lock contention on hot rows). Under the default isolation level, read-heavy workloads see minimal contention because readers never wait for writers.
Visibility map and index‑only scans
The visibility map (VM) is a per‑relation bitmap that tracks which heap pages contain only tuples visible to all active transactions. Each bit corresponds to one heap page; when set, it guarantees that every tuple on that page is all‑visible. This guarantee is what allows an index‑only scan to return results without ever touching the heap.
Vacuum sets all‑visible bits; index‑only scans consult the visibility map and satisfy queries from the index alone.
Because the map is conservative—if a bit is set the condition holds, but an unset bit means nothing definite—PostgreSQL can safely skip heap fetches only when the bit is set. Indexes themselves have no visibility map, so the heap‑page flag is the sole shortcut.
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM accounts WHERE balance > 1000;
When the planner chooses an Index Only Scan, the Buffers output will show hits on the index only (e.g., Buffers: shared hit=12) and zero heap blocks read—provided the VM bits for the relevant pages are set.
Those bits are set exclusively by vacuum and are cleared by any data‑modifying operation on the page. The VM lives in a separate fork named <filenode>_vm alongside the main relation file. If vacuum falls behind, the map becomes stale, index‑only scans degrade to regular index scans with heap fetches, and read latency rises. Keeping vacuum current is therefore not just a maintenance chore—it is the prerequisite for the read‑path optimization that makes MVCC fast for read‑heavy workloads.
The vacuum cost: bloat, autovacuum, and VACUUM FULL
Dead tuples are the price of MVCC's non-blocking reads. Every UPDATE or DELETE leaves behind a tuple version that remains visible to in-flight transactions. Until those transactions end, the space is unreclaimable. Standard VACUUM marks dead tuples reusable for future inserts but, per the documentation, "will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained". This means bloat—the gap between logical data size and on-disk footprint—is normal and expected. Storage overhead and vacuum maintenance are the primary costs of MVCC, but they are not the only ones: the mechanism also incurs CPU overhead for visibility checks on every read and requires monitoring for transaction ID wraparound.
The autovacuum daemon handles this continuously. It "will never issue VACUUM FULL" and instead "maintain steady-state usage of disk space: each table occupies space equivalent to its minimum size plus however much space gets used up between vacuum runs". Standard VACUUM runs concurrently with production traffic: "SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed".
psql -c "VACUUM (VERBOSE, ANALYZE) accounts;"
VACUUM FULL is a different beast. It "actively compacts tables by writing a complete new version of the table file with no dead space", but it "requires an ACCESS EXCLUSIVE lock on the table it is working on, and therefore cannot be done in parallel with other use of the table". It also demands extra disk space for the rewrite and can take hours on large tables. Treat it as a last resort—scheduled maintenance windows only—after autovacuum tuning has failed to control bloat.
Tuning autovacuum for steady state
Autovacuum runs in the background, but its default thresholds assume a modest write rate. On read‑heavy systems with bursty updates, the default scale factors let dead tuples accumulate until the next vacuum cycle, inflating table size and degrading index‑only scans. Two knobs move the needle: shared_buffers (so vacuum can keep more pages in memory) and the autovacuum scale factors (so vacuum triggers earlier).
shared_buffers defaults to 128 MB. On a dedicated server with 1 GB or more RAM, a reasonable starting value is 25 % of system memory; allocating more than 40 % is unlikely to improve performance because PostgreSQL also relies on the operating system cache. The setting is global and requires a restart.
For autovacuum, lower the scale factors so vacuum and analyze fire after a smaller fraction of rows change. The defaults (0.2 for vacuum, 0.1 for analyze) work for light write loads; read‑heavy workloads with occasional spikes often benefit from starting points around 0.05 and 0.02 respectively. Pair these with autovacuum_vacuum_cost_limit raised to 2000–4000 only if I/O headroom exists—monitor iowait and pg_stat_progress_vacuum while increasing incrementally; setting it too high can starve other backend processes of I/O bandwidth. Treat these numbers as initial values—validate them against pg_stat_progress_vacuum and pg_stat_user_tables for your specific workload.
| Parameter | Default | Tuned for read‑heavy steady state (starting points) |
|---|---|---|
shared_buffers |
128 MB | 25 % of RAM (≤40 %) |
autovacuum_vacuum_scale_factor |
0.2 | 0.05 |
autovacuum_analyze_scale_factor |
0.1 | 0.02 |
autovacuum_vacuum_cost_limit |
200 | 2000–4000 |
# postgresql.conf snippet
shared_buffers: '4GB' # example for 16 GB host
autovacuum_vacuum_scale_factor: 0.05
autovacuum_analyze_scale_factor: 0.02
autovacuum_vacuum_cost_limit: 3000
Monitor pg_stat_progress_vacuum and pg_stat_user_tables.n_dead_tup to verify dead‑tuple counts stay low between cycles. If bloat still grows, consider per‑table autovacuum_vacuum_scale_factor overrides via ALTER TABLE … SET (autovacuum_vacuum_scale_factor = 0.01).
When row‑level locking can still win
Row-level locking wins when write contention is extreme and sustained—think high-frequency counter updates on a single hot row, or long-running transactions that pin old snapshots and prevent vacuum from reclaiming dead tuples. In those cases MVCC's version chains grow unbounded, index-only scans degrade because the visibility map stays dirty, and every new writer pays the cost of traversing or cleaning up the chain. The lock-based alternative serializes writers cleanly and avoids version bloat entirely.
That serialization has a cost, though: row-level locking forces readers to wait for writers (and writers to wait for readers), introducing reader‑writer contention that reduces read throughput and increases latency. For read‑heavy workloads where reads dominate, this contention is precisely what MVCC avoids.
That much is true. If your workload is a stream of UPDATE counter SET n = n + 1 on one row, MVCC creates a new tuple version per statement and vacuum must chase each one. HOT (Heap-Only Tuple) updates can alleviate version chaining when the updated column is not indexed and the page has enough free space (governed by fillfactor), but a counter column is often indexed, limiting this benefit. Standard VACUUM can run alongside reads and writes, but it cannot keep up with that churn rate, and VACUUM FULL—the only way to physically compact the table—requires an ACCESS EXCLUSIVE lock that blocks all access.
But read-heavy workloads don't look like that. By definition, reads dominate; the hot rows are reference data, not counters. Long-running transactions are rare because analytics queries run against replicas or materialized views, not the primary. Write bursts are short and spread across many rows, so version chains stay short and vacuum catches up during quiet periods. The overhead that hurts in write-heavy corners is simply not the steady state for the workloads this article addresses.
MVCC wins for read‑heavy workloads if you manage vacuum
MVCC wins for read-heavy workloads because non-blocking reads are the default, not a tuning achievement. The trade-off—storage overhead and vacuum maintenance—is manageable when reads dominate. Apply this checklist:
-
Monitor bloat continuously. Track
pgstattupleorpg_freespacemapmetrics; alert when dead-tuple ratios climb on hot tables. -
Tune autovacuum aggressively. Lower
autovacuum_vacuum_scale_factorto 0.05–0.1, raiseautovacuum_max_workers, and setautovacuum_vacuum_cost_limithigh enough to keep pace with write bursts. - Design for index-only scans. Keep the visibility map current (see #2) and include frequently selected columns in indexes so the executor skips heap fetches entirely.
If you run read-heavy on the primary and accept these three operational habits, MVCC delivers higher throughput and simpler application logic than any locking-based alternative. The vacuum tax is real; the blocking tax is avoidable. Choose the one you can automate.
Top comments (0)