Delete Old Rows in Django in Batches Without Hurting Production

Problem statement

Tables like audit logs, webhook events, Celery task results, notifications, and expired sessions grow forever unless something deletes old rows. When someone finally runs Event.objects.filter(created_at__lt=cutoff).delete(), it locks millions of rows, runs for minutes, generates gigabytes of WAL, lags replicas, and sometimes runs out of memory because Django collects related objects first. Retention needs to run continuously in small, cheap batches.

1. Define retention per table (for example 90 days for events, 7 days for task results) and write it down.
2. Delete in batches of a few thousand rows selected by primary key, each in its own short transaction.
3. Use _raw_delete or SQL for tables without cascades or signals to avoid Django's collector loading rows into memory.
4. Run cleanup frequently (hourly or nightly) from Celery beat or cron, with a time budget.
5. For very large append-only tables, partition by time and drop old partitions instead.

Step-by-step solution

1. Index the cutoff column

Retention queries filter by a timestamp. Make sure created_at (or the expiry column) is indexed, ideally as the leading column of an index used by the batch query:

class Event(models.Model):
    created_at = models.DateTimeField(auto_now_add=True, db_index=True)

2. Delete in batches

import time
from django.db import transaction
from django.utils import timezone
from datetime import timedelta

def purge_events(days=90, batch=5000, pause=0.2, max_seconds=600):
    cutoff = timezone.now() - timedelta(days=days)
    started = time.monotonic()
    total = 0
    while time.monotonic() - started < max_seconds:
        ids = list(Event.objects.filter(created_at__lt=cutoff)
                   .order_by("pk").values_list("pk", flat=True)[:batch])
        if not ids:
            break
        with transaction.atomic():
            deleted, _ = Event.objects.filter(pk__in=ids).delete()
        total += deleted
        time.sleep(pause)
    return total

Each iteration locks only a few thousand rows for a fraction of a second. The pause lets autovacuum, replication, and other queries keep up. The time budget stops the job from running into business hours.

3. Skip the collector when safe

QuerySet.delete() first collects related objects to handle on_delete rules and send pre_delete/post_delete signals. For tables with no relations pointing at them and no delete signals, that is wasted work. The private _raw_delete issues a single DELETE without collecting:

qs = Event.objects.filter(pk__in=ids)
qs._raw_delete(qs.db)

It is a private API, so wrap it in a helper and test after Django upgrades, or use raw SQL: DELETE FROM app_event WHERE id = ANY(%s).

4. Handle cascades deliberately

If other tables reference the rows (for example EventAttachment with on_delete=CASCADE), deleting a batch of events also deletes their attachments, possibly many more rows. Delete children first in their own batches, or set the foreign key to DB_CASCADE-style behavior at the database level with care. Files referenced by deleted rows (uploads) need separate cleanup.

5. Schedule and observe

CELERY_BEAT_SCHEDULE = {
    "purge-events": {"task": "events.tasks.purge_events", "schedule": crontab(minute=15, hour="*/2")},
}

Log how many rows each run deleted and how long it took. If counts keep rising, increase frequency or batch size. Built-in cleanups exist for some tables: clearsessions for sessions, CELERY_RESULT_EXPIRES with celery.backend_cleanup for results.

Explanation

A single large DELETE holds row locks until it commits, writes every deleted row's change to WAL, and leaves all the dead rows for vacuum at once. Django's deletion collector adds memory cost by loading primary keys and related objects. Splitting the work into batches keeps each transaction small, spreads WAL and vacuum work over time, and makes the job easy to pause. Deleting does not shrink the table on disk; vacuum reuses the space, and only a rewrite or dropping partitions returns it.

Edge cases / notes

• Replica lag. Watch replica lag during cleanup; increase the pause if it grows.
• Privacy laws. Retention for personal data may be a legal requirement; document it and include backups in your reasoning.
• Soft deletes. Tables with deleted_at flags still need a hard-delete retention policy eventually.

Next steps

• Reclaim disk after a big cleanup with Remove PostgreSQL Table Bloat With pg_repack.
• Avoid deletes entirely with Partition Large PostgreSQL Tables for Django.
• Align policy with the Django Privacy and GDPR Production Checklist.

FAQ

Why not just call queryset.delete() once?

On large tables it holds locks for a long time, floods WAL, lags replicas, and can exhaust memory in Django's collector.

What batch size should I use?

A few thousand rows is a good start. Measure transaction time and adjust so each batch takes well under a second.

Does deleting rows free disk space?

Not immediately. Vacuum makes space reusable; a table rewrite or partition drop returns it to the operating system.

Is _raw_delete safe?

For tables with no dependent rows and no delete signals, it is efficient, but it is a private API; keep it behind a tested helper.