SQL Table Partitioning — Function on Key Disables Pruning

-- ============================================================
-- RANGE PARTITIONING: split by date — most common for time-series
-- Each partition holds one month of data
-- ============================================================
CREATE TABLE orders (
    order_id     BIGINT NOT NULL,
    customer_id  INT NOT NULL,
    total_amount DECIMAL(10, 2),
    created_at   TIMESTAMP NOT NULL,
    status       VARCHAR(20)
) PARTITION BY RANGE (created_at);

-- 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');
-- ... continue for each month

-- Catch-all partition for values outside defined ranges
-- Without this, out-of-range inserts fail with a hard error
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- ============================================================
-- LIST PARTITIONING: split by discrete category values
-- Each partition holds a specific region's data
-- ============================================================
CREATE TABLE transactions (
    txn_id      BIGINT NOT NULL,
    amount      DECIMAL(12, 2),
    region      VARCHAR(10) NOT NULL,
    txn_date    DATE
) PARTITION BY LIST (region);

CREATE TABLE txn_north_america PARTITION OF transactions
    FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE txn_europe PARTITION OF transactions
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES', 'IT');
CREATE TABLE txn_asia_pacific PARTITION OF transactions
    FOR VALUES IN ('JP', 'KR', 'AU', 'IN', 'SG');
CREATE TABLE txn_default PARTITION OF transactions DEFAULT;

-- ============================================================
-- HASH PARTITIONING: even distribution — no semantic meaning
-- Use when you have no natural range or list dimension
-- ============================================================
CREATE TABLE user_events (
    event_id    BIGINT NOT NULL,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50),
    event_time  TIMESTAMP
) PARTITION BY HASH (user_id);

CREATE TABLE user_events_p0 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_events_p1 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_events_p2 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_events_p3 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- ============================================================
-- COMPOSITE PARTITIONING: range (monthly) + hash sub-partitions
-- Time-based pruning at level 1, even distribution at level 2
-- Solves hot-partition problem when recent months are write-heavy
-- ============================================================
CREATE TABLE audit_log (
    log_id      BIGINT NOT NULL,
    actor_id    INT NOT NULL,
    action      VARCHAR(100),
    log_time    TIMESTAMP NOT NULL
) PARTITION BY RANGE (log_time);

-- Monthly partition, itself partitioned by hash on actor_id
CREATE TABLE audit_log_2026_01 PARTITION OF audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY HASH (actor_id);

CREATE TABLE audit_log_2026_01_p0 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE audit_log_2026_01_p1 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE audit_log_2026_01_p2 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE audit_log_2026_01_p3 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- ============================================================
-- DIAGNOSIS: verify partition pruning is active
-- Run EXPLAIN before and after any WHERE clause change
-- ============================================================

-- GOOD: bare column with direct range comparison — pruning fires
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-02-01';
-- Expected output: Partitions scanned: 1
-- Execution time: ~200ms on 48-partition table

-- BAD: function call on partition key — pruning disabled
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE DATE_TRUNC('month', created_at) = '2026-01-01';
-- Expected output: Partitions scanned: 48
-- Execution time: ~45s — 225x regression

-- BAD: EXTRACT on partition key — same problem, different syntax
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE EXTRACT(year FROM created_at) = 2026
  AND EXTRACT(month FROM created_at) = 1;
-- Expected output: Partitions scanned: 48

-- BAD: OR with non-partition-key column — forces full scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE created_at >= '2026-01-01'
   OR updated_at >= '2026-01-01';
-- Expected output: Partitions scanned: 48
-- Fix: split into two queries with UNION ALL if the OR is unavoidable

-- ============================================================
-- PARTITION SIZE AUDIT: identify hot or imbalanced partitions
-- Run monthly to catch retention and growth anomalies
-- ============================================================
SELECT
    c.relname AS partition_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_stat_user_tables.n_live_tup AS live_rows
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
JOIN pg_class p ON p.oid = i.inhparent
JOIN pg_stat_user_tables ON pg_stat_user_tables.relname = c.relname
WHERE p.relname = 'orders'
ORDER BY pg_total_relation_size(c.oid) DESC;

-- ============================================================
-- SLIDING WINDOW: automated partition management
-- Create future partition, drop expired one — zero downtime
-- Run monthly via cron or internal scheduler
-- ============================================================

-- Step 1: Create next month's partition before data arrives
CREATE TABLE IF NOT EXISTS orders_2026_04 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Step 2: Detach the partition from 13 months ago from the parent
-- DETACH removes catalog entry without locking the parent table
ALTER TABLE orders DETACH PARTITION orders_2025_03;

-- Step 3: Drop the physical files — instant once detached
DROP TABLE orders_2025_03;

-- PostgreSQL 14+: use CONCURRENTLY to avoid any parent table lock during detach
-- ALTER TABLE orders DETACH PARTITION orders_2025_03 CONCURRENTLY;

-- ============================================================
-- AUTOMATED PARTITION CREATION: stored procedure
-- Call monthly to stay 6 months ahead of data arrival
-- Schema: io.thecodeforge.maint for operational procedures
-- ============================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.maint.create_monthly_partitions(
    p_table_name TEXT,
    p_months_ahead INT DEFAULT 6
)
LANGUAGE plpgsql AS $$
DECLARE
    v_start_date DATE;
    v_end_date   DATE;
    v_partition_name TEXT;
    v_current    DATE := DATE_TRUNC('month', CURRENT_DATE);
BEGIN
    FOR i IN 0..p_months_ahead LOOP
        v_start_date     := v_current + (i || ' months')::INTERVAL;
        v_end_date       := v_start_date + INTERVAL '1 month';
        v_partition_name := p_table_name || '_' || TO_CHAR(v_start_date, 'YYYY_MM');

        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
             FOR VALUES FROM (%L) TO (%L)',
            v_partition_name,
            p_table_name,
            v_start_date,
            v_end_date
        );

        RAISE NOTICE 'Ensured partition exists: % (% to %)',
            v_partition_name, v_start_date, v_end_date;
    END LOOP;
END;
$$;

-- Usage: call at the start of each month to stay ahead
CALL io.thecodeforge.maint.create_monthly_partitions('orders', 6);

-- ============================================================
-- PARTITION HEALTH CHECK: detect gaps and size anomalies
-- Run weekly — catches maintenance job failures before inserts fail
-- ============================================================
SELECT
    c.relname                                                    AS partition_name,
    pg_size_pretty(pg_total_relation_size(c.oid))               AS total_size,
    pg_stat_user_tables.n_live_tup                              AS live_rows,
    pg_stat_user_tables.last_autoanalyze                        AS last_analyzed
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
JOIN pg_class p ON p.oid = i.inhparent
JOIN pg_stat_user_tables ON pg_stat_user_tables.relname = c.relname
WHERE p.relname = 'orders'
ORDER BY c.relname;

-- Monitor DEFAULT partition for unexpected row accumulation
-- Rows landing here mean a future partition is missing
SELECT COUNT(*) AS default_partition_rows
FROM orders_default;

-- ============================================================
-- COMPOSITE: range (monthly) + hash (4 buckets per month)
-- Prevents hot-partition problem when traffic grows over time
-- Total partitions: months × hash_modulus (e.g., 48 × 4 = 192)
-- ============================================================
CREATE TABLE event_stream (
    event_id    BIGINT NOT NULL,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50),
    event_time  TIMESTAMP NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (event_time);

-- Monthly range partition, itself partitioned by hash on user_id
CREATE TABLE event_stream_2026_01 PARTITION OF event_stream
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY HASH (user_id);

CREATE TABLE event_stream_2026_01_p0 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE event_stream_2026_01_p1 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE event_stream_2026_01_p2 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE event_stream_2026_01_p3 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- ============================================================
-- PRUNING BEHAVIOR: which queries prune and at which level
-- Understanding this is essential for query design on composite tables
-- ============================================================

-- FULL PRUNING: both range key and hash key in WHERE
-- Planner prunes level 1 (month) then level 2 (hash bucket)
-- Touches exactly 1 leaf partition
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_stream
WHERE event_time >= '2026-01-01' AND event_time < '2026-02-01'
  AND user_id = 12345;
-- Partitions scanned: 1 — the hash bucket for user 12345 within January

-- PARTIAL PRUNING: range key only
-- Level 1 prunes to one month; level 2 scans all 4 hash buckets
-- Still useful — touches 4 partitions instead of 192
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_stream
WHERE event_time >= '2026-01-01' AND event_time < '2026-02-01';
-- Partitions scanned: 4 (all hash sub-partitions of 2026_01)

-- NO PRUNING: hash key only, no range key
-- Level 1 cannot prune — hash key alone doesn't establish a time boundary
-- Scans every month's hash bucket for the matching remainder
-- Often slower than the unpartitioned equivalent due to per-partition overhead
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_stream
WHERE user_id = 12345;
-- Partitions scanned: all leaf partitions across all months

-- ============================================================
-- COMPOSITE PARTITION HEALTH CHECK
-- Verify all sub-partitions exist for each range parent
-- Missing sub-partitions silently drop inserts to the DEFAULT bucket
-- ============================================================
SELECT
    parent.relname  AS range_partition,
    child.relname   AS hash_sub_partition,
    pg_size_pretty(pg_total_relation_size(child.oid)) AS size
FROM pg_inherits i1
JOIN pg_class parent ON parent.oid = i1.inhrelid
JOIN pg_inherits i2  ON i2.inhparent = parent.oid
JOIN pg_class child  ON child.oid = i2.inhrelid
JOIN pg_class root   ON root.oid = i1.inhparent
WHERE root.relname = 'event_stream'
ORDER BY parent.relname, child.relname;