Introduce

The Django ORM is productive, readable, and safe. It is also very easy to misuse once your tables grow. A query that feels instant with 10,000 rows can take seconds, or time out completely, when the table holds 10 million.

The good news is that most slow Django applications are not slow because of the ORM itself. They are slow because of a small set of repeatable mistakes. Fix those, and the same ORM can handle very large tables comfortably.

This guide covers the techniques that matter most when optimizing the Django ORM for millions of records, in the order I would apply them: measure first, then fix queries, then fix the database, then fix the access patterns.

Step 1: Measure Before You Optimize

Guessing is the most expensive way to optimize. Before changing anything, find out what is actually slow.

See the SQL Django generates:

qs = Order.objects.filter(status="paid")
print(qs.query)

Count the queries a view or function runs:

from django.db import connection, reset_queries
from django.test.utils import CaptureQueriesContext

with CaptureQueriesContext(connection) as ctx:
    list(Order.objects.filter(status="paid")[:50])

print(len(ctx.captured_queries))

Ask the database how it runs a query:

print(Order.objects.filter(status="paid").explain(analyze=True))

EXPLAIN ANALYZE is the single most useful tool here. It shows whether PostgreSQL used an index or scanned the entire table, how many rows it examined, and where the time went. During development, django-debug-toolbar shows the query count and duration for every page, and in production, slow query logging (log_min_duration_statement in PostgreSQL) catches the real offenders.

Step 2: Fix the N+1 Query Problem

This is the most common ORM performance problem, and it gets worse as data grows.

# 1 query for orders + 1 query per order for the customer
for order in Order.objects.all()[:1000]:
    print(order.customer.name)

That is up to 1,001 queries. Fix it by loading related objects up front:

• select_related() uses a SQL JOIN. Use it for ForeignKey and OneToOneField relations.
• prefetch_related() runs a second query and joins the results in Python. Use it for many-to-many and reverse foreign key relations.

orders = (
    Order.objects
    .select_related("customer")
    .prefetch_related("items")
    .filter(status="paid")[:1000]
)

If you only need one or two related fields, Prefetch with a restricted queryset keeps the second query lean:

from django.db.models import Prefetch

orders = Order.objects.prefetch_related(
    Prefetch("items", queryset=OrderItem.objects.only("id", "order_id", "price"))
)

Step 3: Load Only the Data You Need

By default, Django selects every column. On wide tables with large text or JSON fields, that wastes memory, network bandwidth, and time.

• only() loads just the listed fields.
• defer() loads everything except the listed fields.
• values() and values_list() return dictionaries or tuples instead of model instances, which is much lighter when you do not need model methods.

# Heavy: full model instances with every column
emails = [u.email for u in User.objects.all()]

# Light: a flat list of values, no model instances
emails = list(User.objects.values_list("email", flat=True))

One caution: if you access a deferred field later, Django runs an extra query per object, which recreates the N+1 problem. Only defer fields you are sure you will not touch.

Step 4: Use Indexes Correctly

On a table with millions of rows, the right index is often the difference between 5 milliseconds and 5 seconds.

Add indexes on columns you frequently filter, join, or sort by:

class Order(models.Model):
    status = models.CharField(max_length=20)
    customer = models.ForeignKey("Customer", on_delete=models.CASCADE)
    created_at = models.DateTimeField()

    class Meta:
        indexes = [
            models.Index(fields=["customer", "-created_at"]),
            models.Index(
                fields=["created_at"],
                condition=models.Q(status="pending"),
                name="pending_orders_created_idx",
            ),
        ]

A few points matter here:

• Composite indexes follow column order. An index on (customer, created_at) helps queries that filter by customer, or by customer and created_at, but not queries that filter by created_at alone.
• Partial (conditional) indexes index only the rows you query often, such as pending orders. They are smaller and faster than indexing everything.
• Indexes are not free. Each one slows down writes and uses disk space. Add the ones your real queries need, and remove unused ones.
• Functions break indexes. Filtering on __lower or __date without a matching expression index forces a full scan. Use an expression index or rewrite the filter as a range.
• icontains and leading wildcards (LIKE '%term%') cannot use a normal B-tree index. For text search on large tables, consider PostgreSQL trigram indexes or full-text search.

Always confirm with EXPLAIN ANALYZE that the planner actually uses the index you created.

Step 5: Stop Using OFFSET Pagination on Big Tables

OFFSET pagination gets slower the deeper you go, because the database still has to read and discard every skipped row. Page 10,000 of a large table is dramatically slower than page 1.

Use keyset (cursor) pagination instead, based on an indexed, unique ordering column:

def next_page(last_id=None, size=100):
    qs = Order.objects.order_by("id")
    if last_id:
        qs = qs.filter(id__gt=last_id)
    return list(qs[:size])

Each page is a fast index range scan, no matter how far into the table you are. Django REST Framework offers CursorPagination for APIs built on the same idea.

Step 6: Count and Check Existence the Cheap Way

Small habits add up across millions of rows.

# Slow: loads every row just to count or test
if len(Order.objects.filter(status="paid")) > 0: ...
if Order.objects.filter(status="paid"): ...

# Fast: lets the database do the work
if Order.objects.filter(status="paid").exists(): ...
count = Order.objects.filter(status="paid").count()

Be aware that COUNT(*) on a very large table can itself be slow in PostgreSQL, because it may need to scan many rows. If you only need an approximate total for a UI, consider caching the count or using an estimate rather than counting on every request.

Step 7: Process Large Querysets Without Exhausting Memory

Iterating over a huge queryset normally loads all results into memory. For batch scripts, use iterator():

for order in Order.objects.filter(status="paid").iterator(chunk_size=2000):
    process(order)

This streams rows in chunks instead of caching the whole result set. Two details to remember: with PostgreSQL, iterator() uses server-side cursors by default, which can conflict with some connection poolers in transaction pooling mode, so check your setup. And iterator() does not support prefetch_related() unless you pass a chunk_size, in which case Django prefetches per chunk.

Step 8: Write in Bulk

Looping with save() runs one query per object. Use bulk methods instead:

Order.objects.bulk_create(new_orders, batch_size=1000)
Order.objects.bulk_update(changed_orders, ["status", "total"], batch_size=1000)

For updates that apply the same change to many rows, skip loading objects entirely and let the database do it in one statement:

Order.objects.filter(status="pending", created_at__lt=cutoff).update(status="expired")

Remember that bulk_create(), bulk_update(), and update() skip save(), signals, and auto_now updates, so handle those yourself when they matter. For very large jobs, combine these methods with background tasks. I covered that pattern in a separate guide on processing millions of records with Django and Celery: https://blog.reiteachaigean.com/blog/process-5-million-records-django-celery

Step 9: Push Calculations into the Database

Python loops over large result sets are slow. Databases are built for aggregation, so use annotate(), aggregate(), Case/When, Subquery, and Exists:

from django.db.models import Count, Sum, Exists, OuterRef

customers = (
    Customer.objects
    .annotate(
        order_count=Count("orders"),
        total_spent=Sum("orders__total"),
        has_open_order=Exists(
            Order.objects.filter(customer=OuterRef("pk"), status="open")
        ),
    )
)

One query returns the final numbers instead of thousands of rows to sum in Python. Be careful when combining several aggregations across different relations in one query, because joins can multiply rows and inflate totals. Use subqueries or separate queries in those cases.

Step 10: Know When to Go Beyond the ORM

The ORM covers most needs, but for some workloads, a different tool is the right answer:

• Materialized views for expensive reports that do not need real-time freshness.
• Raw SQL (Model.objects.raw() or cursor.execute()) for complex queries the ORM cannot express efficiently. Always use parameters, never string formatting.
• Caching with Redis for data that is read often and changes rarely.
• Read replicas to move heavy read traffic off the primary database.
• Table partitioning for very large, time-based tables such as logs or events.
• Connection pooling with PgBouncer when many workers or processes compete for connections.

Reaching for these early adds complexity. Reach for them after you have measured and fixed the basics.

A Quick Optimization Checklist

Before shipping a feature that touches a large table, ask:

• Did I check the query count and run EXPLAIN ANALYZE?
• Are related objects loaded with select_related or prefetch_related?
• Am I loading only the columns I need?
• Do the filter, join, and sort columns have appropriate indexes?
• Am I using keyset pagination instead of deep OFFSET?
• Am I using exists() and count() instead of loading full querysets?
• Am I writing in bulk and calculating in the database?
• Is there a caching or background-processing option for heavy work?

Conclusion

Optimizing the Django ORM for millions of records is mostly disciplined fundamentals. Measure first. Remove N+1 queries. Load only the data you need. Index for your real query patterns. Paginate by key. Write in bulk, and let the database do the calculating.

Do those consistently, and Django handles large tables far better than its reputation suggests. Save the heavier tools, such as materialized views, replicas, and partitioning, for the cases where measurement shows you actually need them.

Frequently Asked Questions

Can Django handle tables with millions of rows? Yes. Django can work with very large tables when queries use proper indexes, load only necessary data, and avoid patterns like N+1 queries and deep OFFSET pagination. Most performance problems come from query design and missing indexes, not from the framework.

What is the difference between select_related and prefetch_related? select_related() uses a SQL JOIN and works for foreign key and one-to-one relations. prefetch_related() runs a separate query and combines results in Python, which suits many-to-many and reverse foreign key relations.

How do I find slow queries in Django? Use django-debug-toolbar in development, QuerySet.explain(analyze=True) to inspect query plans, and PostgreSQL slow query logging in production to catch the queries that take the longest.

Is iterator() better than a normal loop for large querysets? For batch jobs, yes. iterator() streams results in chunks instead of loading the entire queryset into memory. Check compatibility with your connection pooler, since server-side cursors can behave differently in some pooling modes.

When should I use raw SQL instead of the Django ORM? Use raw SQL when a query is too complex for the ORM to express efficiently, and only after you have confirmed the ORM version is the bottleneck. Always pass parameters safely to avoid SQL injection.