Create a Database Index Without Downtime
Build an index concurrently, detect invalid builds, watch replication lag, and avoid locking writes on a large production table.
Objective
Add an index that improves query latency without blocking writes or overloading replicas.
Environment
- PostgreSQL 12+ or a database with an online index option
- A production-like staging table with representative size
- Monitoring for locks, replication lag, and query latency
- A maintenance window or a canary period for verification
Steps
1. Measure the query and baseline
Capture the query plan, latency, and rows scanned for the target query before adding the index.
Expected state: The baseline shows a specific bottleneck the index should improve.
2. Build concurrently on staging
Run CREATE INDEX CONCURRENTLY on a production-sized copy and measure duration, lock waits, and replica impact.
Expected state: The build completes without blocking writes and the duration fits the operational window.
3. Build in production
Run CREATE INDEX CONCURRENTLY outside peak traffic, with monitoring for invalid indexes and replication lag.
Expected state: The index is valid, writes continue, and replicas remain within the lag budget.
4. Verify the plan and latency
Run EXPLAIN (ANALYZE, BUFFERS) and compare latency with the baseline; confirm the planner uses the index.
Expected state: The target query improves and no unrelated query regresses.
5. Clean up or retry
If the concurrent build fails, drop the invalid index and retry in a smaller window rather than running a blocking build.
Expected state: No invalid index remains and the table has no leftover lock.
Decision points
Should the index be created during business hours?
- Outside peak traffic with monitoring
- During peak to measure impact
Start outside peak. Measure the effect on staging before testing production peak.
What if the concurrent build fails?
- Drop the invalid index and retry
- Leave it and try REINDEX
Drop and retry with a smaller window; an invalid index still consumes space and write overhead.
Expected outcome
The index is online, valid, used by the planner, and improved the target query without blocking writes.
Verification
Check pg_index.indisvalid, compare query plans and p95 latency, and confirm replication lag stayed within budget. Drop the index if it does not improve the measured query.
Cleanup
Remove unused indexes, archive the baseline and build timings, and update the query runbook.
Prove the index is needed
An index costs write throughput and storage. Start with a specific query, a measured plan, and a baseline latency. If the query scans few rows or is already fast, the index adds cost without value. Create it on a staging copy first to estimate duration and lock behavior.
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);CONCURRENTLY avoids blocking writes but takes longer and cannot run inside a transaction. If it fails, it may leave an invalid index; check pg_index.indisvalid and drop it before retrying.
Watch replicas and locks
The build reads the table and generates WAL, which can delay replicas. Monitor replication lag and query latency during the build. Use a lock timeout to fail fast if the DDL cannot acquire its brief lock, rather than waiting behind a long transaction.
Verify and decide
Run EXPLAIN (ANALYZE, BUFFERS) before and after. Confirm the planner uses the index and the target query improves. Check that write latency did not regress. If the index is unused or the improvement is negligible, drop it; an unused index is pure overhead.