Using Django ORM with PostgreSQL Partitioned Tables

This walkthrough models a range-partitioned events table in Django — parent DDL via a custom migration, composite uniqueness, a manager that refuses unprunable queries, and scheduled monthly partition DDL — applying the patterns from ORM Integration & Partition-Aware Routing within the broader Partitioning Implementation Patterns & Routing guide.

Prerequisites

• PostgreSQL 13 or later (declarative partitioning with mature pruning; 15+ recommended)
• Django 4.2 or later with psycopg (v3) or psycopg2 installed
• A database role with CREATE on the target schema (partition DDL) and ALTER on the parent table
• django.db.backends logging enabled in staging to inspect generated SQL
• A deploy pipeline step (or cron/systemd timer) that can run management commands after migrate
• A staging database to test the initial migration before production rollout

Step 1 — Create the parent table with a RunSQL migration

Django’s CreateModel cannot emit PARTITION BY RANGE, so the initial migration must pair raw DDL with state_operations: the SQL creates the real partitioned table, while the CreateModel inside state_operations teaches Django’s migration state that the model exists — without Django ever generating its own (unpartitioned) CREATE TABLE. This is the standard approach for range partitioning strategies under Django.

# events/migrations/0001_initial.py
from django.db import migrations, models

PARENT_DDL = """
CREATE TABLE events_event (
    id           bigint GENERATED BY DEFAULT AS IDENTITY,
    tenant_id    integer      NOT NULL,
    event_type   varchar(50)  NOT NULL,
    payload      jsonb,
    occurred_at  timestamptz  NOT NULL,
    PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_event_default PARTITION OF events_event DEFAULT;

CREATE INDEX events_event_tenant_ts_idx
    ON events_event (tenant_id, occurred_at DESC);
"""

class Migration(migrations.Migration):
    initial = True
    operations = [
        migrations.RunSQL(
            sql=PARENT_DDL,
            reverse_sql="DROP TABLE events_event;",
            state_operations=[
                migrations.CreateModel(
                    name="Event",
                    fields=[
                        ("id", models.BigAutoField(primary_key=True)),
                        ("tenant_id", models.IntegerField()),
                        ("event_type", models.CharField(max_length=50)),
                        ("payload", models.JSONField(null=True)),
                        ("occurred_at", models.DateTimeField()),
                    ],
                ),
            ],
        ),
    ]

Operational note: The DEFAULT partition is a deliberate safety net — if the monthly partition job ever misses a boundary, inserts land in events_event_default instead of failing with no partition of relation "events_event" found for row. Monitor its row count; anything above a trickle means automation has stalled.

DBA tip: The index on (tenant_id, occurred_at DESC) is created on the parent, so PostgreSQL automatically clones it onto every future child partition — including ones the management command creates months from now. Per-child index drift is one of the most common partitioned-table regressions; parent-level index definitions eliminate it.

Step 2 — Declare the model with composite uniqueness including the partition key

PostgreSQL requires every unique constraint on a partitioned table to include the partition key, which is why the real primary key is (id, occurred_at) even though Django’s state believes id alone is the key (a state-level fiction that works because id values are unique via the identity sequence; on Django 5.2+ you can instead declare CompositePrimaryKey("id", "occurred_at")). Application-level dedupe keys must follow the same rule — a UniqueConstraint on (tenant_id, external_ref) would be rejected by PostgreSQL, so occurred_at joins the constraint:

# events/models.py
from django.db import models
from .managers import EventManager


class Event(models.Model):
    id = models.BigAutoField(primary_key=True)
    tenant_id = models.IntegerField()
    event_type = models.CharField(max_length=50)
    external_ref = models.CharField(max_length=64)
    payload = models.JSONField(null=True)
    occurred_at = models.DateTimeField(db_index=False)

    objects = EventManager()

    class Meta:
        db_table = "events_event"
        constraints = [
            # partition key must appear in every unique constraint
            models.UniqueConstraint(
                fields=["tenant_id", "external_ref", "occurred_at"],
                name="events_event_dedupe_uniq",
            ),
        ]

Operational note: Generate the constraint migration normally (makemigrations) — AddConstraint runs a real ALTER TABLE on the parent and PostgreSQL propagates the unique index to all existing and future children. No raw SQL needed for this step.

DBA tip: Never mark occurred_at with db_index=True here. Django would add a second, redundant single-column index on every partition alongside the composite parent index from Step 1, roughly doubling write amplification on your hottest column for zero read benefit.

Step 3 — Add a manager that refuses unprunable queries

The single biggest partitioned-table failure in Django is a queryset without an occurred_at filter: Event.objects.filter(tenant_id=42) compiles fine, returns correct rows, and scans every partition doing it. A custom queryset makes the partition-key filter mandatory, turning a silent performance cliff into an immediate exception in development:

# events/managers.py
from django.db import models


class EventQuerySet(models.QuerySet):
    PARTITION_KEY = "occurred_at"

    def _clone(self):
        clone = super()._clone()
        clone._partition_scoped = getattr(self, "_partition_scoped", False)
        return clone

    def filter(self, *args, **kwargs):
        clone = super().filter(*args, **kwargs)
        if any(k == self.PARTITION_KEY or k.startswith(self.PARTITION_KEY + "__")
               for k in kwargs):
            clone._partition_scoped = True
        return clone

    def in_range(self, start, end):
        """Preferred entry point: half-open [start, end) range."""
        return self.filter(occurred_at__gte=start, occurred_at__lt=end)

    def _fetch_all(self):
        if not getattr(self, "_partition_scoped", False):
            raise RuntimeError(
                "Event query has no occurred_at filter — PostgreSQL would "
                "scan every partition. Use .in_range(start, end)."
            )
        super()._fetch_all()


class EventManager(models.Manager.from_queryset(EventQuerySet)):
    pass

Operational note: The guard fires at iteration time, not construction time, so it catches querysets built across multiple call sites — including ones assembled by DRF filters or admin list views. Expect it to flag Event.objects.get(pk=...) too; that is correct behavior, because an id-only lookup is exactly the unprunable query you are trying to eliminate. Provide a get_by_id(id, occurred_at) helper for callers that genuinely need identity lookups.

DBA tip: Avoid occurred_at__date=... lookups even though the guard accepts them — Django compiles the lookup as a cast over the column ((occurred_at AT TIME ZONE ...)::date), which defeats pruning. Range filters on the bare column (__gte/__lt) are the only forms guaranteed to prune.

Step 4 — Create monthly partitions with a management command

Child partitions have a scheduled lifetime, so they belong in an idempotent management command rather than migrations — the same ownership split used in automating quarterly partition creation in PostgreSQL, on a monthly cadence. Run it in the deploy pipeline immediately after migrate, and nightly from cron so long gaps between deploys never outrun the pre-created window:

# events/management/commands/create_event_partitions.py
from datetime import date
from django.core.management.base import BaseCommand
from django.db import connection


def add_months(d: date, n: int) -> date:
    y, m = divmod(d.month - 1 + n, 12)
    return date(d.year + y, m + 1, 1)


class Command(BaseCommand):
    help = "Idempotently create monthly partitions for events_event."

    def add_arguments(self, parser):
        parser.add_argument("--months-ahead", type=int, default=3)

    def handle(self, *args, **opts):
        first = date.today().replace(day=1)
        with connection.cursor() as cur:
            cur.execute("SELECT pg_advisory_xact_lock(42001)")  # serialize DDL
            for i in range(opts["months_ahead"] + 1):
                start, end = add_months(first, i), add_months(first, i + 1)
                name = f"events_event_{start:%Y_%m}"
                # DDL cannot take bind parameters; literals are derived
                # from date objects, so interpolation is injection-safe.
                cur.execute(
                    f"CREATE TABLE IF NOT EXISTS {name} "
                    f"PARTITION OF events_event "
                    f"FOR VALUES FROM ('{start}') TO ('{end}')"
                )
                self.stdout.write(f"ensured {name}")

Operational note: CREATE TABLE IF NOT EXISTS ... PARTITION OF makes the command safe to run from multiple triggers (deploy hook and cron) without coordination, and the advisory lock prevents two concurrent runs — or a run overlapping a migrate that alters the parent — from deadlocking on the parent table’s lock.

DBA tip: FOR VALUES FROM ... TO ... bounds are half-open (>= start AND < end), so consecutive months share a boundary timestamp with no gap and no overlap. Never compute the end bound as “last day of month 23:59:59” — that classic off-by-one drops rows landing in the final second into the DEFAULT partition.

Verification

Confirm pruning through the same connection Django uses, so you are testing the exact SQL path the application takes:

# python manage.py shell
from django.db import connection

sql = """
EXPLAIN (COSTS OFF)
SELECT id, event_type
FROM events_event
WHERE tenant_id = 42
  AND occurred_at >= '2026-07-01+00'
  AND occurred_at <  '2026-08-01+00'
"""
with connection.cursor() as cur:
    cur.execute(sql)
    print("\n".join(row[0] for row in cur.fetchall()))

Expected output — exactly one child partition appears in the plan, and neither events_event_default nor any other month is scanned:

Index Scan using events_event_2026_07_tenant_id_occurred_at_idx
    on events_event_2026_07 events_event
  Index Cond: ((tenant_id = 42) AND (occurred_at >= '2026-07-01 00:00:00+00')
               AND (occurred_at < '2026-08-01 00:00:00+00'))

Also verify the guard rejects unscoped queries (Event.objects.filter(tenant_id=42).count() should raise RuntimeError), and check partition inventory after the first command run:

SELECT c.relname
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'events_event'::regclass
ORDER BY c.relname;

The listing should show events_event_default plus one row per month through your --months-ahead horizon.

Failure mode table

FAQ

Related

• ORM Integration & Partition-Aware Routing — parent guide covering pruning failures, connection routing, and migration ownership across Django, SQLAlchemy, and Hibernate
• SQLAlchemy Horizontal Sharding & Partition Routing — the same enforcement and routing patterns in SQLAlchemy’s ShardedSession
• Automating Quarterly Partition Creation in PostgreSQL — database-side scheduling alternative to the management-command approach used here