How do you safely build or rebuild PostgreSQL indexes online?

1. Question

An orders table keeps receiving writes while a query team requests a composite index. Another table has an index that needs rebuilding because of bloat. The tables are large, the window is off-peak, and ordinary reads and writes cannot be blocked. Give an observable, reversible online plan.

2. Constraints and clarifications

• Confirm the PostgreSQL version, table and index types, replication topology, disk headroom, and write peak.
• Distinguish CREATE INDEX, CREATE INDEX CONCURRENTLY, and REINDEX CONCURRENTLY.
• Concurrent builds reduce write-lock impact but scan more than once, consume CPU/IO, and cannot run inside a transaction block.
• Define acceptable build time, lock wait, latency, and cleanup window before implementation.

3. Core idea

A regular index build can block writes. A concurrent build allows inserts, updates, and deletes to continue but takes longer and consumes more resources. First estimate cost on a shadow environment at realistic scale; in production, set locktimeout, bound statementtimeout, and monitor resources. After creation, verify planner choice, rollback paths, and replica consistency; a successful DDL command is not a successful business rollout.

4. Reference flow

preflight:
  verify_version_replicas_disk_and_query_shape()
  estimate_scan_cost_on_shadow_copy()
  reserve_maintenance_window_and_abort_thresholds()

build:
  set lock_timeout = short
  set statement_timeout = bounded
  CREATE INDEX CONCURRENTLY idx_orders_customer_time
    ON orders (customer_id, created_at DESC)

verify:
  inspect_index_state_and_size()
  EXPLAIN (ANALYZE, BUFFERS) representative_queries()
  compare_write_latency_replica_lag_and_error_rate()

Prefer REINDEX CONCURRENTLY for a rebuild. If a failure leaves an invalid temporary index, identify and clean it according to the documentation before retrying. Deployment scripts should make naming, idempotency, and alerts traceable; do not hide concurrent DDL inside an ordinary transactional migration.

5. Failure cases and trade-offs

A concurrent build can fail because of long transactions, conflicting snapshots, or insufficient disk. A failed object may remain invalid and continue consuming space. CPU, IO, and WAL growth during the build can slow the service and increase replica lag, so throttle or pause it. If the cost is unacceptable, optimize the query, partition the table, or use an online migration tool, but still verify its triggers, backfill, cutover, and rollback behavior.

6. Verification and observability

• Record index state, size, build duration, lock waits, WAL, CPU/IO, and replica lag.
• Compare plans, rows scanned, p95/p99 latency, and write throughput for representative queries.
• Check long transactions, invalid indexes, duplicate indexes, and constraint dependencies.
• Wait through several complete traffic peaks before dropping an old index, and keep a recovery script.

7. Common mistakes

• Assuming CONCURRENTLY takes no locks and ignoring brief lock waits and resource contention.
• Putting CREATE INDEX CONCURRENTLY inside a transaction block, which makes it fail immediately.
• Checking only the DDL return code instead of invalid indexes, replica lag, and the actual query plan.
• Rebuilding a huge table without a disk, WAL, and long-transaction budget.

8. Interview scoring points

Distinguishes concurrent DDL semantics

The candidate explains locks, scan passes, transaction restrictions, and resource costs for regular and concurrent create or rebuild operations.

Plans production preflight checks

The candidate checks version, disk, long transactions, replication topology, and query shape, then estimates with realistic data volume.

Designs failure recovery

The candidate handles invalid indexes, timeouts, disk exhaustion, and replica lag, with cleanup, retry, and rollback steps.

Verifies with business metrics

The candidate compares plans, p95/p99, write latency, WAL, lock waits, and replica lag instead of trusting only a DDL return code.