Postgres invalid indexes silently degrade write performance after failed builds

A common pattern in modern database migrations involves using CREATE INDEX CONCURRENTLY to avoid locking tables during schema changes. However, when this operation fails due to timeouts or data conflicts, it often leaves behind an "invalid" index entry. These remnants do not help with queries but can significantly slow down write operations by forcing the database to maintain them unnecessarily.

What happened

When a developer or automated agent requests a unique index on a live table, the correct approach is to use the concurrent build option. If the process fails midway—perhaps due to a duplicate key violation, a deadlock, or a statement timeout—Postgres does not simply roll back the entire operation. Instead, it commits the initial catalog entry for the index before the build completes. This results in an index that exists in the system but is marked as invalid.

The critical issue arises when migration scripts are retried without checking the state of the database. A common mistake is adding IF NOT EXISTS to the retry command. This allows the migration to pass continuous integration checks, but it ignores the fact that the existing index is broken. The database continues to carry the overhead of this invalid structure, leading to degraded performance that is difficult to diagnose because standard query plans will not show its usage.

Key details

• Invalid indexes are created when a concurrent build fails after the initial catalog transaction commits.
• Two flags determine the cost: indisvalid = false means the index is useless for queries, while indisready = false means writes ignore it.
• If indisready is true, the database still updates the index on every insert or update, wasting CPU and storage.
• Unique constraints from failed builds may still enforce uniqueness rules even if the planner never uses the index for lookups.
• Migration tools like Rails, Knex, and golang-migrate often wrap commands in transactions, which causes CONCURRENTLY operations to fail immediately.
• Cleaning up requires DROP INDEX CONCURRENTLY, which must be run outside of a transaction block.

Background

To understand why this happens, it helps to know that a concurrent index build is not a single atomic transaction. It occurs in three distinct phases across separate transactions. First, the index is registered in the system catalogs as invalid. Second, the database scans the existing table data to populate the index. Third, it waits for any old transactions to finish before marking the index as ready for use.

If the process is interrupted after the first phase but before the final phase, the index remains in the catalog. Depending on exactly when the failure occurred, the index might be in a state where it is not ready for queries but is still being updated by new writes. This is particularly insidious because the index appears to exist, yet it provides no read benefit while actively penalizing write performance.

Why it matters

For teams running their own software, silent performance degradation is a major operational risk. An invalid index that is still being maintained consumes disk space, increases write-ahead log (WAL) volume, and prevents Heap-Only Tuple (HOT) updates. This leads to higher storage costs and slower application response times during data ingestion. Since the index is ignored by the query planner, standard monitoring tools focused on read performance may not flag the issue.

Furthermore, automated deployment pipelines can exacerbate the problem. If a coding agent or CI script retries a failed migration with IF NOT EXISTS, it effectively hides the error. The deployment succeeds, but the database accumulates technical debt in the form of broken indexes. Over time, multiple failed attempts can leave several invalid indexes on a single table, compounding the write overhead and making future maintenance more complex.

What you can do

• Query the pg_index system catalog regularly to find entries where indisvalid is false.
• Check the indisready flag to determine if the invalid index is still consuming write resources.
• Use DROP INDEX CONCURRENTLY to remove invalid indexes without locking the table for reads or writes.
• Ensure migration tools are configured to disable transaction wrapping for concurrent index operations.
• Avoid using IF NOT EXISTS in retries; instead, check for validity and drop the broken index first.
• Add a post-deployment check that raises an exception if any invalid indexes remain in the database.