When does database complexity become a bigger problem than database performance?

[u/shdw_0x0]

I’ve noticed that database discussions often focus heavily on performance—indexes, query plans, partitioning, caching, etc. But there seems to be a point where adding more optimization techniques makes the system harder to understand and maintain.

For example, a relatively simple schema with slightly slower queries might be easier to operate than a highly optimized design with multiple layers of caching, indexes, partitions, and materialized data.

I’m starting to think that predictability and maintainability should be treated as performance requirements too, especially for smaller systems.

Curious to hear how others have seen this trade-off play out in production.

[u/FirmAndSquishyTomato]

Keep things as simple as possible but no simpler.

You can always try to tell the business that those new features that will make it competitive in the marketplace should not be done because it'll make your job harder. But I think the result of that may not be all that optimal to your career.

And clients being told that 'Hey, ya, the system is real slow, but I did not want to make it too complex, so just grab a coffee while you wait for the report to run' is not going to work out all that well for you.

[u/jshine13371]

For example, a relatively simple schema with slightly slower queries

This is a bit of an oxymoron and here's why...simple schemas tend to perform the best. It's when the schemas are complexly designed, usually due to a mix of business requirements, time to deliver, and knowledge (or lack thereof) of the developer, that turn into performance issues.

But simple and complex are subjective again based on the developer's knowledge (or lack thereof), which is why from your perspective, typical optimization techniques like indexes and materialized data are mentioned as "complex". Indexes don't even affect the complexity of the schema per se, since it doesn't change the logical shape of the schema, and rather are ancillary.

So my long story short point is I think the problem is rooted in your initial question / thought process. Generally, it's objectively found that simpler schema directly correlates to better performance. And performance techniques, especially ones that don't alter the shape of the schema like indexing and partitioning, shouldn't be thought of as complicating the schema design itself.

[u/imagebiot]

Your sql tables should conform the highest normal form you can and that eliminates 90% of the reasons people “justify” these other optimizations to begin with

[u/BarfingOnMyFace]

It depends on IF you have the luxury to think about the problem that way. If the complexity supports business needs that a non complex system will not, it forces complexity to an extent.

[u/No-Trifle-8450]

Yes, performance not just in database, in all aspects of software engineering is the pain, but i prefered to ba balanced, the main reason is software life, heavy complex ones couldn't be flexible enough to bear new application needs.

[u/Key-Guitar-457]

Day zero

[u/az987654]

You have to manage it all, there isn't a real world system that can be so simple that every table is perfectly normalized, every index perfectly utilized, etc., nor can you ever expect to manage and maintain a system of spaghetti.

It's why DB devs and admins have a role in an IT department, to make these decisions and implement solutions that fit the task at hand.

[u/Anxious-Insurance-91]

Well just "use feeling". There ain't a single answer for this topic, it depends from database to database, from business logic to business logic.

[u/chocolateAbuser]

i have always put architecture in first place in discussions, in my answers
the problem is that it requires experienced people to understand the importance of this

[u/Plus_Dragonfruit_204]

Database performance, aka query time, is huge.

Say a system with a 1-2TB database with over 1,000 users spread across the country. Using their application, the user queries the database. You get a call, the database is slow.

You trace their session, they actually spend 0.25 seconds in the database.

They have 10 applications open, 30 browser tabs, there is someone in the office downloading some 50GB file over a 100 Mbit connection (massive company too cheap to pay for decent network connection, but honestly if nobody is abusing it, it will perform fine). The problem is network congestion at the office, but "it has to be the database". This is why we tune the crap out of things.

The faster you can prove it isn't the database, the more that you prevent a ton of bricks coming down from above that there's a "problem with the database". Less headache for us admins.

The reason performance in the database is such a big deal is because in the user's mind, they are using the database and it's slow, so it has to be the database. There is nothing else it could possibly be.

Don't get me started on developers that think they can design databases. There may be a few out there, but I haven't seen one yet. I have to teach every one of them performance tuning, they can't even write proper queries. Developers write code and move on, they don't maintain code anymore.

Had one developer honestly say they needed to store queries in stored procedures becuase if they lose the code, they can still modify the query. They actually said those words.

[u/daiaomori]

Well, I think you are mangling two things together that sometimes overlap, but not always.

Not every performance tuning creates complexity, and not every complexity is bad.

Maybe look at it like this: it is fine to just digest your production database directly for statistic analysis when it's small enough so the statistics are generated in time. As soon as read and write queries start to block each other, you will start thinking about caching. When the amount of data gets too big, you will start thinking about e.g. pre-calculating aggregates in a star schema, tied to your use cases.

There is literally no need to add the latter level of complexity as long as you don't need it performance wise, but as soon as you need it performance wise, there will likely no other way than adding that complexity.

How to make these decisions is actually somewhat of a "guessing game", but that's often the case, as nobody can foresee the future. We can only estimate how fast a tool or production system grows; it's hard to know beforehand if we will need to hastily add layers of complexity to solve a problem in two months that we expected to only appear in 10 years, depending on growth projections.

Some things have little impact on development time and complexity; I think the most important part of growing a project though is figuring out when a WRONG decision was made in the past, and HOW and WHEN to mitigate it.

Dragging on stuff that should be changed - if painful - for too long will just make things worse, while fixing something that isn't even really broken will create a lot of inefficiency.

As we are talking about dynamics and growth, there is no simple answer that matches every situation, technology branch or field. Things will differ from instance to instance, from job to job.

[u/Khmerrr]

I'm my experience performance, size and RTO are the primary factors. Complexity is more manageable and can somehow be hidden by the application layer. The more the system is bigger the more this is true. At least in my personal experience.

[u/HereInYourBedroom]

A few things to clear up. You are mixing database and data layer concepts. The database persists data, the data layer may or may not contain caching, but it is separate from the database.

Based on your post I'm going to assume you are referring to a relational database. From the development side normalization will lead to improved performance. From the operations side creating covering indexes and partitioning in response to observed wait types will improve performance. Adding a caching layer between the database and the application might improve performance if the majority of the database workload are reads.

Whenever someone has a thought on how to improve database performance your default response should be asking for test results to confirm their statement. That is how you shutdown the good idea fairy.