How one Django .delete() ran a 4 GB instance out of memory | Slicing Prod Data

ORMs can bring us a lot of simplicity and are easier to maintain than raw SQL commands, but as with everything in life it comes with a cost, in my case the cost was memory, more precisely an OOM (Out Of Memory) error, so in order to register this for the future and also help people to avoid the same mistakes, I decided to write more about it, its causes and possible solutions.

Context

The context in which the OOM occurred was a transform step of a pipeline that has the goal of generating a slice of production data. I know that today there are some tools to implement that, but we had some specific requirements (like leaving some rows of some tables untouched), and in order to use the same models already defined in Django and use them to simplify maintenance, we decided to use a Django command.

To create the slice, we decided to choose a percentage and delete the rest, but the problem is that the first delete involves a cascade of CASCADE rules (yes) by the use of the .delete() method. This method (and other implementation mistakes) was the cause of the OOM error.

Cause

Well, saying that the cause of the error was exclusively about the method is not the whole truth, since this only appeared once I made the first QA test with production data, and this is the cause of most backend issues: Scale and Volume. Locally, I tried to make a good seed of data to test my transform step, but none of it was enough to represent the prod data (more than 150 GB). On top of that, in production the data is much more concentrated in the leaves than in the root: to give a sense of scale (numbers illustrative), the shape looks roughly like grandfather → 20k parents → 100k grandchildren. This created a perfect scenario to expose my implementation error: the wrong use of .delete().

How delete works

When you call .delete() on a queryset, the real work is handed off to a Collector instance. Its job is to work out everything that has to be deleted before deleting anything: it recursively collect()s the base model's cascade relationships, pulling the affected objects into an in-memory dict, and only then issues the actual SQL, one DELETE ... IN (...) per table. (It can skip loading a relation when it's able to "fast delete" it with a single subquery, but anything with its own cascades, signals, or children still gets pulled in.) That "collect the whole tree first, delete second" design is exactly where the memory goes. Three parts of the Collector tell the story: where the objects are kept, how the collection recurses, and when the SQL finally runs.

First, the store (all excerpts from django/db/models/deletion.py, Django 5.2, trimmed for clarity). The objects to be deleted are accumulated in a single in-memory dict:

class Collector:
    def __init__(self, using, origin=None):
        ...
        # Initially, {model: {instances}}, later values become lists.
        self.data = defaultdict(set)   # objects to be deleted are collected here, in memory

The recursion comes from the CASCADE handler: for each related object it finds, it calls collect() again, which collects their related objects, all the way down the tree:

def CASCADE(collector, field, sub_objs, using):
    collector.collect(          # recurse: collect the children of the children ...
        sub_objs,
        source=field.remote_field.model,
        source_attr=field.name,
        nullable=field.null,
        fail_on_restricted=False,
    )

    def collect(self, objs, ...):
        new_objs = self.add(objs, ...)      # stash these instances in self.data
        ...
        for related in get_candidate_relations_to_delete(model._meta):
            field = related.field
            on_delete = field.remote_field.on_delete
            ...
            for batch in batches:
                sub_objs = self.related_objects(related_model, [field], batch)  # fetch the children
                if getattr(on_delete, "lazy_sub_objs", False) or sub_objs:
                    on_delete(self, field, sub_objs, self.using)   # CASCADE -> collect() again

Only once everything is collected does the SQL run, one DELETE ... IN (...) per model, built from the primary keys of every instance sitting in self.data:

    def delete(self):
        ...
        # delete instances
        for model, instances in self.data.items():
            query = sql.DeleteQuery(model)
            pk_list = [obj.pk for obj in instances]          # every collected pk, still in memory
            count = query.delete_batch(pk_list, self.using)  # DELETE FROM ... WHERE pk IN (...)

So the combination of the collect method being called for each cascade-related object and being put in memory by the collector instance, plus the volume of data, plus the structure of the distribution of the data, rapidly uses up the maximum memory capacity of the machine instance (4 GB to be exact).

The initial solution

So the initial solution to this, instead of basically calling the queryset with the delete method, was to delete their related descendants in batches in order to keep memory usage constant, the classical idea of solving the small problems first. Here is the implementation:

The models involved form a Blog → Post → Comment (Fictional models) cascade (grandfather → parent → grandchild), where the volume lives in the leaves. We first compute the primary keys we want to drop with raw SQL (so the ids never all land in Python), and then, instead of one big queryset.delete(), we delete them a bounded batch at a time:

from django.db import transaction

# The naive version: one call for the whole slice. queryset.delete() runs
# Django's Collector, which pulls the cascade descendants (Post -> Comment -> ...)
# into memory before issuing the DELETEs. On prod-scale data this is what
# exhausts a 4 GB instance.
Post.objects.filter(id__in=drop_ids).delete()

# The batched version: delete the same rows a bounded batch at a time, so the
# Collector only ever holds one batch worth of descendants in memory.
def batched_delete(model, ids, *, size=1000):
    # Delete through _base_manager (the unfiltered manager), not .objects:
    # a custom default manager can hide rows (e.g. soft-deleted ones) that
    # the id list still targets, which would silently under-delete.
    manager = model._base_manager
    for start in range(0, len(ids), size):
        with transaction.atomic():
            manager.filter(id__in=ids[start:start + size]).delete()
    return len(ids)

batched_delete(Post, drop_ids)

And after the new implementation was merged, we tried a new round of QA, and the OOM error was fixed, so everything is good, right? Turns out, no. At this moment, even though I had some notion of the structure of the data, I wasn't expecting that these deletions using these defined batches of deletion would take more than 2 hours to (not) be processed, which caused the whole pipeline to time out and thus the need to redesign everything again to solve this new issue.

Conclusion

Even though this first try failed at the goal of solving all the problems of the pipeline and finally delivering it, it helped me to better understand how Django works in the background with its ORM. So, in the next post, I will try to explain what my tries to definitively solve this were (hopefully), as soon as possible.