Partitioning

-- 1) Range partitioning — time-series
CREATE TABLE events (
    id          BIGSERIAL,
    occurred_at TIMESTAMPTZ NOT NULL,
    user_id     BIGINT NOT NULL,
    payload     JSONB NOT NULL,
    PRIMARY KEY (id, occurred_at)            -- partition key must be in PK or unique constraints
) PARTITION BY RANGE (occurred_at);

-- Child partitions per month
CREATE TABLE events_2024_11 PARTITION OF events FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE events_2024_12 PARTITION OF events FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
CREATE TABLE events_2025_01 PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Default partition for stray rows (optional)
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Insert: routes to the right partition automatically
INSERT INTO events (occurred_at, user_id, payload)
VALUES (NOW(), 42, '{"type":"signin"}');

-- 2) List partitioning — multi-tenant
CREATE TABLE invoices (
    id BIGSERIAL,
    tenant_id TEXT NOT NULL,
    total_cents BIGINT NOT NULL,
    PRIMARY KEY (id, tenant_id)
) PARTITION BY LIST (tenant_id);

CREATE TABLE invoices_acme   PARTITION OF invoices FOR VALUES IN ('acme');
CREATE TABLE invoices_beta   PARTITION OF invoices FOR VALUES IN ('beta', 'beta-eu');
CREATE TABLE invoices_others PARTITION OF invoices DEFAULT;

-- 3) Hash partitioning — even distribution by key
CREATE TABLE messages (
    id BIGSERIAL,
    conversation_id BIGINT NOT NULL,
    body TEXT NOT NULL,
    PRIMARY KEY (id, conversation_id)
) PARTITION BY HASH (conversation_id);

CREATE TABLE messages_p0 PARTITION OF messages FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE messages_p1 PARTITION OF messages FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE messages_p2 PARTITION OF messages FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE messages_p3 PARTITION OF messages FOR VALUES WITH (modulus 4, remainder 3);

-- Hash is great when the key has no natural ordering and you want even spread.

-- 4) Partition pruning — the planner skips irrelevant partitions
EXPLAIN ANALYZE
SELECT * FROM events WHERE occurred_at >= '2025-01-15' AND occurred_at < '2025-01-20';
-- Plan: only events_2025_01 is scanned.

-- Pruning works at PLAN time and EXECUTION time (parameterised queries).

-- 5) Indexes — created on each partition
-- A 'global' index doesn't exist; create indexes per partition or on the parent (cascades to all):
CREATE INDEX ON events (user_id, occurred_at);     -- creates on every partition
CREATE INDEX ON events_2025_01 (payload jsonb_path_ops);   -- one-off per partition

-- 6) Constraints
ALTER TABLE events ADD CONSTRAINT events_total_positive
    CHECK (id > 0);                                 -- inherited by all partitions

-- 7) Adding a new partition
CREATE TABLE events_2025_02 PARTITION OF events FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Attach an existing table
CREATE TABLE events_2025_03 (LIKE events INCLUDING ALL);
-- Backfill into it
INSERT INTO events_2025_03 SELECT * FROM source WHERE occurred_at >= '2025-03-01';
ALTER TABLE events ATTACH PARTITION events_2025_03 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- 8) Dropping old data — instant
DROP TABLE events_2024_11;
-- Or detach + archive:
ALTER TABLE events DETACH PARTITION events_2024_11;
-- events_2024_11 becomes a standalone table; can be moved to cold storage / archived / dropped later.

-- 9) Automated partition management with pg_partman (extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'occurred_at',
    p_type         => 'native',
    p_interval     => 'monthly',
    p_premake      => 4                              -- pre-create 4 future partitions
);

-- Cron a maintenance job (built-in scheduler or pg_cron):
SELECT partman.run_maintenance_proc();              -- creates new + drops old per config

-- 10) Foreign keys — pre-PG 12: only PARENT to child; PG 12+: child REFERENCES parent unrestricted
-- BUT: a non-partitioned table can REFERENCE a partitioned table only with caveats; check version.

-- 11) When partitioning helps
--   • Time-series with rolling retention (drop old months instantly)
--   • Tables > 50-100 GB where vacuum on the whole table is expensive
--   • Heavy reads filtering on the partition key (pruning skips most of the data)
--   • Multi-tenant where one tenant dominates (separate per tenant)

-- 12) When partitioning hurts
--   • Small tables (< 1M rows) — overhead exceeds benefit
--   • Random-access workloads not filtering on partition key — pruning useless
--   • Joins between two partitioned tables on different keys — slower
--   • Frequent ALTER TABLE / DDL — must repeat per partition

-- 13) Maintenance gotchas
--   • ANALYZE per partition — autovacuum handles each independently
--   • Statistics: pg_stat_user_tables shows per-partition; use pg_stat_user_tables joined to pg_partitioned_table
--   • Locking: ATTACH/DETACH acquires SHARE UPDATE EXCLUSIVE briefly
--   • Backups: pg_dump exports each partition; restoration may need order

-- 14) Sub-partitioning — partitions can have partitions
CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01')
    PARTITION BY HASH (user_id);
CREATE TABLE events_2025_q1_p0 PARTITION OF events_2025_q1 FOR VALUES WITH (modulus 4, remainder 0);
-- Use only when one dimension isn't enough; complexity rises fast.

-- 15) Common bugs
--   • Partition key not in primary key — error
--   • Forgot to create future partitions → inserts fail with 'no partition'; use DEFAULT or pg_partman
--   • Updating the partition key column — row moves between partitions (PG 11+)
--   • Indexes on parent table — cascade; can be slow on huge tables; create CONCURRENTLY where supported
--   • Foreign keys to partitioned tables — version-dependent; verify
--   • Hash modulus changes — need to redistribute data; plan capacity
--   • Trying to use partitioning for compression — wrong tool; use TOAST tuning or external columnar engines
--   • Big DEFAULT partition — DEFAULT lacks pruning benefits; alert if it grows
--   • Cron not creating new partitions → inserts fail at month boundary

CREATE TABLE events (id bigint, ts timestamptz, payload jsonb)
PARTITION BY RANGE (ts);

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');