Backend Study Lab

System Design · Lesson 18

MVCC, VACUUM, and Table Bloat Explained

Learn how PostgreSQL MVCC, snapshots, dead tuples, autovacuum, visibility maps, freezing, HOT updates, and table or index bloat affect backend performance.

By CloveCode Studio Aug 4, 2026 Updated Aug 4, 2026 10 min read

Quick answer

PostgreSQL uses multiversion concurrency control (MVCC): updates create new row versions, and transactions read versions visible to their snapshots. Obsolete versions are not immediately removed because another snapshot might still need them. VACUUM identifies versions no longer visible to any relevant transaction, makes their space reusable, maintains visibility information, and supports transaction-ID freezing. Autovacuum performs this work continuously according to configurable thresholds and observed changes.

Long transactions and abandoned idle-in-transaction sessions can hold old snapshot horizons, delaying cleanup. High update/delete churn then accumulates dead tuples and extra index entries, increasing pages, cache pressure, scans, and maintenance. Index-only scans may perform heap fetches when pages are not all-visible. These effects can amplify a query regression even when the SQL and index definitions have not changed.

Diagnose with transaction age, backend_xmin, dead/live tuple estimates, autovacuum activity, table/index sizes, visibility behavior, workload churn, and query buffers. Treat bloat estimates as approximate evidence. Ordinary VACUUM usually reuses space inside relation files; it normally does not return that space to the operating system. VACUUM FULL rewrites a table and requires an ACCESS EXCLUSIVE lock, so it is a planned disruptive change, not routine incident first aid.

Shared order-history incident stage

During the order-history incident, a reporting process opens a repeatable transaction, reads recent orders, and remains idle while preparing an export. Its snapshot is older than continuing updates to order statuses and totals. PostgreSQL must preserve row versions that could still be visible to that transaction. Autovacuum runs, but its cleanup horizon cannot advance past all needed versions.

Meanwhile the flash-sale and backfill generate heavy updates. Dead tuple estimates rise, order and index pages grow, and the visibility map marks fewer heap pages all-visible. The candidate covering index from Composite, Covering, and Partial Indexes still contains required columns, but the executor reports heap fetches for visibility checks. More buffers are touched, and the original cardinality problem becomes more expensive.

This stage is not proof that “vacuum is broken.” The evidence must identify an old snapshot or another cleanup blocker, current autovacuum progress, change rate, relation size, and configuration. Running more vacuum workers cannot remove a version that remains visible to an old snapshot. Killing the reporting session may release the horizon, but only after ownership, transaction state, side effects, retry behavior, and export correctness are understood.

The incident response first bounds the report workload, closes abandoned transactions, and verifies cleanup recovery. Any rewrite, reindex, or storage reclamation is planned separately with lock, WAL, replica, capacity, and rollback controls. User recovery still depends on query duration, pool wait, locks, retries, and SLO evidence.

Core mechanism and evidence boundary

MVCC lets readers see a consistent snapshot while concurrent transactions create newer row versions. Each version carries transaction visibility metadata. An UPDATE is conceptually a new version plus an obsolete old version; a DELETE leaves an obsolete version until it is no longer visible. MVCC reduces many reader/writer conflicts, but it does not eliminate row locks, table locks, DDL locks, predicate conflicts, or transaction aborts.

Tuple-header xmin and xmax identify creating and deleting transaction visibility metadata for a row version. By contrast, pg_stat_activity.backend_xmin reports a backend’s current snapshot horizon relevant to cleanup. They are related through MVCC visibility, but they are not interchangeable identifiers or safe application fields.

Vacuum has several jobs. It reclaims dead-row storage for reuse, updates the visibility map used by index-only scans, advances freezing to protect against transaction-ID wraparound, and may update planner-related information when combined with ANALYZE. Plain vacuum can perform most cleanup alongside normal reads and writes, but its default tail-truncation phase can briefly require ACCESS EXCLUSIVE; use TRUNCATE FALSE only when that documented tradeoff fits the maintenance plan. Reusable internal space generally stays in the relation file even when truncation is unavailable or disabled.

VACUUM FULL is different: it rewrites the table into a compact file and requires ACCESS EXCLUSIVE. It needs additional disk space during the operation, generates substantial I/O and WAL effects, blocks access, affects replicas, and changes physical organization. Concurrent reindexing has its own multiple-scan, transaction-wait, resource, and invalid-index failure boundaries.

Autovacuum decisions depend on table changes, thresholds, scale factors, cost controls, worker availability, and freeze protection. A busy system may have a maintenance backlog even when autovacuum is enabled. Per-table tuning is often safer than globally making every worker aggressive. Measure change rate, duration, missed opportunities, and resource contention before changing settings.

Transaction IDs are finite and compared with wraparound-aware rules. Vacuum freezes sufficiently old row versions so their visibility remains safe as transaction IDs advance. PostgreSQL launches anti-wraparound work even under some configurations that otherwise disable autovacuum. Waiting until an age alarm is near the hard boundary leaves little operational choice and can lead to forced protective behavior. Monitor database and relation ages early; do not treat freeze work as ordinary space optimization.

Cleanup horizons can be retained outside the obvious application session. Prepared transactions, replication slots, and standby feedback configurations can preserve required history or WAL according to their own contracts. Investigate the exact PostgreSQL 18 mechanism before changing it. Dropping a slot or changing feedback can affect replication correctness and recovery; it is not a generic bloat switch.

Table and index bloat are related but distinct. Reusing heap space does not necessarily compact each index page, and different access methods have different maintenance behavior. Measure heap and every relevant index separately. Reindexing an index does not compact the heap or correct an application transaction that remains open.

HOT updates can avoid adding new index entries when indexed columns are not changed and a suitable new tuple version fits on the same heap page. Fillfactor and update pattern influence the opportunity. Adding indexes to frequently updated columns can reduce HOT eligibility and increase bloat and WAL. HOT is an optimization under conditions, not a correctness guarantee.

PostgreSQL 18 views such as pg_stat_activity, pg_stat_all_tables, progress views, and relation-size functions provide PostgreSQL-specific evidence, not a SQL standard or universal database contract. Counters and tuple counts are estimates and can reset or lag. transaction_age_seconds, dead_tuple_ratio, heap fetches, and relation bytes are teaching fields, not a stable OpenTelemetry semantic convention. Keep session IDs, query text, and tenant data out of metric labels.

Minimal reproducible PostgreSQL 18 example

Use two sessions in a disposable PostgreSQL 18 database. Create and churn synthetic rows:

CREATE TABLE churn_orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_bucket integer NOT NULL,
  status text NOT NULL,
  total_cents integer NOT NULL
) WITH (fillfactor = 80);

INSERT INTO churn_orders (tenant_bucket, status, total_cents)
SELECT g % 20, 'PENDING', 1000 + (g % 500)
FROM generate_series(1, 100000) AS g;

ANALYZE churn_orders;

Session A holds a snapshot only for the bounded experiment:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT count(*) FROM churn_orders;
-- Leave open only while performing the controlled Session B steps.

Session B updates synthetic rows and inspects approximate evidence:

UPDATE churn_orders
SET status = 'PAID'
WHERE id % 3 = 0;

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count
FROM pg_stat_all_tables
WHERE relname = 'churn_orders';

SELECT pg_size_pretty(pg_relation_size('churn_orders')) AS heap_size,
       pg_size_pretty(pg_total_relation_size('churn_orders')) AS total_size;

VACUUM (VERBOSE, ANALYZE) churn_orders;

Observe that cleanup is constrained while the old snapshot remains. Then ROLLBACK Session A, run ordinary vacuum again, and compare estimates and reusable space. Do not expect the relation file to shrink to its initial byte size.

Identify old transactions with restricted output:

SELECT pid, application_name, state,
       now() - xact_start AS transaction_age,
       wait_event_type, wait_event,
       backend_xmin
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

In production, do not export raw query text or terminate a PID from this list without verifying ownership, transaction state, business effects, and retry behavior.

Failure modes and dangerous misconceptions

Treating MVCC as lock-free concurrency. Writers, row lockers, DDL, constraints, and maintenance still use locks. Snapshot visibility and lock compatibility answer different questions.

Calling every large relation bloated. A large table may contain live business data. Compare live/dead estimates, expected row width, change rate, page use, index size, and query behavior. Bloat estimators are approximations.

Expecting ordinary vacuum to shrink files. Its main outcome is reusable internal space and visibility/freeze maintenance. Filesystem reclamation needs different operations and risk analysis.

Running VACUUM FULL during an incident. Its rewrite and exclusive lock can turn degradation into outage. Restore user service first and schedule disruptive reclamation separately.

Increasing workers without finding the horizon. Vacuum cannot remove versions visible to an old snapshot. Identify long transactions, replication slots, prepared transactions, and other relevant retention boundaries.

Disabling autovacuum for a busy table. This abandons routine cleanup, statistics, and wraparound protection. Tune with evidence rather than removing the safety system.

Adding indexes without update-cost testing. More indexed columns can reduce HOT opportunities, enlarge writes and WAL, and create additional index bloat.

Terminating “idle” sessions indiscriminately. Idle-in-transaction differs from idle outside a transaction. Cancellation and termination require ownership and side-effect analysis.

Using one dead-tuple ratio as a page threshold. Approximate counters can lag, reset, and ignore row-width or page-distribution effects. Combine them with sizes, churn, query buffers, maintenance history, and user impact.

Changing replication retention to free space immediately. Slots and standby feedback protect defined replication behavior. Identify the consumer and recovery requirement before changing them, and verify WAL as well as tuple horizons.

Security, privacy, capacity, and cost implications

Activity views may expose query text, application names, client details, and PII or personal data. Table and index names can reveal tenant or business structure. Use a restricted diagnostic role, select only required fields, redact artifacts, audit termination privileges, and expire incident exports.

Never use PID, transaction ID, tenant, user, order, query text, query identifier, request ID, or trace ID as metric labels. Aggregate bounded environment, database role, operation, maintenance state, and approved relation class. Detailed blocker and snapshot identity belongs in restricted logs.

Vacuum, reindex, and rewrite work consume CPU, I/O, cache, WAL, storage, worker slots, and replica replay capacity. More aggressive maintenance can compete with foreground queries; delayed maintenance creates future cost. Model both sides and tune the hottest tables individually where evidence supports it.

Freeze is a correctness obligation, not optional tidiness. Monitor transaction ages and autovacuum protection with sufficient time to act. Capacity planning must include temporary disk for rewrites and index builds, backup growth, replica lag, and recovery duration.

Testing and production validation

Create churn fixtures with different row widths, update rates, indexed-column changes, fillfactor, and transaction lengths. Verify result correctness while snapshots overlap. Measure live/dead estimates, relation/index bytes, vacuum duration, visibility, heap fetches, buffers, WAL, CPU, I/O, and write latency.

Test old-transaction alerts without embedding identities in metric labels. Exercise bounded session cancellation in staging and confirm transaction rollback, connection release, application error classification, retry limits, and export behavior. Simulate a maintenance backlog and verify the operational stop conditions.

For tuning, compare before/after under foreground concurrency. Check worker availability, autovacuum duration, table churn, user tails, pool wait, replica lag, checkpoints, and storage. Repeat after enough updates to avoid evaluating only a freshly cleaned relation.

In production, close the incident only after old horizons are understood, maintenance catches up, query buffers and heap fetches stabilize, pool and lock waits recover, and the user SLI returns. Physical compaction can remain a separately scheduled task if reusable space and user health are acceptable.

Validate autovacuum configuration through observed table behavior, not only configuration values. A worker may be eligible yet waiting for a slot, throttled by cost settings, blocked briefly, or repeatedly overtaken by churn. Record start and completion, tuples processed, duration, resource use, and the change rate during the same window. This distinguishes insufficient scheduling from an unreclaimable snapshot horizon.

Keep that evidence with the incident timeline for later capacity review.

Operations checklist

  • Identify user impact and query evidence before attributing latency to bloat.
  • Inspect transaction age, state, backend_xmin, ownership, and wait events through restricted views.
  • Check autovacuum activity, worker capacity, table churn, dead/live estimates, sizes, and freeze age.
  • Distinguish old snapshots from lock blockers and connection-pool waiting.
  • Verify index-only heap fetches and visibility under realistic write churn.
  • Assess indexed-column updates, HOT opportunity, fillfactor, index count, and WAL.
  • Use ordinary vacuum for routine cleanup; treat rewrite and reindex as planned changes.
  • Put lock, duration, disk, WAL, replica, and rollback controls around maintenance.
  • Never terminate sessions without side-effect, ownership, idempotency, and retry analysis.
  • Keep PII, query text, PIDs, transaction IDs, and correlation IDs out of metric labels.
  • Validate user SLI, query work, writes, maintenance, replicas, and storage after change.
  • Schedule physical reclamation separately when it is truly required.

Official sources

Continue the learning path

Connect visibility to Composite, Covering, and Partial Indexes, then identify snapshot and lock ownership in Database Lock Contention and Long Transactions. Database Isolation Levels provides the transaction semantics foundation, while Database Query Performance for Backend Systems connects maintenance evidence to users. Continue through System Design or Topics.

Knowledge check

Check your understanding

Answer both questions correctly to mark this lesson as mastered. You can retry without penalty.

1. Dead tuples rise while an old idle-in-transaction report retains backend_xmin; why does another VACUUM not immediately remove all obsolete versions?

2. A covering index plan reports many heap fetches after heavy updates; which evidence explains the boundary?

Keep reading