Query Optimization

Django’s field lookups are one of the ORM’s best features, but time-based lookups can quietly bypass database indexes, turning fast queries into expensive full table scans. A Slow Production Query I ran into this while debugging a 30 second query on a large table (~25 million rows): class Event(models.Model): timestamp = models.DateTimeField(db_index=True) Event.objects.filter(timestamp__date=datetime.date(2026, 1, 5)).count() It generates SQL like this: SELECT COUNT(*) FROM event WHERE timestamp::date='2026-01-05'; At first glance, it looked totally reasonable — a simple filter on an indexed field. But after checking the query plan, I discovered the issue: the query can’t use the index and falls back to a full table scan because it casts the field to a date. ...

When filtering Django querysets across relationships, you can easily end up with duplicate objects in your results. This is a common gotcha that happens with both one-to-many (1:N) and many-to-many (N:N) relationships. Let’s explore why this happens and the best way to avoid it. The Problem When you filter a queryset by traversing a relationship, Django performs a SQL JOIN. If a parent object has multiple related objects that match your filter, the parent object appears multiple times in the result set. ...

If your Django queries feel slow, the problem might not be your database — it might be your ORM. Recently, I was working with a query that took 25 seconds to run through the Django ORM, but the underlying SQL completed in just 2 seconds. With a single change, I got the ORM query down to 2 seconds as well — and reduced the memory footprint by 70%. Let’s walk through what happened, and why using .values() instead of .only() can dramatically improve performance. ...

A simple insert query turned into a silent performance killer. Our frontend pings our server every few minutes to track device activity. Each ping attempts to insert a row into a DevicePingDaily table, which has a unique constraint on (device_id, date) to ensure only one record per device per day. In Django, the logic looked like this: try: DevicePingDaily.objects.create(device=device, date=today) except IntegrityError: pass It seemed harmless. But as traffic grew, latency spiked and API timeouts increased. Observability tools quickly pointed to the culprit: ...

In a Django application, fetching the latest record for each group is a common yet challenging task, especially when working with large datasets. Whether you’re building an analytics dashboard or managing grouped data, finding an efficient solution is key. In this blog post, we’ll explore five different approaches to tackle this problem, ranked from least to most effective based on performance and readability, using the following Book model: class Book(models.Model): title = models.CharField(max_length=255) author = models.ForeignKey( Author, on_delete=models.CASCADE, related_name="books" ) genre = models.CharField(max_length=255) published_at = models.DateTimeField() is_featured = models.BooleanField(default=False) Solution 1: Python Max with Prefetch latest_books = [ max(author.books.all(), key=lambda x: x.published_at, default=None) for author in Author.objects.prefetch_related("books") ] Performs heavy computation in Python rather than leveraging the database, which is bad for performance. Also makes two database queries (better solutions do it in one). ...