Index Maintenance, Bloat, and REINDEX in PostgreSQL

Indexes and tables in PostgreSQL are not static. Because of how Postgres handles updates and deletes, both accumulate dead space over time, called bloat, that slows queries and wastes disk. An index that was fast when you built it can drift into being slow and oversized without anyone touching it. This article covers where that bloat comes from and the maintenance that keeps your storage lean without taking the database down.

This is part 16 and the last of the indexing module in the PostgreSQL Masterclass, following finding slow queries.

Where bloat comes from: MVCC

Postgres uses multi-version concurrency control, which we'll cover in depth in the transactions module. The short version matters here: when you update a row, Postgres doesn't overwrite it. It writes a new version and marks the old one dead. When you delete a row, it marks it dead rather than reclaiming the space immediately. Those dead versions stay in the table and its indexes until VACUUM cleans them.

The upshot is that a table with heavy update and delete traffic constantly generates dead rows. Until they're vacuumed, they take space and slow scans, because a query still has to read past them. This dead-but-not-yet-reclaimed space is bloat.

VACUUM reclaims, autovacuum automates

VACUUM marks dead row space reusable, so new rows can fill it instead of growing the file. It doesn't usually shrink the file on disk; it makes the space available internally, which is what you want for steady-state operation.

You rarely run it by hand, because autovacuum does it automatically. Autovacuum wakes up when a table has accumulated enough changes and vacuums it in the background. For most tables the defaults are fine. For very write-heavy tables, the defaults can fall behind, and you tune autovacuum to run more aggressively on those specific tables. We'll cover that tuning in the operations module; for now, the thing to know is that healthy autovacuum is what keeps bloat in check, and a table where autovacuum has stalled is a table heading for trouble.

Measuring bloat

Before fixing bloat, confirm it exists. The pgstattuple extension measures it directly:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- table bloat
SELECT * FROM pgstattuple('orders');
-- index bloat
SELECT * FROM pgstatindex('orders_customer_id_idx');

The dead_tuple_percent for a table and the avg_leaf_density for an index tell you how much space is wasted. A leaf density well below 90% on a busy index, or a table that's grown far larger than its live data justifies, is a sign maintenance has fallen behind. Don't reindex or rebuild on a hunch; measure first.

REINDEX, without the downtime

VACUUM reclaims space for reuse but doesn't compact an index that's already bloated. For that you rebuild it with REINDEX. The naive form takes an exclusive lock that blocks writes, which you can't afford on a production table. The concurrent form avoids that:

-- rebuilds without blocking reads or writes
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;

REINDEX ... CONCURRENTLY builds a fresh copy of the index alongside the old one, then swaps them, so the table stays writable throughout. It's slower and uses more disk during the rebuild, which is the price of not locking anyone out. For a bloated index on a live system, it's the right tool. The same CONCURRENTLY option exists on CREATE INDEX and DROP INDEX, and you should default to it for any index operation on a production table.

When bloat is in the table itself

If the table (not just its indexes) is badly bloated, VACUUM won't return the space to the operating system. VACUUM FULL will, but it rewrites the whole table under an exclusive lock, so it's a downtime operation. The online alternative is pg_repack, an extension that rebuilds a table and its indexes with only a brief lock, reclaiming the disk without a maintenance window. On a large production table, pg_repack is what you reach for instead of VACUUM FULL.

A maintenance routine

You don't need constant hand-holding, just a few habits:

• Leave autovacuum on and monitor that it's keeping up. pg_stat_user_tables shows n_dead_tup and last_autovacuum per table; a growing dead-tuple count with an old autovacuum timestamp is the warning sign.
• Tune autovacuum to be more aggressive on your hottest write tables rather than globally.
• Measure bloat with pgstattuple before rebuilding, so you rebuild what's actually bloated.
• Use REINDEX CONCURRENTLY for bloated indexes and pg_repack for bloated tables, both online.
• Drop the zero-scan indexes you found in the previous article, since every unused index is dead space that still has to be maintained.

Bloat is one of those problems that's invisible until it isn't, when a query that used to take 10ms creeps to 200ms and disk usage balloons. Keeping autovacuum healthy handles the common case automatically, and knowing the online rebuild tools means the uncommon case doesn't require downtime.

That completes the indexing and performance module: you can now design indexes, read plans, find the slow queries across a whole workload, and keep it all from degrading over time. Next, we move into the transactions and concurrency module, starting with what ACID actually guarantees.