DEV Community

Cover image for Django ORM on Millions of Rows: 6 Fixes That Actually Matter
sizan mahmud0
sizan mahmud0

Posted on

Django ORM on Millions of Rows: 6 Fixes That Actually Matter

#django #python #postgres #performance

Most slow Django apps aren't slow because of the ORM. They're slow because of a few repeatable mistakes that get worse as tables grow.

Quick wins

Measure first

print(Order.objects.filter(status="paid").explain(analyze=True))
Enter fullscreen mode Exit fullscreen mode

Fix N+1

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

Load fewer columns

emails = list(User.objects.values_list("email", flat=True))
Enter fullscreen mode Exit fullscreen mode

Keyset pagination instead of deep OFFSET

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])
Enter fullscreen mode Exit fullscreen mode

Bulk writes and DB-side updates

Order.objects.bulk_update(changed_orders, ["status"], batch_size=1000)
Order.objects.filter(status="pending", created_at__lt=cutoff).update(status="expired")
Enter fullscreen mode Exit fullscreen mode

Also worth checking

  • Composite index column order matches your filters
  • Partial indexes for hot subsets, like pending rows
  • exists() instead of loading a queryset to test it
  • iterator(chunk_size=2000) for batch jobs, and check your pooler's compatibility with server-side cursors

Full guide

Indexes, aggregation in the database, when to go beyond the ORM, a checklist, and an FAQ are in the full post:

👉 https://blog.reiteachaigean.com/blog/django-orm-optimize-millions-of-records

What's your go-to tool for finding slow queries?

Top comments (0)