Self healing Postgres index maintenance

Context

A production Postgres fleet under a heavy, safety critical Rails workload. Index health lived as tribal knowledge: nobody could say which indexes were bloated, unused, or structurally invalid until one of them took a hot query path down with it.

Problem

Index issues were discovered reactively, during incidents. Remediation meant a human running REINDEX by hand, which is its own production risk: done carelessly it locks writes, stacks concurrent maintenance on one hot table, or times out halfway and leaves an invalid index behind.

The decision

Build a closed loop instead of a runbook. A scheduled detector reads the Postgres stats and catalog tables, scores every index (usage, size, churn, vacuum pressure, structural validity) and rolls each up into a category (INVALID, UNUSED, HIGH_BLOAT, INEFFICIENT, OVERSIZED) with an estimated reclaimable size and a recommended action, published to Slack and Datadog. The riskiest categories feed a worker that repairs them automatically with REINDEX INDEX CONCURRENTLY.

REJECTED: a DBA runbook and scheduled maintenance windows. It keeps the human in the loop but also keeps the human as the loop; index health decays faster than anyone rereads a runbook.

Execution

The hard part wasn’t the reindex. It was making automated maintenance boring. The worker validates each index against the catalog and quotes identifiers from it (never from input), takes a per table lock so it can’t thrash one hot table, and respects a global semaphore capping total concurrent reindexes, requeueing with jitter when capacity is full. Statement timeouts scale with the retry count, so long reindexes eventually complete without a human watching, while an ensure block guarantees locks and semaphore slots are released on every exit path. Bloat heuristics were tuned against false positives first: an alert that cries wolf gets muted, and a repair loop that cries wolf gets disabled.

Outcome

Closed loop

Detect → alert → safe auto remediation

2 layer

Concurrency guardrails: table lock + global cap

Zero

Human babysitting per reindex run

Takeaway

Automate the fix only after you've automated the guardrails: a lock, a cap, and a timeout that grows with the retry.