PostgreSQL Declarative Partitioning: Range, Indexes, and pg_partman Automation

CREATE TABLE orders (
  id         BIGSERIAL,
  user_id    UUID NOT NULL,
  amount     NUMERIC(10,2),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_q1 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Default partition catches out-of-range rows
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Creating an index on the parent propagates to all partitions
CREATE INDEX idx_orders_user ON orders (user_id);

-- Unique constraint must include partition key
CREATE UNIQUE INDEX idx_orders_id_created ON orders (id, created_at);

EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-03-31';
-- Should show: Append -> Seq Scan on orders_2025_q1 (only 1 partition)
-- NOT: scans all partitions

-- Create new partition before the month starts
CREATE TABLE orders_2025_q3 PARTITION OF orders
  FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

-- Detach old partition (instant, no data copy)
ALTER TABLE orders DETACH PARTITION orders_2025_q1;

-- Now orders_2025_q1 is a standalone table
-- Archive it to cold storage, then drop
\COPY orders_2025_q1 TO '/archive/orders_q1.csv' CSV HEADER;
DROP TABLE orders_2025_q1;

CREATE EXTENSION pg_partman;

-- Auto-create monthly partitions and manage retention
SELECT partman.create_parent(
  p_parent_table => 'public.orders',
  p_control      => 'created_at',
  p_type         => 'range',
  p_interval     => 'monthly',
  p_premake      => 3  -- create 3 future partitions in advance
);

-- Run maintenance (create future partitions, drop expired ones)
CALL partman.run_maintenance_proc();