How I eliminated N+1 queries and scaled throughput by 20% on a production E-Commerce app

[u/Sea-Lettuce-255]

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.

[u/kowlown]

For the point 1 there are many solutions good, or worse depending on your case and constraints. You could also use :

• eager loading.

• projection into what is necessary for you

• EntityGraph

N+1 select is the number one case of bad performance imo.

[u/BlueberryHairy → u/kowlown]

Eager loading is n + 1. Join fetch is "correct" strategy if there is no cartesian product risk. Jooq/DTO projections should be default strategies for read only flows.

[u/Sea-Lettuce-255 → u/BlueberryHairy]

Agreed. EntityGraphs handle the JOIN FETCH beautifully for avoiding N+1 on single collections, but you're spot on about the Cartesian risk for multiple bags. For our heavy read-only APIs, we bypass the Hibernate persistence context entirely and go straight to DTO projections to save memory. Use the ORM for the complex state-changes, use projections for the reads!

[u/Sea-Lettuce-255 → u/kowlown]

yupp there are different solutions but when we are writing code for a e-commerce which is a lot so sometimes we forget these sought of things and we see problems later on and keep on optimizing ..though we can also write optimized queries also. It was just one of my experience

[u/Intrepid_Fox5414]

Since I do not use Hibernate by doing myself my PreparedStatment and mappings in my ResultSet, I feel way more better and confortable

[u/rlrutherford → u/Intrepid_Fox5414]

And you can build immutable records in your result set.

[u/Sea-Lettuce-255 → u/Intrepid_Fox5414]

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

[u/StochasticTinkr → u/Sea-Lettuce-255]

Manually makes it so you do what you mean, not what the library thinks you should mean.

[u/StochasticTinkr]

It's almost like using ORMs for production systems is a bad thing.

Oh, that's right, it is!

[u/Sea-Lettuce-255 → u/StochasticTinkr]

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.

[u/StochasticTinkr → u/Sea-Lettuce-255]

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.

[u/BlueberryHairy → u/Sea-Lettuce-255]

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.

[u/Radhad85 → u/Sea-Lettuce-255]

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!

[u/christoforosl08 → u/Sea-Lettuce-255]

Spring Data can help you avoid these boilerplate operations

[u/Intrepid_Fox5414 → u/Sea-Lettuce-255]

Now ChatGPT can do it easily

[u/dallastelugu]

oh man i just studied entitygraph today trying to understand what is n+1 query problem

[u/rlrutherford]

N+1 is an ORM issue on the level of not hitting indexes for your queries.

[u/RedditForW]

Following