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

> Compare Postgres table partitioning and sharding strategies — understand the tradeoffs, implementation complexity, and when each approach is the right call.

- **Published:** 2025-12-05
- **Author:** Clixo
- **Reading time:** 5 min read
- **Tags:** postgres, partitioning, sharding, scaling, database
- **Canonical URL:** https://clixo.sh/blog/postgres-table-partitioning-vs-sharding

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:

```sql
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:

```sql
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:

```sql
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.

```mermaid
flowchart LR
  A["Optimize queries and indexes"] --> B["Upgrade server hardware"]
  B --> C["Add read replicas"]
  C --> D["Partition large tables"]
  D --> E["Shard across multiple databases"]
```

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](https://clixo.sh/#contact) and we can diagnose the actual bottleneck.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)