# PostgreSQL Sharding Strategy: When and How to Scale Your Database

> A pragmatic guide to PostgreSQL sharding and partitioning — covering table partitioning, Citus distributed tables, and the honest truth about when you actually need to shard.

URL: https://www.matthewswong.com/en/blog/postgresql-sharding-strategy/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2025-06-10
- Updated: 2026-10-17
- Category: DevOps

PostgreSQL can handle millions of rows and thousands of concurrent connections on a single node — most applications never need sharding. But when you do cross that threshold, the sharding decision shapes your entire architecture for years. In ERP-style workloads, a single database node often handles all of a company's data comfortably. The moment you need horizontal scale — because you're a multi-tenant SaaS serving thousands of customers, or you're managing billions of events — sharding becomes unavoidable. This guide covers the spectrum from table partitioning (which you can and should do earlier) to full distributed sharding with Citus.

## The Spectrum: Partitioning vs Sharding

Partitioning and sharding are related but distinct. Partitioning divides a large table into smaller physical tables (partitions) on the same database node. It improves query performance by pruning — queries with a WHERE clause on the partition key only scan the relevant partition. Sharding distributes those partitions across multiple database nodes, enabling horizontal scale-out. You can use partitioning without sharding, and Citus uses partitioning as the building block for sharding. Start with partitioning; add sharding only when you exhaust vertical scale.

### PostgreSQL Native Table Partitioning

PostgreSQL has supported declarative table partitioning since version 10. The two most useful strategies are RANGE partitioning (by date — excellent for time-series data like audit logs, events, and invoices) and HASH partitioning (by a hash of a tenant ID or user ID — useful for distributing write load evenly). For an ERP system's audit_logs table with millions of rows per year, partitioning by month reduces query time from seconds to milliseconds — because a WHERE created_at BETWEEN query only scans the relevant month's partition instead of the entire table.

> For audit trail tables in an ERP: partition your high-volume tables by created_at month from day one. The migration path from an unpartitioned table to a partitioned one is painful — you need to rename the old table, create the new partitioned parent, copy data, and swap. Doing it upfront when the table is empty costs almost nothing and saves you an emergency maintenance window when the table hits 50M rows.

## Citus: Distributed PostgreSQL

Citus extends PostgreSQL with distributed query execution across a coordinator node and worker nodes. Tables are distributed via a distribution column — Citus hashes the column value and assigns each shard (a subset of rows) to a worker node. Queries that include the distribution column in their WHERE clause are routed directly to the relevant shard. Cross-shard queries are parallelized across workers. Citus 12 introduced schema-based sharding, which is ideal for multi-tenant SaaS — each tenant gets their own schema, and Citus treats schemas as the shard boundary.

### Choosing the Right Distribution Column

The distribution column choice determines query performance. The golden rule: queries that filter on the distribution column are fast; queries that don't are scatter-gather (hitting all shards). For a multi-tenant ERP, tenant_id is the obvious distribution column — all queries from a tenant's session filter on their tenant_id, so data locality is maximized. For an e-commerce platform, user_id or order_id are common choices. Avoid high-cardinality columns that produce skewed distributions (one value contains 90% of the rows), and avoid columns that change frequently.

> **Sharding complicates foreign keys and joins**

> In a sharded PostgreSQL setup, cross-shard foreign keys and joins are either unavailable or very expensive. You lose standard referential integrity guarantees — you must enforce them at the application layer. Transactions that span multiple shards require distributed transaction protocols. Schema migrations must be applied to all shards simultaneously. These constraints change your data modeling significantly. Don't shard until you've genuinely exhausted vertical scaling (a 64-core, 512GB RAM PostgreSQL instance handles enormous workloads) and table partitioning on a single node.

## When You Actually Need Sharding

The signals that indicate you need sharding: your primary database node's CPU or I/O is consistently above 70% despite query optimization and indexing; your largest tables are in the hundreds of gigabytes and growing faster than you can add storage; you have a multi-tenant architecture where tenant data isolation is a hard requirement; your backup and restore windows are unacceptably long. If none of these apply, you don't need sharding — you need better indexing, query optimization, or a read replica.

## Read Replicas Before Sharding

Before considering sharding, add PostgreSQL read replicas. Most applications are read-heavy — 80-90% of queries are reads. A primary + 2 read replicas triples your read capacity with zero schema changes. Route analytics queries, report generation, and read-only API endpoints to replicas; route writes to the primary. In NestJS with TypeORM or Prisma, this is a configuration change — you define multiple data source connections and route queries appropriately. This buys you significant headroom before you ever need to discuss sharding.

## Migration Path: From Single Node to Sharded

The migration from a single PostgreSQL node to a sharded Citus cluster is non-trivial. The recommended path: first add partitioning to your largest tables on the single node. Then provision a Citus coordinator + workers. Migrate partitioned tables to distributed tables using Citus's create_distributed_table() — this copies data shard by shard with minimal downtime. Update your application's connection string to point to the Citus coordinator (which speaks standard PostgreSQL protocol). Run dual-write during migration and validate row counts before cutover. Plan for 2-4 weeks of migration work for a large database.

## Sources & Further Reading

- Citus Data / Microsoft — Understanding Partitioning and Sharding in Postgres and Citus
- Citus Docs — Choosing Distribution Column
- PostgreSQL Documentation — Table Partitioning

## Source links

- https://www.citusdata.com/blog/2023/08/04/understanding-partitioning-and-sharding-in-postgres-and-citus/
- https://docs.citusdata.com/en/stable/sharding/data_modeling.html
- https://www.postgresql.org/docs/current/ddl-partitioning.html

## FAQ

### What is the difference between table partitioning and sharding in PostgreSQL?

Table partitioning divides a large table into smaller physical partitions on the same database node, improving query performance through partition pruning. Sharding goes further by distributing those partitions across multiple database nodes to enable horizontal scale-out. You can use partitioning without sharding — in fact, the post recommends starting with partitioning first and only adding sharding when vertical scaling is genuinely exhausted.

### When does a PostgreSQL deployment actually need sharding?

The post identifies clear signals: your primary node's CPU or I/O is consistently above 70% despite query optimization and indexing, your largest tables are in the hundreds of gigabytes and growing faster than you can add storage, tenant data isolation is a hard architectural requirement, or your backup and restore windows are unacceptably long. If none of those apply, better indexing, query optimization, or read replicas are the right next step — not sharding.

### How should you choose a distribution column when using Citus?

The distribution column determines whether queries hit one shard or scatter across all of them. The golden rule from the post: queries that filter on the distribution column are fast; queries that don't are scatter-gather operations that touch every shard. For a multi-tenant ERP, tenant_id is the natural choice because all queries in a tenant session already filter on it. Columns with skewed value distributions (where one value holds 90% of rows) or columns that change frequently should be avoided.

### Why should you add read replicas before considering sharding?

Most applications are read-heavy — the post notes that 80–90% of queries are reads. Adding a primary plus two read replicas triples read capacity with zero schema changes, simply by routing analytics queries, report generation, and read-only API endpoints to replicas and writes to the primary. This is a configuration-level change in frameworks like NestJS with TypeORM or Prisma, and it buys significant headroom before sharding ever becomes necessary.

### What are the main trade-offs and complications introduced by sharding?

Sharding removes standard referential integrity guarantees — cross-shard foreign keys and joins are either unavailable or very expensive, so integrity must be enforced at the application layer. Transactions spanning multiple shards require distributed transaction protocols, and schema migrations must be applied to all shards simultaneously. The post also warns that the migration from a single node to a Citus cluster is non-trivial and recommends planning for 2–4 weeks of migration work for a large database.

---

- HTML version: https://www.matthewswong.com/en/blog/postgresql-sharding-strategy/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/postgresql-sharding-strategy/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json