PostgreSQL Table Partitioning — Range, List, Hash, and Pruning

-- 1. RANGE partitioning on a time column — the dominant pattern
CREATE TABLE events (
    id         BIGSERIAL,
    user_id    BIGINT NOT NULL,
    event_type TEXT   NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    payload    JSONB,
    PRIMARY KEY (created_at, id)        -- partition key MUST be in unique constraints
) PARTITION BY RANGE (created_at);

-- 2. 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 out-of-range rows instead of erroring (PG 11+)
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- 3. Index on the parent — cascades to all current and future partitions (PG 11+)
CREATE INDEX idx_events_user_created ON events (user_id, created_at DESC);

-- 4. Partition pruning in EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2026-02-10' AND created_at < '2026-02-20'
  AND user_id = 42;
-- Append
--  -> Index Scan using events_2026_02_user_id_created_at_idx on events_2026_02
--     Index Cond: (user_id = 42 AND created_at >= ... AND created_at < ...)
-- (Only events_2026_02 is scanned — pruning worked)

-- 5. Without the partition key, every partition is scanned
EXPLAIN
SELECT count(*) FROM events WHERE user_id = 42;
-- Append scans events_2026_01, events_2026_02, events_2026_03, events_default
-- Slower than the equivalent query on a non-partitioned table

-- 6. Instant archive: detach + drop
ALTER TABLE events DETACH PARTITION events_2025_01 CONCURRENTLY;
-- events_2025_01 is now a standalone table; export or drop without affecting parent
DROP TABLE events_2025_01;

-- 7. LIST partitioning — tenant-scoped multi-tenant table
CREATE TABLE documents (
    id BIGSERIAL, tenant_id INT NOT NULL, body JSONB,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);
CREATE TABLE documents_tenant_1 PARTITION OF documents FOR VALUES IN (1);
CREATE TABLE documents_tenant_2 PARTITION OF documents FOR VALUES IN (2);

-- 8. HASH partitioning — distributes rows evenly, no pruning for selective reads
CREATE TABLE jobs (
    id BIGSERIAL, user_id BIGINT NOT NULL, body JSONB,
    PRIMARY KEY (user_id, id)
) PARTITION BY HASH (user_id);
CREATE TABLE jobs_0 PARTITION OF jobs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE jobs_1 PARTITION OF jobs FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE jobs_2 PARTITION OF jobs FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE jobs_3 PARTITION OF jobs FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 9. pg_partman — automate creation, retention, and cleanup
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'created_at',
    p_type         => 'native',
    p_interval     => 'monthly',
    p_premake      => 4
);
UPDATE partman.part_config
   SET retention = '12 months',
       retention_keep_table = false
 WHERE parent_table = 'public.events';
-- Scheduled maintenance (pg_cron or external scheduler)
-- SELECT partman.run_maintenance();