SQL

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