Overcoming the N+1 Query Trap in a Custom E-Commerce Backend

When building a custom e-commerce backend from scratch, the initial development phase usually feels incredibly smooth. You map out your models, spin up your API routes, and everything works perfectly on your local development environment.

But then you deploy to your VPS, load up a real catalog, and suddenly your category pages are crawling.

This is exactly the challenge I ran into while developing the backend architecture for UvKart, our electronics and hardware e-commerce platform. We track complex product details, varying stock levels, and multiple image URLs across hundreds of technical SKUs. When a user hit the main laptops or UPS category page, the server response time spiked noticeably.

The Diagnosis: The Classic N+1 Problem

After digging into the database logs, the culprit was obvious. The ORM’s default lazy loading was silently killing performance. When rendering a grid of 50 products, the database executed one query to fetch the core products, and then 50 separate, individual queries to fetch the associated brand and image data for each item.

The Fix: Eager Loading

To solve this, I had to completely refactor the product retrieval routes. Instead of relying on standard lazy queries, I implemented eager loading—specifically using joinedload for one-to-one relationships (like the product brand) and subqueryload for one-to-many relationships (like the product image gallery).

Here is the logic shift:

Python

The slow way (Triggering N+1)

products = Product.query.filter_by(category='laptops').all()

The optimized way (Eager Loading)

products = Product.query.options(
joinedload(Product.brand),
subqueryload(Product.images)
).filter_by(category='laptops').all()
This single architectural change dropped our database queries on category pages from 51 down to just 2. The server response times went from over a second to practically instant.

If you want to see the optimized backend in action handling a live tech catalog, you can check out how snappy the category routing is over at UvKart.

Performance optimization rarely requires throwing more hardware at a problem; it almost always comes down to writing smarter queries.