PostgreSQL Partitioning Practical Guide

-- Create parent table
CREATE TABLE orders (
    id          BIGSERIAL,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20),
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (order_date);

-- Create monthly partitions
CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

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

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

-- Per-partition indexes (automatically inherited)
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, order_date);

-- Default partition (catches data that does not fit any range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE events (
    id          BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (event_type);

CREATE TABLE events_user PARTITION OF events
    FOR VALUES IN ('user_signup', 'user_login', 'user_logout');

CREATE TABLE events_order PARTITION OF events
    FOR VALUES IN ('order_created', 'order_paid', 'order_cancelled');

CREATE TABLE events_system PARTITION OF events
    FOR VALUES IN ('health_check', 'deploy', 'config_change');

CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE TABLE user_sessions (
    id         BIGSERIAL,
    user_id    INTEGER NOT NULL,
    session_id UUID NOT NULL,
    data       JSONB,
    expires_at TIMESTAMP
) PARTITION BY HASH (user_id);

-- Even distribution across 4 partitions
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Check if partition pruning is enabled
SHOW enable_partition_pruning;  -- on

-- Verify pruning with EXPLAIN
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT * FROM orders
WHERE order_date >= '2026-03-01'
  AND order_date < '2026-04-01';

-- Example result:
-- Append (actual rows=50000)
--   -> Seq Scan on orders_2026_03 (actual rows=50000)
--        Filter: (order_date >= '2026-03-01' AND order_date < '2026-04-01')
-- orders_2026_01 and orders_2026_02 are NOT scanned!

-- Install pg_partman
CREATE EXTENSION pg_partman;

-- Configure automatic partition management
SELECT partman.create_parent(
    p_parent_table   => 'public.orders',
    p_control        => 'order_date',
    p_type           => 'native',
    p_interval       => 'monthly',
    p_premake        => 3       -- Pre-create 3 months ahead
);

-- Maintenance function (run daily via cron)
SELECT partman.run_maintenance();

# pg_partman maintenance (daily at 2 AM)
0 2 * * * psql -U postgres -d mydb \
  -c "SELECT partman.run_maintenance();" \
  >> /var/log/pg_partman.log 2>&1

-- Manual automation without pg_partman
CREATE OR REPLACE FUNCTION create_monthly_partition(
    p_table TEXT,
    p_year INTEGER,
    p_month INTEGER
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := format('%s_%s_%s',
        p_table,
        p_year,
        LPAD(p_month::TEXT, 2, '0')
    );
    start_date := make_date(p_year, p_month, 1);
    end_date := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, p_table, start_date, end_date
    );

    RAISE NOTICE 'Created partition: %', partition_name;
END;
$$ LANGUAGE plpgsql;

-- Usage example
SELECT create_monthly_partition('orders', 2026, 4);
SELECT create_monthly_partition('orders', 2026, 5);

-- Detach partition (preserve data, exclude from queries)
ALTER TABLE orders DETACH PARTITION orders_2025_01;

-- Keep the detached partition as a separate table or delete it
DROP TABLE orders_2025_01;  -- Instant deletion (even hundreds of millions of rows)

-- For comparison: DELETE is very slow
-- DELETE FROM orders WHERE order_date < '2025-02-01';  -- Do not do this!

-- Check row count per partition
SELECT
    schemaname || '.' || relname AS partition,
    n_live_tup AS row_count,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

-- List partitions
SELECT
    parent.relname AS parent,
    child.relname AS partition,
    pg_get_expr(child.relpartbound, child.oid) AS bounds
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 = 'orders'
ORDER BY child.relname;

1. Partition key MUST be included in PRIMARY KEY
   CREATE TABLE orders (...) PARTITION BY RANGE (order_date);
   -> PK must be in the form (id, order_date)

2. UNIQUE constraints also need to include the partition key

3. Too many partitions increase planning overhead
   -> Recommended to keep under 1000

4. Cross-partition UPDATE is only supported in PostgreSQL 11+