Declarative Table Partitioning

Learning outcomes

• Implement range partitioning for high-volume data
• Utilize partition pruning in multi-tenant schemas

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.

-- Create parent partitioned table
CREATE TABLE metrics (
    metric_id UUID NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    payload JSONB
) PARTITION BY RANGE (recorded_at);

-- Create monthly child partitions
CREATE TABLE metrics_2026_07 PARTITION OF metrics
    FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-08-01 00:00:00+00');

CREATE TABLE metrics_2026_08 PARTITION OF metrics
    FOR VALUES FROM ('2026-08-01 00:00:00+00') TO ('2026-09-01 00:00:00+00');

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.

Primary sources

• PostgreSQL 16 Architecture, MVCC & Query Optimization Manual — PostgreSQL Global Development Group, verified 2026-07-22