PostgreSQL Partitioning: Range, List & Hash Partitioning

Q: Does partitioning replace indexing?

No — they're complementary. Partitioning decides which partitions a query must scan (via pruning); indexes make scanning within a partition fast. Partitioned tables still need appropriate indexes on each partition (define them on the parent to propagate). Treating partitioning as a substitute for indexing leads to slow queries within large partitions.

PostgreSQL Partitioning

Partitioning splits one large logical table into many smaller physical partitions, while queries still treat it as a single table. PostgreSQL routes each row to the right partition based on a partition key, and — the big win — can skip entire partitions that can't match a query, a technique called partition pruning. For very large, growing tables, this keeps queries fast and makes data lifecycle management (dropping old data) nearly instant.

Partitioning is a scaling tool for big tables, not a default. Applied to a table that isn't large, it adds complexity for no benefit. Applied well to a genuinely large, time-series-like table — logs, events, metrics, orders — it's transformative: queries touch only relevant partitions, and "delete last year's data" becomes a DROP of a partition instead of a massive, bloat-inducing DELETE. This page covers the strategies, how pruning works, and where teams go wrong.

TL;DR

Partitioning splits a big table into smaller physical partitions; the app still queries one logical table.
Three strategies: RANGE (by value ranges — dates are the classic), LIST (by discrete values — region, tenant), HASH (even distribution across N partitions).
The payoff is partition pruning: queries that filter on the partition key skip irrelevant partitions entirely.
Data lifecycle gets cheap: dropping old data is DROP TABLE partition (instant) instead of a huge, bloat-heavy DELETE.
Queries must filter on the partition key to benefit — otherwise Postgres scans all partitions.
It's for large tables (think many tens of millions of rows and up); on small tables it's needless complexity. Partitioning is not a substitute for indexing.

Quick Example

Range-partition a large events table by month; new rows route automatically, and date-filtered queries prune to the relevant partition:

The WHERE occurred_at ... filter lets Postgres skip every partition but July — and archiving a month is a metadata operation.

Core Concepts

Partitioning Strategies

Postgres supports three (declarative) partitioning methods, chosen by how you want rows grouped:

Range on a timestamp is by far the most common — it aligns with how time-series data is written (recent partition is hot) and queried (usually a date range), and it makes dropping old data trivial. You can also sub-partition (partition a partition) for multi-dimensional splits, though that adds complexity.

Partition Pruning: The Core Benefit

Pruning is Postgres eliminating partitions that can't contain matching rows. If a query filters WHERE occurred_at >= '2026-07-10', the planner knows only the July partition (and later) can match and skips the rest entirely — turning a scan of a billion-row logical table into a scan of one month's partition. Pruning happens at planning time and, for some cases, at execution time.

The catch: pruning only works when the query filters on the partition key. A query with no condition on occurred_at must consider all partitions — often slower than an unpartitioned table with a good index. Your access patterns must align with your partition key.

Declarative Partitioning

Modern Postgres uses declarative partitioning (PARTITION BY), where you define the partitioned parent and attach partitions, and Postgres handles routing inserts to the right partition automatically. This replaced the old inheritance-plus-triggers approach. Indexes, constraints, and (with care) foreign keys work across partitions, though some features have partition-specific nuances.

When to Use Partitioning

Partitioning helps a specific profile of table:

Good candidates:

Very large tables (tens of millions of rows and growing) where a single table's indexes and maintenance are becoming unwieldy.
Time-series data written recently and queried by time range — logs, events, metrics, orders — where you also want to expire old data.
Clear data lifecycle — you regularly archive/drop old partitions (dropping a partition is instant vs an expensive DELETE).
Natural partition key that most queries filter on.

Poor candidates:

Small or moderate tables — a good index is simpler and sufficient. Partitioning adds overhead without payoff.
Queries that rarely filter on any single key — without pruning, you gain complexity and lose performance.
Expecting it to replace indexing — partitions still need indexes; partitioning complements indexing, it doesn't replace it.

💡 Tip: If you're unsure whether a table is big enough to partition, it probably isn't yet. Reach for proper indexing and query tuning first; introduce partitioning when a genuinely large table with a clear time/lifecycle dimension is straining.

Best Practices

Partition on the Column Your Queries Filter

The partition key must match how you query — usually the timestamp or tenant you filter by — or pruning won't kick in. Design the key around access patterns, not around how data is generated. A mismatch makes partitioning a net negative.

Automate Partition Creation and Retention

For time-based partitioning, you continuously need new partitions and want to drop old ones. Automate this (a scheduled job or an extension like pg_partman) so you don't wake up to inserts failing because next month's partition doesn't exist.

Keep Indexing the Partitions

Partitioning is not a replacement for indexes. Each partition still needs appropriate indexes; define them on the parent so they propagate. Pruning narrows which partitions are scanned; indexes make the scan within a partition fast.

Watch Partition Count

Thousands of tiny partitions add planning overhead and management burden. Choose a granularity (monthly vs daily) that keeps partitions reasonably sized and their number manageable. Too-fine partitioning trades one problem for another.

Test That Pruning Actually Happens

Use EXPLAIN to confirm your real queries prune to the expected partitions. It's easy to write a query (e.g. wrapping the partition key in a function, or filtering on a different column) that defeats pruning silently. Verify with plans.

Common Mistakes

Querying Without the Partition Key

Partitioning a Table That Isn't Big

Adding range partitioning to a few-million-row table introduces complexity, partition management, and planning overhead for no real gain. Index it well instead; partition when it's genuinely large.

Forgetting to Create Future Partitions

With time-based partitioning, if no partition exists for incoming dates, inserts fail (or fall to a default partition that then bloats). Automate partition creation ahead of time.

FAQ

What does partitioning actually speed up?

Queries that filter on the partition key, via partition pruning — Postgres skips partitions that can't match, so a date-range query over a huge events table scans only the relevant month(s) instead of everything. It also makes data lifecycle cheap: dropping old data is an instant DROP TABLE partition rather than a slow, bloat-inducing bulk DELETE. Queries that don't filter on the partition key don't benefit and can be slower.

RANGE, LIST, or HASH — which do I choose?

RANGE for ordered values, especially time (partition by day/month on a timestamp) — the most common choice for logs, events, and metrics. LIST for discrete categories you split on (region, tenant). HASH to spread rows evenly across a fixed number of partitions when there's no natural range or list. Pick the one matching how you query and expire data; RANGE-by-time fits most large operational tables.

When should I partition a table?

When it's genuinely large (tens of millions of rows and growing), has a natural key most queries filter on (usually time), and has a clear data-lifecycle need (regularly archiving/dropping old data). For small or moderate tables, good indexing is simpler and enough — partitioning adds complexity without payoff. If you're unsure it's big enough, it probably isn't yet.

Does partitioning replace indexing?

No — they're complementary. Partitioning decides which partitions a query must scan (via pruning); indexes make scanning within a partition fast. Partitioned tables still need appropriate indexes on each partition (define them on the parent to propagate). Treating partitioning as a substitute for indexing leads to slow queries within large partitions.

Why isn't my partitioned query getting faster?

Almost always because it isn't pruning — the query doesn't filter on the partition key, or it filters in a way that defeats pruning (wrapping the key in a function, comparing a different column). Without a partition-key condition, Postgres considers all partitions. Check EXPLAIN to confirm pruning happens, and ensure your queries filter on the partition key.