Partitioning and Table Design

Table partitioning splits a large table into smaller physical pieces called partitions, while the parent table remains as the query target. PostgreSQL supports declarative partitioning with three strategies: RANGE (partition by value ranges, ideal for dates), LIST (partition by discrete value sets), and HASH (partition by hash of a column, for even distribution). Partition pruning means the planner only scans partitions that can contain matching rows, drastically reducing I/O. Each partition can have its own indexes, tablespace, and autovacuum settings. Partitioning also improves maintenance — dropping old data is as fast as DROP TABLE on a partition rather than a slow DELETE. The partition key should be the column most commonly used in WHERE and JOIN conditions.

Example

-- 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;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned