Introduction: Why Partition Choice Matters for PostgreSQL Partition Pruning
When I first started using table partitioning in PostgreSQL, I assumed that just "having partitions" would automatically make queries faster. It didn't take long to learn the hard way that the type of partitioning and how it interacts with PostgreSQL partition pruning is what really determines whether you see a big performance win or almost no benefit at all.
PostgreSQL partition pruning is the process where the planner skips entire partitions that cannot contain matching rows for a query. If pruning works well, PostgreSQL touches only a small subset of partitions, which means fewer blocks read, fewer indexes scanned, and much lower latency. If pruning works poorly, the planner may still scan most or all partitions, and your "partitioned" table can behave like one giant table split into many expensive pieces.
In my experience, the biggest difference between a good and bad design usually comes down to choosing between range partitioning and hash partitioning on the right key. Range partitions shine when your access patterns are naturally ordered (for example, time-based queries), while hash partitions tend to work better for evenly distributing high-volume workloads across many partitions. But the real trick is understanding how each style enables (or blocks) pruning for your most common WHERE clauses and JOINs.
This tutorial focuses on that practical side: how your choice between range and hash partitioning affects the planner's ability to prune, and how small design decisions—like the partition key, the number of partitions, or the predicates you use—can change query plans dramatically. I'll walk through the patterns that have worked well for me in real systems and where I've seen people (including myself) get burned by the wrong partition strategy.
Prerequisites and Test Setup
PostgreSQL version and extensions
For everything I'll demonstrate, I assume PostgreSQL 12 or newer. Native partitioning and PostgreSQL partition pruning improved a lot around versions 11–13, so if you're on something older, query plans may look very different. I also assume no special extensions are required beyond what ships with core Postgres; any example can run on a plain installation.
Hardware and configuration assumptions
In my own tests, I usually run on a single-node instance with modest resources (for example, 4–8 vCPUs and SSD storage). You don't need a beefy server to reproduce the patterns—what matters is that enable_partition_pruning is on (it is by default) and that you're not constrained by extremely low shared buffers or disk space. For consistent results, I also keep autovacuum on and make sure statistics have been collected after loading data.
Sample schema used in this guide
Throughout the tutorial, I'll use a simplified events table that mimics time-series or logging workloads where partitioning choices are critical. Here's a minimal baseline table before we apply any partitioning strategy:
CREATE TABLE events_base (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL,
event_type text NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb
);
In the next sections, I'll turn this into both range- and hash-partitioned tables and compare how pruning behaves under realistic WHERE clauses and JOINs. This mirrors the way I prototype designs before rolling them into production. PostgreSQL 18: 5.12. Table Partitioning – Official Documentation
PostgreSQL Partition Pruning Explained
What partition pruning actually does
Partition pruning is PostgreSQL's way of deciding which partitions it can safely ignore for a given query. Instead of scanning every child table, the planner compares your WHERE clause (and sometimes JOIN conditions) against each partition's boundary. If a partition cannot possibly contain matching rows, it's pruned away and never touched. In real workloads I've seen queries drop from scanning dozens of partitions to just one or two when pruning is set up well, which is why I treat it as the core benefit of partitioning, not parallelism or "logical organization."
Plan-time vs execution-time pruning
PostgreSQL performs partition pruning at two stages: plan time and execution time. Plan-time pruning happens when the planner can fully evaluate the predicates up front, typically when you use constants or stable expressions:
EXPLAIN SELECT * FROM events_range WHERE occurred_at >= '2024-01-01'::timestamptz AND occurred_at < '2024-02-01'::timestamptz;
With a time-based range partitioning scheme, the planner can often see exactly which partitions intersect that date range and build a plan that touches only those. Execution-time pruning kicks in when the filter values are not known at planning, for example when they come from parameters in prepared statements or joins. In my experience, execution-time pruning is essential for application code that uses generic prepared queries, but it's also more limited: if the partition key is buried in complex expressions, the executor may not be able to prune effectively.
Why pruning dominates partition performance
From a performance perspective, PostgreSQL partition pruning controls how many partitions—and therefore how many indexes and heaps—Postgres has to touch. Every extra partition adds planning overhead, more relcache lookups, more index scans, and more buffer churn. When pruning is tight, each query behaves like it's hitting a small, focused table. When pruning is weak, you&aposve just replaced one big table with many small ones plus extra overhead. That's why I always start any partitioning design by asking: "Will my most common predicates line up with the partition key so that pruning can eliminate almost all other partitions most of the time?" The rest of this guide compares how range and hash partitioning answer that question differently. What’s new in the Postgres 16 query planner / optimizer – Citus Data
Creating a Range-Partitioned Table with Effective Pruning
Defining the partitioned table on the right key
For range partitioning, I always start by asking: which column do my queries naturally filter on in ranges? For time-series or logging workloads, that's almost always a timestamp. To get solid PostgreSQL partition pruning, the partition key must line up with that column. Building on the earlier events_base example, here's how I define a range-partitioned table on occurred_at:
CREATE TABLE events_range (
id bigserial,
user_id bigint NOT NULL,
event_type text NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);
I include occurred_at in the primary key so that each row has a unique key within its partition. In my experience, this pattern keeps indexes simple and avoids subtle issues with duplicate primary keys across partitions.
Creating partitions and loading sample data
Next, I create monthly partitions. I like to keep partitions roughly similar in size, so month or week boundaries work well for most event streams:
CREATE TABLE events_2024_01 PARTITION OF events_range
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events_range
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_2024_03 PARTITION OF events_range
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
Then I load some test data. For demos, I often use generate_series to quickly populate realistic timestamps:
INSERT INTO events_range (user_id, event_type, occurred_at, payload)
SELECT (random() * 100000)::bigint AS user_id,
(ARRAY['login','logout','click','view'])[
(1 + (random() * 4)::int)
] AS event_type,
ts AS occurred_at,
jsonb_build_object('n', g) AS payload
FROM generate_series(
'2024-01-01'::timestamptz,
'2024-04-01'::timestamptz,
interval '5 minutes'
) AS g(ts);
In my own benchmarks, this kind of synthetic dataset has been enough to reveal whether pruning is working or not without needing production data.
Verifying pruning with EXPLAIN
Once the table is populated, I always verify that PostgreSQL partition pruning is doing what I expect. The easiest way is to run EXPLAIN with a date range that should hit only one or two partitions:
EXPLAIN SELECT count(*) FROM events_range WHERE occurred_at >= '2024-02-10'::timestamptz AND occurred_at < '2024-02-20'::timestamptz;
If pruning is effective, the plan should show a Append (or Bitmap Heap Scan / Index Scan) over just events_2024_02, with other partitions absent from the plan. When I see multiple month partitions listed for such a tight range, that's a red flag that my boundaries or predicates don't align well.
It's also worth checking execution-time pruning with a parameterized query, similar to what an application would use:
PREPARE events_by_day(timestamptz, timestamptz) AS
SELECT *
FROM events_range
WHERE occurred_at >= $1
AND occurred_at < $2;
EXPLAIN EXECUTE events_by_day('2024-03-05', '2024-03-06');
In my experience, taking a few minutes to inspect these plans up front has saved me from painful surprises later, like finding out that a "partitioned" table is still touching every partition on a key dashboard query.
Creating a Hash-Partitioned Table and Understanding Its Pruning
Defining a hash-partitioned table on a high-cardinality key
When I switch from range to hash partitioning, it’s usually because I want to spread load evenly across partitions, not because of time-based access patterns. A common choice is a high-cardinality key like user_id. Here’s how I define a hash-partitioned version of the events table:
CREATE TABLE events_hash (
id bigserial,
user_id bigint NOT NULL,
event_type text NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);
Including user_id in the primary key keeps each row unique within its partition and reflects that user_id is the logical sharding key. In my experience, hash partitioning on a low-cardinality column (like a tiny enum) tends to backfire because you don’t get good data distribution or pruning.
Creating hash partitions and loading data
With hash partitioning, I pick a partition count that balances parallelism and overhead. A small, fixed number like 8 or 16 is often a good starting point for OLTP-style workloads:
CREATE TABLE events_hash_p0 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 0); CREATE TABLE events_hash_p1 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 1); CREATE TABLE events_hash_p2 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 2); CREATE TABLE events_hash_p3 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 3); CREATE TABLE events_hash_p4 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 4); CREATE TABLE events_hash_p5 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 5); CREATE TABLE events_hash_p6 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 6); CREATE TABLE events_hash_p7 PARTITION OF events_hash FOR VALUES WITH (MODULUS 8, REMAINDER 7);
I can then reuse the same data generator, just targeting the new table. Because the key distribution is random, hash partitions typically end up with roughly equal row counts, which I’ve found helpful for avoiding hot partitions under heavy concurrency:
INSERT INTO events_hash (user_id, event_type, occurred_at, payload)
SELECT (random() * 100000)::bigint AS user_id,
(ARRAY['login','logout','click','view'])[
(1 + (random() * 4)::int)
] AS event_type,
ts AS occurred_at,
jsonb_build_object('n', g) AS payload
FROM generate_series(
'2024-01-01'::timestamptz,
'2024-04-01'::timestamptz,
interval '5 minutes'
) AS g(ts);
Observing partition pruning behavior for hash partitions
Hash partitions prune very differently from range partitions. PostgreSQL partition pruning only works when the planner can see that the hash of the key maps to a specific subset of partitions. In practice, this means predicates like equality or IN on the partition key are where hash pruning shines. For example:
EXPLAIN SELECT count(*) FROM events_hash WHERE user_id = 424242;
On a well-configured system, the plan should show a single child partition (whichever partition owns that user_id) instead of all eight. When I first experimented with this, it was eye-opening to see that a single = predicate on the partition key could collapse the plan down to one partition, while a broad filter like a timestamp range touched every partition.
Compare that with a query that doesn’t constrain user_id at all:
EXPLAIN SELECT count(*) FROM events_hash WHERE occurred_at >= '2024-02-01'::timestamptz AND occurred_at < '2024-03-01'::timestamptz;
Because the filter is on occurred_at instead of user_id, PostgreSQL partition pruning can’t eliminate any hash partitions—it has to scan all eight. This is the trade-off I always keep in mind: hash partitioning buys me even distribution and good pruning on equality filters of the key, but almost no help for range predicates on other columns.
To see execution-time pruning in action with hash partitions, I use a prepared statement similar to this:
PREPARE events_for_user(bigint) AS SELECT * FROM events_hash WHERE user_id = $1 AND occurred_at >= now() - interval '7 days'; EXPLAIN EXECUTE events_for_user(12345);
If the plan for the EXECUTE step only includes one partition, I know execution-time pruning is kicking in correctly. In my experience, this pattern works very well for user-centric APIs where every request is scoped to a single user, but it’s the wrong tool when most queries are time-window analytics that don’t filter on user_id.
Comparing Range vs Hash Partitioning for PostgreSQL Partition Pruning
Pruning patterns for typical queries
When I compare range and hash partitioning, I always start from the actual query patterns. With time-window queries like dashboards or reports, range partitioning on a timestamp usually gives near-perfect PostgreSQL partition pruning: a query constrained to a few days or a month will hit just the partitions covering that interval. With the hash-partitioned table, though, the same time-based filter can’t eliminate any partitions, because the partition key is user_id instead of occurred_at.
On the flip side, user-scoped queries behave almost the opposite. A range-partitioned table on time can’t prune by user_id, so a query for a single user across a long time span touches many partitions. The hash-partitioned table can prune to exactly one partition when you filter on user_id (or a small set of users), which I’ve found to be a big win for multi-tenant, user-centric APIs.
Query plans and overhead characteristics
In EXPLAIN plans, range partitions tend to show up as a small subset of partitions for well-aligned date predicates, but they can explode to dozens or hundreds of partitions if you query long historical ranges. Hash partitions, by design, are hit more evenly; you’ll often see either one partition (for a single key) or all partitions (for broad scans). In my experience, the planning overhead grows with the number of partitions in both schemes, but it hurts more when pruning is weak, because the planner and executor have to manage far more child relations per query.
Another nuance I’ve run into: range partition boundaries are human-meaningful (months, weeks), which makes it easier to reason about hot vs cold data, attach/detach old partitions, and place indexes differently per partition. Hash partitions don’t map cleanly to real-world ranges, so they’re better thought of as an internal sharding mechanism rather than a lifecycle tool.
When to favor range vs hash in practice
Putting it all together, I usually favor range partitioning when:
- Most heavy queries filter on time ranges (dashboards, reports, retention jobs).
- I need simple archival and drop-old-data workflows.
- I can keep the number of active partitions reasonably low (for example, monthly or weekly).
I lean toward hash partitioning when:
- Most expensive queries are keyed by a high-cardinality ID (user, tenant, account).
- I want to distribute write and read load evenly across partitions.
- Time-based pruning isn’t critical, or I’m already using other strategies for historical data.
In a few systems, I’ve combined both by hash-partitioning on a tenant ID inside a time-partitioned schema, but that’s only worth the added complexity once I’ve maxed out what simple range or hash strategies can deliver. PostgreSQL Documentation: 5.12. Table Partitioning
Advanced Tips: Mixed Strategies and Multi-Column PostgreSQL Partition Pruning
Combining range and hash through sub-partitioning
Once I’m confident that basic partition pruning works, I sometimes reach for mixed strategies to handle really demanding workloads. A common pattern is to partition by range on time and then sub-partition by hash on a tenant or user ID. This keeps time-based pruning strong while smoothing out hotspots between tenants. A simplified version of what I’ve used in practice looks like this:
CREATE TABLE events_mixed (
id bigserial,
tenant_id bigint NOT NULL,
event_type text NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (id, occurred_at, tenant_id)
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2024_01_mixed
PARTITION OF events_mixed
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
PARTITION BY HASH (tenant_id);
CREATE TABLE events_2024_01_t0
PARTITION OF events_2024_01_mixed
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
In this layout, a typical dashboard query with a time filter prunes down to one month, and then an API call scoped to a single tenant prunes further to just one hash sub-partition. In my experience, this mix gives you the best of both worlds, but it also increases planning overhead, so I only apply it where I’ve measured clear wins.
Multi-column partitioning considerations
PostgreSQL’s syntax only lets you specify one partition key expression, but that expression can be an expression on multiple columns (for example, (tenant_id, occurred_at) inside a hash or range expression). The catch is that PostgreSQL partition pruning still depends on the planner understanding that expression. If your queries don’t line up with that exact key or a simplifiable form of it, pruning quickly degrades.
In my own designs, I’ve had better luck nesting partitioning (range, then hash) than trying to cram complex multi-column logic into a single key expression. It’s easier to reason about which partitions will be pruned and why, and EXPLAIN output stays understandable.
When advanced layouts are worth the complexity
These advanced techniques pay off when you have all of the following:
- Very large data volumes, where a simple scheme leaves you with too many rows per partition.
- Clear, consistent access patterns across both time and a high-cardinality key.
- Capacity to monitor query plans carefully and adjust partition definitions over time.
One thing I learned the hard way was that over-engineering partitioning can hurt more than help: extra levels mean more complex query plans, more catalog objects to maintain, and trickier migrations. I now treat mixed and multi-level partitioning as tools for specific, well-measured bottlenecks, not as a default architecture. Documentation: 18: 5.12. Table Partitioning – PostgreSQL
Verifying and Troubleshooting PostgreSQL Partition Pruning
Step-by-step checks to confirm pruning
Whenever I set up partitioning, I treat verification as non-optional. The first step is to use EXPLAIN on a representative query that should touch only a small subset of partitions. For a range-partitioned table:
EXPLAIN SELECT count(*) FROM events_range WHERE occurred_at >= '2024-02-01'::timestamptz AND occurred_at < '2024-03-01'::timestamptz;
In the plan, I look for either a Append or Merge Append node listing only the expected partitions (for example, events_2024_02). If I see many more partitions than expected, I know pruning isn’t doing its job. I repeat the same process for hash-partitioned tables using equality predicates on the hash key.
Common reasons pruning fails
In my experience, most pruning problems fall into a few repeat offenders:
- Predicates don’t match the partition key: filtering on a different column (like
occurred_atwhen you partitioned onuser_id) forces Postgres to consider all partitions. - Expressions hide the key: wrapping the partition key in functions (for example,
date_trunc('day', occurred_at)) can prevent the planner from relating it to the partition bounds. - Parameters at plan time: with prepared statements, generic plans may not get full plan-time pruning; you may need to rely on execution-time pruning and ensure the key appears plainly in the WHERE clause.
- Too many tiny partitions: even if pruning works, a very large number of partitions can overwhelm the planner and negate the gains.
When I hit unexpected behavior, I simplify the query step by step until pruning starts working again, then reintroduce complexity to see exactly what breaks it.
Benchmarking and iterating on improvements
To prove that PostgreSQL partition pruning is actually helping, I like to script repeatable benchmarks. A simple approach is to capture timing and plans before and after a partitioning or query change:
SET enable_seqscan = off; -- optional, to encourage index usage during tests EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events_range WHERE occurred_at >= '2024-02-01'::timestamptz AND occurred_at < '2024-02-02'::timestamptz;
I pay attention to which partitions appear, total execution time, and buffer hits. After adjusting partition boundaries, indexes, or predicates, I re-run the same test to see if the number of scanned partitions and buffers drops. Over time, this loop of “inspect plan → adjust design → re-benchmark” has been the most reliable way for me to turn theoretical partitioning benefits into real-world latency and throughput wins.
Conclusion and Next Steps
Choosing between range and hash partitioning really comes down to aligning PostgreSQL partition pruning with your dominant query patterns. In my experience, range partitioning on time wins for analytics and retention workflows, while hash partitioning on a high-cardinality key shines for user- or tenant-centric OLTP queries. Mixed and sub-partitioned layouts can deliver great results, but only when you have clear access patterns and the appetite for added complexity.
From here, I’d suggest experimenting in a staging environment: model a realistic dataset, build both range and hash variants, and compare EXPLAIN ANALYZE results for your top queries. Once you’re comfortable with pruning behavior, it’s worth exploring related topics like index-only scans on partitions, BRIN indexes for very large time ranges, and automated partition management (creating and dropping partitions on a schedule) to keep performance smooth over the long term.
Hi, I’m Cary Huang — a tech enthusiast based in Canada. I’ve spent years working with complex production systems and open-source software. Through TechBuddies.io, my team and I share practical engineering insights, curate relevant tech news, and recommend useful tools and products to help developers learn and work more effectively.
