robovacreference/explain/table-rewrite
table rewrite
Vacuum makes space reusable inside the file. It almost never gives the file back, so a table that once bloated stays big until something rewrites it. A rewrite copies the live rows into a new file, rebuilds every index, and swaps the two. Four tools do that same work, and all four want free disk for a full second copy of the table plus the WAL burst that writing it produces. What separates them is the lock: how long the table is unavailable, and what you have to install or restart to shorten that window.
| TOOL | LOCK | NEEDS | MAIN RISK |
|---|---|---|---|
| VACUUM FULL | ACCESS EXCLUSIVE, the whole run | Nothing, it is core SQL | Blocks reads and writes until it finishes |
| pg_repack | Brief ACCESS EXCLUSIVE at the start and at the swap | The extension and its client CLI, a primary key or unique index | Blocks DDL for the whole run, a killed run leaves a repack schema |
| pg_squeeze | Brief lock at the swap only | The extension in shared_preload_libraries (restart), wal_level = logical, a replication slot, a replica identity | Copies rows as stored, so the space of dropped columns stays |
| pg-osc | Brief ACCESS EXCLUSIVE at the swap | No server extension, a client CLI and a primary key | Runs outside the database, an interrupted run needs manual cleanup |
No tool wins on merit here, the constraint picks it. With a maintenance window: VACUUM FULL. With wal_level = logical and a restart: pg_squeeze. Without either: pg_repack. With no extensions at all: pg-osc.
Plan the disk before you need it: a cluster at 95% from bloat can no longer run the tool that would fix it. All four rewrite the file, so table storage parameters can be lost: re-apply reloptions afterwards. For indexes alone, REINDEX CONCURRENTLY (Postgres 12+) is cheaper than any of them.