Growing PostgreSQL tables need an explanation of row-version retention and vacuum progress before a rewrite is considered. Check whether obsolete rows can be removed, whether autovacuum is keeping up and whether transaction-ID ageing creates an urgent protection issue. A large relation file after vacuum does not automatically mean vacuum failed.1
This article uses PostgreSQL 18. Its examples read statistics and progress only. No vacuum, table rewrite, transaction termination or tuning change was executed.
Understand what space recovery means
PostgreSQL's multiversion concurrency control preserves older row versions while transactions may still need them. Updating or deleting a row therefore does not immediately remove every old version. Standard vacuum removes versions that are no longer needed and makes their space available for reuse.1
That reusable space normally remains inside the relation file. PostgreSQL documents a limited exception for entirely free pages at the end of a table when the required lock can be obtained. VACUUM FULL instead rewrites the relation, requires extra space for the new copy and takes an ACCESS EXCLUSIVE lock.1
The operational objective for a frequently updated table is often stable reusable capacity, not the smallest possible file after every maintenance cycle. A rewrite followed by immediate regrowth can add disruption without solving the ongoing workload balance.
Do not use “bloat” as a substitute for evidence. Physical size, estimated dead rows, reusable pages, index growth and live-data growth are related but different observations. A table can legitimately grow because the application retains more live records.
Start with the relation and workload
Identify the exact database, schema and table, including whether the application operates on partitions. Record its physical-size trend from existing monitoring, update/delete/insert pattern and the time at which performance or capacity became unacceptable.
Compare table and index growth where those measurements are available. A single aggregate database-size line cannot identify which object needs investigation. Also record backup/replication activity and any maintenance overlapping the incident, because the available I/O budget affects how quickly background work can progress.
Use an existing authorised monitoring role. Table statistics provide estimates, not a row-by-row audit. The following untested SELECT uses the documented PostgreSQL table-statistics fields:3
SELECT schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables;
Keep the capture time and statistics-reset context with the result. A missing recent timestamp needs interpretation alongside activity and configuration; it does not alone prove that autovacuum is disabled. Conversely, a recent vacuum timestamp does not establish that every old row version could be removed.
The statistics reference calls n_live_tup and n_dead_tup estimates.3 Do not turn their ratio into an exact number of bytes that a proposed rewrite will release. That requires additional storage analysis and still depends on the workload continuing during the change.
Determine whether vacuum is making progress
PostgreSQL 18 exposes ordinary vacuum activity, including autovacuum workers, through pg_stat_progress_vacuum. Inspect the backend, database, relation and phase, then compare progress over a bounded interval.2
SELECT pid,
datname,
relid,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count
FROM pg_stat_progress_vacuum;
This does not start vacuum. It observes currently running work, so a completed operation may disappear between samples. The scan counters advance in specific phases, and visibility-map skipping affects their interpretation. A heap-scan percentage is not a percentage of total elapsed maintenance time.2
Do not diagnose a stall solely because one counter stops while the phase changes to index work. Read the phase and relevant counters together. VACUUM FULL reports through pg_stat_progress_cluster, because it rewrites the table rather than following ordinary vacuum's in-place process.2
If monitoring is needed over time, use an existing collection system or a bounded script with a defined interval and output location. Avoid repeatedly running expensive relation scans from an interactive incident session.
Find retention horizons before tuning harder
An old transaction or snapshot may still need row versions that vacuum cannot remove. Investigate transaction age and application purpose, including long-running idle transactions, prepared transactions and relevant slot horizons. PostgreSQL's maintenance guidance explicitly identifies these as items to examine when old transaction state prevents progress.1
Use an existing monitoring role with the required catalogue access. Seeing other sessions' full activity requires the visibility provided by pg_read_all_stats (or an already authorised superuser); ordinary users can see null restricted fields. Missing visibility is not a clean result. Run these untested SELECTs as separate statements in autocommit, not inside a long-lived diagnostic transaction, and record database, server identity and time zone. Samples can change between statements; their times do not make them an atomic cross-view snapshot.3 statement_timestamp() records each statement's start; it is not a per-row event time.6
-- Cluster activity: retain database identity when comparing sessions.
SELECT statement_timestamp() AS captured_at,
datname, pid, usename, application_name, state, xact_start,
backend_xid, age(backend_xid) AS xid_age,
backend_xmin, age(backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
OR xact_start IS NOT NULL);
-- Prepared transactions have no ordinary active-session owner to wait for.
SELECT statement_timestamp() AS captured_at,
database, owner, gid, prepared,
"transaction", age("transaction") AS xid_age
FROM pg_prepared_xacts;
-- Slots are cluster-wide; physical slots may have no database value.
SELECT statement_timestamp() AS captured_at,
slot_name, slot_type, database, active,
xmin, age(xmin) AS xmin_age,
catalog_xmin, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;
backend_xid identifies an assigned transaction ID; backend_xmin is a backend's retained snapshot horizon. xact_start supplies elapsed-time context, while age(xid) counts transactions, not seconds. A prepared transaction's "transaction" column is its XID; the catalogue reference uses this exact column name, despite the maintenance prose referring to transactionid.1, 4 Inspect its transaction coordinator and owner rather than looking only for an idle session. Slot xmin and catalog_xmin protect row and catalogue horizons respectively, including for an inactive consumer.5 Large ages identify candidates to correlate with vacuum progress and application ownership; none of these rows alone proves which table's cleanup is blocked. Null horizons are not numeric zero. Avoid frequent prepared-transaction scans: obtaining that view briefly locks internal structures.4
Do not terminate a session merely because it is old. Determine whether it is completing legitimate work, waiting for user/application action or abandoned after an error. Record the owner and consequences of rollback, including any external effects.
Replication slots can also preserve row or catalogue horizons. A slot's retirement is a consumer-continuity decision, as the separate slot-retention article explains. Dropping it to improve a maintenance metric can require rebuilding the downstream consumer.1
The key distinction is between insufficient maintenance capacity and a horizon that prevents cleanup. Increasing workers or making vacuum more aggressive cannot make still-required versions disposable. Fix the transaction/consumer lifecycle when that is the actual obstacle.
Separate transaction-ID ageing from ordinary space pressure
Vacuum also performs freezing work to prevent transaction-ID wraparound problems. PostgreSQL records ageing information in pg_class.relfrozenxid and pg_database.datfrozenxid, with related handling for multitransaction IDs.1
A database approaching a protection threshold needs a DBA-led response even if its filesystem has space. Conversely, a large table with manageable transaction ages is not necessarily in that emergency state. Keep the capacity and ageing diagnoses separate in the incident record.
Capture relation and database XID ages using the PostgreSQL 18 catalogue pattern below. The relation query runs in the affected database and includes its TOAST relation; it includes ordinary tables and materialised views, so partition leaves are inspected rather than assigning an age to a partitioned parent. The database query reports the cluster's database age bounds.1
SELECT statement_timestamp() AS captured_at,
c.oid::regclass AS relation_name,
age(c.relfrozenxid) AS relation_xid_age,
age(t.relfrozenxid) AS toast_xid_age,
greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS oldest_xid_age
FROM pg_class AS c
LEFT JOIN pg_class AS t ON t.oid = c.reltoastrelid
WHERE c.relkind IN ('r', 'm');
SELECT statement_timestamp() AS captured_at,
datname, age(datfrozenxid) AS database_xid_age
FROM pg_database;
These metadata reads do not vacuum or scan table contents. Interpret the ages against actual settings and warnings; they are transaction counts and do not measure dead-row bytes or elapsed time. They cover ordinary XIDs, not a complete multitransaction-ID assessment. Do not transplant a threshold from another release or use an arbitrary internet number as proof of safety. PostgreSQL 18's documented autovacuum thresholds include a maximum threshold term; older simplified formulas are not the full 18 rule.1
Do not disable anti-wraparound protection or repeatedly cancel its work to hide a load complaint. Escalate with exact relation/database ages, logs, transaction horizons and storage reserve so the DBA can choose a supported recovery path.
Choose a targeted maintenance change
If cleanup is possible but autovacuum consistently falls behind a heavily changed table, review its per-table settings, worker capacity and I/O budget. The reference explains thresholds and cost controls; it does not provide a universal configuration that fits every workload.1
If data changes make planner statistics stale, distinguish ANALYZE from vacuum. Statistics gathering helps planning; vacuum's version cleanup and freezing solve different problems, although maintenance can coordinate them.1 A faster query after analyse does not prove physical space was reclaimed.
If substantial physical compaction is genuinely required, prepare a separately approved rewrite plan. Include locking, extra disk space, transaction duration, backup/recovery coverage and application outage. VACUUM FULL is not a routine harmless “clean” command, and no executable rewrite recipe is provided here.
If the application intentionally retains ever more live data, maintenance tuning alone cannot create an unlimited capacity model. Review retention and growth with the business owner. Deleting records is a separate authorised data-management decision, not a diagnostic test.
Verify steady-state behaviour
Define acceptance around representative workload: obsolete versions become reusable, transaction ages remain within the agreed operational margin, autovacuum completes useful work and application latency remains acceptable. Keep filesystem capacity and relation allocation trends beside those measures.
Test a proposed setting or application-lifetime change in an authorised environment with realistic update volume and concurrency. No such test was run here. Preserve previous settings and define a return condition if maintenance I/O harms the service.
Returning settings does not reverse a completed table rewrite or restore deleted business data. Keep the recovery plan appropriate to the actual change, not just the ease of editing a parameter.
After correction, a stable physical file with reusable pages may be the desired result. Report that evidence directly instead of promising a smaller file. If growth continues, distinguish new live data from newly retained obsolete versions before broadening the intervention.
Learning notes
- Standard vacuum primarily enables reuse; physical shrinking is a different operation.
- Old transaction and consumer horizons can prevent cleanup despite active vacuum.
- Transaction-ID protection and storage-capacity pressure require separate evidence.
Acceptance checklist
- [ ] Relation, TOAST and database XID ages are recorded separately from physical capacity.
- [ ] Session, prepared-transaction and slot horizons were inspected with sufficient visibility.
- [ ] Vacuum phase/progress and workload evidence distinguish capacity limits from retained horizons.
- [ ] Approved validation demonstrates useful cleanup, safe transaction ages and acceptable application latency.
References
- PostgreSQL: Documentation: 18: 24.1. Routine Vacuuming. Accessed 2026-11-01.
- PostgreSQL: Documentation: 18: 27.4. Progress Reporting. Accessed 2026-11-01.
-
PostgreSQL: Documentation: 18: 27.2. The Cumulative Statistics System. Accessed 2026-11-01.
-
PostgreSQL: Documentation: 18: 53.17. pg_prepared_xacts. Accessed 2026-11-02.
-
PostgreSQL: Documentation: 18: 53.20. pg_replication_slots. Accessed 2026-11-01.
-
PostgreSQL 18: Date/Time Functions and Operators. Accessed 2026-11-02.