Table Partitioning

Partitioning is the technique of splitting one large logical table into several smaller physical tables (partitions), while queries and applications continue to interact with it as if it were a single table. PostgreSQL handles routing rows to the correct partition and combining results transparently — you query the parent table, and PostgreSQL figures out which underlying pieces need to be touched.

Declarative Partitioning

Modern PostgreSQL supports declarative partitioning directly at the CREATE TABLE level, using a PARTITION BY clause. Three strategies are available:

RANGE — each partition holds a contiguous range of values (e.g. one month of dates). The most common choice for time-series data.
LIST — each partition holds an explicit list of values (e.g. one partition per country code or per tenant).
HASH — rows are distributed across a fixed number of partitions based on a hash of the partition key, mainly used to spread write load evenly when there is no natural range/list boundary.

Worked Example: Partitioning Orders by Month

A very common real-world pattern: an orders table that grows indefinitely, where most queries filter by a recent date range. Partitioning by month on the order date keeps each partition a manageable size and lets old data be archived or dropped cheaply.

partition-orders-by-month.sql

SQL

-- The parent table declares the partitioning strategy and key.
-- Note: the partition key must be part of any unique/primary key.
CREATE TABLE orders (
  id BIGSERIAL,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  total NUMERIC(10, 2) NOT NULL,
  PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

-- Create one partition per month. Each is a real table under the hood.
CREATE TABLE orders_2026_01 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders_2026_02 PARTITION OF orders
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Inserts are routed automatically to the right partition.
INSERT INTO orders (customer_id, order_date, total)
VALUES (101, '2026-01-15', 49.99);

-- Queries against the parent table look completely normal.
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';

Why Partitioning Helps

Partition pruning — when a query filters on the partition key, PostgreSQL's planner can skip scanning partitions that could not possibly contain matching rows, rather than scanning the whole table.
Cheap bulk deletion of old data — dropping an entire partition (DROP TABLE orders_2025_01) is close to instant, compared to a DELETE that has to find and remove millions of rows one at a time and generates a large amount of dead-row cleanup work.
Smaller working sets — maintenance operations like VACUUM and index rebuilds can run against individual partitions instead of one enormous table.

drop-old-partition.sql

SQL

-- Archiving/removing a full month of old orders is instant —
-- no row-by-row DELETE, no bloat left behind to vacuum.
DROP TABLE orders_2020_01;

Reserve partitioning for genuinely large tables

Partitioning adds real operational complexity: you need a strategy for creating future partitions ahead of time (or an extension/job that automates it), constraints and indexes generally need to be managed per-partition, and some query patterns that don't filter on the partition key can actually get slower. It's the right tool once a table reaches many millions of rows and its size is causing real problems — not something to reach for on every table "just in case."