PostgreSQL MVCC in Production: Beyond Nonblocking Reads

  An update-heavy PostgreSQL table can remain available while quietly becoming more expensive to operate. Transactions continue to commit, application response times initially look acceptable, and no major lock queue is visible. Meanwhile, the table grows, index scans perform more heap fetches, autovacuum runs repeatedly, and storage consumption refuses to settle.

The underlying issue is often not a single slow query. It is the lifecycle of row versions created by Multi-Version Concurrency Control, commonly known as MVCC.

MVCC is the reason PostgreSQL can provide consistent reads while other sessions modify the same tables. It is also the reason PostgreSQL needs vacuuming, transaction-age monitoring and careful handling of long-running snapshots. Understanding only the concurrency benefit gives an incomplete operational picture. A production DBA also needs to understand where old versions remain, when they can be removed, and what prevents that cleanup.

Why MVCC Matters in Production

Without MVCC, a reporting query reading a table might need to wait while another transaction updates it. PostgreSQL takes a different approach. Each statement reads from a transactionally consistent snapshot and sees only the row versions that are valid for that snapshot.

This allows normal reads and writes to proceed concurrently. It does not mean PostgreSQL is lock-free. Two sessions updating the same row can still block each other, explicit row locks still matter, and DDL may request locks that conflict with application traffic.

MVCC therefore provides two operational outcomes:

• Better concurrency because readers normally do not wait for writers.
• Additional maintenance because obsolete row versions must eventually be cleaned.

What an UPDATE Really Does

A PostgreSQL row should not be viewed as one physical object that is continuously overwritten. On a normal heap table, an UPDATE creates a successor tuple containing the new values. The older version remains available while an active snapshot may still need it.

PostgreSQL stores transaction visibility information in system columns:

• xmin identifies the transaction that created the tuple version.
• xmax contains transaction information related to deletion, supersession or row locking.
• ctid identifies the physical location of that particular tuple version.

These columns are useful for controlled troubleshooting, but they are not application keys. An update can change ctid, transaction IDs eventually wrap, and a nonzero xmax does not automatically mean that a visible tuple is dead.

A Two-Session MVCC Test

The following test demonstrates the behaviour more clearly than a definition. Run the marked sections from two separate PostgreSQL sessions.

-- Initial setup
CREATE TABLE mvcc_account_demo
(
    account_id bigint PRIMARY KEY,
    balance    numeric(12,2) NOT NULL
);

INSERT INTO mvcc_account_demo VALUES (1001, 1000.00);

-- Session A BEGIN;

UPDATE mvcc_account_demo SET balance = 900.00 WHERE account_id = 1001;

SELECT ctid, xmin, xmax, account_id, balance FROM mvcc_account_demo WHERE account_id = 1001;

-- Session B, before Session A commits SELECT ctid, xmin, xmax, account_id, balance FROM mvcc_account_demo WHERE account_id = 1001;

-- Session A COMMIT;

-- Session B, run as a new statement SELECT ctid, xmin, xmax, account_id, balance FROM mvcc_account_demo WHERE account_id = 1001;

Session A sees its uncommitted balance of 900.00. Before the commit, Session B continues to see the previously committed value of 1000.00 without waiting. After Session A commits, a new statement in Session B sees 900.00.

The same logical account row temporarily has more than one physical version, but each session sees the version permitted by its snapshot.

Isolation Level Changes the Snapshot

Read Committed

READ COMMITTED is PostgreSQL's default isolation level. Each statement receives a new snapshot. Two queries inside the same transaction can therefore return different committed results if another session commits between them.

This behavior is suitable for most short OLTP transactions. It can be unsuitable for a report that runs several queries and expects all totals to represent the same point in time.

Repeatable Read

REPEATABLE READ retains a stable transaction snapshot after it is established. It is useful for multi-query reports, but keeping that snapshot open for hours can prevent vacuum from removing older row versions.

An updating repeatable-read transaction may fail with could not serialize access due to concurrent update. The application must roll back and retry the complete transaction.

Serializable

SERIALIZABLE adds protection against transaction combinations that could produce a result inconsistent with serial execution. It is useful when a business rule spans several rows and cannot be enforced through one atomic statement or constraint.

Applications using this level must handle SQLSTATE 40001 and retry the entire transaction. Increasing the isolation level without implementing retry logic is an incomplete design.

Where Blocking Still Happens

MVCC separates normal reads from writes, but it cannot allow incompatible changes to proceed without coordination. Blocking remains possible when:

• Two transactions update or delete the same row.
• A query uses SELECT FOR UPDATE or SELECT FOR SHARE.
• A unique or foreign-key check encounters an uncommitted conflicting change.
• Transactions acquire rows or objects in different orders and deadlock.
• DDL requests a table lock that conflicts with active sessions.
• TRUNCATE or VACUUM FULL requires an ACCESS EXCLUSIVE lock.

In a payment transfer, MVCC alone does not validate the business rule. The application still needs atomic debit logic, deterministic locking, rollback handling and protection against submitting the same transfer twice.

Vacuum Is the Other Half of MVCC

An old tuple becomes dead only after PostgreSQL determines that no relevant snapshot can see it. The storage occupied by that tuple must then be made reusable.

Standard VACUUM performs several jobs:

• Makes obsolete tuple space available for reuse.
• Cleans dead index entries when required.
• Updates the visibility map, which can improve index-only scans.
• Freezes old tuple metadata to prevent transaction ID wraparound.

A regular vacuum normally does not shrink the table file. It creates reusable space inside the relation. VACUUM FULL rewrites and compacts the table, but it requires additional working space and an exclusive lock. It should not become routine maintenance for a table whose autovacuum settings are ineffective.

Autovacuum activity also produces I/O and WAL. The aim is not to make vacuum invisible. The aim is to run it frequently enough that cleanup remains manageable and predictable.

A Practical MVCC Health Check

The following grouped checks cover dead tuples, old transaction horizons, active vacuum work and lock blockers. Visibility into other users' full session details may require the pg_read_all_stats role or equivalent privileges.

-- Tables with high estimated dead-tuple volume
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(
        100.0 * n_dead_tup /
        greatest(n_live_tup + n_dead_tup, 1),
        2
    ) AS estimated_dead_pct,
    last_autovacuum,
    autovacuum_count,
    n_tup_upd,
    n_tup_hot_upd
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Long transactions and retained cleanup horizons SELECT
pid,
usename,
application_name,
client_addr,
state,
now() - xact_start AS transaction_age,
backend_xid,
backend_xmin,
wait_event_type,
wait_event,
left(query, 150) AS query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

-- Current vacuum progress SELECT
pid,
datname,
relid::regclass AS table_name,
phase,
heap_blks_scanned,
heap_blks_total FROM pg_stat_progress_vacuum;

-- Waiting sessions and their blocker PIDs SELECT
pid AS waiting_pid,
pg_blocking_pids(pid) AS blocking_pids,
wait_event_type,
wait_event,
left(query, 150) AS waiting_query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

n_dead_tup is an estimate, not an exact measurement of bloat. Use it for trending and candidate identification. A high value becomes more meaningful when combined with table growth, vacuum history, update volume and an old backend_xmin.

Common Production Failure Patterns

Autovacuum Is Running but Cannot Clean

Seeing an autovacuum worker does not prove that useful space is being reclaimed. An active query, transaction-wide snapshot, prepared transaction or replication slot may retain a cleanup horizon older than the tuples being examined.

Idle in Transaction Connections

An abandoned transaction can retain locks, an XID or a snapshot horizon. However, not every session marked idle in transaction is automatically blocking vacuum. Check backend_xid, backend_xmin, held locks and the isolation level before drawing that conclusion.

High-Churn Tables Reach the Threshold Too Late

A percentage-based autovacuum threshold can become too large for a table containing hundreds of millions of rows. By the time the threshold is reached, the table may already contain a significant volume of dead tuples. Such tables often need lower per-table scale factors rather than aggressive cluster-wide settings.

Standby Queries Conflict With Recovery

A long report on a standby may require versions that WAL replay needs to remove. PostgreSQL can delay replay, cancel the standby query, or use hot_standby_feedback to preserve required versions on the primary.

Feedback can reduce query cancellations, but delayed cleanup may increase bloat on the primary. Replica lag, standby conflicts and primary table growth must be monitored together.

Tuning Update-Heavy Tables

PostgreSQL can perform a Heap-Only Tuple, or HOT, update when the modified columns do not require normal index changes and the successor tuple fits on the same heap page. HOT updates reduce index write amplification, but they do not eliminate dead tuples, WAL or vacuum work.

A lower fill factor may reserve page space for future versions. Autovacuum thresholds can also be adjusted for an individual high-churn table. The values below are starting examples and must be validated against the actual modification rate and I/O capacity.

-- Monitor the HOT update ratio
SELECT
    schemaname,
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    round(
        100.0 * n_tup_hot_upd /
        greatest(n_tup_upd, 1),
        2
    ) AS hot_update_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;

-- Example settings for a large update-heavy table ALTER TABLE bank.accounts SET
(
fillfactor = 85,
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 5000,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_analyze_threshold = 5000
);

-- Manual maintenance when measurements justify it VACUUM (VERBOSE, ANALYZE) bank.accounts;

-- Protect an application role from abandoned transactions ALTER ROLE application_user SET idle_in_transaction_session_timeout = '5min';

Changing fillfactor does not immediately reorganize existing pages. Role-level timeout changes apply to new sessions. Test both changes with the application before applying them to production.

Oracle and PostgreSQL Use Different MVCC Machinery

Oracle and PostgreSQL both provide multiversion read consistency, but the old versions are managed differently. This distinction is particularly useful for Oracle DBAs moving into PostgreSQL operations.

Area	PostgreSQL	Oracle
Update handling	Creates a successor tuple version in the heap.	Modifies the data block and records information in undo.
Consistent read	Selects the tuple version visible to the transaction snapshot.	Uses undo to reconstruct an earlier block image when required.
Cleanup concern	Dead tuples, vacuum effectiveness and table or index bloat.	Undo retention, undo pressure and reusable undo space.
Long-query risk	Delayed cleanup and relation growth. Standby queries may be cancelled by recovery conflicts.	ORA-01555 when required undo is no longer available.
Background maintenance	Autovacuum, analyze and tuple freezing.	Automatic undo management.
Same-row changes	Writers can block other writers.	Writers can block other writers.

An Oracle DBA investigating consistent-read trouble naturally checks undo retention. In PostgreSQL, the first checks are usually old snapshots, transaction horizons, dead tuples, vacuum history and replication slots.

Mini Case Study: Vacuum Was Running but Bloat Continued

A payment-status table received continuous updates. Autovacuum workers were visible, but the table and its indexes continued growing. Query latency also increased as the workload progressed.

Root Cause : A reporting connection had retained an old repeatable-read snapshot. Autovacuum could scan the table, but it could not remove versions still visible to that transaction. The table's default scale factor also allowed a large number of obsolete tuples to accumulate before each vacuum started.

Resolution : After confirming that the report was abandoned, the old session was ended through the normal production process. A standard vacuum and analyze were run, table-level autovacuum thresholds were reduced, and the reporting role received an appropriate transaction timeout.

The table file did not immediately shrink, which was expected. The reclaimed space became reusable, dead-tuple growth slowed, and relation size eventually stabilized. The important finding was that an active autovacuum worker is not proof that cleanup can advance.

Lessons 

• Monitor transaction age together with backend_xmin. Age alone does not prove that a session is holding back vacuum.
• Do not disable autovacuum because it creates I/O. Determine why it cannot keep up and tune the affected tables.
• Use VACUUM FULL only after considering its lock, duration and additional disk requirements.
• Review high-churn tables separately. Cluster defaults are rarely ideal for every relation.
• Track HOT update ratios after adding indexes to frequently modified columns.
• Set transaction policies for reporting applications rather than allowing unlimited snapshots.
• On replicas, monitor recovery conflicts, replay lag and primary bloat as one operational trade-off.

Frequently Asked Questions

Why are dead tuples still high after autovacuum?

Vacuum may be blocked by an old snapshot horizon, or it may not have completed enough cleanup. Check backend_xmin, prepared transactions, replication slots, autovacuum logs and pg_stat_progress_vacuum.

Does regular VACUUM reduce the physical table size?

Usually not. It makes internal space reusable. Returning substantial space to the operating system requires a rewrite operation, which must be planned for its locking and storage impact.

Can MVCC prevent deadlocks?

No. Transactions can deadlock when they acquire rows or objects in different orders. Keep transactions short and use a consistent access order.

Should reporting transactions always use Repeatable Read?

Only when several statements genuinely need the same snapshot. Long repeatable-read transactions can retain old versions and increase cleanup pressure.

Should I lower the autovacuum scale factor for every table?

No. Lower values start vacuum earlier but also consume more worker time and I/O. Apply table-level settings where modification volume and cleanup trends justify them.

Conclusion

MVCC is often explained as the feature that stops readers from blocking writers. That description is correct, but it leaves out the part DBAs spend most of their time managing.

Every update begins a lifecycle. PostgreSQL creates a new tuple version, transaction snapshots decide which version each session can see, and vacuum eventually reclaims versions that are no longer required. When cleanup falls behind, the symptoms may appear as table growth, index overhead, replica conflicts, excessive WAL or deteriorating query performance rather than an obvious lock wait.

There is no single MVCC tuning parameter. Healthy operation depends on short transactions, effective autovacuum settings, controlled reporting snapshots, suitable table design and correct application retry logic.

Start with the tables receiving the most updates. Compare total updates with HOT updates, review dead-tuple trends, identify old transaction horizons, and verify that vacuum is making progress rather than simply appearing in the process list. MVCC provides excellent concurrency, but its cleanup path deserves the same attention as its read consistency.

Further Reading

• PostgreSQL MVCC Introduction
• PostgreSQL Transaction Isolation
• PostgreSQL Routine Vacuuming
• PostgreSQL Heap-Only Tuples