PostgreSQL Partitioning Production Guide: Range, List, and Hash Strategies for Billion-Row Tables

-- Range partitioning by month on a billion-row events table
CREATE TABLE events (
    id              BIGSERIAL,
    event_time      TIMESTAMPTZ NOT NULL,
    user_id         BIGINT NOT NULL,
    event_type      TEXT NOT NULL,
    payload         JSONB,
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (event_time);

-- Create 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');

-- Create indexes on each partition (inherited automatically in PG 11+)
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_event_type ON events (event_type);

-- List partitioning by region for a multi-region orders table
CREATE TABLE orders (
    id              BIGSERIAL,
    region          TEXT NOT NULL,
    order_date      TIMESTAMPTZ NOT NULL,
    customer_id     BIGINT NOT NULL,
    total_amount    NUMERIC(12,2),
    status          TEXT NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

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

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

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

-- Default partition for any unmapped region
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Hash partitioning for a high-throughput session store
CREATE TABLE sessions (
    session_id      UUID NOT NULL,
    user_id         BIGINT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_active     TIMESTAMPTZ NOT NULL DEFAULT now(),
    session_data    JSONB,
    PRIMARY KEY (session_id)
) PARTITION BY HASH (session_id);

-- Create 8 hash partitions (modulus must be power of 2 for balanced distribution)
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE sessions_p4 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE sessions_p5 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE sessions_p6 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE sessions_p7 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 7);

-- Check that partition pruning is enabled
SHOW enable_partition_pruning;  -- must be 'on'

-- Verify pruning with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events
WHERE event_time >= '2026-03-01' AND event_time < '2026-03-15';

-- Expected output shows only the March partition scanned:
--   Append (actual rows=1523400 loops=1)
--     -> Seq Scan on events_2026_03 (actual rows=1523400 loops=1)
--           Filter: (event_time >= '2026-03-01' AND event_time < '2026-03-15')
--   Planning Time: 0.452 ms
--   Execution Time: 892.31 ms

-- Runtime pruning with a prepared statement
PREPARE get_events(timestamptz, timestamptz) AS
SELECT * FROM events WHERE event_time >= $1 AND event_time < $2;

-- This will prune at execution time, not planning time
EXECUTE get_events('2026-03-01', '2026-03-08');

# Install pg_partman (on Debian/Ubuntu)
sudo apt-get install postgresql-17-partman

# Or compile from source
git clone https://github.com/pgpartman/pg_partman.git
cd pg_partman
make && sudo make install

-- Enable the extension
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Create the parent table first (must already be partitioned)
CREATE TABLE metrics (
    id              BIGSERIAL,
    recorded_at     TIMESTAMPTZ NOT NULL,
    metric_name     TEXT NOT NULL,
    metric_value    DOUBLE PRECISION NOT NULL,
    labels          JSONB,
    PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

-- Register with pg_partman for automatic management
SELECT partman.create_parent(
    p_parent_table   => 'public.metrics',
    p_control        => 'recorded_at',
    p_interval       => '1 day',
    p_premake        => 7,           -- pre-create 7 future partitions
    p_start_partition => '2026-01-01'
);

-- Update the configuration for retention
UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = false,    -- DROP old partitions (not just detach)
    infinite_time_partitions = true  -- always create future partitions
WHERE parent_table = 'public.metrics';

-- Run maintenance for all managed tables (call via pg_cron or crontab)
SELECT partman.run_maintenance();

-- Or for a specific table
SELECT partman.run_maintenance('public.metrics');

# Schedule with pg_cron (recommended)
SELECT cron.schedule('partman-maintenance', '*/15 * * * *',
    $$SELECT partman.run_maintenance()$$);

# Or with system crontab
echo "*/15 * * * * psql -d mydb -c \"SELECT partman.run_maintenance()\"" | crontab -

-- Create the new partitioned table (different name)
CREATE TABLE events_partitioned (
    LIKE events INCLUDING ALL
) PARTITION BY RANGE (event_time);

-- Create partitions covering the full data range
-- (use pg_partman or manual CREATE TABLE ... PARTITION OF)
SELECT partman.create_parent(
    p_parent_table   => 'public.events_partitioned',
    p_control        => 'event_time',
    p_interval       => '1 month',
    p_premake        => 3,
    p_start_partition => '2024-01-01'
);

-- Trigger function to copy writes to the partitioned table
CREATE OR REPLACE FUNCTION sync_to_partitioned() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO events_partitioned VALUES (NEW.*) ON CONFLICT DO NOTHING;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO events_partitioned VALUES (NEW.*)
            ON CONFLICT (id, event_time)
            DO UPDATE SET
                user_id = EXCLUDED.user_id,
                event_type = EXCLUDED.event_type,
                payload = EXCLUDED.payload;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM events_partitioned
            WHERE id = OLD.id AND event_time = OLD.event_time;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_partitioned
    AFTER INSERT OR UPDATE OR DELETE ON events
    FOR EACH ROW EXECUTE FUNCTION sync_to_partitioned();

-- Migrate in batches of 100,000 rows to avoid long locks
DO $$
DECLARE
    batch_start TIMESTAMPTZ := '2024-01-01';
    batch_end   TIMESTAMPTZ;
    total_migrated BIGINT := 0;
BEGIN
    WHILE batch_start < now() LOOP
        batch_end := batch_start + INTERVAL '1 day';

        INSERT INTO events_partitioned
        SELECT * FROM events
        WHERE event_time >= batch_start AND event_time < batch_end
        ON CONFLICT (id, event_time) DO NOTHING;

        GET DIAGNOSTICS total_migrated = ROW_COUNT;
        RAISE NOTICE 'Migrated % rows for %', total_migrated, batch_start;

        batch_start := batch_end;
        PERFORM pg_sleep(0.1);  -- throttle to avoid overloading
    END LOOP;
END $$;

-- In a single transaction, rename tables
BEGIN;
    ALTER TABLE events RENAME TO events_old;
    ALTER TABLE events_partitioned RENAME TO events;
    -- Drop the trigger from the old table
    DROP TRIGGER IF EXISTS trg_sync_partitioned ON events_old;
COMMIT;

-- Verify, then drop the old table after a safe period
-- DROP TABLE events_old;

ERROR: no partition of relation "events" found for row
DETAIL: Partition key of the failing row contains (event_time) = (2026-04-01 00:00:00+00).

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

-- Use CONCURRENTLY to avoid blocking (PostgreSQL 14+)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;

-- This waits for all existing queries to finish before completing
-- No new queries will see the partition after the command starts

-- Check partition age
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relname LIKE 'events_%'
ORDER BY xid_age DESC;

-- Manually freeze old partitions
VACUUM (FREEZE, VERBOSE) events_2024_01;

-- Create the missing partition
CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- The above will automatically move matching rows from DEFAULT
-- to the new partition (PostgreSQL 13+)

-- Manually analyze the parent table (required for cross-partition statistics)
ANALYZE events;

-- Schedule this via pg_cron
SELECT cron.schedule('analyze-events', '0 4 * * *',
    $$ANALYZE events$$);

-- Aggressive autovacuum for the current month's partition
ALTER TABLE events_2026_03 SET (
    autovacuum_vacuum_scale_factor = 0.01,        -- trigger at 1% dead tuples
    autovacuum_vacuum_cost_delay = 2,             -- reduce throttling
    autovacuum_analyze_scale_factor = 0.005       -- re-analyze frequently
);

-- Relaxed settings for older, read-only partitions
ALTER TABLE events_2025_12 SET (
    autovacuum_vacuum_scale_factor = 0.2,         -- default-like behavior
    autovacuum_enabled = true                     -- keep enabled for XID aging
);

#!/bin/bash
# freeze_old_partitions.sh - Freeze partitions older than 6 months

DB_NAME="production"
PARENT_TABLE="events"
CUTOFF=$(date -d "6 months ago" +%Y_%m)

psql -d "$DB_NAME" -t -A -c "
    SELECT relname FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE relname LIKE '${PARENT_TABLE}_%'
      AND relkind = 'r'
      AND relname < '${PARENT_TABLE}_${CUTOFF}'
    ORDER BY relname
" | while read partition; do
    echo "Freezing $partition..."
    psql -d "$DB_NAME" -c "VACUUM (FREEZE, VERBOSE) $partition"
done

-- Reindex a single partition without blocking reads (PostgreSQL 12+)
REINDEX TABLE CONCURRENTLY events_2026_03;

-- Check index bloat across all partitions
SELECT
    schemaname || '.' || tablename AS partition_name,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
WHERE tablename LIKE 'events_%'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;