The Complete Guide to Partitioning and Sharding: The Order for Moving Past a Single Node

• Introduction
• 1. What Partitioning Actually Solves
• 2. The Three Partitioning Methods
• 3. Partition Pruning — The One Real Win
• 4. The Constraints a Partition Key Imposes
• 5. Partition Count and Planner Cost
• 6. Operating Partitions — Attaching and Detaching
• 7. Where Partitioning Hits Its Limit — When Is It Time to Shard
• 8. What Breaks the Moment You Shard
• Quiz: Test Yourself
• Closing
• References
• Continue Reading

Introduction

This blog already has several posts on PostgreSQL partitioning. PostgreSQL Partitioning Complete Guide covers the syntax and performance of the Range, List, and Hash strategies, and PostgreSQL Partitioning Strategies and Parallel Queries covers how partitioning interacts with parallel execution.

This post takes a different angle. It treats partitioning not as a destination but as one point along a path. As data grows, the road we travel runs index → partitioning → read replicas → sharding. Each stage matters only once the stage before it stops working, and each stage brings a new problem that the previous stage did not have. This post covers "when to start partitioning, when to stop, and when to move to the next stage." The specific goal is to know in advance what breaks the moment you move to sharding.

The reference engine is PostgreSQL 18, and every constraint and default value for partitioning was confirmed against the PostgreSQL 18 documentation. The sharding section focuses on design principles that are not tied to any single product, and it explicitly calls out that product-specific behavior needs to be checked against the documentation for that particular product.

1. What Partitioning Actually Solves

Let's clear up a misconception first. Partitioning does not add disk space, does not increase write throughput, and does not automatically make most queries faster. All partitions live on the same storage on the same server.

Partitioning actually solves four problems.

First, a smaller scan range. When a query only touches specific partitions, it never reads the rest at all. This is partition pruning, and it is the one truly fundamental performance gain that partitioning provides.

Second, turning bulk deletes into a constant-time operation. Deleting the oldest year out of three years of logs takes hours with DELETE and generates a huge number of dead rows and WAL. With monthly partitions, it is twelve calls to DROP TABLE. This difference is the single most common reason teams adopt partitioning.

Third, a smaller unit of maintenance. VACUUM, ANALYZE, and index rebuilds run per partition instead of across the whole table. A past partition that no longer receives updates effectively needs no maintenance at all.

Fourth, smaller indexes. Each partition carries its own separate index, so each individual index is smaller and fits into cache more easily.

Put the other way around, if you do not need any of the four things above, partitioning is a net loss. Planning time goes up, unique constraints become more restricted, and there is more operational work to do. "The table is big, so let's partition it" is not, by itself, a justification.

2. The Three Partitioning Methods

PostgreSQL's declarative partitioning provides three methods. These are the definitions straight from the documentation.

RANGE — splits data by ranges of a key column. Per the documentation, "the ranges should always have inclusive lower bounds and exclusive upper bounds." Get this rule wrong and data that falls right on a boundary either disappears or overlaps.

CREATE TABLE events (
  id         bigint       GENERATED ALWAYS AS IDENTITY,
  tenant_id  bigint       NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload    jsonb        NOT NULL
) PARTITION BY RANGE (occurred_at);

-- August 2026: includes 08-01 00:00:00, excludes 09-01 00:00:00
CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

LIST — in the documentation's words, each partition is defined "by explicitly listing which key value(s) appear in each partition." Use it when the set of values is finite and stable, like region codes, countries, or status values.

HASH — in the documentation's words, you give each partition "a modulus and a remainder, and each partition will hold the rows for which the hash value of the partition key divided by the modulus produces the specified remainder." The goal is an even split of the value distribution, and range-based pruning is not possible with it.

-- Spread tenants evenly across eight slices
CREATE TABLE events_h (LIKE events INCLUDING ALL) PARTITION BY HASH (tenant_id);
CREATE TABLE events_h_0 PARTITION OF events_h FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE events_h_1 PARTITION OF events_h FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ... the remaining 6

RANGE and LIST can both have a DEFAULT partition, which catches rows that do not belong anywhere else. However, for reasons we will see in sections 5 and 6, the rule of thumb is to keep the DEFAULT partition empty.

3. Partition Pruning — The One Real Win

Pruning is the optimization that looks at partition definitions and removes, from the plan, any partition that cannot possibly satisfy the condition. It is controlled by enable_partition_pruning, and as the documentation itself marks it "the default," the default value is on.

Pruning happens at two different points.

Plan-time pruning — when the WHERE condition is a constant, pruning happens while the plan is being built, and the excluded partitions leave no trace in the EXPLAIN output; they simply are not there.

Execution-time pruning — when a parameter value is only determined during execution (a bind value in a prepared statement, a subquery result, the inner side of a Nested Loop), pruning happens as the query runs. Here, EXPLAIN output shows Subplans Removed.

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

 Aggregate  (cost=4210.55..4210.56 rows=1 width=8)
            (actual time=31.204..31.205 rows=1 loops=1)
   Buffers: shared hit=2048
   ->  Seq Scan on events_2026_08 events  (cost=0.00..3901.20 rows=123740 width=0)
         (actual time=0.011..21.882 rows=123740 loops=1)
         Filter: ((occurred_at >= '2026-08-01 00:00:00+09'::timestamptz)
              AND (occurred_at <  '2026-09-01 00:00:00+09'::timestamptz))
         Buffers: shared hit=2048
 Planning Time: 0.502 ms
 Execution Time: 31.240 ms

What you need to check here is whether only a single partition shows up in the plan. If all thirty-six partitions are listed out, pruning has failed, and you are getting none of the benefit of partitioning at all.

Remember the two most common reasons pruning fails. First, wrapping the partition key in a function disables pruning. WHERE date_trunc('month', occurred_at) = ... cannot be reduced to a range over the key. Second, if the partition key does not appear in the condition at all, the planner naturally scans every partition. This is why choosing the partition key is such a decisive choice.

4. The Constraints a Partition Key Imposes

A partition key does not just determine performance. It also determines what kinds of constraints your schema is even able to express.

This is the most important restriction the documentation states. To create a unique constraint or primary key on a partitioned table, "the partition key of the table must not include any expressions or function calls and the constraint's columns must include all of the partition key columns."

This single sentence changes your entire design.

-- On a table partitioned by occurred_at,
-- this cannot be created: the partition key (occurred_at) is missing
ALTER TABLE events ADD CONSTRAINT uq_events_id UNIQUE (id);

-- only this works: it has to include the partition key
ALTER TABLE events ADD CONSTRAINT uq_events_id_time UNIQUE (id, occurred_at);

In other words, once you partition by time, you cannot express "id is globally unique" as a database constraint. Instead, the application or a sequence has to guarantee that on its own. This is one more reason people reach for globally unique identifiers such as UUID or UUIDv7.

Exclusion constraints carry the same restriction. Per the documentation, they "must include all the partition key columns" and "must use equality comparisons for these columns."

A few other restrictions the documentation spells out:

• A BEFORE ROW trigger on INSERT cannot change which partition a new row ultimately ends up in.
• You cannot mix temporary and permanent tables within the same partition tree.

A practical order for choosing a partition key: first, look at what condition most of your queries always carry. Second, look at which axis your data retention policy cuts along. Third, confirm that axis does not conflict with your unique constraint requirements. If no single axis satisfies all three, that can be a sign that partitioning is premature.

5. Partition Count and Planner Cost

The intuition that "the more finely you slice partitions, the better" is wrong. The documentation directly pushes back on it: "do not assume that a large number of partitions will always be better than a small number, or vice versa."

On the actual numbers, the documentation says: "the query planner is generally able to handle partition hierarchies with up to a few thousand partitions fairly well, provided that typical queries allow the planner to prune all but a small number of partitions."

The cost shows up in two places. In the documentation's own words, "planning times become longer and more memory is consumed if many partitions survive pruning," and the scarier side of that is memory: "especially if many sessions touch large numbers of partitions, the server's memory consumption can grow considerably over time, because each partition's metadata must be loaded into the local memory of each session that touches it."

There is also guidance by workload type: "it may be more reasonable to use a larger number of partitions in a data warehousing type of workload than in an OLTP type of workload, because in data warehouses the majority of processing time is usually spent on execution, making planning time less significant."

Summed up as a practical rule of thumb: for OLTP, keep the partition count in the tens to low hundreds, and if you need more than that, widen the partition interval (daily to monthly) or detach old partitions out to an archive. And do not forget max_locks_per_transaction. Its default is 64, and the documentation itself cites queries against a parent table with many child tables as a case that requires raising it. Touch hundreds of partitions inside a single transaction and you will run straight into this limit.

6. Operating Partitions — Attaching and Detaching

Operating a partitioned table is, for the most part, a repeating cycle of "create future partitions ahead of time, detach past partitions."

Creating ahead of time. When a row arrives for a range that has no partition, you either get an error or the row lands in the DEFAULT partition. Neither is good. Schedule a batch job that creates at least two or three periods' worth of partitions in advance.

Attaching.

Warning: ALTER TABLE ... ATTACH PARTITION only takes a SHARE UPDATE EXCLUSIVE lock on the parent, but it takes an ACCESS EXCLUSIVE lock on the table being attached itself and on the DEFAULT partition, if one exists. If the DEFAULT partition holds a lot of data, the scan that checks for rows overlapping the new range takes a long time, and for that entire duration all access to the DEFAULT partition is blocked. When a DEFAULT partition exists, the documentation recommends "creating a CHECK constraint which excludes the values that are to be moved" before attaching. The better answer is to not have a DEFAULT partition at all, or to always keep it empty.

When attaching an existing table as a partition, adding a CHECK constraint ahead of time lets the operation skip the validation scan.

-- Create a CHECK constraint proving the range before attaching
ALTER TABLE events_2026_10_staging
  ADD CONSTRAINT chk_range
  CHECK (occurred_at >= '2026-10-01' AND occurred_at < '2026-11-01');

ALTER TABLE events ATTACH PARTITION events_2026_10_staging
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Detaching. DETACH PARTITION has a concurrent mode. In the documentation's words, specifying CONCURRENTLY "runs with a reduced lock level so that other sessions accessing the partitioned table are not blocked." Make this option your default for retention-policy batch jobs.

-- Detach while minimizing lock impact
ALTER TABLE events DETACH PARTITION events_2023_08 CONCURRENTLY;

-- Once detached it is just an ordinary standalone table, so handle it freely
-- dump it to an archive, or
DROP TABLE events_2023_08;

Indexes. You cannot use CREATE INDEX CONCURRENTLY directly on a partitioned table. The documentation states that "concurrent index creation on a partitioned table is currently not supported," and it instructs you to build the index concurrently on each individual partition and then, last, build it non-concurrently on the parent.

Partition-wise joins and aggregates. enable_partitionwise_join and enable_partitionwise_aggregate let tables with matching partition boundaries join or aggregate partition by partition. Both parameters default to off, because they increase planning cost. If you run an analytical workload that frequently joins large tables with aligned partition boundaries, it is worth trying them on.

7. Where Partitioning Hits Its Limit — When Is It Time to Shard

Partitioning is a story that happens inside one server. If you hit any one of the following three situations, partitioning cannot take you any further.

First, when write throughput reaches the limit of a single node. No matter how you slice partitions, WAL is still a single stream and every commit goes to a single disk. Reads can be spread across replicas; writes cannot.

Second, when data outgrows a single node's storage or backup window. Once backup and recovery time exceeds the RTO the business can tolerate, you have to split things up physically.

Third, when data has to be physically separated for geographic or regulatory reasons. This is not a performance problem — it is a requirement.

Keeping the order matters here. Sharding is the last resort. In most cases there is still something left to try before it: index and query tuning, spreading reads across read replicas, shrinking scans with partitioning, moving old data out to a separate analytical store, adding a caching layer, and vertical scaling. Weigh hardware cost against engineering time, and vertical scaling is still frequently the cheapest answer.

In the PostgreSQL ecosystem, there are three paths to implementing sharding.

• Application-level sharding — the application looks at the shard key and decides which database to go to. It is the simplest and most controllable option, but you have to build routing and rebalancing yourself.
• Federation based on postgres_fdw — foreign tables get attached as partitions, so data on other nodes can be queried as if it were one table. How far condition push-down reaches determines performance.
• Distributed extensions or distributed SQL engines — extensions such as Citus, or separate distributed SQL products. Check each product's own documentation for its behavior and constraints; this is an area where the supported scope varies a great deal from version to version.

8. What Breaks the Moment You Shard

Before you decide to shard, you need to know exactly what you are giving up. There are four things.

First, cross-shard joins. Joining two tables that have different shard keys means pulling data from multiple nodes into one place. There are two responses. Either place tables that are joined frequently under the same shard key so the join always finishes inside a single shard (co-location), or replicate small, rarely changed tables onto every shard (reference tables). If a lot of your joins are not covered by either of these, your shard key choice is wrong.

Second, global uniqueness and sequences. A bigserial on each shard collides with the others. The responses are giving each shard a different sequence start value and increment, using a UUID-family identifier, or encoding the shard number into the high bits. PostgreSQL 18 added a uuidv7() function that carries time ordering, which makes it a candidate whenever you need sort locality.

Third, transactions. An atomic update spanning multiple shards requires two-phase commit, and two-phase commit brings along the problem of locks being left behind if the coordinator dies. In practice, the answer is usually to design the domain so that only transactions staying within a single shard are allowed. When a transaction has to span shards, you reach for compensating transactions, as in the saga pattern, to keep things consistent.

Fourth, rebalancing. Growing from 8 shards to 16 involves moving data. With a naive modular hash, almost all the data moves. You need to adopt consistent hashing or virtual shards (an indirection layer that maps logical shards onto physical nodes) from the very start, or you will suffer for it later.

On top of all this comes operational cost. Backups, monitoring, version upgrades, and incident response all multiply by the number of shards. Sharding is not a technical decision — it is an organizational one.

Quiz: Test Yourself

SELECT count(*) FROM events
WHERE date_trunc('month', occurred_at) = '2026-08-01'::timestamptz;

SELECT count(*) FROM events
WHERE occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

Closing

Partitioning and sharding sound similar and look similar in diagrams, but they are fundamentally different in nature. Partitioning is physical design inside a single database, and it is relatively easy to reverse. Sharding is a change to your system architecture, and reversing it is close to impossible.

This is why the order matters. If indexes and query tuning solve the problem, stop there. If it is about scan range or retention policy, partition. If it is read load, add replicas. Only if you are still hitting a single-node limit after all that should you consider sharding. Writing down, in numbers, "the evidence for moving to the next stage" at each step shortens the arguments an organization has with itself.

To see partition pruning for yourself, try the PostgreSQL Playground; if you need large volumes of test data, use the Mock Data Generator.

References

• PostgreSQL 18, Table Partitioning: https://www.postgresql.org/docs/18/ddl-partitioning.html (retrieved 2026-08-15)
• PostgreSQL 18, ALTER TABLE: https://www.postgresql.org/docs/18/sql-altertable.html (retrieved 2026-08-15)
• PostgreSQL 18, CREATE INDEX: https://www.postgresql.org/docs/18/sql-createindex.html (retrieved 2026-08-15)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (retrieved 2026-08-15)
• PostgreSQL 18, Lock Management: https://www.postgresql.org/docs/18/runtime-config-locks.html (retrieved 2026-08-15)
• PostgreSQL 18, Write Ahead Log: https://www.postgresql.org/docs/18/runtime-config-wal.html (retrieved 2026-08-15)

Continue Reading

• Previous: The Complete Guide to Zero-Downtime Schema Changes — the lock levels DDL takes
• Next: The Complete Guide to Connection Pools — the contract a pooling mode makes
• PostgreSQL Partitioning Complete Guide — Range, List, and Hash syntax and performance
• PostgreSQL Partitioning Strategies and Parallel Queries — the interaction with parallel execution
• MongoDB Sharding Complete Guide — the sharding model of a different engine
• PostgreSQL Playground — experiment with partition pruning
• Mock Data Generator — generate large volumes of test data