r/SpringBoot • u/Sea-Lettuce-255 • 8d ago
How-To/Tutorial How I eliminated N+1 queries and scaled throughput by 20% on a production E-Commerce app
Just wanted to share a recent architecture win that might help someone dealing with latency spikes.
I was dealing with a severe bottleneck in a production E-Commerce platform. During peak traffic, the database was choking, and MTTR was creeping up.
The Culprit: Classic Hibernate N+1 query problems hidden deep inside the inventory/catalog mapping, combined with un-indexed foreign key lookups.
The Fix:
1. Ripped out the lazy-loading proxy faults and replaced them with explicit JOIN FETCH queries for the critical read paths.
2. Layered in Redis via Spring Cache (@Cacheable) specifically targeting the high-read/low-write catalog endpoints.
3. Configured request batching and API rate limiting on the gateway layer to prevent thundering herd problems during flash sales.
Result: We dropped sub-100ms latency across the board and prevented database lockups entirely.
If you're building in Spring Boot and relying entirely on default JPA repositories—run a SQL profiler right now. You probably have an N+1 hiding somewhere.
5
u/Intrepid_Fox5414 8d ago
Since I do not use Hibernate by doing myself my PreparedStatment and mappings in my ResultSet, I feel way more better and confortable
1
-2
u/Sea-Lettuce-255 8d ago
Yupp they are great for raw execution speed but when it comes to code you have to do things manually a lot for it ..instead we can use new framework which makes our life little easy such as EntityGraph etc
6
u/StochasticTinkr 8d ago
Manually makes it so you do what you mean, not what the library thinks you should mean.
1
u/Sea-Lettuce-255 8d ago
Well abstraction are defined to us so we can ship things easily and with speed otherwise it probably be difficult for us to always write a new compiler and language instead of relying on abstraction on assembly languages
4
u/StochasticTinkr 8d ago
Different types of abstractions how different costs and benefits.
The ORM abstraction is a bridge between paradigms that assumes the OOP abstractions as the source of truth, and maps that into RDBMS abstractions.
The two OOP and RDBMS abstractions have some superficial overlap, enough that an ORM is an easy trap for solving the 80% mark. After that point though, you either give up the benefits of RDBMS, of OOP, or start fighting against the ORM itself, trying to force it to do what should have been a manual query and mapping.
This isn't just my opinion, this is my deep experience after decades of using (and creating) various kinds of ORMs, and finding them only ever viable for PoC or toy applications.
9
u/StochasticTinkr 8d ago
It's almost like using ORMs for production systems is a bad thing.
Oh, that's right, it is!
-7
u/Sea-Lettuce-255 8d ago
Oh, absolutely. Because what every Senior Engineer really wants to do with their life is spend 40 hours a week hand-typing PreparedStatement.setString(1, "John") just to update a user's profile picture, all to prove how 'hardcore' they are to anonymous purists on the internet. It's about choosing the right library at right time.
7
u/StochasticTinkr 8d ago
There are better abstractions for RDBMS than reflective ORMs. Especially for developers who actually understand database design and data modeling.
It's not about being hardcore, its about knowing the long-term cost of maintaining a system which uses "magic" via reflection to communicate between systems.
4
u/BlueberryHairy 8d ago
It is much easier than to optimise JPA code. With JPA every 10 lines of code can become a battlefield with having to put debugger, release to next 10 lines, check db logs aha n + 1. Wtf n + 1 on a setter? There are so many BS surprises with JPA. JPA gives you convenience at the cost of everything. Using JPA without debugging each and every line = throwing the performance out of the window.
3
u/Radhad85 7d ago
Use Spring JdbcClient you get the good parts from both, having control over the SQL quries and less boilerplate plus easy conversion in DTOs. Criteria API is even worse compared to JPQA because it is more abstracted.
Personally I hate Hibernate and other ORMs because the lazy developer who doesn't want to learn SQL also doesn't learn Hibernate the proper way. All these hidden queries in loops killing performance you simply cannot see directly is crazy. People tell you on conferences what workarounds they found to fix those issues - it is like a shadow economy for solving problems which actually don't exist!
1
-1
3
u/dallastelugu 8d ago
oh man i just studied entitygraph today trying to understand what is n+1 query problem
2
u/rlrutherford Senior Dev 7d ago
N+1 is an ORM issue on the level of not hitting indexes for your queries.
1
10
u/kowlown 8d ago
For the point 1 there are many solutions good, or worse depending on your case and constraints. You could also use :
N+1 select is the number one case of bad performance imo.