PostgreSQL Partitioning — Range, List, Hash & Time-Based Strategies

Why partition tables?#

A single table with 500 million rows is painful. Queries slow down, vacuuming takes forever, index bloat grows, and backups stall. Partitioning splits one logical table into smaller physical tables. Queries only touch the partitions they need.

PostgreSQL has supported declarative partitioning since version 10, and it keeps getting better.

When partitioning makes sense#

• Tables with hundreds of millions of rows
• Time-series data (logs, events, metrics) where old data can be dropped
• Multi-tenant systems where tenants rarely query across each other
• Tables where most queries filter on one predictable column

Partitioning is not a substitute for indexing. If a B-tree index solves your problem, use that first.

The three partitioning strategies#

Range partitioning#

Split rows by a continuous range of values. Most common for time-series data.

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    created_at  timestamptz NOT NULL,
    event_type  text NOT NULL,
    payload     jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

The upper bound is exclusive. A row with created_at = '2026-02-01 00:00:00' goes into events_2026_02, not events_2026_01.

List partitioning#

Split rows by a discrete set of values. Useful for region, tenant, or status columns.

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    region      text NOT NULL,
    total       numeric NOT NULL,
    created_at  timestamptz NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('us-east', 'us-west');

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('eu-west', 'eu-central');

CREATE TABLE orders_apac PARTITION OF orders
    FOR VALUES IN ('ap-southeast', 'ap-northeast');

Hash partitioning#

Distribute rows evenly across N partitions using a hash of the partition key. Good for spreading write load when no natural range or list exists.

CREATE TABLE sessions (
    id          uuid NOT NULL,
    user_id     bigint NOT NULL,
    data        jsonb
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions
    FOR HASH WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FOR HASH WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
    FOR HASH WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
    FOR HASH WITH (MODULUS 4, REMAINDER 3);

Hash partitioning does not support partition pruning on range queries. It only prunes on equality.

Partition pruning#

The query planner eliminates partitions that cannot contain matching rows. This is the main performance win.

EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

The plan should show only events_2026_03 being scanned. If you see all partitions, check that enable_partition_pruning is on (it is by default) and that your WHERE clause matches the partition key type exactly.

Runtime pruning#

PostgreSQL 11 added runtime partition pruning. Even with parameterized queries or subqueries, the planner prunes at execution time:

PREPARE recent_events(timestamptz, timestamptz) AS
SELECT * FROM events WHERE created_at >= $1 AND created_at < $2;

EXECUTE recent_events('2026-03-01', '2026-04-01');

Only the March partition is scanned despite the prepared statement.

Declarative partitioning details#

Default partition#

Catch rows that do not match any defined partition:

CREATE TABLE events_default PARTITION OF events DEFAULT;

Without a default partition, inserting a row that matches no partition raises an error. With one, unmatched rows land in the default. Be careful: a large default partition defeats the purpose.

Indexes on partitioned tables#

Create indexes on the parent table. PostgreSQL propagates them to all partitions:

CREATE INDEX idx_events_type ON events (event_type);

Each partition gets its own physical index. You can also create indexes on individual partitions if needed.

Unique constraints#

Unique and primary key constraints must include the partition key:

-- This works
ALTER TABLE events ADD PRIMARY KEY (id, created_at);

-- This does NOT work (partition key not included)
-- ALTER TABLE events ADD PRIMARY KEY (id);

This is a fundamental limitation of declarative partitioning.

pg_partman for automated management#

Manually creating monthly partitions is tedious. The pg_partman extension automates it.

-- Install the extension
CREATE EXTENSION pg_partman;

-- Configure automatic partitioning
SELECT partman.create_parent(
    p_parent_table  => 'public.events',
    p_control       => 'created_at',
    p_type          => 'native',
    p_interval      => 'monthly',
    p_premake       => 3
);

This creates partitions for the current month, three months ahead, and manages retention.

Retention policy#

Drop old partitions automatically:

UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

Run the maintenance function on a schedule (cron or pg_cron):

SELECT partman.run_maintenance();

This creates upcoming partitions and drops expired ones.

Time-based partitioning for logs and events#

The most common partitioning use case. A practical setup:

1. Partition by month for moderate-volume tables (under 100M rows/month)
2. Partition by day for high-volume tables (over 100M rows/month)
3. Pre-create partitions at least 3 periods ahead
4. Set retention to drop partitions older than your compliance window
5. Use a default partition as a safety net

Dropping a partition is instant compared to deleting millions of rows:

-- Instant: drop the entire partition
DROP TABLE events_2025_01;

-- Slow: delete rows from a monolithic table
-- DELETE FROM events WHERE created_at < '2025-02-01';

Migrating an existing table to partitioned#

PostgreSQL does not support ALTER TABLE ... PARTITION BY on an existing table. You must:

1. Create a new partitioned table with the same schema
2. Create the required partitions
3. Copy data from the old table into the new one
4. Swap names in a transaction

BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;
COMMIT;

For zero-downtime migration, use logical replication or tools like pg_rewrite.

Monitoring partitions#

Keep an eye on partition health:

-- Count rows per partition
SELECT
    child.relname AS partition_name,
    pg_stat_get_live_tuples(child.oid) AS row_count
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'events'
ORDER BY child.relname;

Watch for: unbalanced partitions, a growing default partition, and missing future partitions.

Key takeaways#

1. Range partitioning is the go-to for time-series data — logs, events, metrics
2. List partitioning works for discrete categories — regions, tenants, statuses
3. Hash partitioning distributes evenly but only prunes on equality
4. Partition pruning is your main performance win — verify it with EXPLAIN
5. pg_partman automates partition creation, pre-making, and retention
6. Unique constraints must include the partition key — plan your schema accordingly
7. Dropping partitions is instant — the killer feature for log/event retention

---

Article #419 in the Codelit engineering series. Explore our full library of system design, infrastructure, and architecture guides at codelit.io.