Table Partitioning in PostgreSQL

Why Partition Tables?

Partitioning splits a large table into smaller physical pieces while presenting a single logical table. It improves query performance through partition pruning, makes maintenance faster (VACUUM, backups), and enables efficient data lifecycle management (drop old partitions instead of deleting rows).

Range Partitioning (Time-Series)

Range partitioning is ideal for time-series data where queries typically filter by date range.

-- Create the partitioned parent table
CREATE TABLE events (
  id BIGSERIAL,
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE events_2025_03 PARTITION OF events
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Indexes are created per partition
CREATE INDEX ON events (created_at);
CREATE INDEX ON events (event_type);

List Partitioning

-- Partition by region
CREATE TABLE orders (
  id BIGSERIAL,
  customer_id INT NOT NULL,
  region TEXT NOT NULL,
  total NUMERIC(10,2),
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE orders_us   PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu   PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-central');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('apac-south', 'apac-east');

-- Default partition catches everything else
CREATE TABLE orders_other PARTITION OF orders DEFAULT;

Hash Partitioning

-- Evenly distribute data across 4 partitions
CREATE TABLE sessions (
  id UUID NOT NULL,
  user_id INT NOT NULL,
  data JSONB,
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Pruning & Maintenance

-- Verify partition pruning is working
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2025-02-01' AND created_at < '2025-03-01';
-- Should show: scans only events_2025_02, skips other partitions

-- Drop old data instantly (instead of slow DELETE)
DROP TABLE events_2024_01;

-- Detach before dropping (non-blocking in PG14+)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;
DROP TABLE events_2024_01;

-- Automate partition creation (example for monthly partitions)
CREATE TABLE events_2025_04 PARTITION OF events
  FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');