Partition Large PostgreSQL Tables in a Django App

Problem statement

Some Django tables grow without bound: analytics events, API request logs, audit trails, sensor readings, notifications. At hundreds of millions of rows, deleting old data with DELETE ... WHERE created_at < ... takes hours, bloats the table, and hammers autovacuum; indexes become huge; and queries that only care about recent data still pay for the whole table's size. PostgreSQL's declarative partitioning splits one logical table into child tables by a key such as month. Old months can be dropped instantly, and queries for recent data touch only recent partitions.

1. Partition only tables that are huge and naturally time-ordered with retention needs; most Django tables do not need it.
2. Create the parent table with PARTITION BY RANGE (created_at) in a RunSQL migration, and child partitions per month.
3. Include the partition key in the primary key (PRIMARY KEY (id, created_at)); unique constraints must contain it.
4. Keep the Django model pointing at the parent table; inserts and queries are routed automatically.
5. Automate future partitions with pg_partman or a scheduled management command; drop or detach old ones for retention.
6. Always filter on created_at in queries so PostgreSQL can prune partitions.

Step-by-step solution

1. Decide whether to partition

Good candidates: append-mostly tables, hundreds of millions of rows, queries mostly over recent time ranges, and a retention policy (keep 13 months). Poor candidates: tables queried mainly by primary key across all time, or with many foreign keys pointing to them (foreign keys referencing partitioned tables are supported since PostgreSQL 12 but add constraints).

2. Create the partitioned table

# analytics/migrations/0001_events_partitioned.py
from django.db import migrations, models

class Migration(migrations.Migration):
    initial = True
    operations = [
        migrations.RunSQL(
            sql="""
            CREATE TABLE analytics_event (
                id bigint GENERATED BY DEFAULT AS IDENTITY,
                created_at timestamptz NOT NULL,
                user_id bigint,
                name varchar(100) NOT NULL,
                payload jsonb NOT NULL DEFAULT '{}',
                PRIMARY KEY (id, created_at)
            ) PARTITION BY RANGE (created_at);
            CREATE INDEX analytics_event_name_created ON analytics_event (name, created_at);
            CREATE TABLE analytics_event_2026_10 PARTITION OF analytics_event
                FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
            CREATE TABLE analytics_event_2026_11 PARTITION OF analytics_event
                FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
            """,
            reverse_sql="DROP TABLE analytics_event;",
            state_operations=[
                migrations.CreateModel(
                    name="Event",
                    fields=[
                        ("id", models.BigAutoField(primary_key=True)),
                        ("created_at", models.DateTimeField()),
                        ("user_id", models.BigIntegerField(null=True)),
                        ("name", models.CharField(max_length=100)),
                        ("payload", models.JSONField(default=dict)),
                    ],
                    options={"db_table": "analytics_event", "managed": True},
                ),
            ],
        ),
    ]

state_operations tells Django about the model without letting it create the table its own way. Django still treats id as the primary key for ORM purposes, which works for inserts and lookups, while PostgreSQL enforces uniqueness on (id, created_at).

3. Query with the partition key

recent = Event.objects.filter(created_at__gte=timezone.now() - timedelta(days=7), name="signup")

With created_at in the filter, the planner prunes to the relevant partitions. Queries without it scan every partition, which can be slower than an unpartitioned table.

4. Create future partitions automatically

pg_partman (available on many managed services) maintains partitions ahead of time and can drop old ones by retention. Without it, a monthly management command run by a scheduler works:

def ensure_partition(month_start):
    nxt = (month_start + timedelta(days=32)).replace(day=1)
    name = f"analytics_event_{month_start:%Y_%m}"
    with connection.cursor() as c:
        c.execute(f"""CREATE TABLE IF NOT EXISTS {name} PARTITION OF analytics_event
                      FOR VALUES FROM (%s) TO (%s)""", [month_start, nxt])

Create partitions at least one or two months ahead, and alert if the next month's partition is missing; inserts for a range with no partition fail (unless you add a DEFAULT partition, which then grows unchecked).

5. Retention

ALTER TABLE analytics_event DETACH PARTITION analytics_event_2025_09 CONCURRENTLY;
-- optional: pg_dump the detached table to archive storage
DROP TABLE analytics_event_2025_09;

Verification check: EXPLAIN SELECT ... WHERE created_at >= now() - interval '7 days' lists only recent partitions; inserting an event lands in the correct child table (SELECT tableoid::regclass FROM analytics_event ORDER BY id DESC LIMIT 1); dropping an old partition takes under a second.

Explanation

Declarative partitioning makes a parent table a router: rows are stored in child tables chosen by the partition key, and the planner excludes partitions whose ranges cannot match a query's filter. Each partition has its own smaller indexes and is vacuumed independently. Removing a whole partition is a catalogue operation rather than millions of row deletions. Django works with the parent table as an ordinary table, but cannot generate the partitioning DDL itself, which is why migrations use RunSQL with matching state operations.

Edge cases / notes

• Converting an existing table requires creating a new partitioned table, copying data in batches (or attaching the old table as a partition with a matching constraint), and switching names in a short window.
• Global unique constraints across partitions are only possible when they include the partition key.
• Foreign keys to the partitioned table are supported in recent PostgreSQL but must reference the full primary key.
• Too many partitions (thousands) slow planning; monthly or weekly is usually right.

Next steps

• For other big-table work, see Django Schema Changes on Large Tables.
• Partitioned audit trails pair well with Keep an Audit Log of Changes in Django.

FAQ

Does Django support PostgreSQL partitioning?

Not natively in migrations, but a model can use a partitioned table created with RunSQL and state_operations.

When should I partition a Django table?

When it holds hundreds of millions of rows, is time-ordered, and old data must be removed regularly.

Why must the primary key include the partition key?

PostgreSQL enforces unique constraints per partition, so they must include the partitioning column.

What happens if a partition for new data does not exist?

Inserts fail unless a default partition exists. Create partitions ahead of time and monitor.