Query Cost Escalation Without Indexes on Growing Tables
Sequential scan queries on a large table without appropriate indexes consume CPU and I/O proportionally to table size. At 1 million rows a missing index is invisible. At 10 million rows the same query causes user-visible latency. At 50 million rows it triggers database CPU saturation, connection pool exhaustion, and cascading failures across all users sharing the database: while the query plan has remained unchanged throughout.
PostgreSQL primary with growing OLTP table, no index on high-cardinality filter column
Degradation Replay
Nominal: Table Small: Sequential Scan Undetected
Table under 500k rows; sequential scan completes in <50ms: indistinguishable from indexed access at this scale
- ·Query runs in 20-50ms: no alerts, no complaints
- ·EXPLAIN shows Seq Scan but cost estimate is low (table is small)
- ·Database CPU impact of sequential scan less than 1%
- ·No index on filter column but this is not yet visible as a problem
- ·Entire table fits in shared_buffers: sequential scan reads from memory, not disk
- ·Query plan with Seq Scan functionally equivalent to index scan at this data volume
- !Problem dormant: sequential scan is a latent performance debt that grows with the table
Operational simulation model only, not a production forecast. Degradation stages are derived from structured operational knowledge, not measured telemetry. Do not use for capacity planning or incident response.
Run With Your Parameters
Adjust the parameters below to see how metric values shift across degradation stages. Formulas are deterministic: same inputs always produce the same output.
Simulation Parameters
Database queries generated per API request
Computed Degradation Stages
Table under 500k rows; sequential scan completes in <50ms: indistinguishable from indexed access at this scale
Table grows to 5-10 million rows; sequential scan duration climbs to 1-3 seconds
Table at 20 million rows; sequential scan takes 5-10 seconds; concurrent queries saturating CPU
Table at 50 million rows; sequential scan takes 25-40 seconds; connection pool exhausted at moderate traffic
CREATE INDEX CONCURRENTLY completes; query planner switches from Seq Scan to Index Scan
Threshold Events
Connection pool at 100%: N+1 pattern at 15 queries/request generates 30,000 concurrent DB queries. P95 latency inflecting non-linearly.
2,000 API requests generating 30,000 DB queries (15× amplification). Pool of 100 connections cannot sustain this query rate without queuing.
Interpretation
15 DB queries per API request generates 82,500 QPS at 5,000 RPS. Connection pool at 100%.
N+1 query pattern generates 15× DB amplification per request
Batch related entity fetches with IN() queries or DataLoader pattern. Add ORM eager-loading for known one-to-many relationships. At 5,000 RPS, fixing N+1 reduces DB load from 82,500 to ~5,000 QPS.
Parameterized simulation: not a production forecast. Values derived from deterministic formulas applied to your parameters. Do not use for capacity planning or operational decisions without validation.
Propagation Model
Sequential scan reads every page in the table; at N pages, cost is O(N); at 10× table size, query reads 10× more pages, consumes 10× CPU and I/O
Stabilizes: Cost only stabilizes if an index is added or data is deleted: grows indefinitely with table size
At 50 concurrent requests × 5-second sequential scan = 250 connection-seconds consumed per second; connection pool exhausts when request duration × concurrency exceeds pool size
Stabilizes: Pool pressure releases only when query latency drops (via index) or traffic drops
Sequential scan queries hold connections for their full duration; other queries queue waiting for free connections; writes also delayed: entire application experiences elevated latency, not just the unindexed query path
Stabilizes: Resolves immediately when index is added: query plan switches to index scan
Recovery Patterns
CREATE INDEX CONCURRENTLY on filter column
5-15 minutes for a 50M row table; immediate query improvement once index is active- ·CONCURRENTLY builds index without locking table for reads or writes: safe for production
- ·Index creation increases write overhead going forward: each INSERT/UPDATE must update the index
- !Index must be verified with EXPLAIN ANALYZE after creation: planner occasionally ignores index if statistics are stale
Partial index for filtered queries
Same as full index but smaller: builds faster on large tables- ·Partial index only helps queries that match the index predicate: must match exactly
- ·Smaller index is faster to build and uses less disk
- !Other query patterns on the same column may still require a full index
Operational Summary
Missing indexes on large tables are a class of performance problem that is entirely invisible in development and staging, then suddenly catastrophic in production. The mathematical relationship is simple: a sequential scan of N rows takes O(N) time. An index scan takes O(log N) time. At 500k rows, the difference is 10-50ms. At 50 million rows, the difference is 3ms vs 40 seconds.
The incident is almost always traced to a feature shipped when the table was small, where the sequential scan looked fine in testing. The table grew. The query plan never changed. The cost grew with the data.
CREATE INDEX CONCURRENTLY is the fastest incident resolution in the PostgreSQL operational playbook. It takes 5-15 minutes on a 50M row table, requires no downtime, and completely resolves the incident within seconds of completing. The only reason not to have done it already is that no one looked at the query plan.