Sarah Chen, a senior database administrator at a mid-sized e-commerce company, remembers the exact moment she found the culprit. It was a Tuesday afternoon in early 2024. A routine glance at the Postgres slow query log showed a query against the orders table that consistently took 47 seconds. After weeks of profiling, testing indexes with hypopg, and running EXPLAIN ANALYZE dozens of times, she identified the fix: a single missing b-tree index on the order_status and created_at columns. The query dropped to 12 milliseconds. The annual compute cost of that one slow query, spread across autoscaling replicas and reserved instances, was roughly $250,000.

The $250,000 Query That Almost Wasn't Found

Chen's discovery did not come from a dashboard alert or a cost anomaly detector. It came from pg_stat_statements, the Postgres extension that tracks query execution statistics. She had set up a weekly review of the top queries by total execution time. The orders query had been climbing the list for weeks, but no one had flagged it because it ran infrequently — only a few hundred times per day during end-of-month reporting.

“The latency was bad but not catastrophic,” Chen explained in a talk at a local Postgres user group. “If you only look at p99 latency, you might miss it. The cost was in the cumulative CPU burn across all the replicas.”

The query performed a sequential scan on a table with over 50 million rows. Each scan read roughly 8 GB of data from disk, hammering the I/O subsystem. On an RDS db.r6g.8xlarge instance, which costs around $10,000 per month, one slow query can pin CPU at 100% for seconds at a time, forcing the autoscaler to spin up additional read replicas. Chen estimated that this single query was responsible for roughly two extra replicas running 24/7 during the last week of each month.

The fix took less than an hour to deploy: a CREATE INDEX CONCURRENTLY statement that ran in the background. The results were immediate. CPU dropped by 15% across the cluster. The extra replicas were decommissioned. The monthly bill fell by around $20,000.

Why Indexing Is the First Place to Look for Waste

Missing indexes are the most common cause of unnecessary compute spend in Postgres deployments. A sequential scan on a large table can consume orders of magnitude more CPU and I/O than an index scan. The problem is especially acute in SaaS companies that grew quickly, where schema design was done under pressure and index maintenance fell by the wayside.

The economics are stark. A single missing index on a table with 10 million rows can cause a query to run 100x slower. At cloud instance pricing, that 100x penalty translates directly into dollars. Some estimates put the typical waste from bad queries at 15–30% of total database spend for companies running Postgres in production.

A well-known example comes from Etsy, which in 2013 conducted a company-wide index cleanup that saved an estimated $1 million per year in database costs. More recently, a DBA at a fintech startup recounted how they identified a handful of missing indexes that reduced the monthly AWS bill by $80,000 in a single afternoon. “It was embarrassing how easy it was,” they said. “We just ran pg_stat_user_indexes, saw tables with full scans, and added indexes.”

The fix is cheap. A DBA's time to investigate and deploy an index might be a few hours. Even at a fully loaded cost of $500 per hour, that's a one-time cost of $2,000. The annual savings can be in the hundreds of thousands. The return on investment is absurdly high, yet many organizations underinvest in indexing expertise.

Another example comes from a logistics company that ran a daily shipment reconciliation query. The query joined a 200-million-row shipments table with a 50-million-row orders table on a non-indexed column. Each run took over 30 minutes and consumed nearly 100% CPU on a dedicated read replica. After adding a composite index on the join columns, the query dropped to under 2 minutes. The company estimated that the index saved roughly $12,000 per month in compute and allowed them to decommission one read replica entirely. The DBA who implemented the fix spent less than a day on the entire investigation, including testing with a staging environment.

The Economics of a Missing Index at Scale

At scale, the cost of a missing index compounds. A single slow query might not trigger alarms, but when it runs thousands of times per day across dozens of replicas, the waste becomes significant. Cloud providers charge for compute, storage, and data transfer. A sequential scan that reads 8 GB of data 500 times per day moves 4 TB of data daily. That's roughly $200 per month in data transfer alone, on top of the CPU cost.

Autoscaling multiplies the waste. When one query spikes CPU, the autoscaler adds replicas. Those replicas then serve other queries, masking the root cause. The team sees a gradually rising baseline cost and assumes it's normal growth. Chen's company had been paying the $250,000 premium for over a year before she traced it.

The math works out simply. An RDS 8xlarge instance costs about $10,000 per month. If a missing index causes you to need two extra instances for one week per month, that's $5,000 per month in waste. Over a year, that's $60,000 for just one query. Chen's case was worse because the query ran across multiple replicas and caused cascading effects.

Compare that to the cost of a DBA. A senior DBA's salary might be around $150,000 per year. If they spend even a month tracking down and fixing such issues, the cost is a fraction of the savings. Yet many companies treat database administration as a part-time responsibility for a backend engineer, who may lack the deep knowledge of Postgres internals needed to spot these problems.

The compounding effect is not just about money; it also affects system stability. A missing index that causes frequent CPU spikes can lead to increased latency for other queries, cascading timeouts, and even application-level failures. In one documented case, a social media company experienced intermittent site outages during peak hours, traced to a single missing index on a user activity table. The index reduced CPU by 40% and eliminated the outages entirely. The cost of the downtime, in terms of lost revenue and engineering hours, was estimated to be several times the direct compute savings.

The Real Cost: Not Just Compute, But Developer Time

The compute waste is only part of the story. The hidden cost is developer and operations time. Chen spent three weeks profiling the query, testing hypothetical indexes with hypopg, and carefully deploying the change. During those weeks, she was not working on other performance improvements or supporting new feature launches.

The incident also caused context-switching across the team. When the query slowed down, it triggered PagerDuty alerts for the on-call engineer at 2 AM. The engineer spent hours investigating, escalating to the DBA team, and applying temporary mitigations like increasing instance size. That time could have been spent building features that generate revenue.

Opportunity cost is hard to measure, but it is real. A startup that loses two weeks of engineering time to a database fire drill might miss a product launch deadline. A mature company might delay a migration to a new storage engine. In both cases, the missing index is not just a compute cost; it is a tax on productivity.

Chen's advice to other teams is simple: “Pay for indexing expertise upfront. It's the cheapest insurance you can buy for your database performance.” She recommends that every team with a Postgres deployment invest in at least one person who understands query planning, index types, and the tools available for monitoring.

To put a finer point on it, consider the cost of a single on-call incident. If an engineer spends 4 hours at 2 AM investigating a slow query, at a fully loaded hourly rate of $100, that's $400. If the incident happens once a month, that's nearly $5,000 per year in wasted engineering time for a single query. Multiply that across multiple teams and multiple incidents, and the hidden costs quickly dwarf the compute costs. A proactive indexing strategy can eliminate these incidents entirely, freeing up engineering time for higher-value work.

What a Good DBA Does Differently

A proactive DBA does not wait for slow queries to surface. They regularly review pg_stat_user_indexes to find indexes that are never used and tables that lack indexes. They use hypopg to test hypothetical indexes without actually creating them, reducing the risk of adding unnecessary indexes. They run EXPLAIN ANALYZE on every query that shows up in the slow query log, and they understand the difference between a sequential scan that is okay and one that is wasteful.

They also automate. Tools like pg_qualstats can track which columns are used in WHERE clauses and suggest indexes automatically. Some teams have built internal dashboards that show the estimated dollar cost of each query per execution, based on the instance type and data read. This makes the economic impact visible to developers who might otherwise ignore a slow query.

The goal is to keep the index hit ratio above 99%. That means 99% of all queries should be able to use an index to find rows, rather than scanning the entire table. When the ratio drops below that threshold, it's time to investigate. A good DBA treats index maintenance as a continuous process, not a one-time cleanup.

Chen's team now runs a weekly review of the top 10 queries by total cost. They have a Slack bot that posts a report every Monday morning. If a new query appears in the top 10, it gets flagged for investigation. This discipline has prevented several potential cost spikes since the original incident.

Beyond the basics, a good DBA also understands the nuances of index types. For example, a partial index can be a powerful tool for queries that filter on a specific status, like 'WHERE order_status = 'pending'. This index is smaller and faster than a full index on the column. Similarly, covering indexes that include all columns needed by a query can eliminate table lookups entirely. Chen's team uses partial indexes extensively for their orders table, reducing index size by about 40% compared to full indexes. This not only speeds up queries but also reduces storage costs and write overhead.

When Not to Index: The Counterintuitive Economics

Indexing everything is not the answer. Indexes have their own costs: they consume storage, slow down writes, and can cause bloat if not maintained. For OLAP workloads that scan large portions of a table, a sequential scan may be cheaper than an index scan that requires random I/O. For write-heavy tables, every additional index adds overhead to INSERT, UPDATE, and DELETE operations.

Consider a log table with a seven-day TTL. Queries against it are rare, and when they happen, they often scan the entire table. Adding an index would slow down the nightly purge job and consume storage that could be used for other data. Chen's rule of thumb is to index only for queries that matter — those that run frequently, are user-facing, or cost more than a certain dollar threshold per month.

The balance between read and write cost must be evaluated per table. A high-traffic transactional table might tolerate a few extra milliseconds per write if it saves seconds per read. A reporting table that is bulk-loaded nightly might not benefit from any indexes at all. The decision should be driven by data, not dogma.

Chen learned this lesson the hard way. Early in her career, she added indexes to every column that appeared in a WHERE clause. The result was a table with 15 indexes, write performance that degraded by 40%, and a storage bill that increased by 20%. She had to drop half of them after measuring the actual usage. “Indexing is a trade-off, not a free lunch,” she says.

A more nuanced example comes from a payment processing company that had a transactions table with heavy write volume (thousands of inserts per second). They initially added indexes on every foreign key and status column to speed up reporting queries. However, the write overhead became so severe that the database could not keep up with the insert rate, causing backpressure on the application. After careful analysis, they removed two indexes that were rarely used for critical queries, and the write performance improved by 25%. The reporting queries that used those indexes still ran acceptably fast due to the smaller table size. This case illustrates that indexing decisions must consider the entire workload, not just read performance.

How to Build an Index Budget for Your Team

The most effective approach is to treat indexes as infrastructure debt with an interest rate. Every missing index costs money every month it is not added. Every unnecessary index costs money every month it remains. The key is to measure the cost and prioritize fixes based on the interest rate.

Start by tracking query cost in dollar terms per execution. If your RDS instance costs $10,000 per month and runs 10 million queries, the average cost per query is $0.001. But a query that reads 100x more data costs $0.10 per execution. If it runs 10,000 times per month, that's $1,000 per month — a clear target for optimization.

Set a threshold for action. Some teams use $100 per month per query as a trigger for investigation. Others use a multiple of the average query cost. The important thing is to have a consistent process. Automate alerts for new slow queries using tools like pg_stat_statements and custom scripts that compare current execution times to a baseline.

Review the top 10 queries by total cost every week. This is a lightweight process that can be done in 30 minutes. Over time, the list will shrink, and the team will develop intuition for which patterns are expensive. Chen's team has maintained this practice for over a year, and their database costs have remained flat despite 30% growth in transaction volume.

Another effective practice is to conduct a quarterly index audit. During the audit, the DBA reviews all indexes for usage statistics using pg_stat_user_indexes and pg_stat_all_indexes. Indexes that have not been used in the past 90 days are candidates for removal. This prevents index bloat and keeps the database lean. Chen's team typically finds 5–10 unused indexes per quarter, saving roughly $500–$1,000 per month in storage and write overhead.

The final piece is culture. Developers need to understand that a slow query is not just a performance bug; it is a cost bug. When the team treats query performance as a financial metric, the incentives align. The DBA becomes not just a firefighter, but a financial analyst who optimizes the company's spend on data infrastructure.

To embed this culture, Chen's team includes a 'query cost' field in every bug report and feature ticket. If a new feature introduces a query that reads more than a certain amount of data, it triggers a review. This has led to several design changes before deployment, saving countless hours of post-hoc optimization. For example, a new dashboard feature initially queried the entire orders table to compute aggregates. After a quick review, the team added a materialized view that refreshed nightly, reducing the query cost from $500 per month to essentially zero.

In summary, the $250,000 query is not an outlier. It is a symptom of a common pattern: ignoring the economics of indexing until the bill arrives. By adopting a proactive, data-driven approach to index management, teams can save significant money, reduce developer toil, and improve system reliability. The tools are available, the math is simple, and the return on investment is enormous. The only missing piece is the discipline to act.