Table partitioning

When tables hit millions or billions of rows, even indexes slow down. Partitioning splits a large table into smaller physical pieces. Queries that filter on the partition key only scan relevant partitions, a technique called partition pruning.

Partition pruning

There is no hard rule, but partitioning typically pays off above 10 million rows or when the table is several gigabytes. Below that, proper indexes are usually enough. The key factor is whether your queries consistently filter on the partition key.

partitioning.sql
sql
-- Create a partitioned table
CREATE TABLE orders_partitioned (
  order_id VARCHAR,
  customer_id VARCHAR,
  order_status VARCHAR,
  order_purchase_timestamp TIMESTAMP
) PARTITION BY RANGE (order_purchase_timestamp);

-- Create individual partitions
CREATE TABLE orders_2017 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');

CREATE TABLE orders_2018 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');

CREATE TABLE orders_2019 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

PARTITION BY RANGE creates the parent. Each partition handles a date range. Inserts automatically go to the right partition.

The performance benefit of partitioning comes from partition pruning. When you filter on the partition key, PostgreSQL skips irrelevant partitions entirely.

partition_pruning.sql
sql
-- EXPLAIN ANALYZE shows partition pruning
EXPLAIN ANALYZE
SELECT *
FROM orders_partitioned
WHERE order_purchase_timestamp >= '2018-06-01'
  AND order_purchase_timestamp < '2018-12-01';

-- You should see: "Partitions removed: 2"
-- Only the 2018 partition is scanned

When you filter on the partition key, PostgreSQL skips irrelevant partitions entirely. This is the performance win.

Partitioning adds complexity and is not worth it for: small tables (under 1 million rows), queries that do not filter on the partition key, or tables where most queries need all partitions anyway. The overhead of partition management only pays off at scale.

Fill in the blanks: Create a partition

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Partitioned orders table

Loading practice…