ORM Integration & Partition-Aware Routing

Object-relational mappers generate SQL you never review, and on a partitioned schema that invisibility is expensive: a query that omits the partition key scans every partition instead of one. This guide — part of Partitioning Implementation Patterns & Routing — covers making Django, SQLAlchemy, and Hibernate cooperate with partitioned tables and sharded topologies: enforcing partition-key predicates at the ORM layer, routing connections to the correct shard, keeping migration tools from fighting the partition automation described in Automated Partition Creation Workflows, and containing N+1 query amplification once lazy loads start crossing shard boundaries.

Problem Framing

A SaaS platform partitions its events table by month using standard range partitioning strategies. Hand-written SQL in psql prunes perfectly: a one-month report touches one partition and returns in 40 ms. The Django application tells a different story. Event.objects.get(pk=evt_id) scans all 36 partitions because the primary-key lookup carries no occurred_at predicate. A serializer that walks order.events.all() triggers the same full-tree scan per order. p95 latency for the events API is 30× the raw SQL baseline, and pg_stat_user_tables shows sequential scans climbing on partitions that should be cold archives.

The second failure arrives at deploy time. The partition automation job created events_2026_08 overnight; the next makemigrations run, seeing tables that exist in the database but not in model state, proposes migrations that conflict with them — and on a team that auto-applies migrations in CI, that proposal ships. Meanwhile the Hibernate service that shares the schema binds its tenant_id parameter as bigint against a varchar partition key, silently disabling plan-time pruning for every query it runs.

None of these are database problems. The schema is correct. They are integration problems: the ORM’s model of the world (one logical table, one connection, surrogate primary keys) does not match the database’s (many physical segments, many nodes, composite keys). Closing that gap is the subject of this page.

Architecture Overview

Partition-aware ORM integration has one load-bearing rule: the partition key must be present in the query before it leaves the ORM, because everything downstream — shard choice, connection routing, planner pruning — depends on it. The diagram below shows the path an ORM query takes and where the key must be injected.

Miss the key at the ORM layer and both downstream decisions degrade at once: the router cannot pick a shard (so it fans out to all of them) and the planner cannot prune (so each shard scans every partition). One missing predicate multiplies into shards × partitions wasted work.

Why ORMs Defeat Partition Pruning

Three ORM behaviors account for nearly all pruning failures in production.

Missing partition-key predicates. ORMs are built around surrogate primary keys. Event.objects.get(pk=...), Hibernate’s session.find(Event.class, id), refresh_from_db(), and every foreign-key traversal fetch rows by id alone. On a table partitioned by occurred_at or tenant_id, an id-only predicate gives the planner nothing to prune with, so it appends every partition into the plan. The fix is structural, not per-query: composite identity (see the primary-key step below) plus a query layer that refuses to run unscoped queries.

Implicit casts. Plan-time pruning requires the bound parameter’s type to match the partition column. ORMs and drivers routinely violate this: a JDBC setObject() call that sends a String for a varchar column via an inferred text cast, a Python datetime.date bound against a timestamptz column, or a lookup like Django’s occurred_at__date that wraps the partition column in a cast expression (occurred_at::date), which makes the predicate unusable for pruning no matter how the parameter is typed. Each looks harmless in application code and each turns a one-partition scan into a full Append across the tree.

Lazy loads. Lazy relationship loading issues follow-up queries the application never sees, and those queries carry only the foreign key — never the partition key. On a single node this defeats pruning quietly; on a sharded topology it is worse, because the session may not even know which shard owns the related row and falls back to querying all of them. This is the mechanism behind N+1 amplification across shards: a list view that renders 50 orders with a lazy-loaded relation issues 1 + 50 queries on a monolith, but 1 + (50 × number-of-shards) shard queries once identity_chooser-style lookups fan out. A 4-shard topology turns 51 queries into 201, and every one of them full-scans its partition tree.

Step 1 — Enforce the Partition Key at the Query Layer

Do not rely on code review to keep partition-key filters in queries. Encode the requirement in the ORM so an unscoped query fails in development, loudly. In Django, a custom QuerySet can track whether any filter touched the partition key and refuse to execute otherwise:

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


class PartitionScopedQuerySet(models.QuerySet):
    """Refuses to execute unless a partition-key filter has been applied."""

    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 _fetch_all(self):
        if not getattr(self, "_partition_scoped", False):
            raise RuntimeError(
                "Query on partitioned table lacks an occurred_at filter; "
                "PostgreSQL cannot prune partitions. Add .filter(occurred_at...)"
            )
        super()._fetch_all()


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

The same guard exists in the other ORMs: SQLAlchemy’s before_execute event can inspect the compiled statement and reject SELECTs on partitioned tables that lack the key column in their WHERE clause, and Hibernate’s @Filter with a session-level parameter appends a tenant predicate to every query against annotated entities. The Django walkthrough builds this guard into a complete working model.

Guards catch the missing-predicate class of failures. The implicit-cast class needs a different tool: log generated SQL in staging (django.db.backends logger, SQLAlchemy echo=True, hibernate.show_sql) and run representative statements through EXPLAIN in CI, asserting that the plan names exactly one partition.

Step 2 — Route Connections with Django Database Routers

Django models a sharded topology as multiple DATABASES aliases plus a router class that picks an alias per operation. The router API’s limitation matters more than its shape: db_for_read receives only the model class and sparse hints (usually a related instance), not the queryset’s filters — so it cannot see the partition key you so carefully injected in Step 1. Reliable Django sharding therefore routes explicitly at the call site with .using(), and keeps the router as a safety net that prevents cross-shard relations and misdirected migrations:

# settings.py
DATABASES = {
    "default": {...},                      # unsharded metadata (tenants, auth)
    "shard_0": {"ENGINE": "django.db.backends.postgresql", "HOST": "pg-shard-0", ...},
    "shard_1": {"ENGINE": "django.db.backends.postgresql", "HOST": "pg-shard-1", ...},
}
DATABASE_ROUTERS = ["events.routers.ShardRouter"]

# events/routers.py
SHARD_COUNT = 2

def shard_for(tenant_id: int) -> str:
    return f"shard_{tenant_id % SHARD_COUNT}"

class ShardRouter:
    SHARDED_APPS = {"events"}

    def db_for_read(self, model, **hints):
        if model._meta.app_label in self.SHARDED_APPS:
            instance = hints.get("instance")
            if instance is not None and hasattr(instance, "tenant_id"):
                return shard_for(instance.tenant_id)
        return None            # caller must pass .using(shard_for(tenant_id))

    db_for_write = db_for_read

    def allow_relation(self, obj1, obj2, **hints):
        return obj1._state.db == obj2._state.db

    def allow_migrate(self, db, app_label, **hints):
        if app_label in self.SHARDED_APPS:
            return db.startswith("shard_")
        return db == "default"

# call site — explicit and prunable
events = (Event.objects.using(shard_for(tenant_id))
          .filter(tenant_id=tenant_id, occurred_at__gte=since))

Note that tenant_id appears twice: once in using() to pick the shard, once in filter() so the chosen shard’s planner can prune its local partitions. Dropping either half reintroduces one of the two failure modes from the architecture section.

Step 3 — Shard Sessions in SQLAlchemy

SQLAlchemy’s horizontal sharding extension is the most complete ORM-native router of the three: ShardedSession accepts three callables that decide shard placement for writes, identity-map lookups, and arbitrary statements. The design decision that matters is what execute_chooser and identity_chooser do when no shard hint is available — return all shards (silent fan-out) or raise (fail fast). Default to raising:

from sqlalchemy.ext.horizontal_shard import ShardedSession
from sqlalchemy import create_engine, select

shards = {
    f"shard_{i}": create_engine(f"postgresql+psycopg://pg-shard-{i}/app")
    for i in range(4)
}

def shard_chooser(mapper, instance, clause=None):
    return f"shard_{instance.tenant_id % 4}"

def identity_chooser(mapper, primary_key, *, lazy_loaded_from, **kw):
    if lazy_loaded_from is not None:
        # related row lives on the same shard as its parent
        return [lazy_loaded_from.identity_token]
    raise LookupError("identity lookup without shard hint — pass tenant context")

def execute_chooser(context):
    tenant_id = context.execution_options.get("tenant_id")
    if tenant_id is not None:
        return [f"shard_{tenant_id % 4}"]
    raise LookupError("statement without tenant_id execution option")

Session = sessionmaker(
    class_=ShardedSession,
    shards=shards,
    shard_chooser=shard_chooser,
    identity_chooser=identity_chooser,
    execute_chooser=execute_chooser,
)

with Session() as session:
    rows = session.scalars(
        select(Event).where(Event.tenant_id == 42, Event.occurred_at >= since),
        execution_options={"tenant_id": 42},
    ).all()

The lazy_loaded_from branch is the N+1 containment mechanism: SQLAlchemy stamps each loaded instance with the shard it came from (identity_token), so lazy loads follow the parent to a single shard instead of fanning out. The SQLAlchemy walkthrough covers a production configuration including result merging for deliberate multi-shard reads.

Step 4 — Resolve Tenants in Hibernate Multi-Tenancy

Hibernate approaches routing from the multi-tenant direction: a CurrentTenantIdentifierResolver names the tenant for the current unit of work, and a MultiTenantConnectionProvider maps that identifier to a DataSource. Mapped onto a sharded topology, “tenant identifier” becomes “shard name”, which pairs naturally with tenant-value schemas built on list partitioning techniques inside each shard.

public class ShardIdentifierResolver
        implements CurrentTenantIdentifierResolver<String> {

    @Override
    public String resolveCurrentTenantIdentifier() {
        String shard = ShardContext.current();     // request-scoped ThreadLocal
        if (shard == null) {
            throw new IllegalStateException(
                "No shard bound to this request; refusing default routing");
        }
        return shard;
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;   // fail if an open Session already targets another shard
    }
}

# application.properties
hibernate.multi_tenant_connection_provider=com.example.ShardConnectionProvider
hibernate.tenant_identifier_resolver=com.example.ShardIdentifierResolver
hibernate.default_batch_fetch_size=32

Throwing on a missing shard context — rather than falling back to a default — is deliberate: a silent default sends writes to the wrong shard, which is far harder to repair than a failed request. default_batch_fetch_size collapses N lazy collection loads into ceil(N/32) IN-list queries, which matters doubly here because each avoided query is an avoided shard round trip. The Hibernate walkthrough implements the connection provider and request filter end to end.

Step 5 — Keep Migrations and Partition DDL Out of Each Other’s Lane

Schema migration tools (Django migrations, Alembic, Flyway) and partition automation both issue DDL against the same tables, and unmanaged overlap produces two recurring incidents: autogeneration proposing DROP TABLE for child partitions it does not recognize, and partition jobs failing because a migration altered the parent mid-run.

Draw the ownership boundary by lifetime. The migration tool owns objects with a stable lifetime — the parent table, columns, indexes, constraints. Partition automation owns objects with a scheduled lifetime — child partitions created ahead of time and dropped at retention. Then make each tool blind to the other’s objects. In Alembic, filter child partitions out of autogenerate reflection:

# alembic/env.py
import re

PARTITION_CHILD = re.compile(r"^(events|metrics)_\d{4}_\d{2}$")

def include_object(obj, name, type_, reflected, compare_to):
    if type_ == "table" and PARTITION_CHILD.match(name or ""):
        return False           # never diff, never drop child partitions
    if type_ == "table" and name and name.endswith("_default"):
        return False
    return True

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    include_object=include_object,
)

Django needs the inverse trick: child tables never enter model state at all (they are created by RunSQL or a management command), so makemigrations cannot see or drop them — the risk is instead a parent-table migration written as a plain CreateModel, which produces an unpartitioned table. Parent DDL must go through RunSQL with state_operations, as the Django walkthrough shows. Finally, serialize the two writers: run partition automation and migrations under the same advisory lock (pg_advisory_xact_lock) so a deploy-time ALTER TABLE on the parent never interleaves with a CREATE TABLE ... PARTITION OF.

Step 6 — Put the Partition Key in the Primary Key

PostgreSQL requires every primary key and unique constraint on a partitioned table to include the partition key. ORMs assume a single-column surrogate key. Resolving this tension is a schema decision that every ORM integration inherits:

CREATE TABLE events (
  id           bigint GENERATED BY DEFAULT AS IDENTITY,
  tenant_id    integer      NOT NULL,
  occurred_at  timestamptz  NOT NULL,
  payload      jsonb,
  PRIMARY KEY (id, occurred_at)        -- partition key is part of identity
) PARTITION BY RANGE (occurred_at);

SQLAlchemy and Hibernate model composite keys natively — SQLAlchemy with two primary_key=True columns, Hibernate with @IdClass or @EmbeddedId:

class Event(Base):
    __tablename__ = "events"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    occurred_at: Mapped[datetime] = mapped_column(
        TIMESTAMP(timezone=True), primary_key=True
    )
    tenant_id: Mapped[int]

Two consequences follow. First, every identity lookup now requires both columns — session.get(Event, (evt_id, occurred_at)) — which is exactly what pruning needs, so the constraint that felt like friction is actually the fix for the get(pk=...) full scan. Second, foreign keys referencing the partitioned table must carry both columns too, which is why high-volume partitioned tables are usually referenced logically (application-checked) rather than with declared FKs. Django supports composite primary keys natively from 5.2 (CompositePrimaryKey); on earlier versions the standard workaround is to let Django believe id is the primary key while the database enforces the composite one.

Configuration Reference

Operational Contrast

ORM-layer routing occupies the middle ground between two adjacent architectures. Compared with hand-rolled application-level sharding logic, the ORM approach reuses machinery you already run — sessions, unit-of-work, identity maps — and keeps routing decisions next to the data model, at the cost of accepting each framework’s constraints (Django routers cannot see query filters; Hibernate binds tenancy to session lifecycle). Hand-rolled logic is framework-independent and can route on anything, but reimplements identity tracking and per-shard result merging that ShardedSession provides for free.

Compared with proxy routing architectures, the trade inverts. A proxy (ProxySQL, HAProxy with SQL awareness) routes without any application change and covers every client uniformly — including the ad-hoc scripts and BI tools an ORM guard never sees. But a proxy parses SQL text after the ORM has already generated it: it cannot inject a missing partition-key predicate, cannot fix an implicit cast, and cannot contain lazy-load fan-out, because by the time the query reaches it the damage is committed. In practice the layers compose: ORM-level enforcement guarantees prunable queries, and a proxy handles fleet-wide concerns like failover and read/write splitting beneath it.

Failure Modes

Common Mistakes

• Trusting get(pk=...) on a partitioned table. Identity lookups without the partition key scan the whole partition tree. Adopt composite identity so the ORM is forced to carry the key, or wrap lookups in helpers that require it.
• Letting the router be the only routing mechanism in Django. db_for_read never sees queryset filters, so shard choice by router alone works only for instance-bound operations. Route explicitly with .using() and keep the router as the guard against cross-shard relations.
• Filtering on an expression over the partition key. occurred_at__date, lower(tenant_id), or a cast in a Hibernate criteria wraps the column and defeats pruning even though the “filter is there”. Filter on the bare column with correctly typed values.
• Running makemigrations/autogenerate against a database with live partition automation and applying the output unreviewed. Add a CI gate that fails on any generated DropTable touching partition-named tables.

FAQ

Related

• Using Django ORM with PostgreSQL Partitioned Tables — complete walkthrough: RunSQL parent DDL, partition-scoped manager, and pruning verification
• SQLAlchemy Horizontal Sharding & Partition Routing — ShardedSession configuration, chooser callables, and multi-shard result handling
• Hibernate Partition-Aware Multi-Tenant Routing — connection providers, tenant resolvers, and request-scoped shard binding
• Automated Partition Creation Workflows — the partition DDL automation your migration tooling must coexist with