Table Partitioning

Split large tables into smaller partitions for better query performance and manageability.

Overview

Table partitioning divides a large table into smaller physical sub-tables (partitions) while presenting a unified logical table interface. PostgreSQL supports three partition strategies: RANGE (for date/numeric ranges), LIST (for discrete values like country codes), and HASH (for even distribution). Partitioning helps with: query performance (partition pruning eliminates irrelevant partitions), bulk delete (drop a partition instead of DELETE), and data lifecycle management. Declarative partitioning (PostgreSQL 10+) uses PARTITION BY on the parent table and PARTITION OF for child tables.

Key Points

•PARTITION BY RANGE(col), LIST(col), or HASH(col)
•Partition pruning: queries only scan relevant partitions
•Child tables inherit parent structure automatically
•Indexes must be created on each partition individually (or via parent)
•Foreign keys cannot reference partitioned tables (PG limitation)
•Dropping a partition is instant; deleting millions of rows is slow

Examples

Range partitioning by date

-- Partitioned parent table
CREATE TABLE orders (
  id         BIGSERIAL,
  order_date DATE NOT NULL,
  customer_id INTEGER,
  total NUMERIC
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Query automatically targets relevant partitions
SELECT * FROM orders WHERE order_date >= '2024-06-01';

-- Index on partition
CREATE INDEX ON orders_2024 (customer_id);

Table Partitioning

Overview

Key Points

Examples

Exercises

Knowledge Check

What is partition pruning?