Zero-Downtime Index Maintenance: Mastering REINDEX CONCURRENTLY and pg_repack
- 2 days ago
- 3 min read
In high-concurrency, high-throughput PostgreSQL databases, B-Tree indexes naturally experience physical page splitting due to continuous INSERT, UPDATE, and DELETE operations. Over time, these index structures accumulate dead space and structural fragmentation—a state known as Index Bloat.
Bloated indexes degrade database performance in two major ways:
They waste precious RAM inside shared_buffers, reducing memory available for table caching.
They force the query executor to read significantly more 8KB index pages from disk to satisfy index scans.
Executing a standard REINDEX command rebuilds index files efficiently, but it acquires an AccessExclusiveLock on the parent table, completely blocking incoming read and write transactions. For mission-critical 24/7 systems, this level of locking is unusable.
This guide provides an operational deep dive into two zero-downtime solutions for index maintenance: PostgreSQL's native REINDEX CONCURRENTLY and the open-source extension pg_repack.
1. Physical Mechanics: Why B-Tree Indexes Suffer Bloat
To manage index lifecycle effectively, DBAs must understand how B-Tree page splits create permanent structural bloat.
+-----------------------------------------------------------------------+
| B-TREE INDEX PAGE SPLITTING |
| |
| [ Page 1 (8KB - 100% Full) ] ---> Insert new key causes Page Split! |
| |
| [ Page 1 (50% Full) ] [ Page 2 (50% Full) ] |
| |
| * If subsequent DELETE operations remove adjacent keys, Page 1 & 2 |
| retain empty slots that are rarely reclaimed without REINDEXing. |
+-----------------------------------------------------------------------+
When a new key is inserted into a fully packed 8KB index leaf page, PostgreSQL splits the page into two separate pages, distributing existing keys roughly 50/50. If later business transactions delete those surrounding keys, the B-Tree leaf pages remain sparse and underutilized, causing the overall index file size to expand indefinitely.
2. Solution 1: Native REINDEX CONCURRENTLY
Introduced in PostgreSQL 12, REINDEX CONCURRENTLY allows DBAs to rebuild bloated indexes online without blocking concurrent application queries.
-- Rebuilding a specific bloated index online
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
-- Rebuilding all indexes on a table online
REINDEX TABLE CONCURRENTLY public.orders;
Under the Hood: The 6-Stage Execution Lifecycle
Unlike standard REINDEX, which locks the table and rebuilds the index in a single pass, REINDEX CONCURRENTLY executes through six distinct phases:
Plaintext
+-----------------------------------------------------------------------+
| REINDEX CONCURRENTLY EXECUTION STAGES |
| |
| Stage 1: Register temporary target index in system catalog (ccnew) |
| Stage 2: Pass 1 Heap Scan - Build initial B-Tree structure |
| Stage 3: Pass 2 Heap Scan - Catch up with concurrent DML writes |
| Stage 4: Mark new index VALID; mark old index INVALID (ccold) |
| Stage 5: Wait for active transactions to clear old index references |
| Stage 6: Drop the old bloated index from physical storage |
+-----------------------------------------------------------------------+
Handling Invalid Indexes After Failures
If a network partition or system cancellation interrupts REINDEX CONCURRENTLY, an invalid index remains in the system catalog (named idx_name_ccnew).
Crucial Rule: Invalid indexes do not serve read queries, but PostgreSQL continues updating them on every write, causing write amplification!
How to Clean Up Invalid Indexes
-- 1. Identify invalid indexes
SELECT relname
FROM pg_class
WHERE relispartition = false
AND relkind = 'i'
AND relfilenode = 0;
-- 2. Drop the invalid index concurrently
DROP INDEX CONCURRENTLY idx_orders_customer_id_ccnew;
3. Solution 2: Online Index Maintenance with pg_repack
While REINDEX CONCURRENTLY is built into PostgreSQL, pg_repack remains a powerful alternative when you want to rebuild indexes alongside online table compaction.
# Rebuild all indexes on table 'orders' online using 4 parallel workers
pg_repack -h localhost -U postgres -d app_db --table orders --only-indexes --jobs 4
Architectural Comparison: REINDEX CONCURRENTLY vs pg_repack
Feature / Dimension | REINDEX CONCURRENTLY | pg_repack --only-indexes |
Prerequisites | Native (No extension required) | Requires pg_repack C-extension |
Lock Level | ShareUpdateExclusiveLock | RowExclusiveLock |
Parallel Index Building | Single-threaded per index | Multi-threaded (--jobs N flag) |
Interrupted Failure State | Leaves _ccnew invalid index | Drops temporary objects cleanly via triggers |
Storage Requirement | 1x size of target index | 1x size of target index |
4. Production Checklist for Zero-Downtime Index Maintenance
Follow these guidelines when scheduling online index rebuilds in high-traffic environments:
Verify Storage Headroom: Ensure the host disk has free capacity equal to at least 100% of the largest index being rebuilt.
Increase statement_timeout: Long-running concurrent index builds can time out if restrictive session settings exist. Temporarily raise timeouts:
SET statement_timeout = '8h';Monitor Locks and Blocking: Monitor pg_stat_activity during Stage 4 and Stage 5 when PostgreSQL acquires brief metadata locks to swap catalog pointers.
Tune max_parallel_maintenance_workers: Speed up index scans on large tables by allocating multiple CPU cores:
SET max_parallel_maintenance_workers = 4;Summary
Use REINDEX CONCURRENTLY as your standard zero-downtime tool for routinely defragmenting B-Tree, GIN, or GiST indexes.
Use pg_repack --only-indexes when you need multi-threaded parallel index creation (--jobs) or want to rebuild indexes alongside full online table compaction.
Comments