SQL

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. ...

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: ...

The Django ORM is a powerful tool but certain aspects of it are counterintuitive, such as the SQL order of execution. Let’s look at an example of this trap and how we can fix it using subqueries: class Book(models.Model): class Meta: constraints = [ models.UniqueConstraint( fields=["name", "edition"], name="%(app_label)s_%(class)s_unique_name_edition", ) ] name = models.CharField(max_length=255) edition = models.CharField(max_length=255) release_year = models.PositiveIntegerField(null=True) I want to write a query that reads: ...