Partitioning¶

Handler: PartitionHandler (DIFF phase)

Partitioning is declared on PGTableMeta and requires the table to be a true PostgreSQL partitioned table (not traditional inheritance).

Declaring a Partitioned Table¶

class Meta(PGTableMeta):
    pg_partition = {"strategy": "RANGE", "columns": ["created_at"]}

Partition Strategies¶

Partition Columns¶

# Range partition by date
pg_partition = {"strategy": "RANGE", "columns": ["created_at"]}

# List partition by category
pg_partition = {"strategy": "LIST", "columns": ["category"]}

# Hash partition by id
pg_partition = {"strategy": "HASH", "columns": ["id"]}

Attaching Partitions¶

Child partitions are declared via pg_partitions:

class Meta(PGTableMeta):
    pg_partition = {"strategy": "RANGE", "columns": ["created_at"]}
    pg_partitions = [
        {"name": "events_2024_q1", "bound": "FOR VALUES FROM ('2024-01-01') TO ('2024-04-01')"},
        {"name": "events_2024_q2", "bound": "FOR VALUES FROM ('2024-04-01') TO ('2024-07-01')"},
    ]

Partitions are automatically attached/detached during migration.

Partition Bounds Syntax¶

RANGE bounds are half-open: the lower bound is inclusive, the upper bound is exclusive. Use UNBOUNDED for open-ended ranges:

{"name": "events_archived", "bound": "FOR VALUES FROM ('2023-01-01') TO (MAXVALUE)"}

HASH partitions use modulus/remainder for round-robin distribution:

{"name": "events_hash_0", "bound": "FOR VALUES WITH (MODULUS 4, REMAINDER 0)"},
{"name": "events_hash_1", "bound": "FOR VALUES WITH (MODULUS 4, REMAINDER 1)"},
{"name": "events_hash_2", "bound": "FOR VALUES WITH (MODULUS 4, REMAINDER 2)"},
{"name": "events_hash_3", "bound": "FOR VALUES WITH (MODULUS 4, REMAINDER 3)"},

DEFAULT Partition¶

{"name": "events_default", "bound": "DEFAULT"}

A DEFAULT partition catches all rows that do not match any other partition bound. Only one DEFAULT partition is allowed per partitioned table.

Sub-Partitioning¶

Create partitions of partitions by declaring a partition strategy on a partition table:

class Event(Base):
    __tablename__ = "events"
    ...

    class Meta(PGTableMeta):
        pg_partition = {"strategy": "RANGE", "columns": ["created_at"]}
        pg_partitions = [
            {"name": "events_2024", "bound": "FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')",
             "sub_partition": {"strategy": "LIST", "columns": ["category"]}},
        ]

Sub-partitions can use a different strategy than the parent. Each sub-partition level has its own pg_partitions entries.

Indexes on Partitioned Tables¶

Creating an index on the parent table automatically creates matching indexes on all partitions:

class Meta(PGTableMeta):
    pg_partition = {"strategy": "RANGE", "columns": ["created_at"]}
    pg_indexes = [
        PgIndexSpec("ix_events_user_id", ["user_id"]),
    ]

The index is propagated to all existing and future partitions. Unique indexes (including PKs) must include the partition key.

FK Constraints on Partitioned Tables¶

Foreign keys can reference a partitioned table (referencing the parent, referencing is routed to partitions). Foreign keys from a partitioned table must include the partition key:

class OrderItem(Base):
    __tablename__ = "order_items"
    ...

    class Meta(PGTableMeta):
        pg_partition = {"strategy": "HASH", "columns": ["order_id"]}

FK from order_items to orders must include order_id in the FK columns.

Lifecycle¶

DETACH CONCURRENTLY (PG 16+)¶

PostgreSQL 16+ supports DETACH CONCURRENTLY, which avoids the ACCESS EXCLUSIVE lock normally required:

ALTER TABLE events DETACH PARTITION events_2023 CONCURRENTLY;

The partition becomes an independent table. A background worker finalizes the detach. You can monitor with pg_detach_pending in pg_partitioned_table.

Maintenance Operations¶

Individual partitions support the same maintenance as regular tables:

• ANALYZE partition_name; on each partition
• VACUUM partition_name; on each partition
• Index creation directly on a partition (not propagated to parent)
• Tablespace management per partition

Inheritance vs Partitioning¶

PartitionHandler only tracks tables with pg_partition declared (true PG partitioning). Traditional inheritance (pg_inherits) is handled separately via PgTableHandler.

Partitioning vs Inheritance: