Your Django API can look perfectly clean in Python and still be painfully slow.#

You might write:

orders = Order.objects.all()
 

and think:

"It's just one query. How bad could it be?"

Then your API works perfectly with 10 records.

You test it with 100 records.

Still fine.

Then production has 100,000 orders, and suddenly an endpoint that used to respond in 200 milliseconds takes several seconds.

You start adding more Python code.

Maybe you add caching.

Maybe you increase the server size.

Maybe you add more workers.

But sometimes the real problem is much simpler:

Your application is asking the database to do far more work than it needs to.#

This is why Django query optimization matters.

The goal isn't to make every query complicated.

The goal is to understand what Django is actually asking the database to do, identify unnecessary work, and make the database return only what your application really needs.

In this article, we'll start with a slow Django API and progressively optimize it.

We'll look at:

• How Django ORM queries actually work
• How to detect slow queries
• The N+1 query problem

select_related()

prefetch_related()

only() and defer()

values() and values_list()

exists() vs count()

count() vs len()

• Filtering efficiently
• Database indexes

select_for_update()

• Pagination
• Query ordering
• Bulk operations

annotate() and aggregation

• How to inspect SQL with EXPLAIN
• Why optimization should start with measurement
• How to build a practical query-optimization workflow

The most important idea throughout this article is:

Don't optimize Django code. Optimize the work Django asks the database to perform.

1. Where Does API Performance Actually Go?#

Let's start with a simple API. Imagine an e-commerce application with these models:

class Customer(models.Model):
    name = models.CharField(max_length=255)
    email = models.EmailField()
 
 
class Order(models.Model):
    customer = models.ForeignKey(
        Customer,
        on_delete=models.CASCADE,
    )
    status = models.CharField(max_length=50)
    total_amount = models.DecimalField(
        max_digits=12,
        decimal_places=2,
    )
    created_at = models.DateTimeField(auto_now_add=True)
 
 
class OrderItem(models.Model):
    order = models.ForeignKey(
        Order,
        on_delete=models.CASCADE,
        related_name="items",
    )
    product_name = models.CharField(max_length=255)
    quantity = models.PositiveIntegerField()
    price = models.DecimalField(
        max_digits=12,
        decimal_places=2,
    )
 

Now suppose we build an endpoint:

GET /api/orders/

The endpoint returns:

[
    {
        "id": 1,
        "customer": "Rong",
        "total": 150.00
    }
]
 

A simple Django implementation might be:

def get_orders(request):
    orders = Order.objects.all()
 
    data = []
 
    for order in orders:
        data.append({
            "id": order.id,
            "customer": order.customer.name,
            "total": order.total_amount,
        })
 
    return JsonResponse(data, safe=False)
 

It looks harmless. But let's ask an important question:

How many SQL queries does this code execute?

If there are 100 orders, you might expect:

1 query

because:

Order.objects.all()

is one query. But that's not necessarily what happens.

2. Django ORM Is Lazy#

Before optimizing queries, you need to understand one fundamental Django ORM behavior:

QuerySets are lazy.

When you write:

orders = Order.objects.all()

Django doesn't immediately send a query to PostgreSQL.

You've created a QuerySet.

The database query is executed when Django actually needs the results.

For example:

orders = Order.objects.all()
 
print(orders)

or:

for order in orders:
    ...
 

or:

list(orders)

or:

orders.first()

can cause database access.

This is important because your Python code and your database queries are not necessarily a one-to-one relationship.

This:

orders = Order.objects.filter(status="pending")

doesn't necessarily mean:

"Run a SQL query right now."

It means:

"Build a query that can be executed when the results are needed."

3. One Line of Python Can Become Many SQL Queries#

Let's return to our example.

orders = Order.objects.all()
 
for order in orders:
    print(order.customer.name)
 

The first iteration causes Django to fetch the orders:

SELECT *
FROM order;

But then we access:

order.customer
 

The customer is a related object.

Django may need another query:

SELECT *
FROM customer
WHERE id = 1;
 

Then another order:

SELECT *
FROM customer
WHERE id = 2;

Then:

SELECT *
FROM customer
WHERE id = 3;

And so on.

If there are 100 orders, you could end up with approximately:

1 query for orders
+
100 queries for customers
 
= 101 queries
 

Your Python code looks simple.

Your database sees something very different.

This is one of the most famous Django performance problems:

The N+1 Query Problem#

4. Understanding the N+1 Query Problem#

The N+1 problem means that your application executes:

1 query
+
N additional queries

where N is the number of objects being processed.

For example:

orders = Order.objects.all()
 
for order in orders:
    print(order.customer.name)
 

Conceptually:

flowchart TD
    A[Fetch 100 Orders] --> B[1 Query]

    B --> C[Order 1]
    B --> D[Order 2]
    B --> E[Order 3]
    B --> F[...]
    B --> G[Order 100]

    C --> H[Query Customer 1]
    D --> I[Query Customer 2]
    E --> J[Query Customer 3]
    G --> K[Query Customer 100]

The result is:

1 + 100 = 101 queries

And this can become much worse.

Imagine an API returning:

Orders
  └── Customer
  └── Sales Representative
  └── Warehouse
  └── Currency
  └── Payment Terms
 

You can accidentally create hundreds or thousands of queries.

This is why simply looking at the Python code isn't enough.

You need to think about the database.

5. The First Optimization: select_related()#

For a ForeignKey or OneToOne relationship, Django provides:

select_related()

Instead of:

orders = Order.objects.all()

we can write:

orders = Order.objects.select_related("customer")

Now Django can fetch the order and customer using a SQL join.

Conceptually:

flowchart LR
    A[Django] --> B[PostgreSQL]
    B --> C[Orders]
    B --> D[Customers]
    C --> E[JOIN]
    D --> E
    E --> F[Combined Result]

Instead of:

SELECT orders
SELECT customer 1
SELECT customer 2
SELECT customer 3
...

the database can perform a join.

Conceptually:

SELECT
    order.*,
    customer.*
FROM order
INNER JOIN customer
    ON customer.id = order.customer_id;

Now our code can remain simple:

orders = Order.objects.select_related("customer")
 
for order in orders:
    print(order.customer.name)

The important difference is that accessing:

order.customer.name

doesn't require another query for every order.

6. When Should You Use select_related()?#

Use select_related() primarily for relationships where the related object can be joined directly:

• ForeignKey
• OneToOneField

For example:

Order.objects.select_related("customer")

Or multiple relationships:

Order.objects.select_related(
    "customer",
    "sales_rep",
    "currency",
)

You can also follow relationships:

Order.objects.select_related(
    "customer__company",
)

This is useful when your API needs data several relationships deep.

But don't blindly add every relationship to select_related().

You should fetch what the endpoint actually needs.

7. What About ManyToMany and Reverse Relationships?#

Now suppose we want to return the items in every order.

We have:

class OrderItem(models.Model):
    order = models.ForeignKey(
        Order,
        on_delete=models.CASCADE,
        related_name="items",
    )

We might write:

orders = Order.objects.all()
 
for order in orders:
    for item in order.items.all():
        print(item.product_name)

Now we have another N+1 problem. The first query gets orders.

Then every:

order.items.all()

can trigger another query.

If we have 100 orders:

1 query
+
100 item queries
 
= 101 queries

But select_related() isn't the right tool here.

Why?

Because an order can have many items.

Joining everything into one result can multiply rows.

This is where prefetch_related() becomes useful.

8. prefetch_related()#

We can write:

orders = Order.objects.prefetch_related("items")

Now Django retrieves the orders and their related items efficiently.

Conceptually:

flowchart LR
    A[Django] --> B[Query Orders]
    A --> C[Query Order Items]

    B --> D[Order Objects]
    C --> E[Item Objects]

    D --> F[Match Items to Orders]
    E --> F

Instead of:

1 order query
+
N item queries

we can get something closer to:

1 order query
+
1 item query

So 101 queries can become approximately 2.

That's a massive difference.

9. select_related() vs prefetch_related()#

This distinction is worth remembering.

A simple mental model:

select_related() uses a SQL JOIN.

prefetch_related() performs separate queries and combines the results in Python.

For example:

Order.objects.select_related("customer")

and:

Order.objects.prefetch_related("items")

You can also combine them:

orders = Order.objects.select_related(
    "customer",
).prefetch_related(
    "items",
)

Now one endpoint can efficiently load both types of relationships.

10. You Can Prefetch With Its Own QuerySet#

Sometimes you don't want every related object.

Suppose an order has hundreds of items, but your API only needs active items.

Instead of:

Order.objects.prefetch_related("items")

you can customize the prefetch:

from django.db.models import Prefetch
 
 
orders = Order.objects.prefetch_related(
    Prefetch(
        "items",
        queryset=OrderItem.objects.filter(
            is_active=True,
        ),
    )
)
 

This is much better than loading unnecessary records.

You can think about it as:

Don't just optimize the number of queries. Optimize what each query retrieves.

11. Don't Fetch Columns You Don't Need#

Another common performance problem is retrieving entire database rows when you only need a few fields.

Imagine:

Customer.objects.all()

but the API only needs:

id
name

The database might return many additional columns.

For example:

id
name
email
phone
address
description
created_at
updated_at
...
 

If the table is wide, this can become expensive.

Django provides:

only()

and:

defer()

For example:

customers = Customer.objects.only(
    "id",
    "name",
)

This tells Django that you only need those fields initially.

12. Be Careful With only()#

only() is useful, but it isn't automatically a performance win in every situation.

Suppose you write:

customers = Customer.objects.only(
    "id",
    "name",
)

and later:

print(customer.email)

Django may need another query to load the deferred field.

So you could accidentally create additional queries.

This is why query optimization isn't about blindly adding optimization methods.

You need to understand what the code actually accesses.

13. values() Can Be Even Better for Read-Only APIs#

Sometimes you don't need Django model objects at all.

Suppose you want:

[
    {
        "id": 1,
        "name": "Rong"
    }
]

You can use:

Customer.objects.values(
    "id",
    "name",
)

Instead of returning model instances, Django returns dictionaries.

For example:

customers = Customer.objects.values(
    "id",
    "name",
)

Result:

[
    {
        "id": 1,
        "name": "Rong",
    },
]
 

This can be useful when you're building simple read-only data structures.

14. Usevalues_list()#

If you only need one or a few fields, values_list() can be even more convenient.

For example:

customer_ids = Customer.objects.values_list(
    "id",
    flat=True,
)
 

Result:

[1, 2, 3, 4, 5]

Instead of:

customers = Customer.objects.all()
 
customer_ids = [
    customer.id
    for customer in customers
]
 

The database only returns the field you need.

This is a simple but useful optimization.

15. Don't Use count() When You Only Need to Know If Something Exists#

Imagine this:

if Order.objects.filter(
    customer_id=customer_id,
).count() > 0:
    ...

You're asking the database:

"How many matching records are there?"

But you don't actually care about the number.

You only care:

"Does at least one exist?"

Use:

if Order.objects.filter(
    customer_id=customer_id,
).exists():
    ...
 

The intention is clearer, and Django can use an existence-oriented query.

This is an excellent example of a broader principle:

Tell the database exactly what information you need.

16. count() vs len()#

Another common mistake is:

orders = Order.objects.all()
 
if len(orders) > 0:
    ...

If you need the count of database records, prefer:

orders.count()

The important thing is understanding when each one makes sense.

If the QuerySet hasn't been evaluated and you only need the database count:

orders.count()

is generally appropriate.

If you've already loaded the objects and need their length, using:

len(orders)

can use the already-loaded results.

So there isn't a universal rule of:

"Always use count."

The better rule is:

Know whether your QuerySet has already been evaluated and what information you actually need.

17. Filter as Early as Possible#

Suppose your database contains one million orders.

But your API only needs pending orders.

Don't do:

orders = Order.objects.all()
 
pending_orders = [
    order
    for order in orders
    if order.status == "pending"
]

You're pulling potentially one million records into your application.

Instead:

orders = Order.objects.filter(
    status="pending",
)

Now PostgreSQL does the filtering.

Conceptually:

flowchart LR
    A[1,000,000 Orders] --> B[Database Filter]
    B --> C[10,000 Pending Orders]
    C --> D[Django]

The database is designed to filter data efficiently.

Let it do database work.

18. Database Indexes#

Now we reach one of the most important database-level optimizations:

Indexes.

Suppose your API frequently executes:

Order.objects.filter(
    status="pending",
)
 

If the table contains millions of rows, PostgreSQL may need to inspect a large amount of data depending on the query and available indexes.

An index can make lookups much faster.

For example:

class Order(models.Model):
    status = models.CharField(
        max_length=50,
        db_index=True,
    )
 

Now Django creates a database index for that field through migrations.

You can also define indexes in Meta:

class Order(models.Model):
    ...
 
    class Meta:
        indexes = [
            models.Index(
                fields=["status"],
            ),
        ]
 

19. But Don't Index Everything#

Indexes aren't free.

An index:

• consumes storage,
• needs to be maintained,
• can slow down writes,
• increases database overhead.

So adding:

db_index=True
 

to every field isn't good optimization.

Indexes should support actual query patterns.

For example, if your application frequently performs:

Order.objects.filter(
    customer_id=customer_id,
    status="pending",
)
 

a composite index might be useful:

class Meta:
    indexes = [
        models.Index(
            fields=["customer", "status"],
        ),
    ]
 

The right index depends on how your application queries the database.

20. The Order of Fields in a Composite Index Matters#

Suppose you have:

models.Index(
    fields=["customer", "status"],
)
 

This index is designed around that column order.

If your application commonly queries:

Order.objects.filter(
    customer_id=123,
    status="pending",
)
 

the index can be useful.

But if your application primarily searches:

Order.objects.filter(
    status="pending",
)
 

you should investigate whether the chosen index is actually helping.

Don't design indexes based only on the model.

Design them based on real query patterns.

21. Pagination Is Query Optimization Too#

Imagine your API returns:

GET /api/orders/
 

and there are:

5,000,000 orders
 

Returning all five million records is obviously a bad idea.

Even if the database query itself is reasonably fast, your application still has to:

• retrieve the records,
• create Python objects,
• serialize them,
• send them over the network.

Use pagination.

For example:

GET /api/orders/?page=1&page_size=50
 

Now you're asking for a small portion of the dataset.

Conceptually:

flowchart LR
    A[5,000,000 Orders] --> B[Pagination]
    B --> C[50 Orders]
    C --> D[Serializer]
    D --> E[API Response]
 

Pagination reduces database, memory, serialization, and network work.

22. Ordering Can Become Expensive#

Consider:

Order.objects.order_by("-created_at")
 

If you're frequently ordering a huge table by created_at, an appropriate index may help.

For example:

class Meta:
    indexes = [
        models.Index(
            fields=["-created_at"],
        ),
    ]
 

But again, don't assume.

The database query planner decides how to execute the query.

You should inspect the actual query plan.

That brings us to one of the most useful tools for database optimization.

23. EXPLAIN#

When a query is slow, don't guess.

Ask PostgreSQL how it plans to execute the query.

Django provides:

queryset.explain()
 

For example:

queryset = Order.objects.filter(
    status="pending",
)
 
print(queryset.explain())
 

You might see information such as:

Seq Scan
Index Scan
Bitmap Heap Scan
Nested Loop
Hash Join
Sort
 

The output tells you how PostgreSQL plans to access the data.

24. Why EXPLAIN Matters#

Imagine you add an index:

status = models.CharField(
    max_length=50,
    db_index=True,
)
 

You might assume:

"The query is now optimized."

But PostgreSQL might still decide not to use the index.

Why?

Because the optimizer considers things such as:

• table size,
• selectivity,
• estimated cost,
• available indexes,
• statistics,
• sorting,
• joins.

For example, if 95% of rows have:

status = "active"
 

an index on status may not provide much benefit for that particular query.

This is why optimization should be based on evidence rather than assumptions.

25. Bulk Operations#

Another common performance problem is updating records one at a time.

Imagine:

for order in orders:
    order.status = "archived"
    order.save()
 

If there are 10,000 orders, you could execute thousands of SQL UPDATE statements.

When possible, use:

Order.objects.filter(
    created_at__lt=cutoff_date,
).update(
    status="archived",
)
 

Now the database can perform the update as a single operation.

Conceptually:

flowchart LR
    A[10,000 Orders] --> B[One UPDATE Query]
    B --> C[Database Updates Records]
 

Instead of:

Python
 ↓
UPDATE 1
UPDATE 2
UPDATE 3
...
UPDATE 10,000
 

This can dramatically reduce database round trips.

26. Bulk Create#

The same idea applies to inserts.

Avoid:

for item in items:
    OrderItem.objects.create(
        order=order,
        product_name=item["name"],
        quantity=item["quantity"],
    )
 

for large batches when you don't need per-object custom behavior.

Instead:

OrderItem.objects.bulk_create(
    [
        OrderItem(
            order=order,
            product_name=item["name"],
            quantity=item["quantity"],
        )
        for item in items
    ]
)
 

This can significantly reduce the number of database round trips.

But remember that bulk operations have behavioral differences from calling save() on every instance, so use them when their semantics fit your application.

27. Don't Query Inside Loops#

This deserves its own rule.

Avoid:

for customer_id in customer_ids:
    customer = Customer.objects.get(
        id=customer_id,
    )
 

If there are 1,000 IDs:

1,000 queries
 

Instead:

customers = Customer.objects.filter(
    id__in=customer_ids,
)
 

Now you can retrieve the required customers in one database operation.

The general pattern is:

Bad:
 
Python loop
    ↓
Database
    ↓
Python loop
    ↓
Database
    ↓
...
 
Better:
 
Python
    ↓
One well-designed database query
 

28. Transactions and select_for_update()#

Performance isn't only about reading.

Sometimes you need safe concurrent updates.

Imagine two workers try to modify the same inventory record at exactly the same time.

You might use:

from django.db import transaction
 
 
with transaction.atomic():
    stock = (
        Stock.objects
        .select_for_update()
        .get(product_id=product_id)
    )
 
    stock.quantity -= 1
    stock.save()
 

select_for_update() tells the database to lock the selected row for the duration of the transaction.

This isn't simply a performance optimization.

It's a concurrency and correctness tool.

But understanding it is important because database locking can itself affect performance.

If transactions hold locks for too long, other transactions may have to wait.

So:

Optimize not only how fast queries execute, but also how long transactions hold database resources.

29. Avoid Huge Transactions#

Consider:

with transaction.atomic():
    process_10_000_orders()
 

If processing takes several minutes, the transaction may remain open for a long time.

That can cause:

• locks to remain active,
• other transactions to wait,
• more database resources to be consumed,
• larger transaction-related overhead.

Transactions should protect the operations that need atomicity.

They shouldn't automatically wrap an enormous amount of unrelated work.

30. Don't Move Database Work Into Python#

A common beginner optimization mistake is trying to replace database work with Python.

For example:

orders = Order.objects.all()
 
for order in orders:
    if order.status == "pending":
        ...
 

Instead:

orders = Order.objects.filter(
    status="pending",
)
 

Another example:

customers = Customer.objects.all()
 
for customer in customers:
    if customer.id in customer_ids:
        ...
 

Instead:

customers = Customer.objects.filter(
    id__in=customer_ids,
)
 

The database is built to perform filtering, joining, aggregation, sorting, and other data operations.

Use it.

31. Aggregation Instead of Loading Everything#

Suppose you want the total sales amount.

A bad approach might be:

orders = Order.objects.all()
 
total = sum(
    order.total_amount
    for order in orders
)
 

You're loading every order into Python.

Instead, let PostgreSQL calculate the sum.

from django.db.models import Sum
 
 
total = Order.objects.aggregate(
    total=Sum("total_amount"),
)
 

The database performs the aggregation.

This is much more efficient for large datasets.

The same principle applies to:

Count
Sum
Avg
Min
Max
 

and more complex annotations.

32. annotate() Can Move Computation Into SQL#

Suppose we want the number of items in each order.

Instead of:

for order in orders:
    item_count = order.items.count()
 

we can use:

from django.db.models import Count
 
 
orders = Order.objects.annotate(
    item_count=Count("items"),
)
 

Now each order can have:

order.item_count
 

available from the query.

This can prevent another query per order.

Again, the principle is:

If the database can calculate the information efficiently, don't retrieve a large dataset and calculate it one object at a time in Python.

33. A Slow API Example#

Let's combine several problems.

Suppose we have:

def get_orders(request):
    orders = Order.objects.all()
 
    result = []
 
    for order in orders:
        result.append({
            "id": order.id,
            "customer": order.customer.name,
            "items": [
                {
                    "name": item.product_name,
                    "quantity": item.quantity,
                }
                for item in order.items.all()
            ],
            "item_count": order.items.count(),
        })
 
    return JsonResponse(result, safe=False)
 

This endpoint may look reasonable.

But think about what happens.

For 100 orders:

Query orders
    ↓
Query customer for each order
    ↓
Query items for each order
    ↓
Count items for each order
 

You can quickly end up with hundreds of queries.

34. Optimizing the API#

We can start by identifying the relationships.

Order
 ├── customer      → ForeignKey
 └── items         → Reverse ForeignKey
 

So:

select_related("customer")
 

for the customer.

And:

prefetch_related("items")
 

for the items.

Then:

from django.db.models import Count
 
 
orders = (
    Order.objects
    .select_related("customer")
    .prefetch_related("items")
    .annotate(
        item_count=Count("items"),
    )
)
 

Now the architecture is much more efficient.

flowchart TD
    A[API Request] --> B[Optimized QuerySet]
 
    B --> C[select_related customer]
    B --> D[prefetch_related items]
    B --> E[annotate item_count]
 
    C --> F[Database]
    D --> F
    E --> F
 
    F --> G[Orders + Related Data]
    G --> H[Serializer]
    H --> I[API Response]
 

The exact number of queries depends on the query construction and related operations, but the important point is that we have eliminated the obvious per-object database access pattern.

35. Don't Optimize Without Measuring#

This is probably the most important lesson in the entire article.

You shouldn't see:

Order.objects.all()
 

and immediately replace it with:

Order.objects.select_related(...)
 

without understanding whether the related data is actually needed.

Optimization should follow a process.

flowchart TD
    A[API Is Slow] --> B[Measure]
    B --> C[Identify Slow Query]
    C --> D[Inspect SQL]
    D --> E[Inspect Query Plan]
    E --> F[Apply Optimization]
    F --> G[Measure Again]
    G --> H{Improved?}
    H -->|Yes| I[Keep Change]
    H -->|No| D
 

The workflow is:

Measure
   ↓
Find bottleneck
   ↓
Understand query
   ↓
Optimize
   ↓
Measure again
 

Not:

API is slow
   ↓
Add caching
   ↓
Add indexes everywhere
   ↓
Add select_related everywhere
   ↓
Hope it works
 

36. How to Inspect Queries in Django#

During development, you can inspect executed queries.

For example:

from django.db import connection
 
print(len(connection.queries))
 
for query in connection.queries:
    print(query)
 

You can also use Django Debug Toolbar during development to inspect:

• SQL queries
• query count
• duplicate queries
• query execution time

This is particularly useful when diagnosing N+1 problems.

If an endpoint unexpectedly performs:

1 query
 

versus:

101 queries
 

the difference becomes immediately visible.

37. Query Optimization in Django REST Framework#

The same principles apply to DRF.

Suppose you have:

class OrderViewSet(ModelViewSet):
    queryset = Order.objects.all()
    serializer_class = OrderSerializer
 

And your serializer contains:

class OrderSerializer(serializers.ModelSerializer):
    customer_name = serializers.CharField(
        source="customer.name",
    )
 
    class Meta:
        model = Order
        fields = [
            "id",
            "customer_name",
        ]
 

A developer might only look at the serializer and think:

"It's just one field."

But:

source="customer.name"
 

accesses a related object.

If the queryset doesn't load the customer efficiently, you can create an N+1 problem.

So optimize the queryset:

class OrderViewSet(ModelViewSet):
    serializer_class = OrderSerializer
 
    def get_queryset(self):
        return Order.objects.select_related(
            "customer",
        )
 

Now the serializer can access:

order.customer.name
 

without triggering a separate query for every order.

38. A Very Important DRF Rule#

When using Django REST Framework:

Don't only optimize your serializer. Optimize the QuerySet that feeds the serializer.

Your serializer determines what data is accessed.

Your QuerySet determines how that data is loaded.

They need to work together.

For example:

Serializer
    ↓
Needs customer.name
    ↓
QuerySet
    ↓
select_related("customer")
 

Or:

Serializer
    ↓
Needs order.items
    ↓
QuerySet
    ↓
prefetch_related("items")
 

This relationship becomes especially important in large APIs.

39. Don't Accidentally Destroy Your Optimization#

Suppose you create:

queryset = Order.objects.select_related(
    "customer",
)
 

Then later somewhere else:

queryset = queryset.select_related(None)
 

or construct a completely new queryset without the optimization.

Your original optimization is gone.

This is why query construction should be deliberate and easy to understand.

In larger applications, it's often useful to keep database-loading decisions close to the view/queryset that owns the API use case.

40. Optimization Is About Data Flow#

A useful way to think about Django query optimization is to follow the data.

Ask:

What data does the API need?
        ↓
Which database tables contain it?
        ↓
Which relationships connect those tables?
        ↓
How many rows do I need?
        ↓
Which columns do I need?
        ↓
Can the database filter it?
        ↓
Can the database aggregate it?
        ↓
Do I need joins?
        ↓
Do I need prefetching?
        ↓
Does an index support the query?
 

This way of thinking is much more powerful than memorizing Django ORM methods.

41. A Practical Optimization Checklist#

When you encounter a slow Django API, walk through this checklist.

1. Count the queries#

Ask:

How many SQL queries does this endpoint execute?
 

If you expected 5 and see 500, investigate immediately.

2. Look for loops containing queries#

Watch for:

for item in items:
    Model.objects.get(...)
 

or:

for order in orders:
    order.customer
 

or:

for order in orders:
    order.items.all()
 

These are common N+1 patterns.

3. Check relationships#

Ask:

ForeignKey?
    → select_related()
 
OneToOne?
    → select_related()
 
ManyToMany?
    → prefetch_related()
 
Reverse ForeignKey?
    → prefetch_related()
 

4. Retrieve only what you need#

Consider:

values()
values_list()
only()
defer()
 

when appropriate.

5. Filter in the database#

Prefer:

Order.objects.filter(status="pending")
 

over retrieving everything and filtering in Python.

6. Use database aggregation#

Prefer:

Order.objects.aggregate(...)
 

over loading thousands of rows and calculating totals in Python.

7. Check indexes#

Look at your actual query patterns.

Don't add indexes randomly.

8. Use pagination#

Don't return thousands or millions of records in one API response.

9. Inspect the query plan#

Use:

queryset.explain()
 

when a query is genuinely slow or complex.

10. Measure again#

After optimization:

Before:
    150 queries
    2.4 seconds
 
After:
    5 queries
    280 ms
 

Now you know the optimization actually helped.

42. The Biggest Mistake: Optimizing Too Early#

There's another side to query optimization.

You don't want to turn every simple query into an unreadable piece of ORM code just because it might be faster.

For example:

Order.objects.select_related(
    "customer",
    "customer__company",
    "customer__company__region",
    "currency",
    "warehouse",
    "sales_rep",
    "sales_rep__department",
    ...
)
 

might look "optimized."

But if the endpoint only needs:

order.id
order.customer.name
 

you've created unnecessary complexity and potentially unnecessary database work.

Good optimization is not:

"Fetch everything in advance."

Good optimization is:

"Fetch exactly what this use case needs, in the most efficient way."

43. Keep Your Code Readable#

Performance improvements shouldn't make your application impossible to maintain.

Compare:

orders = (
    Order.objects
    .select_related("customer")
    .prefetch_related("items")
    .annotate(item_count=Count("items"))
    .filter(status="pending")
    .order_by("-created_at")
)
 

with a giant chain containing every relationship in the database.

The first version communicates something useful:

"This API needs pending orders, their customers, their items, item counts, and newest orders first."

That's readable.

Optimization should improve the system without making the code harder for the next developer to understand.

44. A Better Way to Think About Performance#

When a Django API is slow, don't immediately ask:

"How can I make Django faster?"

Instead ask:

"What work is my application asking the database to perform?"

Then break the problem down.

flowchart TD
    A[Slow API] --> B[Application Layer]
    A --> C[Database Layer]
    A --> D[Network / External Services]
 
    B --> E[Serialization]
    B --> F[Python Processing]
 
    C --> G[Query Count]
    C --> H[Query Complexity]
    C --> I[Indexes]
    C --> J[Locks]
 
    D --> K[External API Latency]
 

Sometimes the bottleneck is the database.

Sometimes it's serialization.

Sometimes it's an external API.

Sometimes it's simply returning too much data.

The point is to measure before changing things.

45. From 101 Queries to a Better API#

Let's revisit where we started.

Our original code:

orders = Order.objects.all()
 
for order in orders:
    print(order.customer.name)
 

could produce an N+1 query pattern.

We improve it:

orders = Order.objects.select_related(
    "customer",
)
 

Now imagine we also need items:

orders = (
    Order.objects
    .select_related("customer")
    .prefetch_related("items")
)
 

And perhaps we only need pending orders:

orders = (
    Order.objects
    .filter(status="pending")
    .select_related("customer")
    .prefetch_related("items")
)
 

And we need the number of items:

orders = (
    Order.objects
    .filter(status="pending")
    .select_related("customer")
    .prefetch_related("items")
    .annotate(
        item_count=Count("items"),
    )
)
 

Now the QuerySet describes the actual data requirements of the endpoint.

That's the goal.

46. The Core Principles#

If you don't remember all the Django ORM methods from this article, remember these principles.

Principle 1: Measure first#

Don't optimize based on assumptions.

Principle 2: Avoid N+1 queries#

Look carefully at relationships accessed inside loops and serializers.

Principle 3: Use select_related() for single-valued relationships#

Usually:

ForeignKey
OneToOneField
 

Principle 4: Use prefetch_related() for collections#

Usually:

ManyToMany
Reverse ForeignKey
 

Principle 5: Let the database filter data#

Use:

filter()
 

instead of loading unnecessary records into Python.

Principle 6: Return only the data you need#

Consider:

values()
values_list()
only()
 

when appropriate.

Principle 7: Let the database aggregate data#

Use:

Count()
Sum()
Avg()
Min()
Max()
 

instead of loading huge datasets into Python.

Principle 8: Use indexes based on real query patterns#

Don't index every column.

Principle 9: Paginate large datasets#

Don't send millions of records through your API.

Principle 10: Keep optimization readable#

Fast code that nobody can maintain is not a good long-term solution.

47. Final Mental Model#

Django makes database access feel like Python.

That's one of its greatest strengths.

You can write:

orders = Order.objects.filter(
    status="pending",
)
 

instead of manually writing SQL.

But that convenience can also hide what's happening underneath.

When you write:

order.customer.name
 

there might be a database query.

When you write:

order.items.all()
 

there might be another query.

When you write:

for order in orders:
    order.items.count()
 

you might accidentally create hundreds of queries.

So learning Django query optimization means learning to see both sides:

Python ORM
     ↓
Django QuerySet
     ↓
SQL
     ↓
PostgreSQL
     ↓
Query Plan
     ↓
Database Work
 

Once you start thinking this way, query optimization becomes much less mysterious.

You stop asking:

"Which Django trick makes this faster?"

And start asking:

"What database work am I causing, and how can I reduce unnecessary work?"

That is the real skill.

Conclusion#

Django's ORM is powerful because it lets developers work with database records using Python objects and expressive QuerySets.

But abstraction doesn't remove database costs.

A simple-looking Django endpoint can accidentally execute hundreds of queries, load thousands of unnecessary objects, perform expensive Python-side calculations, or scan huge database tables.

The solution isn't to make every QuerySet complicated.

It's to understand the relationship between your Django code and the database underneath it.

Start with the basics:

Measure
   ↓
Find unnecessary queries
   ↓
Fix N+1 problems
   ↓
Fetch only required data
   ↓
Filter and aggregate in the database
   ↓
Add appropriate indexes
   ↓
Inspect query plans
   ↓
Measure again
 

And remember the most important rule:

The fastest query is often the query you didn't need to execute.

Once you become comfortable reading Django ORM code and mentally translating it into SQL, you'll start writing APIs differently.

You'll think about query count before adding another loop.

You'll recognize N+1 problems inside serializers.

You'll design indexes from actual access patterns.

And you'll know when select_related(), prefetch_related(), annotate(), values(), or exists() actually solve a problem instead of simply adding them because they are "optimization tools."

That's when Django query optimization stops being a collection of tricks and becomes a way of designing better backend systems.