Declarative Table Partitioning

Mental model

Declarative Table Partitioning splits a logically huge table into smaller physical partition tables on disk, while presenting a unified table interface to application queries.

Theory

PostgreSQL supports three partitioning strategies:

• Range: Partition by date or numeric ranges (PARTITION BY RANGE (created_at)).
• List: Partition by explicit discrete values (PARTITION BY LIST (region)).
• Hash: Partition by hash modulus (PARTITION BY HASH (tenant_id)).

During query planning, Partition Pruning automatically excludes partitions whose bounds fall outside the WHERE clause predicates, reducing scanned data volume from terabytes to gigabytes.

Alternatives and trade-offs

• Single Giant Table: Simple schema; suffers from bloated indexes and slow maintenance (VACUUM, REINDEX).
• Declarative Partitioning: Fast drop of expired data (DROP TABLE metrics_2024_01), but primary keys must include the partition key column.

Failure modes and misconceptions

1. Missing Partition Key in Primary Key: PostgreSQL requires unique constraints and primary keys on partitioned tables to include all partition key columns.
2. Dynamic Insertion Failures: Inserting a row outside defined partition ranges raises an error unless a DEFAULT partition is created.

Decision scenario

Use Range Partitioning by month for high-volume event tables (>50 million rows/month) so historical data can be archived or dropped instantaneously using DROP TABLE instead of slow DELETE statements.

Learning outcomes

• Design Range, List, and Hash declarative partitioning schemes.
• Enable partition pruning to eliminate unnecessary disk scans.
• Manage automated partition creation for temporal and multi-tenant databases.

Trade-offs

Partitioning accelerates query pruning and bulk data archiving, but cross-partition primary key constraints require including the partition key in all unique indexes.