One of the first traps people fall into is trusting the EXPLAIN output like gospel. I did this with a client’s overloaded MySQL instance that was intermittently freezing during checkout flows. EXPLAIN was telling me a query was using the right index, estimated cost looked fine—but once I ran it with actual parameters hitting real production data, I saw a half-second delay we couldn’t afford during peak traffic.
The thing is, EXPLAIN doesn’t run the query. It just predicts the plan. In production, caching layers, I/O patterns, and table sizes do whatever they want. Use SHOW PROFILE or ANALYZE in Postgres if you actually want truth. Even better, log slow queries with real execution metrics. If you haven’t set long_query_time to something sane, fix that first. I usually set it to half a second in staging to filter out noise, then dial up or down.
And pro tip: if your EXPLAIN output shows Using temporary; Using filesort in MySQL, you’re in for a bad time when that query hits a huge data set. Optimizer sometimes chooses awful join orders when the stats diffs are too small to matter, but they matter.
Freak Index Collisions and Bloat
This came up during a mess with a Django app that had accumulated a dozen auto-created indexes across migrations. We had the same field indexed four ways—unique, BTREE, and with partial filters. The query planner in Postgres kept flip-flopping between them because the cost estimates were too close. It wasn’t until I used pg_stat_user_indexes that I realized one of them had never been used in six months.
Here’s a quick fire list to keep your indexes in shape:
Use REINDEX monthly if you’re seeing index bloat (watch pgstattuple)
Drop multi-column indexes that aren’t actually getting used by compound WHERE clauses
If you have a lot of deletes, remember Postgres doesn’t reclaim index space like you’d expect
Beware auto-indexing done by your ORM — not all of these help, many live forever
Use CREATE INDEX CONCURRENTLY in prod. Even if you think load is low, regular CREATE locks writes
And don’t assume index usage in dev matches prod. Especially if local has a tenth the data volume.
Optimization Services That Only Surface the Obvious
I got a trial of a performance dashboard promising query optimization insights. Name withheld but you can guess—one of those observability startups trying to be the “Datadog for databases.” Within minutes, it flagged an unindexed foreign key lookup on a user_country table. Cool, except… that exact join hit fast in prod because it had a perfect 1:1 cache hit ratio via Redis. Literally never touched the DB. The report had flagged it as high-latency due to dev data volumes being tiny enough to generate full-table scans.
The real-world issue was a different table: user_notifications had an array field JSONB column, and one over-aggressive filter caused Postgres to deserialize every object. No flag. No suggestion. Complete silence. So yeah—some optimization tools don’t actually look at real-world I/O or query volume. They just re-skin EXPLAIN or tap into pg_stat_statements but do nothing deeper. Be real about your workload.
When Query Caching Backfires
Memcached and Redis are wonderful until they’re not. I broke an AdSense revenue dashboard for a multi-niche site because we cached the top 10 CTR pages per niche with a 1-hour TTL. Everything was fine for “tech” and “food” niches, but one new vertical—”eMobility Adventures” or some LinkedIn-sounding name—had zero traffic at the time of caching. So the entire column was blank for an hour, even after traffic kicked in.
The query itself was optimized—no problem. But the caching logic didn’t fall back to DB revalidation when the response was empty. Just because you cache doesn’t mean cache miss logic shouldn’t be smart. If I’d implemented a stale-while-revalidate model back then, we could’ve shown some initial data and updated silently instead of showing literal nothing.
Malformed UTF-8 Can Nuke a Fast Query
This was one of those unspoken disasters. A legacy product table had a description field with mixed encodings—some old imports were ISO-8859-1, others were malformed UTF-8. A fulltext MATCH AGAINST operated fine until someone searched for the word “coöperation” with the Unicode diaeresis.
Suddenly, MySQL freaks out, falls back to a full table scan, and logs spike. Turns out hitting certain byte patterns makes the optimizer give up on the fulltext index. There’s zero doc page covering this. You either know or you guess, or in my case, stare at SHOW PROCESSLIST trying to guess why your CPU fans are overnighting themselves to Heathrow.
The aha moment was realizing this query’s performance degraded only when users typed curved quotes or paste from Google Docs. Literally punctuation broke my whole plan.
Subqueries That Scale Like Crime
You’d think developers would know to de-nest queries by now. And yet, here we are. I saw this last month in a Laravel app doing analytics queries with 6 nested layers:
SELECT * FROM (
SELECT user_id, COUNT(*) AS views FROM (
SELECT * FROM page_views WHERE timestamp > NOW() - INTERVAL 7 DAY
) AS recent_page_views
GROUP BY user_id
) AS weekly_views
This was being called during an admin dashboard load. Every refresh nuked the DB with temp tables and swapped to disk. It only made sense once I walked through EXPLAIN ANALYZE and saw that the inner subquery was building a full in-memory copy of all page views before filtering. Just move the WHERE condition to the outer layer. Or better: materialize it—and not with ORM-level caching, but proper table-level structures. I ended up building a rolling materialized view with scheduled updates. Response time dropped from 7s to 180ms.
The Weird Stuff You Only See with Read Replicas
I had this odd replication lag issue on a Postgres read replica used for reporting dashboards. Every now and then, the analytics would show incomplete data from the past ~10 minutes. The master was fast, indexes looked clean. But it wasn’t until I realized the replica was hosted in a separate AWS region (for “latency reduction”—lol), that replication lag jumped to over 60 seconds during bursts.
Turns out one batch job was vacuuming aggressively and causing WAL volume spikes. Replica couldn’t keep up. Again, absolutely nothing in error logs. Just metrics lying by omission.
If you’re relying on a read replica, make sure you’re monitoring pg_stat_replication and tracking delay, not just whether the replica is “healthy.” e.g., pg_last_xact_replay_timestamp()
Undocumented Behavior in GROUP BY and LIMIT
This one’s more subtle. I was working on a query that grouped results by region, ordered them by revenue, and limited to one line per region. Initially built like:
SELECT region, MAX(revenue) FROM sales GROUP BY region ORDER BY MAX(revenue) DESC LIMIT 10;
Guess what? That LIMIT doesn’t do what you think. It limits the total number of groups post-aggregation, not the highest revenue per region once you add ORDER BY across group aggregates. You might think you’re getting top regions, but you’re just getting the first 10 buckets alphabetically, maybe, depending on the planner.
To get what I wanted, I had to use a window function: ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) and then WHERE row_number = 1.
Fun part: none of the query builders I’ve used — not Prisma, not Sequelize — make this easier. They default to nested SELECTs that are way less readable. Just write raw SQL if it takes fewer characters to say what you’re doing. Clarity over abstraction most days.
Mistaking Load Balancing for Optimization
We had a client using a cloud-managed Postgres cluster and assumed their load balancer was smart enough to split analytic vs transactional reads. It wasn’t. Their app basically spray-gunned random read queries at any instance, and the stats collector had no differentiation. So heavier queries would pile up on the same replica that served login auths. Result? Login latency spiked during dashboard use. All because the load balancer was round-robin dumb.
Sometimes you think you’ve tuned the DB because you’re seeing low response times—but unless the traffic pattern is stratified by query weight, your optimizations are short-lived. Use read intent routing when possible, or build your own with connection poolers that differentiate by query context. PgBouncer at least lets you tag sessions sensibly even if it’s not a silver bullet.
And don’t assume RDS Proxy or Cloud SQL automatically does this well. They mostly don’t. They’re better than nothing, but nothing beats routing logic tied to your actual app-defined access paths.
Legit Speed Gains From Archiving Old Rows
I know this one seems obvious until you forget. A client’s order_events table had grown to something obscene—hundreds of millions of rows, most untouched in years. But every lookup still hit the whole thing because our queries filtered using user_id, but Postgres insisted on scanning multiple partitions because the planner hadn’t updated stats in weeks.
We sliced off the archive into a separate table and added a BEFORE INSERT trigger to shuffle data older than 18 months. Query performance jumped, even though structure was the same. Half the gain came from table-level bloat shrink. Other half? Just making things explainable. The optimizer gave us better plans once the active set fit in RAM again.
PostgreSQL’s autovacuum isn’t as hands-free as you think, especially once the row churn starts to slow. Manual intervention—like scheduled VACUUM ANALYZE—reclaims more than space. It makes queries predictable again.
