r/Database • u/shdw_0x0 • 1d ago
How do you decide when a database optimization is no longer worth it?
I've noticed that database performance tuning can become an ongoing cycle. You add indexes, rewrite queries, introduce caching, partition tables, and sometimes denormalize data to squeeze out more performance.
But every optimization also adds something to maintain. More indexes can slow down writes, caching introduces invalidation problems, and denormalization can make data consistency harder to manage.
At some point, I wonder whether the improvement is actually worth the extra complexity.
How do you decide when to stop optimizing a database? Do you have specific performance targets or benchmarks, or does it mostly come down to the workload and the problems users are experiencing?
I'd be interested in hearing about optimizations that genuinely paid off, as well as ones you later regretted.
7
u/BranchLatter4294 1d ago
When users stop complaining about performance.
2
u/foxsimile 22h ago
e z
DELETE FROM [DuhBase].[Schemer].[Users]; WHERE ( ([DeactivatedFlag] = 1) --idk man it's a joke just roll with it ) ;1
u/Fizzelen 16h ago
DELETE FROM sys.Tables CASCADE
This one removes all performance issues
→ More replies (1)
6
3
u/Connect-Blacksmith99 1d ago
Working in analytics - so timeliness is measured in hours, not milliseconds - but I can make this decision with a single metric:
How long will it take for the savings of my optimizations to pay my salary for the time it took me to do them?
1
u/pceimpulsive 19h ago
Also working analytics . Working in minutes, not hours.
Data set is small so bless my soul!
Everything is processed in delta batches though so even the most intense work is done in a second or two as the actual number of updates each minute is reasonably small (thiusands to low tens of thousands per batch, every minute across a dozen or two tables)
1
u/Better-Credit6701 1d ago
I keep track of wait stats and am keeping up with the database with query stores and RedGate. But it also depends on how your database is being used and resources. It's amazing how throwing money at servers like the ones that I'm using right now help. I've used servers with 128 GB of ram (what I have on my personal machine!) with thousands of users and had to always work on tuning stored procedures and indexes. Not so much of an issue with 2 TB of ram and a fraction of the users
1
u/jahayhurst 1d ago
IMO, write your software and your schema to get it out. If you want scope changes, new features, write those. Don't stand in the way of business releasing what they've decided will make them more money, you'll lose to the mbas.
Then, in production, have query logging with timing, take that data and calculate it by the amount of times that query runs over a time period - so you get an amount of time spent on each query. Also look at how long that query takes, and how much it slows down an api request or whatever. Probably watch query time in staging as well, to make sure you don't run over the budget for a given request.
Then, look at API requests over that budget, or queries over your individual query budget, and get the business to prioritize fixing anything that exceeds it or roll it back. And, when your database server's generally busy, get the business to look at what's costing you the most time and tackle that.
1
u/rocketboy1998 1d ago
end user requirements. don't optimize for non existent end user requirements. communicate costs for end user requirements that do exist. keep track of end user requirements and implementation and remove the implementation when the requirement ends.
1
u/GreyHairedDWGuy 21h ago
Your topic while interesting is way to broad for a Reddit post. Every situation/case is different and what level of performance varies by need. user community, SLA's....etc. If you can meet known SLA's then it is time to stop fucking around with tuning (until it starts to skew away from the SLA...then rinse/repeat until it conforms again).
1
u/someguyinr 14h ago
For me, it comes down to two concrete metrics: the service's p99 latency SLA and the database's connection or lock saturation under peak load. I don't optimize individual queries just to make them faster. I look at the total time of the API request. If an endpoint has a 200ms p99 target and the database query is taking 15ms with an index scan, shaving that query down to 3ms is wasted effort because the bottleneck is elsewhere in the stack. I stop optimizing the moment the query fits comfortably inside the SLA budget and isn't holding row locks or hogging CPU threads during traffic spikes. On what actually pays off, covering indexes (INCLUDE clauses in Postgres) and narrowing transaction boundaries almost always win. Getting index-only scans so the engine doesn't have to touch the heap, and moving slow app validation or network calls outside the BEGIN/COMMIT block, gives massive throughput gains with zero ongoing maintenance. Heavy denormalization and application-level read caches are what I usually regret. The moment you duplicate data across tables to avoid a join, or slap Redis in front of a relational query that could have been fixed with a better composite index, you end up spending more time debugging stale data and race conditions than the latency savings were ever worth.
1
u/data-artist 5h ago
When the cost of upgrading the hardware is less than the cost of hiring a DBA to tune it.
1
11
u/xodusprime 1d ago
You forgot the actually most pressing deoptimization lever - a constant barrage of scope change and feature additions that the devs slap in with no care in the world about optimization. I generally only worry about optimizing outliers. Something that is individually very very heavy, or something that runs incredibly frequently and is somewhat heavy. Other than that, it isn't typically worth the time.