Table Partitioning in PostgreSQL

Some tables grow without bound. Events, logs, orders, time-series readings: they only ever get bigger, and eventually a single table with hundreds of millions of rows makes everything slower, queries, index maintenance, VACUUM, and especially deleting old data. Partitioning splits one big logical table into many smaller physical tables, so the database works on the slice a query needs instead of the whole thing. It's the first real tool in the scaling module, and it's the right answer for large, time-based data.

This is part 32 of the PostgreSQL Masterclass and the start of the scaling and operations module, following row-level security.

What partitioning does

A partitioned table looks like one table to your application, but Postgres stores the rows across multiple child tables (partitions), each holding a defined slice of the data. You query the parent as normal; Postgres routes the query to only the relevant partitions. That routing, called partition pruning, is the main win: a query for last week's events touches only the partitions covering last week, not all of history.

The other big win is data lifecycle. Deleting a month of old events from a huge table is a slow, bloat-generating DELETE. With partitioning, you drop the whole month's partition in an instant, with no bloat, because you're removing a table rather than marking millions of rows dead.

Range partitioning by time

The most common scheme is range partitioning on a timestamp. You declare the parent partitioned, then create a partition per time window:

CREATE TABLE events (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  type       text NOT NULL,
  created_at timestamptz NOT NULL,
  data       jsonb
) PARTITION BY RANGE (created_at);

-- one partition per month
CREATE TABLE events_2024_03 PARTITION OF events
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE events_2024_04 PARTITION OF events
  FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

Inserts route automatically by created_at. A query filtering on created_at prunes to the matching partitions. Dropping March later is one command:

DROP TABLE events_2024_03;   -- instant, no bloat, no long DELETE

One requirement to know: the partition key must be part of any primary key or unique constraint. So a partitioned events table can't have a plain PRIMARY KEY (id); it needs PRIMARY KEY (id, created_at). This is the most common surprise when adopting partitioning.

List and hash partitioning

Range is the common one, but two others exist. List partitioning splits by a discrete value, like region or tenant:

CREATE TABLE orders (...) PARTITION BY LIST (region);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US', 'CA');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('DE', 'FR', 'ES');

Hash partitioning spreads rows evenly across a fixed number of partitions by hashing the key, useful when you want to distribute load but have no natural range or list. Range-by-time covers the large majority of real cases, so reach for it first and use list or hash only when the data has a natural discrete or evenly-distributed key.

Automating partition creation

Partitions don't create themselves. If new data arrives for a month with no partition, the insert fails (or lands in a default partition, if you made one, which then grows unbounded and defeats the purpose). So you need to create future partitions ahead of time.

Most teams automate this: a scheduled job, pg_cron, or the pg_partman extension that manages partition creation and retention for you. pg_partman is the common choice, because it handles both making new partitions ahead of need and dropping old ones past your retention window, which is exactly the lifecycle you want:

-- pg_partman handles rolling creation and retention automatically
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control => 'created_at',
  p_interval => '1 month'
);

Whatever you use, the rule is: never let inserts arrive with no partition to hold them. Create partitions in advance.

When partitioning is and isn't worth it

Partitioning adds real complexity: more objects to manage, the partition-key constraint rule, automation to maintain, and query plans that only prune well when the partition key is in the WHERE clause. It pays off when:

• The table is large (hundreds of millions of rows or more) and growing.
• Queries filter on a natural partition key, usually time, so pruning helps.
• You age out old data, so dropping partitions replaces expensive deletes.

It's not worth it for tables that are merely medium-sized, or where queries don't filter on the partition key (then every query hits every partition and you've added complexity for nothing). A well-indexed 10-million-row table doesn't need partitioning; a 500-million-row append-only events table with a 90-day retention absolutely does.

Partitioning is how large, time-based tables stay fast and manageable. Split by time with range partitioning, keep the partition key in your queries so pruning works, automate partition creation and retention with pg_partman, and turn painful bulk deletes into instant DROP TABLEs. For the right table it's transformative; for the wrong one it's overhead, so apply it where the data actually calls for it.

Next, we tackle a different scaling limit: connections, and how PgBouncer lets Postgres serve far more clients than it could on its own.