Postgres Table Partitioning vs Sharding: Which One Do You Actually Need?

Your largest table has crossed a threshold where queries are slow, VACUUM takes hours, and the storage size is making your DBA nervous. The next step is unclear: partition the table, or shard the database? These two strategies are frequently confused, serve different problems, and have very different implementation costs. This guide clarifies both.

What Partitioning and Sharding Actually Mean

Partitioning splits a large table into smaller physical sub-tables within a single Postgres database. The application still connects to one database and queries one logical table. Postgres routes reads and writes to the correct partition transparently.

Sharding splits data across multiple independent Postgres databases or servers. Each shard is a full database. The application — or a routing layer — must know which shard contains the data it needs.

The core difference: partitioning is a single-server optimization. Sharding is a distributed systems problem.

Postgres Table Partitioning

Postgres supports declarative partitioning since version 10. You define a partition key and a strategy: range, list, or hash.

Range Partitioning

Most commonly used for time-series data. Partition by month, quarter, or year:

CREATE TABLE events (
  id          BIGSERIAL,
  tenant_id   BIGINT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  payload     JSONB
) PARTITION BY RANGE (occurred_at);
 
CREATE TABLE events_2025_q1
  PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
 
CREATE TABLE events_2025_q2
  PARTITION OF events
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

Queries that filter on occurred_at benefit from partition pruning — Postgres skips partitions whose range cannot contain matching rows. This keeps query plans fast even as the total dataset grows into billions of rows.

List Partitioning

Useful when you want to route by a discrete value, like region or status:

CREATE TABLE orders (
  id        BIGSERIAL,
  region    TEXT NOT NULL,
  total     NUMERIC
) PARTITION BY LIST (region);
 
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu', 'uk');

Hash Partitioning

Distributes rows evenly across N partitions using a hash of the partition key. Useful when there is no natural range or list, and you want to split a massive table into more manageable pieces:

CREATE TABLE user_events (
  user_id BIGINT NOT NULL,
  event   TEXT
) PARTITION BY HASH (user_id);
 
CREATE TABLE user_events_0 PARTITION OF user_events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... repeat for 1, 2, 3

What Partitioning Buys You

• Faster queries through partition pruning
• Faster VACUUM and AUTOVACUUM because each partition is a smaller table
• Ability to drop old partitions instantly (DROP TABLE on a partition is instant; DELETE on millions of rows is not)
• Local indexes per partition, which are smaller and easier to maintain

What Partitioning Does Not Solve

Partitioning is still one server. If your write throughput saturates a single Postgres instance's CPU, memory, or disk I/O, adding more partitions will not help. Partitioning improves efficiency; it does not add capacity.

Postgres Sharding

Sharding distributes data across multiple databases or servers. Each server holds a subset of the data and handles a fraction of the total load. This is horizontal scaling.

Implementation Options

Application-level sharding: The application maintains a shard map (tenant X lives on shard 2, tenant Y lives on shard 5) and connects to the correct database. Simple to understand, painful to operate. Cross-shard queries are your problem.

Citus: An open-source Postgres extension (now part of Azure) that manages sharding transparently. Distributed tables are sharded by a distribution column. The coordinator node routes queries to the correct worker shards.

Foreign Data Wrappers: Postgres can query remote Postgres databases via FDW. This is rarely a practical sharding solution for write-heavy workloads but works for read queries against multiple databases.

What Sharding Costs

• Cross-shard queries become expensive or impossible. Aggregating data across shards requires either a scatter-gather pattern or a separate analytics pipeline.
• Distributed transactions are hard. Two-phase commit exists, but it is slow and complex. Most teams avoid cross-shard writes by designing the shard key to keep related data co-located.
• Operational overhead multiplies. Backups, failover, monitoring, and migrations are now N problems, not one.

When to Use Partitioning vs Sharding

Choose partitioning when:

• You have a large table and query patterns that filter by a natural range (time, region, tenant)
• AUTOVACUUM is struggling because a single table is too large
• You want to drop old data efficiently (time-series archival)
• Your total write throughput fits on one server

Choose sharding when:

• Write throughput has genuinely saturated a single Postgres instance
• Dataset size exceeds what you can reasonably store on one server
• You have multi-region requirements and need data locality per region
• You have a clear shard key that keeps related data together (usually tenant ID or user ID)

The order to try things:

1. Optimize queries and indexes first — most "scaling problems" are actually query problems.
2. Upgrade your server hardware — a larger instance buys significant headroom.
3. Add read replicas for read-heavy workloads.
4. Partition large tables to improve query efficiency and operational manageability.
5. Shard only when the above are genuinely exhausted.

Teams reach for sharding far too early. The operational cost is real. Most products that think they need sharding actually need partitioning and better indexing.

If you are dealing with a slow Postgres instance and are unsure whether you need partitioning, read replicas, or a different approach entirely, start a build and we can diagnose the actual bottleneck.