Chapter 7: Postgres declarative partitioning via SQLAlchemy. When to partition, how to declare it, and pg_partman for automation.

When to partition

  • Tables > 100 GB.
  • Heavy date-range queries.
  • Need to drop old data fast (TRUNCATE / DROP partition).
  • Per-tenant isolation.

For smaller: don’t bother. See Postgres Partitioning .

Declarative partitioning in SQLAlchemy

class Event(Base):
    __tablename__ = "events"
    __table_args__ = {
        "postgresql_partition_by": "RANGE (occurred_at)",
    }
    
    id: Mapped[int] = mapped_column(primary_key=True)
    occurred_at: Mapped[datetime] = mapped_column(primary_key=True)
    payload: Mapped[dict] = mapped_column(JSONB)

Note: partition key must be in the primary key.

Partition definitions

SQLAlchemy doesn’t have first-class partition declarations. Create them via raw SQL in migrations:

# Alembic migration
op.execute("""
    CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
""")
op.execute("""
    CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01')
""")

Manual partition creation is a chore. pg_partman automates:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control      := 'occurred_at',
    p_type         := 'range',
    p_interval     := 'monthly',
    p_premake      := 4
);

A periodic maintenance job creates new partitions ahead of time:

SELECT partman.run_maintenance();

Schedule via pg_cron or external cron.

Range partitioning

By time:

CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

By id ranges:

CREATE TABLE events_part_a PARTITION OF events FOR VALUES FROM (0) TO (1000000);

Common for time-series + retention.

List partitioning

__table_args__ = {"postgresql_partition_by": "LIST (region)"}

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-north');

For categorical splits.

Hash partitioning

__table_args__ = {"postgresql_partition_by": "HASH (user_id)"}

CREATE TABLE messages_p0 PARTITION OF messages FOR VALUES WITH (modulus 8, remainder 0);
CREATE TABLE messages_p1 PARTITION OF messages FOR VALUES WITH (modulus 8, remainder 1);
-- ... 8 total

Even distribution; great for high-throughput by-user. Can’t drop “old” partitions (no logical aging).

Indexes on partitioned

class Event(Base):
    ...
    __table_args__ = (
        Index("ix_events_user_occurred", "user_id", "occurred_at"),
        {"postgresql_partition_by": "RANGE (occurred_at)"},
    )

Postgres creates indexes on each partition. Each partition has its own (small, fast).

Querying partitions

stmt = select(Event).where(Event.occurred_at >= '2026-02-01', Event.occurred_at < '2026-03-01')

Postgres planner prunes — only scans matching partitions.

For cross-partition queries: still works, may scan many partitions.

Partition pruning verification

explain = await session.execute(text("EXPLAIN " + str(stmt.compile(compile_kwargs={"literal_binds": True}))))
for row in explain:
    print(row[0])
# Look for "Append" with only relevant partitions

If query still scans all partitions: predicate doesn’t allow pruning. Common causes:

  • Function on the partition key: date_trunc('month', occurred_at).
  • Parameter not yet known at plan time (workaround: prepared statements with parameter inlining).

Retention via DROP

DROP TABLE events_2024_01;  -- instant

vs

DELETE FROM events WHERE occurred_at < '2024-02-01';  -- hours

Drop a partition; instant. The big op win of partitioning.

Foreign keys

Foreign keys to partitioned tables: supported. Foreign keys from partitioned tables to others: supported.

Composite per-partition FKs are checked per partition. Watch insert performance.

Constraints

Each partition can have its own constraints; or inherited from parent. Standard CHECK constraints help the planner with pruning.

Migration to partitioned

Existing big table → partitioned:

  1. Create new partitioned table with the same shape.
  2. Copy data in batches (or via logical replication).
  3. Switch writes to new table.
  4. Drop old table.

Or: pg_partman 5+ has an import mode. Or: pg_repack with partitioning support.

Common mistakes

1. Partitioning small tables

Postgres handles 100 GB tables fine. Below that: partitioning is overhead with no benefit.

2. Wrong partition key

Queries don’t filter by partition key → no pruning. Identify query patterns first.

3. Manual partition creation

Forget; midnight inserts fail. Use pg_partman.

4. Hundreds of partitions

Planner overhead. Aim <100 in most cases.

5. Not using EXPLAIN

Verify pruning works. Don’t assume.

What’s next

Chapter 8: RLS and multi-tenancy.

Read this next

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .