When Indexes Become Liabilities: Lessons from PostgreSQL Performance Disasters in Production
Indexes are among the most powerful tools in a PostgreSQL developer's arsenal. They can reduce query latency from seconds to milliseconds, keep infrastructure bills manageable, and ensure that applications scale gracefully under load. Yet the same mechanism that accelerates reads can quietly become a source of systemic failure when applied carelessly. Across production environments at companies ranging from early-stage startups to established enterprises, a recurring pattern emerges: teams invest heavily in application code quality, testing pipelines, and cloud infrastructure — and then treat indexing as an afterthought.
The consequences are rarely immediate. A poorly conceived indexing strategy tends to reveal itself gradually, manifesting first as occasional slowdowns during peak traffic, then as escalating AWS RDS or Aurora costs, and finally as full-blown incidents that wake engineers up at 3 a.m. Understanding how these failures unfold is the first step toward preventing them.
Case Study One: The Composite Index Applied in the Wrong Order
A mid-sized SaaS company operating a multi-tenant PostgreSQL database built a composite index on (account_id, created_at, status) to accelerate a dashboard query that filtered records by account and sorted results by date. The index was created early in development, when the status column had only two distinct values. Over eighteen months, the product expanded to include a dozen status values, and the query pattern shifted — most dashboard queries now filtered by status first, then by account_id.
The index remained in place, unchanged. PostgreSQL's query planner, presented with a composite index whose leading column was account_id, could not efficiently satisfy queries that led with status. In many cases, the planner opted for a sequential scan of a table that had grown to forty million rows. Query execution times climbed from under 100 milliseconds to more than eight seconds.
The lesson: Composite index column ordering is not a set-and-forget decision. The leading column in a composite index must reflect the most selective and most frequently used filter in your actual query workload. As application behavior evolves, index definitions must evolve with it. Regularly reviewing pg_stat_user_indexes and pg_stat_user_tables provides the data needed to identify indexes that are no longer being used as intended.
Case Study Two: Index Bloat from High-Churn Tables
A logistics platform stored real-time shipment tracking events in a PostgreSQL table, with updates arriving at a rate of several thousand rows per minute. The engineering team had indexed every column they anticipated filtering on — eight indexes in total on a single table. What they had not accounted for was the write amplification this created.
Every INSERT and UPDATE operation required PostgreSQL to maintain all eight indexes simultaneously. Dead tuples accumulated faster than autovacuum could reclaim them. Index bloat grew unchecked. Within four months, the primary table's physical size had grown to 200 GB, while the indexes themselves consumed an additional 340 GB. Read performance, paradoxically, degraded — bloated indexes require more I/O to traverse, and the buffer cache became saturated with stale index pages.
The team eventually ran REINDEX CONCURRENTLY on the most bloated structures and eliminated five of the eight indexes after confirming through query plan analysis that the planner rarely used them. Storage costs dropped by roughly 40 percent, and average query latency on the table fell by more than half.
The lesson: More indexes do not mean better performance. On high-write tables, every index carries a real cost — in write latency, storage consumption, and vacuum overhead. Index your actual query patterns, not your hypothetical ones. Tools such as pgBadger and the pg_stat_statements extension are invaluable for identifying which indexes are earning their keep.
Case Study Three: Missing Partial Indexes on Soft-Delete Patterns
A healthcare technology firm used a standard soft-delete pattern, adding an is_deleted boolean column to most of their core tables. The vast majority of application queries — well over 95 percent — included a WHERE is_deleted = FALSE clause. Their indexes, however, were defined without any partial index conditions, meaning every index included both active and deleted records.
As deleted records accumulated over time, the ratio of active to deleted rows shifted dramatically in some tables. Indexes that had once served primarily active records were now bloated with entries for rows the application would never query. The planner occasionally chose these indexes for queries that would have been better served by a targeted partial index, resulting in unnecessary index scans across a much larger dataset than necessary.
Refactoring the most critical indexes to use a WHERE is_deleted = FALSE predicate reduced index sizes by 60 to 80 percent on the affected tables and materially improved query plan quality.
The lesson: Partial indexes are one of PostgreSQL's most underutilized features. When a significant portion of your dataset is logically excluded from the majority of your queries — whether through soft deletes, archival flags, or status filters — a partial index that mirrors that exclusion condition will almost always outperform a full index on the same column set.
Case Study Four: Indexing Foreign Keys Inconsistently
A fintech startup experienced severe lock contention during bulk operations that deleted parent records from a transactions table. The root cause was straightforward but easy to miss: several child tables referencing the parent via foreign keys had no indexes on the foreign key columns themselves.
When PostgreSQL enforces referential integrity during a parent-row deletion, it must verify that no referencing rows exist in child tables. Without an index on the foreign key column, this verification requires a sequential scan of the child table. Under concurrent load, these sequential scans held locks long enough to create cascading delays across the application.
The lesson: Every foreign key column that participates in a referential integrity constraint should, in nearly all cases, be indexed. This is a foundational practice that is surprisingly easy to neglect, particularly as schemas evolve and new relationships are added over time. Automated schema auditing tools or custom queries against information_schema can surface unindexed foreign keys before they become a production problem.
Building a Healthier Indexing Culture
The scenarios above share a common thread: indexing decisions were made at a point in time and never revisited. Production databases are not static systems. Query patterns shift, data volumes grow, and application logic evolves in ways that invalidate earlier assumptions.
A sustainable indexing strategy requires ongoing attention. Scheduling periodic reviews of pg_stat_user_indexes to identify unused or low-efficiency indexes, monitoring index bloat through pgstattuple, and integrating query plan analysis into the development workflow — not just incident response — are practices that separate teams who stay ahead of performance problems from those who are perpetually reacting to them.
Indexes should be treated as first-class architectural decisions, documented, reviewed, and subject to the same scrutiny as any other infrastructure choice. The cost of neglecting them rarely appears on a single invoice or in a single incident report — but it accumulates, quietly and persistently, until it cannot be ignored.