PostgreSQL Table Partitioning

Table Partitioning in PostgreSQL

Partitioning splits a large table into smaller, manageable chunks based on a partition key. Queries that filter on the partition key only scan relevant partitions (partition pruning), dramatically improving performance on tables with millions of rows.

Partition Types

Creating a Range-Partitioned Table

-- Parent table
CREATE TABLE events (
    id          bigserial,
    event_type  text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Monthly partitions
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');

-- Default partition catches anything that doesn't match
CREATE TABLE events_default PARTITION OF events DEFAULT;

Auto-Creating Partitions

Use pg_partman or a cron job to create future partitions automatically:

-- Install pg_partman
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3  -- Create 3 months ahead
);

Performance Benefits

-- This query only scans the January partition
EXPLAIN SELECT * FROM events WHERE created_at BETWEEN '2026-01-15' AND '2026-01-20';
-- Output: "Append" with only events_2026_01 scanned

Maintenance Benefits

• Fast data deletion — Drop a partition instead of DELETE (instant vs hours)
• Independent VACUUM — Each partition is vacuumed separately
• Archival — Detach old partitions and move to cheaper storage

Eazy SaaS Tip: Partition any table exceeding 10 million rows or 10 GB. Time-based partitioning with monthly intervals is the most common and easiest to manage. Always include a DEFAULT partition to catch unexpected data.