DevOps
PostgreSQL Sharding Strategy: When and How to Scale Your Database
May 202512 min read
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. I've built PostgreSQL-backed systems for ERP use cases where a single database node handles all of Commsult Indonesia'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.
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 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.
PostgreSQL Scaling Decision Tree
Start Here: Is your primary DB > 70% CPU/IO?
│
No ──► Do you have read replicas?
│ No ──► Add read replicas (PRIMARY + 2 REPLICAS)
│ Yes ──► Add more read replicas or optimize queries
│
Yes
│
▼
Are your largest tables partitioned?
│
No ──► Add table partitioning (RANGE by date, HASH by tenant_id)
│ This often solves the problem without sharding
│
Yes
│
▼
Do you have a multi-tenant architecture?
│
Yes ──► Citus Schema-based sharding (1 schema per tenant)
No ──► Citus Row-based sharding (distribute by entity key)
─────────────────────────────────────────────────────────
Citus Worker Architecture:
┌─────────────────┐
│ Coordinator │ ← Application connects here
│ (PostgreSQL) │ ← Routes queries, parallelizes joins
└────────┬────────┘
│
┌──────┴──────┐
▼ ▼
Worker 1 Worker 2
Shards 0-3 Shards 4-7
(tenant A-M) (tenant N-Z)From building the audit trail system for Commsult's 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 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.
-- PostgreSQL Range Partitioning (by month)
CREATE TABLE audit_logs (
id UUID DEFAULT gen_random_uuid(),
user_id UUID NOT NULL,
action VARCHAR(50) NOT NULL,
resource VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
) PARTITION BY RANGE (created_at);
-- Create monthly partitions
CREATE TABLE audit_logs_2025_01
PARTITION OF audit_logs
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE audit_logs_2025_02
PARTITION OF audit_logs
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- Index each partition (auto-inherited from parent)
CREATE INDEX ON audit_logs (created_at, user_id);
-- Citus: Distribute a table across shards
SELECT create_distributed_table('orders', 'tenant_id');
SELECT create_distributed_table('invoices', 'tenant_id');
-- Reference tables (replicated to all shards for JOINs)
SELECT create_reference_table('products');
SELECT create_reference_table('currencies');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.
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.
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.
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.
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.