Table Partitioning

RANGE Partitioning

Partition by a continuous range of values — ideal for time-series data.

-- Create partitioned table
CREATE TABLE orders (
  id         BIGSERIAL,
  user_id    INT,
  total      NUMERIC(10,2),
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

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

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

-- Default partition catches unmatched rows
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Query automatically routes to correct partition(s)
SELECT * FROM orders WHERE created_at >= '2024-01-01';

-- EXPLAIN shows partition pruning
EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

LIST Partitioning

CREATE TABLE customers (
  id      SERIAL,
  name    TEXT,
  region  TEXT NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE customers_us  PARTITION OF customers FOR VALUES IN ('US', 'CA');
CREATE TABLE customers_eu  PARTITION OF customers FOR VALUES IN ('DE', 'FR', 'GB');
CREATE TABLE customers_apac PARTITION OF customers FOR VALUES IN ('JP', 'CN', 'AU');
CREATE TABLE customers_other PARTITION OF customers DEFAULT;

-- Insert routes automatically
INSERT INTO customers (name, region) VALUES ('Alice', 'US');  -- goes to customers_us

HASH Partitioning

Evenly distribute rows across N partitions based on a hash of the partition key.

CREATE TABLE events (
  id      BIGSERIAL,
  user_id INT NOT NULL,
  payload JSONB
) PARTITION BY HASH (user_id);

-- Modulus = total partitions, remainder = 0-based index
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Attach & Detach Partitions

-- Detach a partition (becomes standalone table, instant in PG14+)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- CONCURRENTLY in PG14: non-blocking
ALTER TABLE orders DETACH PARTITION orders_2024_01 CONCURRENTLY;

-- Attach an existing table as a new partition
ALTER TABLE orders ATTACH PARTITION orders_2024_03
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Drop an old partition
DROP TABLE orders_2024_01;

-- Move data from old partition to archive
INSERT INTO archive_orders SELECT * FROM orders_2024_01;
ALTER TABLE orders DETACH PARTITION orders_2024_01;
DROP TABLE orders_2024_01;

Indexes on Partitioned Tables

-- Create index on parent: propagates to all partitions
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Indexes are created on each partition automatically
-- Verify:
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'orders%';

-- Primary key must include partition key
ALTER TABLE orders ADD PRIMARY KEY (id, created_at);

-- Unique constraints must include partition key too
ALTER TABLE orders ADD UNIQUE (id, created_at);

-- 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 parent.oid = inhparent
JOIN pg_class child  ON child.oid  = inhrelid
WHERE parent.relname = 'orders';