Stop Adding Indexes Like a Junior Dev: 5 Indexing Patterns That Turned a 12-sec Query into 8ms
Rewriting Java gets approval. Reading the execution plan earns ownership — 5 Postgres indexing patterns that separate juniors from seniors
The incident channel lit up at 2:47 PM. “Checkout timeouts — orders not completing.” Grafana showed one query against the orders table taking 12,000 milliseconds. Twelve seconds. Every request to the “My Orders” page was piling up, saturating the connection pool, and cascading into a full outage.
The developers bumped spring.datasource.hikari.maximum-pool-size from 10 to 50 and redeployed. The outage got worse: the database now had fifty concurrent twelve-second queries instead of ten, CPU pinned at 100%, and the queue kept growing.
A senior developer never opened the Java code. They opened a psql session, ran EXPLAIN ANALYZE, and had the query down to single-digit milliseconds in twenty minutes. No refactor, no pool tuning, no cache layer. Two indexes and a test.
The difference isn’t deep knowledge of Postgres internals. It’s a habit: juniors change the code they can see; seniors read the plan the database actually executed. Here are the five patterns that turn that habit into something repeatable.
