EduQuery API

Overview

When building this e-commerce platform for online courses, the catalog API needed to return courses alongside nested relationships like instructors, categories, and active discounts. Because of how the Django ORM lazily evaluates data, a naive implementation resulted in numerous hidden database queries firing on a single page load. The goal was to refactor the data layer to optimize query execution without compromising the complex filtering requirements.

Problem Statement

The N+1 Query Trap

The /api/courses/ endpoint allowed users to filter by category, price range, and instructor rating. Initially, fetching a list of courses resulted in a separate database hit for each course's Instructor and Review relationship due to the N+1 query problem. During local testing with Locust, simulating concurrent requests quickly saturated the local database connections and degraded response times.

Solution

I rewrote the queryset logic to fetch related data upfront. I utilized Django's select_related() to perform SQL INNER JOINs for single-valued relationships like Instructor and Category. Then, I used prefetch_related() for many-to-many relationships to group queries efficiently at the Python level.

To optimize search filters, I added database indexes (db_index=True) to the most frequently queried fields. Finally, for the static categories endpoint, I implemented Redis caching with a reasonable TTL, bypassing the database entirely for data that rarely changes.

Lessons Learned

This project taught me how to profile Django applications using Django Debug Toolbar. I learned that minimizing the number of database queries is often more impactful than micro-optimizing Python code, and I gained hands-on experience identifying and fixing N+1 query regressions during development.