PostgreSQL Internals: VACUUM, WAL & Bloat - Complete Notes

An in-depth, practical and interview-focused guide to heap tuple versions, visibility, HOT, dead tuples, vacuuming, autovacuum, transaction ID freezing, bloat, write-ahead logging, checkpoints, crash recovery, durability, archiving and operational diagnosis.

00. One mental model for the whole topic

PostgreSQL separates a logical row from its physical row versions, and separates transaction durability from immediate data-file writes. MVCC creates old tuple versions that later need cleanup. WAL makes committed changes recoverable before dirty table and index pages reach disk.

01. Heap storage, pages and tuple locations

An ordinary PostgreSQL table uses heap storage: row versions are placed where suitable free space exists, rather than being physically ordered by a primary key.

Relations, forks and pages

Tables and indexes are relations stored in relation files. Large relation files can be split into file segments, but SQL normally treats them as one object. A heap has several forks:

• The main fork stores heap pages and tuples.
• The free space map fork, suffixed _fsm, records approximate free space.
• The visibility map fork, suffixed _vm, records all-visible and all-frozen pages.
• The initialization fork, suffixed _init, exists for unlogged relations.
• Large values can be moved to an associated TOAST table, which has its own storage and indexes.

The usual page size is 8 KiB, chosen when PostgreSQL is compiled. A heap page has a fixed header, an array of item identifiers, free space, tuple data and normally no special area. Item identifiers grow forward from the page front while tuple data grows backward from the page end.

A tuple identifier, or TID, is commonly displayed as (block_number,item_number). The system column ctid exposes the current row version's TID. It is useful for diagnosis and tightly scoped maintenance, but it is not a durable key. An update, table rewrite or row movement changes it.

SELECT id, xmin, xmax, cmin, cmax, ctid
FROM accounts
WHERE id = 42;

SELECT
  pg_size_pretty(pg_relation_size('accounts')) AS heap,
  pg_size_pretty(pg_indexes_size('accounts')) AS indexes,
  pg_size_pretty(pg_total_relation_size('accounts')) AS total;

02. Heap tuple lifecycle and system columns

PostgreSQL performs an update by creating a new tuple version and making the old version no longer current. This is the physical basis of MVCC.

INSERT, UPDATE and DELETE

1. INSERT creates a tuple version whose xmin identifies the inserting transaction.
2. UPDATE creates a replacement tuple with a new xmin. The old version's tuple metadata records that it was superseded. Its ctid participates in the version chain.
3. DELETE does not immediately erase bytes. It records deletion metadata on the existing version.
4. When no possible snapshot can need an old version, it becomes dead and can be pruned or vacuumed.
5. Reclaimed space becomes available for later tuples. Plain vacuum usually does not compact every page or return internal free space to the operating system.

This is often summarized as "UPDATE is insert plus mark old," but it is not two user-visible SQL statements. It is one atomic PostgreSQL operation with tuple header changes, locking, index work and WAL records governed by the storage engine.

CREATE TEMP TABLE version_demo (
  id bigint PRIMARY KEY,
  status text
);

INSERT INTO version_demo VALUES (1, 'new');
SELECT id, status, xmin, xmax, ctid FROM version_demo;

UPDATE version_demo SET status = 'paid' WHERE id = 1;
SELECT id, status, xmin, xmax, ctid FROM version_demo;

-- The logical key stays 1. xmin and usually ctid identify a new version.

03. Snapshots, visibility and dead tuples

A tuple is not dead merely because a newer version exists. It is dead only when no transaction that PostgreSQL must honor can still see it.

The visibility question

A snapshot represents which transactions were in progress around a query's visibility boundary. PostgreSQL combines the snapshot with the inserting and deleting transactions' states to decide whether a version belongs in the query's view. In Read Committed, each command gets a fresh snapshot. Repeatable Read and Serializable normally retain a transaction snapshot, subject to their additional rules.

A transaction that started earlier can keep an old version visible even after another transaction commits an update. VACUUM must not remove that old version. This required retention boundary is often discussed as the cleanup horizon. Long transactions, abandoned idle transactions, prepared transactions and some replication-slot states can hold horizons back.

Why long transactions are maintenance problems

A long-running query may be read-only and hold no troublesome row lock, yet its old snapshot can prevent removal of many versions produced by unrelated writers. The table and indexes grow, future scans visit more pages, and vacuum repeatedly encounters versions it cannot remove. An idle in transaction session is especially wasteful because it retains transactional state while doing no useful work.

SELECT
  pid,
  usename,
  state,
  xact_start,
  backend_xid,
  backend_xmin,
  wait_event_type,
  wait_event,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

SELECT gid, prepared, owner, database, transaction
FROM pg_prepared_xacts
ORDER BY prepared;

04. HOT updates and page pruning

Heap-only tuple, or HOT, updates reduce index churn by keeping a version chain on one heap page when indexed values do not require new ordinary index entries.

When HOT is possible

• The update does not change columns referenced by the table's indexes, excluding summarizing indexes.
• The page holding the old tuple has enough free space for the new tuple version.

An index entry can continue pointing to the original page item identifier. PostgreSQL follows the page-local HOT chain to the visible version. Dead intermediate versions can be pruned during normal page access, including some reads, so HOT can reduce both new index entries and reliance on a later full index-cleanup cycle.

Lowering fillfactor reserves page space during initial filling and later inserts, increasing the chance of same-page updates. It also makes the table larger when new. This is a workload tradeoff, not a universal optimization. It is most valuable for frequently updated rows whose indexed columns usually remain unchanged.

SELECT
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  n_tup_newpage_upd,
  round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

ALTER TABLE frequently_updated_accounts SET (fillfactor = 80);
-- Existing pages are not magically repacked. A rewrite may be needed
-- if the operational decision is to apply the new layout immediately.

05. Table bloat and index bloat

Bloat is avoidable physical space and work left behind when the useful data becomes much smaller than the relation footprint or when reusable space is poorly matched to future writes.

Table bloat

Dead tuple space inside heap pages can usually be reused after vacuum. That space is not always harmful if the table will soon refill it. It becomes operational bloat when the relation remains needlessly large, scans and cache usage suffer, or free space cannot serve the workload well. Deleting 90 percent of a table may leave a large file because plain vacuum preserves internal pages for reuse.

Index bloat

Non-HOT updates can create new index entries for new tuple versions. Deletes and obsolete tuple versions leave entries that vacuum's index-cleanup phase can remove. B-tree page splits, sparse pages and changing key distributions can also leave an index much larger than its useful content. Index bloat increases index scan I/O, cache pressure, write amplification and maintenance time.

Common causes

• High update or delete volume with vacuum arriving too late.
• Long snapshots or old replication-related horizons blocking tuple removal.
• Large tables using scale-factor thresholds that allow too many changes before vacuum.
• Updates to indexed columns, preventing HOT and generating index entries.
• Pages packed too tightly for a strongly update-oriented workload.
• One-time bulk deletion followed by little or no future reuse.
• Repeatedly skipped or incomplete index cleanup.
• Disabled autovacuum, exhausted workers, excessive throttling or lock conflicts.

SELECT
  s.schemaname,
  s.relname,
  s.n_live_tup,
  s.n_dead_tup,
  s.n_mod_since_analyze,
  s.last_autovacuum,
  s.autovacuum_count,
  pg_size_pretty(pg_relation_size(s.relid)) AS heap_size,
  pg_size_pretty(pg_indexes_size(s.relid)) AS index_size
FROM pg_stat_user_tables AS s
ORDER BY pg_total_relation_size(s.relid) DESC;

SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'accounts'
ORDER BY pg_relation_size(indexrelid) DESC;

The optional official pgstattuple extension can estimate or inspect tuple and free space more directly. Its exact functions and locking costs differ, so read the matching server version's manual and test on representative data. Estimation is usually safer for a first production pass.

06. What plain VACUUM actually does

Plain vacuum is routine online maintenance. It finds removable versions, cleans related index entries when appropriate, marks heap space reusable, updates maps and advances maintenance metadata.

Conceptual phases

1. Scan heap pages that may need processing, using the visibility map to skip safe pages.
2. Identify dead tuple identifiers that are removable below the cleanup horizon.
3. Remove corresponding entries from indexes when index cleanup is selected.
4. Remove dead heap line pointers and make tuple space reusable.
5. Update the free space map and set valid visibility-map bits.
6. Freeze eligible old transaction and multixact metadata.
7. Optionally truncate completely empty pages from the physical end.

The implementation can make multiple passes when maintenance memory cannot hold every dead tuple identifier. Index cleanup may use parallel workers for eligible indexes. Current PostgreSQL can skip index cleanup when there are very few dead tuples because scanning every index could cost more than the immediate benefit.

VACUUM (VERBOSE) public.accounts;

VACUUM (ANALYZE, VERBOSE) public.accounts;

VACUUM (INDEX_CLEANUP ON, VERBOSE) public.accounts;

VACUUM (TRUNCATE OFF, VERBOSE) public.busy_events;

VACUUM (PARALLEL 4, VERBOSE) public.large_fact_table;

Options worth understanding

• VERBOSE reports what happened, including freezing and cleanup details.
• ANALYZE follows vacuum with statistics collection.
• FREEZE uses aggressive freezing cutoffs for a controlled special case.
• INDEX_CLEANUP AUTO is the normal policy; ON and OFF force a choice.
• TRUNCATE OFF avoids the tail-truncation attempt and its stronger lock.
• SKIP_LOCKED lets a multi-relation maintenance command skip relations it cannot lock.
• BUFFER_USAGE_LIMIT controls the maintenance buffer-access ring.

07. VACUUM FULL, ANALYZE and rewrite choices

VACUUM FULL solves a different problem from routine vacuum, while ANALYZE solves planner estimation rather than physical cleanup.

VACUUM FULL

VACUUM FULL rewrites the table into a compact new file and rebuilds its indexes. It usually returns much more space to the operating system, but it requires an ACCESS EXCLUSIVE lock, blocks all table use, takes time and needs temporary disk for the new copy until the operation completes. The table gets a new physical layout, so tuple locations change.

Use it only after measuring a durable need to shrink and planning the lock window, extra disk, WAL volume, replica impact and recovery path. Autovacuum never chooses VACUUM FULL. Routine vacuum should normally keep a write-heavy table in a reusable steady state.

Related rewrite choices

• CLUSTER rewrites the heap in an index order and rebuilds indexes, with a strong lock.
• Some ALTER TABLE forms rewrite the table as part of a schema change.
• REINDEX or REINDEX CONCURRENTLY targets index structure rather than heap compaction.
• TRUNCATE is efficient when the intent is to remove every row, but has distinct MVCC and locking semantics.
• Online rewrite extensions exist, but they add operational dependencies and are not core behavior.

ANALYZE

ANALYZE samples table contents and stores statistics used by the planner, including value distributions, null fractions and distinct-value estimates. It does not remove dead tuples or shrink a relation. Autoanalyze triggers independently from autovacuum based on data-change counters.

-- Reclaim dead space for reuse and maintain visibility/freezing:
VACUUM public.orders;

-- Refresh planner statistics after a data-distribution change:
ANALYZE public.orders;

-- Do both:
VACUUM (ANALYZE) public.orders;

-- Exceptional offline compaction after proving the need:
VACUUM (FULL, ANALYZE) public.archived_events;

08. Free space map and visibility map

The free space map answers "where might a tuple fit?" The visibility map answers "which heap pages are safe to skip for specific visibility or freezing work?"

Free space map

The FSM stores approximate available space per heap or index page in a compact tree. Insert and update paths consult it to find a page likely to fit a tuple without scanning the whole relation. Vacuum updates it after reclaiming space. Because entries are approximate and races are possible, finding a page through the FSM does not guarantee that enough room remains when the writer reaches it.

Visibility map

A heap visibility map stores two conservative bits per heap page:

• All-visible: every tuple on the page is known visible to all active and future transactions. Vacuum can skip some work, and an index-only scan may avoid a heap visibility fetch.
• All-frozen: every tuple is frozen, so even anti-wraparound vacuum can skip the page.

Vacuum sets valid bits after checking a page. Any data modification clears the relevant all-visible state. The map is conservative: a missing bit means "not proven," not necessarily "false." Indexes do not have their own visibility map because MVCC visibility belongs to heap tuples.

09. Autovacuum architecture

Autovacuum is a launcher plus worker processes that schedule table-level VACUUM and ANALYZE based on statistics and safety ages.

1. The launcher wakes and considers databases over time.
2. It starts workers, limited by configured worker slots and maximum active workers.
3. A worker connects to one database and checks tables for vacuum or analyze eligibility.
4. The worker runs eligible maintenance with cost-based throttling and table-specific settings.
5. Workers avoid duplicating the same table work, but several can operate in one database.

The default launcher nap is one minute per database cycle. With multiple databases, launches are distributed across that interval. A few very large eligible tables can occupy all workers and delay smaller tables, so worker capacity and per-table thresholds matter.

10. Autovacuum thresholds and per-table tuning

Threshold plus scale factor allows small tables to trigger after a fixed number of changes and large tables to trigger in proportion to estimated size.

Update and delete vacuum trigger

Conceptually, a table becomes eligible after changed tuples exceed:

vacuum_trigger =
  min(
    autovacuum_vacuum_threshold
      + autovacuum_vacuum_scale_factor * estimated_table_tuples,
    autovacuum_vacuum_max_threshold
  )

-- PostgreSQL 18 defaults:
-- threshold = 50
-- scale factor = 0.20
-- maximum threshold = 100,000,000

The maximum threshold prevents the proportional formula from requiring more than 100 million updates or deletes by default on enormous tables. On versions before that setting exists, or when it is set to -1, there is no such cap.

Insert vacuum trigger

Insert-triggered vacuum exists because append-only tables still need visibility-map maintenance and freezing. Its scale component is based on unfrozen pages, not simply live tuple count:

insert_trigger =
  autovacuum_vacuum_insert_threshold
    + autovacuum_vacuum_insert_scale_factor * estimated_tuples_on_unfrozen_pages

-- Defaults: 1000 and 0.20
-- A threshold of -1 disables insert-based triggering.

Analyze trigger

analyze_trigger =
  autovacuum_analyze_threshold
    + autovacuum_analyze_scale_factor * estimated_table_tuples

-- Defaults: 50 and 0.10
-- Inserts, updates and deletes contribute to the change count.

Why large busy tables often need smaller scale factors

At 100 million rows, a 20 percent scale component represents 20 million changes before ordinary eligibility, unless capped. That can be much too late for a hot table even if the percentage sounds reasonable. Configure lower per-table values based on actual change rate, vacuum duration, acceptable dead-tuple backlog and available I/O.

ALTER TABLE public.orders SET (
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 2
);

-- Inspect effective relation options:
SELECT relname, reloptions
FROM pg_class
WHERE oid = 'public.orders'::regclass;

11. Transaction ID wraparound and freezing

Normal transaction IDs are 32-bit values compared in a circular space. Old tuple metadata must be frozen before wraparound could make the past appear to be the future.

The XID ring

There are roughly four billion 32-bit XID values. For a normal XID, roughly two billion are considered older and two billion newer using modulo arithmetic. A tuple left with ordinary old transaction metadata for too long could eventually be misclassified. PostgreSQL prevents this by marking sufficiently old committed tuple versions frozen.

Modern PostgreSQL normally preserves the displayed original xmin and records frozen state in tuple flags. A frozen version is treated as safely older than every normal transaction for visibility purposes.

A normal vacuum can skip all-visible pages, so it may not freeze every older tuple. An aggressive vacuum visits every page that might contain unfrozen XID or multixact state, skipping pages already marked all-frozen. Current PostgreSQL can also eagerly freeze selected all-visible pages during normal vacuum to reduce later aggressive work.

Multixact wraparound

Multiple transactions can hold compatible row locks on one tuple. PostgreSQL may represent them through a multixact ID stored in tuple state. Multixact IDs and member storage also have finite ranges and separate freeze ages. Monitor relminmxid and mxid_age() for workloads with heavy shared row locking or foreign-key checks.

SELECT
  datname,
  age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

SELECT
  c.oid::regclass AS relation,
  greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
  mxid_age(c.relminmxid) AS mxid_age
FROM pg_class AS c
LEFT JOIN pg_class AS t ON t.oid = c.reltoastrelid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 30;

12. What prevents vacuum from keeping up

Slow cleanup is often a horizon, scheduling or capacity problem rather than a broken VACUUM command.

Hot standby queries can conflict with replay of vacuum cleanup records. PostgreSQL must choose between delaying recovery and canceling conflicting standby queries according to standby delay settings. Enabling hot_standby_feedback can reduce query cancellations by telling the primary about standby horizons, but it can increase primary bloat. That is a deliberate availability versus cleanup tradeoff.

13. Monitoring vacuum and diagnosing bloat

Diagnose in layers: workload and transaction horizons, maintenance history, active progress, physical sizes, then deeper structural inspection.

Table statistics

SELECT
  relid::regclass AS relation,
  n_live_tup,
  n_dead_tup,
  n_mod_since_analyze,
  n_ins_since_vacuum,
  last_vacuum,
  last_autovacuum,
  vacuum_count,
  autovacuum_count,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

A growing n_dead_tup, old last_autovacuum, large n_ins_since_vacuum and rising physical size are signals, not standalone verdicts. Compare them with transaction ages, per-table options and write rate.

Active progress

SELECT
  p.pid,
  p.relid::regclass AS relation,
  p.phase,
  p.heap_blks_total,
  p.heap_blks_scanned,
  p.heap_blks_vacuumed,
  p.index_vacuum_count,
  p.num_dead_item_ids,
  p.max_dead_item_ids
FROM pg_stat_progress_vacuum AS p;

Heap scanning, index vacuuming, index cleanup and heap cleanup are distinct phases. A phase that appears stationary may be doing work whose counter is not the one being watched. Join the PID to pg_stat_activity for wait events and query context.

Effective configuration

SELECT name, setting, unit, source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
   OR name LIKE 'vacuum%'
ORDER BY name;

SELECT
  n.nspname,
  c.relname,
  c.reloptions
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.reloptions IS NOT NULL
ORDER BY 1, 2;

Replication slots

SELECT
  slot_name,
  slot_type,
  active,
  xmin,
  catalog_xmin,
  restart_lsn,
  confirmed_flush_lsn,
  wal_status,
  safe_wal_size
FROM pg_replication_slots
ORDER BY active, slot_name;

A safe diagnostic order

1. Confirm the symptom: disk growth, slow scans, replica lag, old XID age or write latency.
2. Measure relation, index, TOAST and WAL sizes separately.
3. Find long transactions, prepared transactions and replication horizons.
4. Check whether autovacuum ran, is running, is waiting, or never became eligible.
5. Inspect effective global and per-table configuration.
6. Compare cleanup rate with update, delete and insert rate over time.
7. Use deeper bloat inspection only when deciding between tuning, reindexing or rewriting.
8. Plan availability, disk, WAL and replica consequences before any rewrite.

14. Vacuum performance and tuning tradeoffs

Vacuum must finish soon enough to control debt without monopolizing the I/O system. The correct target is sustainable completion, not minimum instantaneous resource usage.

Cost-based delay

Vacuum assigns estimated costs to cached-page hits, reads and dirtying. When accumulated cost reaches a limit, a cost-enabled vacuum sleeps briefly. Higher cost limit or lower delay makes work more aggressive. Autovacuum has its own cost settings and can distribute a global cost allowance among active workers.

Memory and repeated index work

Vacuum collects dead tuple identifiers before cleaning indexes. Insufficient maintenance memory can require repeated index vacuum cycles. Raising the relevant maintenance memory can help a large cleanup, but multiply memory assumptions by concurrent maintenance workers and operations. Anti-wraparound failsafe behavior may skip index cleanup and cost delays to prioritize safety.

I/O behavior

• A vacuum reads heap pages, may dirty them, scans indexes and writes WAL for necessary changes.
• A buffer access strategy limits pollution of shared buffers during large scans.
• Parallel vacuum can parallelize eligible index phases, not every heap phase.
• Tail truncation may briefly need ACCESS EXCLUSIVE; disable it per operation if that lock is undesirable.
• Running smaller vacuums earlier is often cheaper than processing a large accumulated backlog.

15. WAL fundamentals and the write-ahead rule

Before PostgreSQL allows a changed data page to reach durable storage, the WAL needed to explain that change must already be durable up to the page's WAL position.

Why WAL exists

Without WAL, committing a transaction safely could require flushing every changed table and index page. These pages are scattered and expensive to synchronize. WAL turns the durability path into mostly sequential append and flush work. Data pages can be written later in batches.

1. A backend changes a copy of a page in shared buffers and creates WAL records.
2. The records receive ordered WAL positions and enter shared WAL buffers.
3. At durable commit, the commit record and preceding required WAL are flushed to stable storage.
4. The client can receive success even though corresponding heap and index pages remain dirty in memory.
5. Background writing and checkpoints eventually flush dirty data pages.
6. After a crash, startup replays WAL records not already reflected in data files.

16. WAL records, LSNs, segments and buffers

WAL is an ordered byte stream split into files. An LSN identifies a byte position in that stream.

Log sequence numbers

The pg_lsn type is internally a 64-bit WAL position and is printed as two hexadecimal numbers such as 16/B374D848. LSNs can be compared, and subtraction gives the number of bytes between positions. They measure WAL generation, archiving, replication receive, flush and replay progress.

SELECT pg_current_wal_lsn() AS before_lsn;

-- Run the operation in a separate controlled step.

SELECT
  pg_current_wal_lsn() AS after_lsn,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), '0/00000000'::pg_lsn)
  ) AS position_from_zero;

-- In practice, save before_lsn and subtract it from after_lsn:
SELECT pg_size_pretty('0/3000000'::pg_lsn - '0/1000000'::pg_lsn);

Segment files

WAL files live under pg_wal. The common segment size is 16 MiB, selected at initdb, and segments contain WAL pages and variable records. Segment filenames also encode a timeline, which distinguishes histories after recovery or promotion.

What a WAL record represents

Record contents depend on the resource manager and operation: heap changes, index changes, commits, checkpoints, relation operations and many other internal actions. A record has checksums and references enough data for its recovery routine. WAL records describe physical changes, but some also carry logical-decoding information when configured.

WAL buffers and writer

wal_buffers is shared memory for WAL not yet written out. Its default automatic sizing is normally reasonable. Backends insert records; a WAL writer and commit-time flush paths move data toward storage. If buffers fill under heavy generation, foreground backends may have to write WAL while holding internal page locks, increasing latency.

17. Commit durability settings

Durability depends on PostgreSQL settings and an I/O stack that truthfully honors flush requests.

Turning off fsync can leave the cluster unrecoverably corrupt after an operating system or power failure. It is suitable only when the whole cluster can be recreated. In contrast, asynchronous commit does not violate database consistency: some recent transactions reported as committed can disappear as though they had aborted.

Group commit

Several concurrent transactions can share one expensive WAL synchronization. This is group commit. It improves throughput because one flush establishes durability for multiple commit records. commit_delay can intentionally wait for more group members, but unnecessary delay increases latency and must be benchmarked.

18. Checkpoints, full-page images and crash recovery

A checkpoint establishes that all data-file changes before its redo point are safely represented on disk, bounding where recovery must begin.

Checkpoint sequence

1. The checkpointer begins writing dirty buffers over a controlled interval.
2. Required WAL was already written ahead of corresponding data pages.
3. At completion, PostgreSQL records checkpoint information and the recovery redo position.
4. Old WAL no longer needed for crash recovery may be recycled, unless archiving, slots, standby needs or other retention rules require it.

Checkpoints start after checkpoint_timeout or when WAL volume approaches max_wal_size, whichever comes first. max_wal_size is a soft limit, not a disk-usage guarantee. Archiver failure or a lagging replication slot can make pg_wal grow far beyond it.

Why full-page images exist

PostgreSQL commonly writes 8 KiB pages while hardware sectors can be smaller. A power failure can leave a torn page containing part old and part new content. With full_page_writes enabled, the first modification of a page after each checkpoint logs a complete page image. Recovery can restore the whole image before applying later records.

More frequent checkpoints mean more "first changes after checkpoint," so they often increase full-page-image WAL. wal_compression can compress such images at a CPU cost. This is why checkpoint tuning affects both dirty-page I/O and WAL volume.

Crash recovery

1. Startup reads control and checkpoint information.
2. It begins redo from the checkpoint's required WAL position.
3. For each record, recovery checks whether the target page already contains that change using page LSN ordering.
4. Missing changes are replayed; full-page images repair pages when required.
5. Uncommitted transactions have no durable commit record and their versions remain invisible, later becoming cleanup candidates.
6. When a consistent end is reached, normal service can resume.

19. WAL archiving, PITR and replication connection

The same ordered WAL stream used for crash recovery can be retained for point-in-time recovery and transported to physical standbys.

Base backup plus WAL equals PITR

A physical base backup provides a starting copy of cluster files. A complete, continuous sequence of archived WAL can replay that copy forward to a target time, transaction, name or LSN. A backup without all required WAL is not a valid PITR chain. Archive commands must be idempotent, must not overwrite a different file with the same name and must return success only after safe storage.

Physical streaming replication

A primary streams WAL to a standby, which writes, flushes and replays it. Different LSNs represent different lag stages:

• Sent: primary has transmitted WAL.
• Written: standby operating system has accepted it.
• Flushed: standby has made it durable.
• Replayed: standby has applied it to data pages and queries can see its effect.

Synchronous replication can make commits wait for selected standby stages. Asynchronous replication lets the primary commit without waiting, so failover can lose the latest transactions that had not reached the promoted standby.

Logical decoding and replication

At wal_level = logical, logical decoding reconstructs row-level change streams from WAL. Logical slots retain required WAL and catalog horizons until consumers confirm progress. A failed logical consumer can therefore cause both WAL growth and vacuum retention. Slot lifecycle belongs in monitoring and disaster-recovery procedures.

SELECT
  application_name,
  state,
  sync_state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn))
    AS replay_byte_lag
FROM pg_stat_replication;

20. Monitoring and tuning WAL

WAL incidents usually appear as commit latency, excessive generation, checkpoint pressure, archive backlog, slot retention, replica lag or disk exhaustion.

WAL generation

SELECT
  wal_records,
  wal_fpi,
  pg_size_pretty(wal_bytes) AS wal_bytes,
  wal_buffers_full,
  stats_reset
FROM pg_stat_wal;

High wal_fpi relative to workload can point toward frequent checkpoints or a workload touching many distinct pages after each checkpoint. Rising wal_buffers_full suggests foreground insertion is filling WAL buffers; verify write rate and latency before changing wal_buffers.

Checkpoint pressure

SELECT *
FROM pg_stat_checkpointer;

SELECT
  backend_type,
  object,
  context,
  writes,
  fsyncs,
  write_time,
  fsync_time
FROM pg_stat_io
WHERE object = 'wal';

Compare requested checkpoints with timed checkpoints, write and sync time, WAL generation and latency. Frequent requested checkpoints commonly indicate that max_wal_size is too small for the workload or that a bulk operation is producing an expected burst.

Archive health

SELECT
  archived_count,
  last_archived_wal,
  last_archived_time,
  failed_count,
  last_failed_wal,
  last_failed_time,
  stats_reset
FROM pg_stat_archiver;

Questions before tuning

• Is generation caused by useful DML, full-page images, rewrites, vacuum or index maintenance?
• Are checkpoints time-driven or WAL-volume-driven?
• Is commit time spent writing, flushing, waiting for synchronous standbys or elsewhere?
• Are archived segments completing at least as fast as they are generated?
• Which slot, standby, backup or summary requirement owns the oldest retained WAL?
• Does available disk cover a peak plus failure-response time rather than only average volume?

21. How MVCC maintenance and WAL amplify each other

Tuple-version debt and WAL pressure often form one feedback loop, so treating them as unrelated incidents misses the root cause.

1. Non-HOT updates write a new heap tuple and new index entries.
2. Those physical changes generate WAL and dirty more pages.
3. Old versions and index entries increase relation size until cleanup.
4. Larger relations make scans, vacuum and cache use more expensive.
5. Cleanup and later reindex or rewrite operations generate their own I/O and WAL.
6. Extra WAL can increase checkpoint frequency, archive work and replica lag.
7. Replica feedback or slots may retain cleanup horizons, feeding bloat again.

22. Maintenance playbooks

Hot update-heavy table is growing

1. Graph heap, index, dead-tuple and write-rate trends.
2. Find old transactions, prepared transactions, slots and standby feedback horizons.
3. Check HOT ratio and which indexed columns change most often.
4. Inspect current autovacuum threshold, completion interval and active worker pressure.
5. Lower per-table scale factor or threshold so vacuum starts earlier.
6. Increase table-specific vacuum capacity only within measured I/O headroom.
7. Consider fillfactor for future same-page updates after validating storage cost.
8. After controlling the cause, decide whether existing excess justifies reindex or rewrite.

One-time bulk delete

1. If removing every row, decide whether TRUNCATE semantics fit before using DELETE.
2. For selective deletion, batch if transaction size, locks, WAL or replica lag require it.
3. Allow plain vacuum to make space reusable and update maps.
4. If future inserts will reuse the space, avoid an unnecessary rewrite.
5. If the table will remain much smaller, plan VACUUM FULL, CLUSTER or another controlled rewrite.
6. Budget temporary disk, strong locks, WAL generation and replica recovery.

XID age alert

1. Measure datfrozenxid, oldest relations, TOAST ages and multixact age.
2. Find and resolve old open or prepared transactions that block horizon progress.
3. Review old replication slot xmin and catalog_xmin.
4. Let anti-wraparound vacuum run; do not repeatedly cancel the safety worker.
5. Give it I/O and worker capacity, then confirm relation ages actually advance.
6. After safety is restored, correct ordinary thresholds and alert lead time.

pg_wal disk is growing

1. Confirm filesystem usage and time to exhaustion.
2. Check archiver failures and archive destination health.
3. Inspect physical and logical slot retention, standby state and restart_lsn.
4. Compare WAL generation rate with normal baseline and recent bulk operations.
5. Do not delete files manually from pg_wal.
6. Repair the owner of retention, or deliberately remove an obsolete slot with rebuild consequences understood.
7. Add capacity only as response time, not as a substitute for fixing unbounded retention.

Checkpoint or commit latency spikes

1. Separate WAL write, WAL sync, checkpoint writeback and synchronous-standby waits.
2. Check requested checkpoint frequency and full-page-image rate.
3. Verify storage latency and truthful flush behavior.
4. Increase max_wal_size or checkpoint interval only with recovery time and disk headroom.
5. Keep checkpoint_completion_target spreading writes across the interval.
6. Use asynchronous commit only for transactions whose loss policy explicitly allows it.

23. Edge cases and common mistakes

Details that often surprise people

• A rolled-back update can still leave physical work for later cleanup.
• A read-only old snapshot can cause write-heavy tables to retain versions.
• Vacuuming a GIN index can also process its pending list.
• Plain vacuum can briefly seek a strong lock only for tail truncation.
• A table can be large and healthy because its internal free space is being reused steadily.
• Freezing is needed for static data even when there are no dead tuples.
• Index-only scans depend on heap visibility-map state, not only covering index columns.
• Temporary tables require session-managed analyze and vacuum behavior because autovacuum cannot access them.
• Unlogged tables reduce normal WAL for their data but are truncated after crash recovery and cannot serve as durable replicated data.
• Logical and physical replication consume the same WAL foundation but expose different semantics.

24. Practice problems

1. Explain a row update physically

Session A updates account 42 while session B holds an older Repeatable Read snapshot. What happens to the heap versions, visibility and cleanup?

2. Calculate a vacuum trigger

A table has an estimated 5,000,000 tuples. Its threshold is 1,000, scale factor is 0.01 and maximum threshold is 100,000,000. Approximately how many updates or deletes trigger eligibility?

3. Choose VACUUM or VACUUM FULL

A queue table deleted 70 percent of rows but will refill to its old size tomorrow. Disk is not currently constrained. Which maintenance is appropriate?

4. Diagnose ineffective vacuum

Autovacuum runs every few minutes but n_dead_tup and heap size keep rising. Name the first checks.

5. Explain commit before data-page flush

How can PostgreSQL safely acknowledge a commit while changed heap pages remain only in memory?

6. Diagnose WAL growth beyond max_wal_size

pg_wal is much larger than max_wal_size. Is PostgreSQL violating its configuration?

7. Reduce update amplification

A table updates last_seen_at constantly, and that column is in an index used only once per month. What should be evaluated?

8. Plan PITR

Why is copying the data directory nightly not enough for recovery to 14:37?

25. Interview Q&A

Why does PostgreSQL UPDATE create a new tuple?

MVCC must let old snapshots see the previous committed state while newer snapshots see the update. A new tuple version preserves both states without overwriting data that a concurrent reader may need.

When is a tuple dead?

A superseded or deleted version is dead when no transaction or retained horizon PostgreSQL must honor can still see it. Before that it can be recently dead but not removable.

What is HOT?

Heap-only tuple update is an optimization used when indexed columns do not need new ordinary index entries and the new version fits on the same page. It creates a page-local version chain, reduces index churn and enables earlier pruning of intermediate versions.

What does fillfactor do?

It leaves a percentage of page space unused during filling so future updates may fit on the same page. Lower fillfactor can improve HOT rates for update-heavy tables but makes the initial table larger.

What is the difference between VACUUM and VACUUM FULL?

Plain vacuum performs online cleanup and makes internal space reusable, usually without shrinking the file. VACUUM FULL rewrites a compact table and rebuilds indexes, returns more disk to the operating system and requires an ACCESS EXCLUSIVE lock and extra disk.

Does VACUUM update planner statistics?

Only when used with ANALYZE. Vacuum maintains cleanup, maps and freezing metadata. Analyze samples data distributions used for query planning.

What triggers autovacuum?

Update/delete changes use a fixed threshold plus a scale factor times estimated table tuples, capped by a current maximum threshold. Inserts have a separate threshold and scale factor based on unfrozen pages. Anti-wraparound age can force vacuum independently. Analyze has its own change threshold and scale factor.

Why can autovacuum run but remove little?

Old transactions, prepared transactions or replication horizons can keep versions visible. Index cleanup may be skipped, locks can interfere, and concurrent writes can create new debt while vacuum runs.

What are the FSM and VM?

The free space map records approximate per-page room for new tuples. The visibility map records conservative all-visible and all-frozen bits per heap page, supporting vacuum skipping, index-only scans and freeze skipping.

Why is transaction ID wraparound dangerous?

Normal 32-bit XIDs are compared in a circular range. Without freezing, a very old inserting XID could eventually appear to be in the future. Vacuum freezes old tuple state, and forced anti-wraparound vacuum prevents reaching the danger point.

What is WAL's write-ahead rule?

WAL describing a data-page change must reach durable storage before that changed page can be durably written. A transaction's commit WAL must be durable before a synchronous durable commit is acknowledged.

What is an LSN?

A log sequence number is a byte position in the WAL stream, represented by pg_lsn. Comparing and subtracting LSNs measures WAL progress and byte distance for generation, recovery and replication.

What does a checkpoint do?

It flushes dirty data pages over time and records a recovery boundary so crash recovery can begin from a known redo point. It also starts a new cycle in which the first change to each page normally emits a full-page image.

Why are full-page images needed?

A crash can interrupt an 8 KiB page write and leave mixed old and new sectors. The first post-checkpoint modification logs a full image so recovery can restore a valid whole page before replaying later records.

What is the difference between fsync off and synchronous_commit off?

Turning off fsync can cause unrecoverable corruption after system failure. Asynchronous commit keeps the database consistent but can lose recent transactions that were acknowledged before WAL reached durable storage.

How does WAL support replication?

Physical standbys receive and replay the primary's WAL to reproduce its data-file changes. Logical decoding reconstructs logical changes from WAL when the server and objects are configured for it.

What causes table and index bloat?

Common causes include high version churn, late vacuum, old cleanup horizons, non-HOT updates, repeated page splits and one-time deletions whose space is not reused. Bloat is a measured workload-relative condition, not simply a large file.

How would you investigate bloat in production?

Measure size and dead-tuple trends, inspect maintenance timestamps and progress, find old transactions and slots, check HOT ratios and effective thresholds, then use a supported structural inspection tool if a reindex or rewrite decision needs stronger evidence.

Why can pg_wal exceed max_wal_size?

It is a soft checkpoint target. Archive failure, replication slots, standby needs, wal_keep_size, summarization and heavy production can retain additional segments.

What is the operational goal for autovacuum?

It should complete cleanup, map maintenance and freezing faster than the workload creates debt, while staying within acceptable I/O and latency budgets. Timely smaller work is usually better than rare emergency cleanup.

26. Concise cheat sheet

• Logical rows are stable ideas; heap tuples are replaceable physical versions.
• xmin creates a version; xmax is not a simple deleted boolean.
• ctid is a physical locator, never a durable application key.
• Old snapshots can prevent cleanup without blocking ordinary writes.
• HOT needs unchanged indexed columns and room on the same page.
• Plain vacuum reuses space; full vacuum rewrites and shrinks.
• FSM finds space; VM proves all-visible or all-frozen page state.
• Autovacuum eligibility depends on thresholds, but workers and locks determine completion.
• Freezing is correctness maintenance, not just space cleanup.
• WAL is durable sequential repair information, not a SQL audit trail.
• Commit can be durable before table pages are written because WAL comes first.
• More checkpoints can mean more full-page images and more WAL.
• max_wal_size is not a hard disk cap.
• Never manually delete files from pg_wal.
• Measure the entire feedback loop: writes, versions, vacuum, WAL, archive and replicas.

27. Official reference map

These PostgreSQL 18 manual sections are the primary sources for the behavior, syntax and current defaults described in this guide.

• Routine vacuuming, space recovery, visibility maps, freezing and autovacuum
• VACUUM syntax, options, locking and command behavior
• ANALYZE syntax and planner statistics
• Autovacuum thresholds, workers, cost delay and freezing settings
• Database page and heap tuple layout
• Free space map
• Visibility map
• Heap-only tuples
• xmin, xmax, cmin, cmax and ctid system columns
• Vacuum and rewrite progress reporting
• Table, WAL, I/O, checkpoint, archive and activity statistics
• Write-ahead logging and redo fundamentals
• Storage reliability, page writes and WAL protection
• Checkpoint, WAL buffer and group commit configuration
• WAL files, segments, records and LSN internals
• WAL, durability, checkpoint, archiving and recovery settings
• Continuous archiving and point-in-time recovery
• Physical streaming replication and standby behavior