intro
Table scans are killing your query performance and partitioning could cut query times from minutes to milliseconds, but only if you do it right. Pick the wrong partitioning method, forget to verify index usage or migrate data carelessly dropping a partition or two and it’s more hassle than it’s worth: you might end up with a system that's slower and harder to maintain than before.
This guide focuses on PostgreSQL table partitioning. It covers the two dominant strategies, how to confirm your query planner is actually using partitions, and how to migrate without downtime.
Note: SQL also has a PARTITION BY clause used in window functions. That's a separate concept. If you're looking for that, see our SQL PARTITION BY guide.
Why Partition Large Tables?
Partitioning splits one logical table into multiple physical segments. Queries that filter on the partition key only scan the relevant segments, skipping the rest entirely. Partitioning is similar to indexing in that indexing allows queries reading your data access data faster by making use of pointers, and partitioning simply ignores certain partitions where data doesn’t reside.
When Size Becomes the Enemy
When your data grows, you will remember a simple rule of thumb: sequential scans will become more expensive. That is because PostgreSQL will no longer be able to return large amounts of data from the cache, index lookups might start causing random I/O, and functions like the Postgres Autovacuum functionality may slow down.
You will quickly realize that the threshold isn’t a fixed number, but when you’re working with data involving tens of millions of rows, it’s time to do something.
Look for these patterns in your production workload:
The Two Dominant Strategies: Time-Based vs. Hash Partitioning
Now for the two dominant strategies involving partitioning in PostgreSQL. We’re going to be looking into time-based, or range, partitioning method, and hash partitioning (even distribution of data across a fixed amount of partitions) too.
Time-based (Range) Partitioning
Range partitioning splits data based on a continuous value, commonly a timestamp, a letter, or a number. Each partition holds a defined range.
1
CREATE TABLE events (
2
id BIGSERIAL,
3
created_at TIMESTAMPTZ NOT NULL,
4
user_id BIGINT,
5
payload JSONB
6
) **PARTITION BY RANGE (created_at);**
7
8
CREATE TABLE events_2026_q1
9
PARTITION OF events
10
**FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');**
11
12
CREATE TABLE events_2026_q2
13
PARTITION OF events
14
FOR VALUES FROM ('2026-04-01') TO ('2026-08-01');
Partitioning by range works extremely well when your queries filter by a certain value, e.g. time. That way, PostgreSQL's planner can eliminate all partitions outside the query's date range before reading a single row.
Hash Partitioning
In a database context, hash partitioning distributes rows across a fixed number of partitions based on a hashed key value. Each row lands in a partition determined by a mathematical function that looks like so: hash(key) % num_partitions.
1
CREATE TABLE users (
2
id BIGSERIAL NOT NULL,
3
email TEXT NOT NULL,
4
org_id BIGINT NOT NULL
5
) PARTITION BY HASH (org_id);
6
7
CREATE TABLE users_p0
8
PARTITION OF users
9
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
10
11
CREATE TABLE users_p1
12
PARTITION OF users
13
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
14
15
CREATE TABLE users_p2
16
PARTITION OF users
17
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
18
19
CREATE TABLE users_p3
20
PARTITION OF users
21
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Partitioning by hash distributes load evenly and works well when your queries filter by a high-cardinality key like org_id or user_id, but you have no time-based access patterns.
Decision Matrix: Choosing a Partitioning Type
| Criteria | Range Partitioning | Hash Partitioning |
|---|---|---|
| Query patterns | Filters by date ranges | Filters by ID |
| Concurrency and write operations | Write loads are more difficult due to the fact that your database has to determine the exact partition | Partitioned evenly |
| Backfill complexity | More difficult (inserting into the correct range) | Less difficult (the hash value is provided automatically) |
If you archive or delete historical data regularly, range partitioning wins. If you want data to be distributed evenly on a hash value, choose hash partitioning.
The Core Performance Payoffs
Heavy read operations and partition pruning are what makes partitioning valuable. Think about it - read operations know where data resides so they only read necessary data and pruning drops a partition and keeps the remaining data intact. Cool, right?
Performance payoffs do depend on your execution plan though and for a deeper walkthrough of reading PostgreSQL execution plans, see Using the Explain Plan to Analyze Query Execution in PostgreSQL. You can also visualize this directly using DbVisualizer's Explain Plan feature, which renders the plan as a tree and makes it easy to spot which partitions are scanned.
For a broader reference on using EXPLAIN across query types, see SQL EXPLAIN: The Definitive Tool to Optimize Queries.
Choosing the Right Partitioning Key
Before partitioning your database, you have to understand one key thing (get it?): you have to properly choose the key that your database will use to partition your data. In other words, you have to carefully choose according to what column(s) your data will be partitioned.
Here’s a trick: you align the key with your most common filters in your WHERE clause. In other words, the partition key should match the column your queries filter on most often. If 90% of your queries include WHERE org_id = ..., partitioning by created_at gives you nothing for those queries.
Review your slow query log, find the most expensive queries, identify the columns they act on, and that should give you enough of a head start to identify a candidate for a partitioning key.
FAQ
Do I Choose Range-Based or Hash Partitioning?
The answer to this question is it depends on your use case. Use range-based partitioning when you know based on what data you filter your queries (e.g. based on a time value, letters, numbers, etc. - range partitioning works well for search engines, for example), and use hash partitioning if you want to split your data into an even amount of partitions.
Do I Even Need Partitioning?
It depends, but if you don’t work with more than 100 million records in one go, the answer is probably not.
