Everything Worked…But Not Well
APIs are affected by the way data is retrieved from the database, and that's something that affected a recent teammate and me during development. In our regular development process, during review and maintenance, the APIs worked, responses came back well, no visible errors. But during this process, we noticed that things started to slow down. Endpoints that used to respond fast suddenly began taking longer, CPU usage on the database increased, and retrieval speed became terrible under load.
At first, we thought:
- Maybe the server wasn't powerful enough
- Maybe network latency was the issue
- Maybe too many requests were hitting the API
But the real issue was hiding inside our database queries.
What Actually Is The N+1 Query Problem?
The N+1 query problem is one of the most common performance issues in backend development, especially when working with relational databases and ORMs(as I, a fan of Django, do). I’m gonna explain this more in SQL terms using retrieval speed and database load.
The problem happens when your application performs:
- 1 query to retrieve parent records
- Then N extra queries to retrieve related child records
So instead of making one optimized query, the application keeps asking the database more questions repeatedly. This increases the query count, read time, database load, and the API response time. 1 issue, several systems affected.
A Simple Example
Let's say we have 2 tables:
- Users
- Orders
We first retrieve all users.
SELECT * FROM users;
Now imagine there are 100 users. Then, for every user retrieved, another query runs to fetch their orders.
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
And it keeps going. So instead of 1 query, you now have 101 queries. That extra load destroys retrieval speed.
Why This Problem Is Dangerous
The dangerous part is that during development, you might not even notice it. If your database only has just about 5 users and 10 orders, Everything still feels fast.
But once production data grows:
- Hundreds of users
- Thousands of records
- More API requests
The database starts struggling badly. This is why some APIs feel fast during testing but start to slow down after deployment.
The Mistake We Made
The issue wasnt obvious at first because the code itself looked completely normal. We had an endpoint that needed to return users alongside their related orders.
Something like:
- User information
- Their recent orders
- Order counts
- Related details
The endpoint worked perfectly during development. But internally, Django was fetching related records separately for every user being serialized. Which meant the API kept hitting the database repeatedly behind the scenes.
The code looked clean.
users = User.objects.all()
serializer = UserSerializer(users, many=True)
But in the serializer, related order data was being accessed for every single user.
class UserSerializer(serializers.ModelSerializer):
orders = OrderSerializer(many=True)
class Meta:
model = User
fields = ['id', 'name', 'orders']
This is where the real problem started. When Django tried to serialize the response, it kept querying orders separately per user. So if there were 500 users, the system would do:
- 1 query to retrieve users
- Hundreds of additional queries to retrieve orders
That is the N+1 query problem. The scary part is that nothing looked wrong from the surface. The endpoint returned the correct data and no errors, just slow performance. And as traffic increased, the database load became worse. It was slow because the application kept repeatedly asking for related data instead of retrieving it efficiently.
The fix was adding query optimization directly to the queryset.
users = User.objects.prefetch_related('orders')
serializer = UserSerializer(users, many=True)
That single optimization drastically reduced the number of database hits. Instead of querying orders repeatedly for every user, Django retrieved them efficiently in bulk. Same endpoint and response, much better performance.
Understanding select_related()
One major fix for this problem in Django is:
select_related()
select_related() is used for relationships like:
- ForeignKey
- OneToOneField
What it basically does is perform a SQL JOIN and retrieve related data in a single query. Instead of this:
users = User.objects.all()
for user in users:
print(user.profile.phone)
Which may generate multiple queries…
You do this:
users = User.objects.select_related('profile')
for user in users:
print(user.profile.phone)
Now Django joins the tables together and retrieves everything at once.
So instead of:
- 1 query for users
- Multiple queries for profiles
You now get:
- 1 optimized query
Much faster and cleaner.
Understanding prefetch_related()
Another optimization is:
prefetch_related()
This is used mostly for:
- ManyToMany relationships
- Reverse ForeignKey relationships
Unlike select_related(), this does not use SQL JOINs directly. Instead, Django performs separate queries but combines the results efficiently in memory.
For example:
users = User.objects.prefetch_related('orders')
Django may run:
SELECT * FROM users;
SELECT * FROM orders WHERE user_id IN (1,2,3,4...);
Instead of:
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
Which is far more efficient. So rather than hundreds of queries, you may only have two.
The Difference Between select_related() And prefetch_related()
select_related()
- Uses SQL JOINs
- Best for ForeignKey and OneToOne relationships
- Retrieves related data in a single query
prefetch_related()
- Uses multiple optimized queries
- Combines data in Python memory
- Best for ManyToMany and reverse relationships
Both solve the same problem differently.
The Result
After fixing the queries:
- Response times dropped massively
- Database load reduced
- API became stable again
- Retrieval speed improved significantly
The funny thing is that we didnt upgrade the server. We didnt increase RAM. We didnt change infrastructure. We simply reduced unnecessary queries.
What This Experience Taught Me
One thing this experience taught me is that backend performance is not always about server power. Sometimes your database is suffering simply because of how you are querying it. A badly written retrieval pattern can quietly destroy performance even when your infrastructure is good. Now whenever I build endpoints, I dont just ask “Does this work?” I also ask “How many queries is this generating behind the scenes?” Because sometimes the biggest backend problem is not the logic. Its the retrieval pattern hiding underneath it. Let me know what you think, thanks for reading, like, share, comment and follow for more.
SOCIAL SHARE CARD GENERATOR