What is PostgreSQL partitioning

· Category: SQL & Databases

Short answer

Partitioning splits a large table into smaller physical pieces called partitions, improving query performance and maintenance for very large datasets.

How it works

PostgreSQL supports declarative partitioning by range, list, and hash. A partitioned table is a parent table with no storage; rows are routed to child partitions based on the partition key.

Steps

  1. Create a partitioned table:
CREATE TABLE events (
  id BIGINT,
  created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
  1. Create partitions:
CREATE TABLE events_2025 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
  1. Create indexes on each partition.
  2. Attach and detach partitions for archiving.
  3. Query the parent table; the planner prunes irrelevant partitions.

Why it matters

Partitioning improves scan efficiency, enables faster bulk loads and deletions, and allows maintenance operations like VACUUM and reindexing on smaller subsets.r