Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Range-partitioned table by year/month
CREATE TABLE orders (
  id         BIGSERIAL,
  user_id    INTEGER NOT NULL,
  total      NUMERIC(10,2),
  status     TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) 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 — catch-all for unmatched rows
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Index on the partitioned table (created on all partitions)
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- Query — planner automatically prunes to relevant partitions
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

-- Drop old partition instantly (vs slow DELETE)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
DROP TABLE orders_2024_01;

-- Check partition structure
SELECT inhrelid::regclass AS partition_name, pg_get_expr(c.relpartbound, c.oid)
FROM pg_inherits
JOIN pg_class c ON c.oid = inhrelid
WHERE inhparent = 'orders'::regclass;