Cheatsheet for partitioning with SQLAlchemy.

When to partition

  • Table > 100 GB.
  • Heavy date-range queries.
  • Need to drop old data fast (DROP partition vs DELETE).
  • Per-tenant isolation at partition level.

Declarative partitioned table

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)

Partition key MUST be in the primary key.

Partition definitions (in migrations)

def upgrade():
    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')
    """)

pg_partman (automation)

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
);

Maintenance job creates new partitions / drops old:

SELECT partman.run_maintenance();

Schedule via pg_cron.

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');

Hash partitioning

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

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

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 over only matching partitions

Drop old partition

DROP TABLE events_2024_01;        -- instant

vs DELETE — hours on big tables.

Common mistakes

  • Partitioning <100GB tables — overhead with no benefit.
  • Wrong partition key — no pruning.
  • Manual partitioning without pg_partman — forget; inserts fail.
  • Hundreds of partitions — planner overhead.

Read this next

If you want my pg_partman + retention script, it’s at rajpoot.dev .

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 .