Online VACUUM, REINDEX, and Table Rewrites

The one thing to understand first

Over time tables and indexes accumulate bloat — dead tuples and empty space left by MVCC. There are two fundamentally different ways to deal with it: reclaiming space for reuse within the object (online, cheap) versus physically shrinking the object and returning space to the OS (expensive, traditionally blocking). Choosing the wrong one causes outages.

*Routine VACUUM reclaims space for reuse without shrinking the file; returning space to the OS is a separate, far more expensive operation — and there is an online tool for every job, so you should never need the blocking one.* Match the tool to the goal and bloat cleanup is invisible to users.

Plain VACUUM: online, but doesn't shrink files

Regular VACUUM (and autovacuum) marks dead tuple space reusable and updates the free space and visibility maps. It runs with a weak lock that allows concurrent reads and writes. What it does not do is return space to the operating system — the file stays the same size, with free space inside it for future rows. For steady-state churn this is exactly what you want, and it should be handled automatically by a well-tuned autovacuum.

VACUUM FULL: shrinks, but locks everything

VACUUM FULL rewrites the entire table into a new compact file and swaps it in, returning space to the OS. The catch: it holds an ACCESS EXCLUSIVE lock for the whole rewrite — no reads, no writes — and needs disk space for a full second copy. On a large production table this is an outage. Reserve it for cases where you must reclaim disk and can afford a maintenance window.

REINDEX CONCURRENTLY: online index rebuild

Index bloat is reclaimed by rebuilding the index. The online form builds a fresh copy alongside the old one under a weak lock and swaps them:

REINDEX INDEX CONCURRENTLY idx_orders_customer;
REINDEX TABLE CONCURRENTLY orders;   -- every index on the table

This is the standard, safe remedy for index bloat. Like CIC, a failure can leave an INVALID index to drop, and it cannot run inside a transaction block.

pg_repack: online table compaction

To compact a table (not just its indexes) without the lock of VACUUM FULL, the pg_repack extension is the standard tool. It creates a shadow copy of the table, uses triggers to capture changes during the copy, builds the indexes, and swaps the new table in — holding the strong lock only for the brief final swap. The result is a fully compacted table with seconds of locking instead of minutes or hours:

-- Compact a bloated table online
pg_repack -d app --table orders
-- Reads and writes continue throughout; only a momentary lock at swap.

Layer 3 — Watch it happen on your own database

Do not rebuild blindly — measure. The pgstattuple extension reports actual dead space:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');          -- table bloat: dead_tuple_percent
SELECT * FROM pgstatindex('idx_orders_pk');   -- index bloat: leaf_fragmentation

-- Cross-check the file size before/after a rebuild to prove space returned:
SELECT pg_size_pretty(pg_total_relation_size('orders'));

The number that decides everything is dead_tuple_percent (tables) or leaf_fragmentation (indexes). A high value justifies a rebuild; a low one means autovacuum is already keeping up and a VACUUM FULL would be a pointless outage. Re-check pg_total_relation_size after a pg_repack/REINDEX CONCURRENTLY to confirm space actually returned to the OS.

Layer 4 — The levers this hands you

There is an online tool for every cleanup goal — match the tool to the job:

• Prevention beats cure. Tune autovacuum per-table on high-churn tables so bloat never accumulates in the first place.
• Eliminate long transactions that hold back the vacuum horizon and let dead tuples pile up.
• **Use appropriate fillfactor** to enable HOT updates and cut index bloat at the source.
• **Avoid VACUUM FULL in production** — its ACCESS EXCLUSIVE lock and need for a full second copy make it an outage; reach for pg_repack instead.

Layer 5 — What an Oracle DBA should expect vs what they get

Bloat is the cost of PostgreSQL's MVCC model, and the tooling differs sharply from Oracle:

• Bloat itself is more visible. Oracle's in-place updates + UNDO mean tables don't accumulate dead row versions the way Postgres heaps do — managing bloat is a uniquely Postgres discipline with no exact Oracle counterpart.
• **VACUUM FULL ≈ ALTER TABLE ... MOVE / online redefinition,** but the plain form is fully blocking. The online equivalent of Oracle's segment shrink (ALTER TABLE ... SHRINK SPACE) is pg_repack — an external extension, not built in.
• **REINDEX CONCURRENTLY ≈ ALTER INDEX ... REBUILD ONLINE,** with the same INVALID-on-failure caveat as CIC.
• **Routine VACUUM has no real Oracle analogue** — there is no background process Oracle DBAs tune to reclaim dead-tuple space, because UNDO handles old versions differently. Autovacuum tuning is the new core skill.

Key takeaway

Routine VACUUM/autovacuum reclaims dead space online for reuse but does not shrink files; to return space to the OS use REINDEX CONCURRENTLY for indexes and pg_repack for tables — both online — and reserve the blocking VACUUM FULL for a maintenance window. Always measure first with pgstattuple, and prevent bloat at the source by tuning autovacuum, killing long transactions, and using fillfactor for HOT updates.