PostgreSQL Partitioning Strategies: Range, List, and Hash

I once took over a log table with 300 million rows that was growing by 2 million rows per day. The business team complained that queries for data from the past week kept getting slower, while the DBA said, 'All the indexes are in place, and we have run VACUUM, but nothing helps.' I took a look: the queries were indeed using indexes, but each still took two or three seconds. Worse, every VACUUM took more than 40 minutes, autovacuum could not keep up, and table bloat kept getting worse.

The root cause was neither incorrect indexes nor stale statistics: this table should never have been a single table in the first place. After partitioning it by month, queries for the past week scanned only one or two partitions, reducing the search to a small slice within a 20-million-row partition. VACUUM touched only hot partitions, while old cold partitions could simply be DETACHed and archived. The application behavior did not change, but performance improved by an order of magnitude.

Many people treat partitioning as an advanced technique they will never need. The opposite is true: once a table reaches tens of millions of rows, partitioning is a more fundamental optimization than adding indexes. Indexes optimize how to search through a pile of data; partitioning avoids touching that pile altogether. This article explains PostgreSQL's three declarative partitioning strategies, how pruning works, how to choose a partition key, and how to maintain partitions, while highlighting the pitfalls I have repeatedly encountered in production.

1. Why Partition: Split One Large Table into Many Small Tables

To understand partitioning, first understand where the pain of large tables comes from. For a table with hundreds of millions of rows, slow queries are only one part of the problem:

1. Slow queries: Even with indexes, B-tree depth, random I/O from heap fetches, and cold data diluting hot data in the cache can push individual queries from milliseconds into seconds.
2. Slow maintenance: VACUUM, ANALYZE, and REINDEX all operate at table scale. A VACUUM over 300 million rows can take tens of minutes while consuming I/O. Autovacuum often cannot keep up with writes, dead tuples accumulate, bloat worsens, and a vicious cycle begins.
3. Slow bulk operations: Deleting historical data with DELETE removes rows one by one and writes WAL for every row. Deleting 50 million rows can hold locks for hours, while DROPping a partition is a metadata operation that finishes in seconds.
4. Concentrated availability risk: The entire table shares its data files and indexes, so disk corruption and repair operations such as pg_repack or rebuilding affect all data.

The core idea of partitioning is simple: split one large logical table into multiple physical child tables, or partitions. Each child is an independent physical table with its own data files and indexes. The application still uses the same logical table name for SELECT and INSERT; PostgreSQL automatically routes operations to the correct partition internally.

The benefits are structural:

• Queries scan only relevant partitions through partition pruning, shrinking the scan range from the entire table to one partition;
• Maintenance operations work at a smaller granularity, so VACUUM can run only on hot partitions;
• Historical archiving becomes a seconds-long operation because old partitions can be DETACHed and then DROPped without deleting rows individually;
• Corruption in one partition does not affect other partitions.

The tradeoffs are more complex DDL, extra overhead for cross-partition queries and aggregation, and restrictions on primary and foreign keys. Partitioning is therefore not a case of 'the more, the better'; it must solve the right problem.

2. Declarative Partitioning: Choosing Among Three Strategies

PostgreSQL 10 introduced declarative partitioning, with clean syntax and no application-level changes. The three strategies correspond to three data distributions:

1. RANGE: Partition by range, the standard choice for time-series data

Time-based partitioning accounts for 90% of real-world use cases. The syntax is: the parent table declares the partitioning method, and each child table declares its range.

CREATE TABLE events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  created_at  TIMESTAMPTZ NOT NULL,
  user_id     BIGINT NOT NULL,
  payload     JSONB,
  PRIMARY KEY (id, created_at)          -- The primary key must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE events_default PARTITION OF events DEFAULT;  -- Fallback for uncovered data

Two details matter. First, ranges are inclusive at the lower bound and exclusive at the upper bound, [FROM, TO), so adjacent partition boundaries neither overlap nor leave gaps. Second, primary keys and unique constraints must include the partition key. PostgreSQL maintains only local indexes, as discussed in Section 5, so it cannot guarantee uniqueness across partitions. Including the partition key in the constraint lets PostgreSQL know that rows with the same key can belong to only one partition.

2. LIST: Partition by enumerated values, such as region or tenant

When data has a natural discrete dimension such as region, tenant ID, or status code, LIST partitioning is more intuitive than RANGE partitioning:

CREATE TABLE users (
  id       BIGINT GENERATED ALWAYS AS IDENTITY,
  region   TEXT NOT NULL,
  name     TEXT,
  PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'KR', 'SG');

CREATE TABLE users_emea PARTITION OF users
  FOR VALUES IN ('DE', 'FR', 'GB', 'RU');

CREATE TABLE users_default PARTITION OF users DEFAULT;

A query for users in China scans only the users_apac partition. Adding a DEFAULT partition is strongly recommended. Otherwise, inserting an uncovered region value fails immediately, and creating the missing partition requires moving the data out first.

3. HASH: Even distribution as a fallback when there is no natural dimension

When data has no obvious query dimension and the table is simply too large, HASH partitioning distributes rows evenly by hash value. It is commonly used for write-heavy, read-light workloads:

CREATE TABLE sessions (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  user_id     BIGINT NOT NULL,
  token       TEXT NOT NULL,
  PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

-- How many? Usually 8/16/32; powers of two make expansion easier
CREATE TABLE sessions_p0 PARTITION OF sessions
  FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
  FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ...through p7

HASH partitioning provides naturally even writes, provided the hash key has enough distinct values, but its pruning benefits are weaker than RANGE or LIST partitioning. PostgreSQL can prune to one partition only when the query condition includes user_id = ?; range queries and aggregations must scan every partition. HASH is therefore intended to distribute writes and limit the size of individual partitions, not to accelerate general queries. HASH partitions are also painful to expand. Moving from 8 partitions to 16 changes the hash modulus and requires redistributing the data. In production, either create more partitions initially, such as 16 or 32, or use a tool such as pg_partman for online redistribution.

3. Partition Pruning: Without It, Partitioning Is Pointless

Partitioning derives 90% of its performance benefit from partition pruning. When a query's WHERE clause includes the partition key, the optimizer can determine which partitions can never match, skip them entirely, and scan only the remaining partitions.

EXPLAIN SELECT * FROM events
 WHERE created_at >= '2025-01-15' AND created_at < '2025-02-15';

The execution plan will contain only the events_2025_01 and events_2025_02 partitions. Every other partition is absent. Reducing 300 million rows to 20 million inevitably improves performance.

Pruning can happen at two different times, and understanding the distinction is important:

• Plan-time pruning: The WHERE condition contains constants, such as the literals above. The optimizer removes irrelevant partitions while generating the plan, so those partitions do not appear in the plan at all.
• Runtime pruning: The WHERE condition contains parameters, as with PREPARE / EXECUTE, prepared statements, or PL/pgSQL variables. The optimizer does not know their values during planning, so it generates an Append node containing every possible partition. At execution time, it skips unnecessary subplans based on the actual parameter values; the plan reports them as Subplans Removed.

Runtime pruning still eliminates partitions, but the plan must enumerate them all. With very many partitions, planning itself becomes expensive, and the cost becomes noticeable at several hundred partitions. This makes whether query conditions include the partition key the decisive test of a partitioning design. Without the key, pruning fails and PostgreSQL scans every partition, making the query slower than it was before partitioning because of the additional Append overhead. That is the central topic of the next section.

Always verify pruning with EXPLAIN; do not rely on intuition. If the plan contains a partition you expected to be pruned, pruning did not take effect, usually because the WHERE condition did not reach the partition key.

4. Choosing a Partition Key: Follow the Queries

There is only one rule for choosing a partition key, but it determines everything: the partition key must appear in the filter conditions of most queries.

Why emphasize this so strongly? Pruning works only when the WHERE clause includes the partition key. If you partition by created_at, but 80% of business queries retrieve all orders for a user by user_id, the partition key provides no benefit. Every query must scan all partitions. This is the most common reason partitioning makes a table slower.

In practice, choose in this order:

1. Identify the most frequent query templates and see what they filter on. Log tables are usually queried by time; order tables may be queried by time or by user, so measure the workload first.
2. If frequent queries share one dimension, typically time, use it as a RANGE partition key. This is the ideal case.
3. If there are two frequent dimensions, such as time and user, use the more common one as the partition key and index the other. Alternatively, use multilevel partitioning, or subpartitioning: first partition by time with RANGE, then partition each child by user_id with HASH.

CREATE TABLE events (...) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

CREATE TABLE events_2025_01_p0 PARTITION OF events_2025_01
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ...

Multilevel partitioning is powerful, but its DDL complexity grows exponentially. Keep the total partition count under control, ideally within a few hundred, because planning overhead and metadata pressure both increase beyond that point. Do not use multilevel partitioning unless necessary; a single partitioning level usually offers the best cost-benefit ratio.

Here is one cautionary example. Someone used LIST partitioning on a status column with only three values: processing, completed, and canceled. Most queries filtered by time or user and never included status. Every query therefore scanned all three partitions, making the partitioned table pointless. The partition key needs sufficient cardinality and must be frequently used in queries; partitioning is meaningful only when both conditions hold.

5. Partitioning and Indexes: Local, Not Global

This is the layer beginners most often misunderstand: a partitioned table does not have a true global index.

When you run CREATE INDEX on the parent table, PostgreSQL actually creates a local index on each child partition:

-- Create an index on the parent table and cascade it to all existing partitions
CREATE INDEX idx_events_user ON events (user_id);
-- Actual result: events_2025_01 and events_2025_02 each have an idx_events_user

When you add a partition, PostgreSQL automatically creates the corresponding index on it, so no manual maintenance is required. This aspect of the design is convenient.

However, local indexes have several implications:

• For a cross-partition query, each partition uses its own local index; there is no B-tree spanning partitions. A query containing the partition key can prune to one partition and use its local index perfectly. A query without the partition key scans every partition and uses each partition's local index, but the results still have to be merged.
• Unique constraints must include the partition key, as noted earlier, because PostgreSQL cannot guarantee uniqueness across partitions. This is a hard PostgreSQL restriction, not a design flaw: a global unique index would conflict with features such as partition pruning and archival through DETACH.
• To make cross-partition queries fast, either use pruning to narrow the query to a few partitions or reconsider the partition key.

This also answers a common question: 'Do partitioned tables still need indexes?' Yes, and every partition needs them. Partitioning determines which pile to scan; indexing determines how to locate rows quickly within that pile. They work together rather than replacing one another. Ideally, partition pruning reduces the data to one partition, and that partition's local index then locates rows efficiently within it.

6. Partition Maintenance: Create New Partitions and Archive Old Ones Automatically

Once a partitioned table goes live, operational concerns appear immediately. If next month's partition has not been created, writes will fail unless a DEFAULT partition catches them, which can hurt pruning performance. Meanwhile, three-year-old cold data continues consuming storage and backup capacity. Both problems must be automated.

1. Create new partitions automatically

The most general solution is the pg_partman extension. Once configured, it runs periodically, creates the next N partitions in advance, and applies policies to old partitions such as archiving, DETACH, or compression:

CREATE EXTENSION pg_partman;

-- Maintain the events table monthly, create 4 partitions in advance, and retain 12
SELECT partman.create_parent(
  'public.events', 'created_at', 'native', 'monthly',
  p_premake := 4
);

If you do not want an extension, use a scheduled task such as cron or a Kubernetes CronJob to run a script that executes CREATE TABLE ... PARTITION OF ... for the coming months. The logic is simple, but do not forget to implement it. This is the most common source of partitioning incidents: one day, the DEFAULT partition unexpectedly starts receiving new data, and moving that data back into proper partitions becomes a lengthy chore.

2. Archive old partitions with DETACH

Archiving historical data is one of the best capabilities of partitioned tables. If a table contains five years of data and you need to remove data older than two years, deleting rows individually is disastrous. With partitions, use DETACH directly:

-- PostgreSQL 14+: CONCURRENTLY does not block reads or writes
ALTER TABLE events DETACH PARTITION events_2023_01 CONCURRENTLY;

-- events_2023_01 is now an independent table that can be handled freely
-- Export it to object storage, compress and move it into cold storage, or simply DROP it
DROP TABLE events_2023_01;

DETACH is a metadata operation and completes in seconds. The CONCURRENTLY keyword, available in PostgreSQL 14 and later, avoids blocking reads and writes, at the cost of taking slightly longer because it must wait for transaction boundaries. Production archival should generally use CONCURRENTLY.

Two common archival strategies are cold-storage archival, where the detached partition is exported to S3, other object storage, or a columnar database such as ClickHouse, and in-place compression, where the detached partition is processed with pg_compress or exported to a columnar format and then attached again. The former suits historical data that is almost never queried; the latter suits data that occasionally needs to be revisited.

7. Pitfalls: Cross-Partition JOINs and Aggregation, Primary Keys, and Partition Limits

Partitioning is not a silver bullet. Its pitfalls are specific, and I have encountered the following repeatedly.

Pitfall 1: Cross-partition JOINs and aggregations are slow because each partition is processed before results are merged

A seemingly harmless aggregation such as SELECT COUNT(*) FROM events becomes 'COUNT each partition separately, then merge them with Append' on a partitioned table. With only a few partitions, the difference is negligible. However, aggregations involving JOINs, such as events JOIN users GROUP BY ..., become significantly more complex when executed across partitions.

The more serious problem is joining two large partitioned tables. If the tables are not partitioned on the same key with identical boundaries, PostgreSQL cannot perform a partitionwise join. It must instead join all partitions from one table against all partitions from the other, and the cost explodes. Enabling SET enable_partitionwise_join = on; allows PostgreSQL to attempt aligned joins, but both sides must use exactly the same partitioning scheme. This must be planned from the beginning and is difficult to retrofit.

Pitfall 2: Primary keys and unique constraints must include the partition key

This has already been emphasized, but it is worth repeating because it causes so many mistakes. The following will fail:

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  created_at TIMESTAMPTZ NOT NULL,
  payload JSONB,
  PRIMARY KEY (id)            -- ❌ Fails: the primary key omits the partition key
) PARTITION BY RANGE (created_at);
-- ERROR: unique constraint on partitioned table must include all partitioning columns

The correct definition is PRIMARY KEY (id, created_at). This means that an application fetching one row by id must also include created_at to prune to one partition. If the application knows only the id and does not include time in single-row lookups, either accept scanning all partitions, where each partition has its own local index on id, or use HASH partitioning with id as the partition key.

Pitfall 3: The double-edged nature of the DEFAULT partition

A DEFAULT partition prevents insert failures, but its data cannot participate in efficient pruning because PostgreSQL does not know which values it contains and must scan it. Moving data from DEFAULT into a proper partition also requires moving that data out before creating the partition, because PostgreSQL verifies that DEFAULT contains no rows matching the new range. In production, use DEFAULT as insurance, but monitor it and raise an alert. If any data enters it, investigate and migrate the data immediately.

Pitfall 4: Too many partitions sharply increase planning overhead

Every partition is a table in PostgreSQL's metadata. Once there are hundreds of partitions, planning a SELECT requires enumerating them all for pruning, and DDL becomes slower as well. As a rule of thumb, keep a table's partition count within a few hundred. Beyond that, consider hierarchy, such as RANGE by year plus subpartitions, or change the architecture entirely by sharding or separating hot and cold data across instances.

Pitfall 5: Assuming partitioning solves everything

Partitioning solves scale, not disorder. If a query lacks indexes or the SQL is poorly written, with problems such as SELECT *, N+1 queries, or missing required columns, partitioning cannot rescue it. After pruning to one partition, you still need the right indexes and query optimizations. Treating partitioning as a cure-all is one of the most common beginner mistakes.

Summary

• Partitioning provides structural benefits: queries scan only relevant partitions, maintenance operates at a smaller granularity, and historical data can be archived in seconds with DETACH. Once tables reach tens of millions of rows, partitioning is more fundamental than adding indexes.
• The three strategies match three distributions: RANGE suits time-series data and covers 90% of cases, LIST suits discrete dimensions such as region or tenant, and HASH suits data without an obvious dimension when the goal is simply to distribute writes evenly.
• Partition pruning is the critical mechanism: query conditions must contain the partition key for pruning to work. Choose the partition key based on the filter conditions of the most frequent queries. Choose the wrong key, and a partitioned table can be slower than a single table.
• Partitioned tables use local indexes, not global indexes: every partition has its own indexes, and unique constraints must include the partition key. Partitioning determines which pile to scan; indexes determine how to locate rows within that pile. The two work together.
• Operations must be automated: use pg_partman or scheduled tasks to create new partitions in advance, and archive old partitions in seconds with DETACH ... CONCURRENTLY. Most partitioning incidents happen because someone forgot to create next month's partition.

Key takeaway: Indexes optimize how to find data faster within a pile; partitioning avoids touching that pile altogether. The former accelerates a search, while the latter reduces the problem's dimensionality. When a large table reaches the point where even indexes cannot save it, it is time to consider partitioning.