阅读中文版本

Database Reindex Maintenance Workflow

Assess index bloat, build replacements concurrently, swap or drop old indexes with minimal locking, and monitor the effect on query latency.

Overview

Rebuild bloated indexes or add missing ones without taking the database down. Confirm the new index size is smaller, plans use it, and both read latency and write throughput were within normal ranges during and after the change.

Steps

1. Measure index bloat and identify candidates using statistics. Expected state: Only genuinely bloated indexes are targeted.
2. Build the replacement concurrently. Expected state: Builds do not block writes.
3. Swap or drop the old index in a controlled step. Expected state: The change is deliberate and observable.
4. Monitor query latency and write cost after the change. Expected state: The effect is verified, not assumed.

CREATE INDEX CONCURRENTLY orders_created_idx_new ON orders (created_at);
-- then swap
DROP INDEX CONCURRENTLY orders_created_idx;
ALTER INDEX orders_created_idx_new RENAME TO orders_created_idx;

Verification

• Bloated indexes are rebuilt with a lower size, query latency is unchanged or better, and writes were not blocked.

Decision points

• Rebuild in place or build a new one? Options: Build new, then swap; Rebuild in place. Recommended: Build new concurrently and swap; in-place rebuild can hold locks or long transactions.
• Do it during peak or off-peak? Options: Off-peak; Any time. Recommended: Concurrent builds still add IO; prefer off-peak for large tables.