PostgreSQL Partitioning

Faster queries, easier maintenance of large tables.

Range Partitioning¶

CREATE TABLE orders (
    id SERIAL, 
    created_at TIMESTAMPTZ NOT NULL, 
    total DECIMAL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_q1 PARTITION OF orders 
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

List Partitioning¶

CREATE TABLE logs (id SERIAL, level TEXT, message TEXT) 
PARTITION BY LIST (level);

CREATE TABLE logs_error PARTITION OF logs 
FOR VALUES IN ('error','fatal');

pg_partman¶

CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders','created_at','native','monthly');

• 100M+ rows
• Time-series data
• DROP PARTITION instead of DELETE

Best Practices¶

Partitioning pays off starting from approximately 100 million rows or when you need to efficiently delete old records (DROP PARTITION is instant, DELETE can take hours). PostgreSQL automatically performs partition pruning — a query with a WHERE condition on the partition key reads only relevant partitions.

For time series, range partitioning by month or week is the most common choice. Consider using pg_partman for automatic management — it creates new partitions in advance and can archive or drop old ones. Hash partitioning is useful for even data distribution, for example by tenant_id in multi-tenant applications. Remember that indexes need to be created on each partition separately, although PostgreSQL 11+ can do this automatically via partitioned indexes.

Partitioning for Large Tables¶

Speeds up queries and simplifies maintenance.