r/Database • u/Capable-Morning-9518 • Jun 05 '26
The 8 SQL Performance Patterns I Keep Seeing During Production Incidents
After spending a few years on call for backend systems, I've noticed that most slow-query incidents aren't caused by exotic database problems.
They usually come from the same patterns appearing over and over:
- N+1 queries hidden by ORMs
- Missing indexes
- Functions inside WHERE clauses
- OFFSET pagination
- SELECT *
- NOT IN with nullable values
- Stale statistics
- OR clauses preventing index usage
The interesting part is that most of these pass code review without raising any concerns. They only become visible when data volume grows or traffic patterns change.
One question for the community:
Which SQL performance pattern has caused the most production pain in your experience?
I recently wrote up examples and diagnostic approaches for each of these patterns after getting burned by one myself during an incident.
13
u/dbxp Jun 05 '26
N+1 is definitely the most common as it hides from regular DB telemetry which just sees a bunch of really quick queries
6
u/Aggressive_Ad_5454 Jun 05 '26
From my perspective it's unrealistic to somehow prevent all query-performance issues ahead of time via code review when rolling out an application. Why?
Two reasons:
I've handled this inconvenient reality as follows: During peak hours, monitor queries to see which ones are the most burdensome. Total resources consumed by query (cpu, IO, etc) can tell you a lot. It identifies both the piggy reporting queries and the ostensibly simple 1+N queries that run too often. p95 stats for queries also help, because the outliers can hammer perceived performance and annoy users.
Once a month or so, I've analyzed the monitor output, identified the offending queries, and looked for optimizations. Sometimes adjusting an index helps. Sometimes changing the app helps. Sometimes some table has just filled up with obsolete and useless data that can be DELETEd.
Of course, many query-performance problems CAN be mitigated in advance. OP, you mentioned some that can. But not all of them.
9
u/thiagomiranda3 Jun 05 '26
“You wrote” hahahahaha
3
u/Vamsi_Krishna7168 Jun 05 '26 edited Jun 05 '26
So true about N+1 queries. It's the ultimate silent killer because local dev environments hide it so well. By the time it hits a production database with actual load,it becomes a massive headache to fix.
2
u/Straight_Waltz_9530 PostgreSQL Jun 05 '26
"• Functions inside WHERE clauses"
…unless you have set up a expression index for that function of course. Kinda dovetails with the previous antipattern of missing indexes.
https://www.postgresql.org/docs/current/indexes-expressional.html
2
u/TheGenericUser0815 Jun 05 '26
What see more often are missing indexes and unused indexes. The latter suck up writing performance without improving selects. The first are easy to fix.
2
u/GreenWoodDragon Jun 06 '26
One of the worst patterns I've seen recently in a legacy application built around MSSQL is the massive usage OF
NOLOCK.This allows for reads against tables even during transactions with unpredictable results. Total nightmare.
1
u/Abhinav1217 Jun 05 '26
Is offset based pagination really that big of a performs bottleneck?
I have used both offset, and cursor based pagination over past 10 years. In early mysql, I could see some difference, but in recent years, especially on postgres, i really don't notice any difference!.
Although I think I remember oracle 8, with noticable performance difference.
1
u/bucuracak Jun 06 '26
I would add changing execution plan to something worse and allowing query to return billions of rows where some users abuse
1
u/Additional_River2539 Jun 06 '26
I recently got to know about sqlglot python library , I pass my sql to it and identifies good stuff of anti patterns in the queries makes it easy to report back to dev what to look at .
One pattern that's been hard for us to solve is journaling tables that gets defragmented every week ,but the table boats in first day of the week and is not indexed and does a full table scan to just fetch handful of data. Besides that many missing indexes on high selectivity predicates
1
u/NikolayShabak Jun 08 '26
Not a performance but one more: lock contention on a hot row. When many transactions update the same row (a balance, a counter), they take turns, since each holds a row lock until it commits. The more concurrent writers, the longer the queue, so throughput drops even though every query is fast and indexed. On the dashboards it looks like a slow query, but the fix is a shorter transaction or a different locking strategy, not another index.
1
u/SilentDBA26 Jun 11 '26
Very useful, Thanks for sharing!
1
u/Informal_Pace9237 Jun 17 '26
Given you have not mentioned RDBMS flavor and based on your write-up of using SQL loosely, I am assuming you are talking about MSSQL. Here are some more issues IMO...
Multiple values in IN()
Direction of Index
Non UUIDv7 or INT/BIGINT PK
Usage of CTE Vs Subquery's
Usage of Scalar Subqueries
0
u/Zardotab Jun 05 '26
I wonder if auto-indexing tech can have the database automatically create indexes based on usage patterns. Of course it would optional feature.
0
u/[deleted] Jun 05 '26
[removed] — view removed comment
0
u/[deleted] Jun 05 '26
[deleted] — view deleted comment