Table Partitioning

Why Partition

Partitioning splits one logical table into smaller physical tables. Queries touch only the partitions they need, and old data can be dropped or archived cheaply instead of deleting millions of rows.

Range Partitioning

CREATE TABLE logs (
  id bigint, created_at timestamptz, message text
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2026_01 PARTITION OF logs
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE logs_2026_02 PARTITION OF logs
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Hash Partitioning

CREATE TABLE audit_events (id int, data jsonb)
  PARTITION BY HASH (id);
CREATE TABLE audit_events_p0 PARTITION OF audit_events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Partition Pruning

When the planner sees a WHERE filter on the partition key, it skips unrelated partitions automatically. To benefit, the partition key must be part of the filter.

Key Points

• Partition by RANGE for time-series data.
• Partition by HASH to spread IO evenly.
• Queries prune partitions when the partition key is filtered.
• Detach and drop old partitions instead of deleting rows one by one.