VACUUM FULL, CLUSTER, REINDEX, Concurrent Maintenance, and Maintenance Windows

Learning outcomes

Routine autovacuum should handle routine maintenance. Sometimes evidence points to a heavier operation: the heap must physically shrink, rows should be reordered for locality, or an index must be rebuilt because of corruption or severe bloat. VACUUM FULL, CLUSTER, and REINDEX solve different problems and have different lock, disk, write-amplification, and recovery implications. This lesson turns “maintenance window” into a specific engineering decision.

1. Start with the diagnosed problem

2. Create a disposable maintenance target

DROP TABLE IF EXISTS app.ch09_maintenance_lab;CREATE TABLE app.ch09_maintenance_lab (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    customer_id integer NOT NULL,    created_at timestamptz NOT NULL,    payload text NOT NULL);INSERT INTO app.ch09_maintenance_lab(customer_id, created_at, payload)SELECT (g % 1000) + 1,       timestamptz '2026-01-01 00:00:00+00' + (g || ' seconds')::interval,       repeat(md5(g::text), 6)FROM generate_series(1, 50000) AS g;CREATE INDEX ch09_maintenance_created_idx    ON app.ch09_maintenance_lab(created_at);ANALYZE app.ch09_maintenance_lab;DELETE FROM app.ch09_maintenance_lab WHERE id % 3 = 0;VACUUM app.ch09_maintenance_lab;SELECT pg_size_pretty(pg_relation_size('app.ch09_maintenance_lab')) AS heap,       pg_size_pretty(pg_indexes_size('app.ch09_maintenance_lab')) AS indexes;

The lab creates churn but does not assert a specific bloat ratio. Whether a rewrite materially shrinks this small test relation depends on page allocation and tail layout.

SELECT a.pid, a.usename, a.state, a.xact_start, a.wait_event_type, a.wait_event, a.queryFROM pg_stat_activity AS aWHERE a.datname = current_database()  AND a.pid <> pg_backend_pid()ORDER BY a.xact_start NULLS LAST;SELECT pg_total_relation_size('app.ch09_maintenance_lab') AS total_bytes,       pg_relation_size('app.ch09_maintenance_lab') AS heap_bytes,       pg_indexes_size('app.ch09_maintenance_lab') AS index_bytes;

Relation size is only one part of disk planning: a rewrite or concurrent rebuild can need additional temporary/new relation storage while old structures still exist. Check the actual tablespace/filesystem headroom externally before a production operation.

3. VACUUM FULL: compact heap, maximum blocking

VACUUM FULL rewrites the table into a new file and requires ACCESS EXCLUSIVE, blocking ordinary table access for the duration. It also needs temporary disk headroom for the rewrite. It cannot be executed inside an explicit transaction block.

SELECT pg_size_pretty(pg_relation_size('app.ch09_maintenance_lab')) AS before_heap;VACUUM (FULL, ANALYZE, VERBOSE) app.ch09_maintenance_lab;SELECT pg_size_pretty(pg_relation_size('app.ch09_maintenance_lab')) AS after_heap;

On a production table, capture the lock window, replication/WAL implications, storage headroom, application downtime strategy, and post-rewrite ANALYZE requirements before execution.

4. CLUSTER: rewrite for physical locality

CLUSTER rewrites the table according to an index. That can improve locality for workloads that scan data in the indexed order, but subsequent writes gradually erode the ordering. Like VACUUM FULL, CLUSTER takes an ACCESS EXCLUSIVE lock on the table while clustering.

CLUSTER (VERBOSE) app.ch09_maintenance_labUSING ch09_maintenance_created_idx;ANALYZE app.ch09_maintenance_lab;SELECT indexrelid::regclass AS index_name, indisclusteredFROM pg_indexWHERE indrelid = 'app.ch09_maintenance_lab'::regclass;

indisclustered records the selected clustering index; it does not guarantee that every future row remains perfectly ordered.

5. REINDEX and concurrent rebuilds

REINDEX rebuilds index structures. It is the right tool for an index problem; it does not compact heap pages. The concurrent form uses a multi-phase process to reduce disruption to normal writes, but it takes longer, performs additional work, has restrictions, and cannot run inside a transaction block.

REINDEX INDEX app.ch09_maintenance_created_idx;

REINDEX INDEX CONCURRENTLY app.ch09_maintenance_created_idx;

6. Use the correct progress view

SELECT pid, datname, relid::regclass, phase,       heap_blks_total, heap_blks_scanned, heap_blks_vacuumedFROM pg_stat_progress_vacuum;

SELECT pid, datname, relid::regclass, command, phase,       heap_tuples_scanned, heap_tuples_writtenFROM pg_stat_progress_cluster;

SELECT pid, datname, relid::regclass, index_relid::regclass,       command, phase, lockers_total, lockers_done,       blocks_total, blocks_done, tuples_total, tuples_doneFROM pg_stat_progress_create_index;

Do not look for VACUUM FULL in pg_stat_progress_vacuum; rewrite-style progress is reported through pg_stat_progress_cluster. Likewise, concurrent reindex phases are index-build progress, not vacuum progress.

7. Maintenance-window acceptance checklist

Before heavy maintenance, document: the diagnosed symptom and baseline evidence; expected lock mode/window; disk headroom on the relevant tablespace; WAL/replication impact and replica lag tolerance; backup/recovery status; long transactions; application drain/read-only strategy if needed; progress queries; cancellation behavior; post-operation ANALYZE/validation; and objective acceptance checks such as size, query latency, index validity, and business counts.

SELECT count(*) AS visible_rows,       min(created_at), max(created_at)FROM app.ch09_maintenance_lab;SELECT indexrelid::regclass AS index_name,       indisvalid, indisready, indisclusteredFROM pg_indexWHERE indrelid = 'app.ch09_maintenance_lab'::regclassORDER BY index_name::text;SELECT pg_size_pretty(pg_relation_size('app.ch09_maintenance_lab')) AS heap,       pg_size_pretty(pg_indexes_size('app.ch09_maintenance_lab')) AS indexes;

8. Production judgment: define the maintenance window as an SLO decision

Heavy maintenance trades availability and resource headroom for a physical improvement. The acceptance decision therefore belongs with service-level objectives (SLOs): how much blocking or latency degradation is allowed, how much replica lag is tolerable, how much temporary disk can be consumed, and what recovery point/backup validation is required before starting. For very large tables, rehearse the command and progress/abort procedure on representative non-production data rather than estimating duration from row count alone.

If continuous availability is required and a core operation cannot meet the lock window, consider architecture or migration alternatives rather than forcing the command into production. Third-party online repack tools can be evaluated separately, but they are not mandatory for this course and introduce their own prerequisites, locks, triggers, disk, and failure modes.

9. Wrong approach and cleanup

The wrong workflow is “database is slow → run VACUUM FULL, CLUSTER, and REINDEX everything.” Each operation solves a different physical problem and can itself create an incident if locks, disk, replication, or recovery are ignored. Diagnose first, select one mechanism, validate, and record the result.

DROP TABLE IF EXISTS app.ch09_maintenance_lab;

Authoritative references

These mechanisms are version-sensitive. Use the documentation for the PostgreSQL major you actually operate.

• PostgreSQL 18 — VACUUM
• PostgreSQL 18 — CLUSTER
• PostgreSQL 18 — REINDEX
• PostgreSQL 18 — Progress Reporting