PostgreSQL Table Partitioning

Partitioning splits one huge table into smaller pieces called partitions. Applications still see a single table and use its name as usual. Behind the scenes, PostgreSQL stores each row in the piece that fits it.

  Table: sales (one name for your queries)
     |
     +-- sales_2024  (rows from 2024)
     +-- sales_2025  (rows from 2025)
     +-- sales_2026  (rows from 2026)

Picture a filing cabinet. One giant drawer holding every paper makes searching slow. Separate drawers labeled by year let you open only the drawer you need.

Why Partition a Table

• Queries that filter by the partition key read only the matching partitions.
• Deleting old data takes a fraction of a second, because you drop a whole partition instead of deleting millions of rows.
• Maintenance such as VACUUM runs on small pieces.
• Indexes stay smaller and fit in memory more easily.

Partitioning pays off for tables with many millions of rows. Small tables gain nothing from it.

Partition Methods

Method	Splits Rows By	Typical Use
RANGE	A span of values	Dates and numeric ranges
LIST	A fixed list of values	Country, region, or status
HASH	A hash of the value	Even spread without natural groups

Partitioning Examples

Each method needs a parent table and a set of partitions. Pruning then keeps queries fast.

Range Partitioning Example

Create the parent table and name the partition key.

CREATE TABLE sales (
  sale_id   BIGSERIAL,
  sale_date DATE NOT NULL,
  amount    NUMERIC(10,2),
  PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

A primary key on a partitioned table must include the partition key column. That rule explains why sale_date joins the key.

Create one partition for each year. The upper limit is exclusive.

CREATE TABLE sales_2025 PARTITION OF sales
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE sales_2026 PARTITION OF sales
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Insert rows into the parent. PostgreSQL routes each row to the right partition.

INSERT INTO sales (sale_date, amount) VALUES ('2025-06-10', 120.00);
INSERT INTO sales (sale_date, amount) VALUES ('2026-02-14', 80.50);

  Row dated 2025-06-10  --> sales_2025
  Row dated 2026-02-14  --> sales_2026

An insert with a date that fits no partition fails with an error. Create a default partition to catch strays.

CREATE TABLE sales_other PARTITION OF sales DEFAULT;

Partition Pruning

Pruning means skipping partitions that cannot hold matching rows. The planner reads your WHERE clause and ignores the rest.

EXPLAIN SELECT * FROM sales
WHERE sale_date = '2026-02-14';

The plan shows a scan of sales_2026 only. The 2025 partition never gets touched. Filter on the partition key in every query that should benefit.

List Partitioning Example

CREATE TABLE customers (
  id      BIGSERIAL,
  name    TEXT,
  country TEXT NOT NULL,
  PRIMARY KEY (id, country)
) PARTITION BY LIST (country);

CREATE TABLE customers_india PARTITION OF customers
  FOR VALUES IN ('India');

CREATE TABLE customers_europe PARTITION OF customers
  FOR VALUES IN ('France', 'Germany', 'Spain');

Hash Partitioning Example

Hash partitioning spreads rows evenly. Choose the number of partitions up front.

CREATE TABLE events (
  id      BIGINT,
  payload TEXT
) PARTITION BY HASH (id);

CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Managing Partitions

Dropping, indexing, and planning decide whether partitioning stays easy over the years.

Removing Old Data Fast

Detach a partition to remove it from the table while keeping its data. Drop it when you no longer need the rows.

ALTER TABLE sales DETACH PARTITION sales_2025;
DROP TABLE sales_2025;

Deleting a year of rows with DELETE would create millions of dead rows and take minutes. Dropping a partition finishes almost instantly.

Indexes on Partitions

An index created on the parent table builds a matching index on every partition, and on partitions added later.

CREATE INDEX idx_sales_amount ON sales (amount);

Planning Tips

• Pick a partition key that appears in most WHERE clauses.
• Avoid thousands of tiny partitions, because planning time grows with the count.
• Create future partitions ahead of time, or automate it with a scheduled job.
• Test queries with EXPLAIN to confirm pruning happens.

Practice Exercise

Create a table named visits partitioned by month on a visit_date column. Add three monthly partitions and a default partition. Insert a few rows per month. Run EXPLAIN on a query for one month and confirm that only one partition appears in the plan. Detach the oldest partition afterward.