Postgres at scale before you shard

The wall you hit is usually not the wall you think

By the time someone opens a document titled "sharding strategy," the database has already been telling them what is wrong for weeks and nobody read it. A single PostgreSQL instance on hardware you can rent by the hour today comes with well over a hundred vCPUs, a terabyte or more of RAM, and block storage that will serve hundreds of thousands of IOPS. Teams planning a shard key are rarely anywhere near that ceiling. They are near a different ceiling that produces identical symptoms from inside the application: p99 latency climbing at peak, connections stacking up, a nightly job that used to take forty minutes and now takes six hours.

There are four distinct resource walls in a Postgres system and the application cannot tell them apart. Query cost: bad plans, missing indexes, an ORM issuing 400 queries where one would do. Working set: the pages you touch regularly no longer fit in RAM, so reads that were nanoseconds become storage round trips. Concurrency: more active backends than the process model handles, so the box spends its time on context switches and lock bookkeeping. Write and maintenance throughput: WAL volume, autovacuum falling behind, single-table operations that no longer finish in a maintenance window.

Sharding addresses the second and part of the fourth. It makes the third measurably worse, because every shard needs its own connection pool and your total connection count multiplies. It does nothing at all for the first. A plan that reads four million rows to return fifty reads four million rows on every shard you own, and now there is a coordinator in front of it adding a network hop.

So the first move is never a shard key. It is fifteen minutes with three system views. pg_stat_statements ordered by total_exec_time tells you where the CPU goes. pg_stat_database gives you the ratio of blks_hit to blks_read, which tells you whether your working set is still in memory. And pg_stat_activity grouped by wait_event_type tells you what the backends are actually doing right now. That last query is the highest-value thing in this article. If most backends sit on Lock or LWLock, you have contention, not capacity, and buying a bigger machine will change nothing. If they sit on IO:DataFileRead, your working set left RAM and the fix is either memory or fewer pages touched. If they sit on Client, the database is waiting on your application and the problem is upstream.

Rung one: the top ten queries are the whole problem more often than not

Install pg_stat_statements if it is not already on. Then sort by total_exec_time, not by mean. This is the single most common mistake in query tuning. The query that takes four milliseconds and runs nine hundred thousand times an hour is your load. The two-second report that runs twice a day is not, no matter how ugly it looks in a slow-query log. Sort by total time and the list usually collapses to five or six statements that account for most of the database's working life.

Then run EXPLAIN (ANALYZE, BUFFERS, SETTINGS) on each of them, against production-shaped data. Four things are worth looking for and they cover most of what we find.

Estimated rows versus actual rows off by more than about ten times, anywhere in the tree. That is where the planner made a structural choice on bad information, and everything above that node inherits the mistake. Causes are stale statistics, correlated columns the planner assumes are independent, a function whose selectivity it cannot estimate, or a parameter it cannot see. Correlated columns are fixable: CREATE STATISTICS teaches the planner that city and postal code are not independent, and multi-column estimates stop being the product of two unrelated fractions.

A nested loop with a large outer relation. Nested loop is the right join for a handful of rows and catastrophic for a million. It almost always appears downstream of a bad row estimate, so fix the estimate rather than reaching for a planner hint.

Rows Removed by Filter in the tens of thousands. You are reading pages off storage to throw the contents away. Either the index does not exist or the one you have does not cover the predicate you are actually filtering on.

A sort or hash that spilled. When the plan says external merge Disk: 240MB, that node needed more than work_mem and went to temporary files. Raising work_mem for that one statement, in that one session, is usually better than raising it globally.

Two planner settings are wrong by default on any machine with solid-state storage. random_page_cost defaults to 4.0, telling the planner a random page read costs four times a sequential one; on NVMe that ratio is closer to 1.1, and at the default the planner systematically avoids index scans in favor of advice that was correct in 2005. effective_cache_size defaults to 4GB and is a hint, not an allocation. On a machine with 256GB of RAM, telling the planner it has 4GB of cache produces conservative, wrong plans. Both are free to change and both change plans immediately.

One more: default_statistics_target at 100 is thin for skewed columns. Raise it per column with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS, then ANALYZE. Raising it cluster-wide makes every analyze slower for very little return.

Rung two: connections, and the outage nearly every team has had

Postgres forks a backend process per connection. That design is excellent for isolation and unhelpful for concurrency at scale, and it is behind the most common self-inflicted database outage we see. Each backend carries its own memory contexts, its own catalog caches, and a slot in shared structures every other backend touches.

work_mem is where this becomes a memory incident. It is not a per-connection budget but a per-node one: each sort, hash join, and hash aggregate may allocate up to work_mem independently. A query with three hash joins and a sort at work_mem = 64MB can reach for a quarter of a gigabyte by itself. Multiply by the backends running similar plans and the arithmetic that mattered was never the one in the configuration comment.

max_connections is not a throughput dial either. Past roughly two to four times your core count in active backends, more makes the system slower. A 32-core machine does less work at 800 concurrent queries than at 100, because the time goes into scheduling and shared-memory contention, and the failure mode is a cliff rather than a slope.

The fix is a pooler. PgBouncer in transaction mode lets the application open as many client connections as it likes while mapping them onto a small server pool, handing a real backend to a client only for the duration of a transaction. Start the server pool near two to three times the core count and tune from wait metrics. Three details bite reliably:

Session-scoped features break in transaction mode. SET outside an explicit transaction, session-level advisory locks, LISTEN and NOTIFY, temporary tables, and WITH HOLD cursors all assume a stable backend. Give the few components that need them a separate session-mode pool instead of downgrading the whole system.

Prepared statements. Most drivers use protocol-level prepared statements by default, and for years those were incompatible with transaction pooling. PgBouncer added named prepared statement support in 1.21 with max_prepared_statements. On an older build, either upgrade the pooler or turn off server-side prepare in the driver, and know which you chose: a silent re-plan on every execution is a large CPU difference that appears nowhere in the application logs.

Pool math across instances. Twenty application pods each holding a local pool of twenty connections is four hundred connections arriving at the database, not twenty. Count what arrives at peak, including background workers and cron containers people forgot about.

While you are here, set idle_in_transaction_session_timeout. A connection parked inside an open transaction holds its snapshot, and a held snapshot stops vacuum from reclaiming any row version newer than it. That is a bloat generator, and it presents as a storage problem three weeks later with no obvious cause.

Rung three: indexes, including the ones you should delete

Index work is where the largest single-query wins live, and the useful moves are more specific than "add an index on the column in the WHERE clause."

Column order in a composite index is not cosmetic. Equality predicates first, then the range or sort column. An index on (tenant_id, created_at) serves WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 50 as one index scan with no sort node. Reverse the columns and the same query becomes a scan plus a sort.

Partial indexes are the cheapest large win available. If half a percent of a hundred-million-row table is in status = 'pending' and that is the only status your hot path queries, an index with WHERE status = 'pending' is a couple of orders of magnitude smaller, stays resident in cache, and costs almost nothing to maintain because most writes never touch it.

Covering indexes with INCLUDE enable index-only scans, which skip the heap entirely. The catch that surprises people: index-only scans need the visibility map to be current, and the visibility map is maintained by vacuum. If EXPLAIN (ANALYZE) shows a large Heap Fetches number on an index-only scan, it is not index-only in practice, and the fix is vacuum, not the index.

BRIN indexes are for append-only, physically-correlated columns. On a time-series table where created_at increases with physical position, a BRIN index is kilobytes where a B-tree is gigabytes. On a column with no correlation to heap order it is worse than useless, because the planner will use it and then scan most of the table.

GIN for jsonb containment and full-text search, remembering that its write amplification is deferred rather than avoided: the pending list under fastupdate keeps inserts fast and pays for it during a later merge, which surfaces as an unexplained latency spike on a write path. And expression indexes must match the expression exactly — an index on lower(email) does nothing for WHERE email ILIKE $1.

Now the part teams skip. Every index is a tax on every write, and an index on a frequently updated column also defeats the heap-only-tuple path described below. Query pg_stat_user_indexes for idx_scan = 0 after a full business cycle that includes month-end, because reporting workloads use indexes nothing else touches, and check the replicas separately since their counters are their own. Then drop the dead ones with DROP INDEX CONCURRENTLY.

Build every production index with CREATE INDEX CONCURRENTLY. It costs two table passes instead of one and does not block writes. It can also fail partway and leave an index marked invalid, which still costs you write maintenance while serving no reads, so check pg_index.indisvalid after every build and drop the failures.

Rung four: the physical layer, where "we need a bigger box" is usually born

Postgres is multiversion. An UPDATE writes a new row version and leaves the old one dead until vacuum reclaims it; a DELETE only marks. If vacuum falls behind, the table grows, scans read pages full of garbage, indexes carry pointers to tuples nobody wants, and the instance looks I/O bound. It is not. It is carrying dead weight, and more storage throughput just carries it faster.

The default that hurts large tables specifically is autovacuum_vacuum_scale_factor at 0.2, with a threshold of 50 rows. Autovacuum waits until roughly twenty percent of the table is dead before it runs. On a five-million-row table that is a million dead rows and mildly wasteful. On a five-hundred-million-row table it is a hundred million dead rows, and the vacuum that eventually triggers is enormous, slow, and arrives without regard for your traffic pattern. Set it per table on the big ones:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 10000);

Small and frequent beats rare and huge. Two companion settings matter as much. The cost-based throttle was calibrated for spinning disks, so on NVMe raise autovacuum_vacuum_cost_limit substantially or drop autovacuum_vacuum_cost_delay to a couple of milliseconds, or vacuum is politely rate-limited into permanent failure. And autovacuum_max_workers at three cannot service two hundred partitions; the workers become the bottleneck and nothing tells you.

Heap-only tuple updates are worth engineering for deliberately. When an update changes no indexed column and the new version fits on the same page, Postgres skips index maintenance entirely. So do not index the column your hot path updates constantly, such as a last_seen_at, unless a query needs it, and set fillfactor to 85 or 90 on heavily updated tables so the new version has room to land locally. Both cut write amplification without changing the schema your application sees.

Transaction ID wraparound belongs on your monitoring even though you will not think about it for years. XIDs are 32-bit, and autovacuum_freeze_max_age defaults to 200 million and forces an anti-wraparound vacuum that cannot be skipped and should not be cancelled. Track age(relfrozenxid) per table and alert early, because the day it matters, it matters as an outage rather than as a graph.

For bloat that already exists, pg_repack rebuilds a table and its indexes without holding a long exclusive lock; VACUUM FULL does the same job and holds ACCESS EXCLUSIVE for the entire rewrite, which on a large table means downtime you did not schedule.

Rung five: partitioning is not sharding and does not behave like it

Declarative partitioning splits one logical table into many physical ones inside the same instance. It is frequently mistaken for a scaling technique because it is often the last thing that works before a team gives up, but what it actually buys is maintenance, not capacity.

What it buys. Retention becomes a metadata operation: DETACH PARTITION followed by DROP TABLE instead of deleting two hundred million rows, which generates WAL, generates bloat, and takes hours. Vacuum and index builds run per partition, so each unit of maintenance is small enough to finish. Indexes stay small enough to stay in cache, which is often the real win. And the planner prunes partitions that cannot contain matching rows, both at planning time and, for parameterized plans, at execution time.

What it costs. Any query without the partition key in its predicate touches every partition. A lookup by order_id on a table partitioned by created_at fans out across the whole set, which is the common accident when a scheme is chosen for the write path without auditing the read paths. Unique constraints must include the partition key, a real change in what the database can enforce that will surface in application code. Planning time grows with partition count: tens to low hundreds is comfortable, thousands needs deliberate testing.

Use pg_partman or equivalent to automate creation and retention, so nobody discovers on a Sunday that the current month has no partition. When attaching an existing table, add a matching CHECK constraint and validate it first, or the ATTACH holds a lock while it scans the whole table to prove the constraint.

The thing to keep straight: all partitions share one WAL stream, one buffer cache, one process pool, one server. Partitioning improves locality and makes maintenance tractable. It does not add write throughput, and if write throughput is your actual wall, partitioning will not move it.

Rung six: send the reads somewhere else

Streaming replicas are cheap and underused. Reporting, exports, search backfills, anything read-only and tolerant of a second of lag belongs on a replica rather than on the machine taking your writes.

Two mechanics decide whether this goes well. hot_standby_feedback lets a replica tell the primary not to remove row versions its running queries still need, which prevents query cancellations on the replica and creates bloat on the primary. It is a real trade, not a best practice, and the failure of either choice is quiet. Pick one deliberately and write down which.

The other is read-your-writes. A user posts a comment, the read goes to a replica that has not received it, and the comment is gone. Solve it once in the data access layer: route reads back to the primary for a short window after a write from the same session, or capture the write LSN and wait for the replica to reach it. Teams that solve it per endpoint end up with half-solutions and a bug class that reproduces once a month.

Logical replication is the more surgical tool. Publish a subset of tables into a separate database with its own indexes and its own vacuum profile, without carrying the whole cluster along. It is the right answer when the analytics team wants twelve columns from four tables and currently gets them from a nightly job against your primary.

Which points at the move that resolves more scaling pressure than anything else here: separating workloads that want opposite things. Analytics wants large sequential reads, generous work_mem, and long-lived snapshots. Transaction processing wants small random reads and short transactions. On one instance they compete over the buffer cache, where a single analytical scan evicts the working set the whole application depends on. Move analytics to a replica, a columnar store, or a warehouse, and the primary gets quieter than any shard split would have made it, for a fraction of the engineering.

Rung seven: take the wrong workloads out of the database

Postgres is a good enough queue, a good enough cache, a good enough document store, and a good enough search engine, which is why so much lands there and stays. Some of it should not.

Job queues. SELECT ... FOR UPDATE SKIP LOCKED is genuinely good and carries a team a long way with no extra infrastructure. Its failure mode is churn: a busy queue table produces dead tuples faster than anything else in the schema and will be the first place autovacuum falls behind. Partition it, tune its autovacuum settings hard, and know the rate at which a dedicated broker wins.

Session state and rate-limit counters. High write rate, no durability requirement, no relational value, and every write competes with real work for WAL bandwidth. This is what an in-memory store is for.

Large binaries. Object storage, with a row holding the key and the metadata. Blobs in the heap wreck your cache hit ratio and inflate every backup.

Append-only event logs that are only ever scanned. A columnar format on object storage answers those questions faster and stops the events table dominating your maintenance schedule.

Removing a workload that never belonged has taken more pressure off primaries in our work than any configuration change we have made.

Symptom, assumption, and the fix that is not sharding

Symptom	Common assumption	What it usually is	The actual fix
p99 spikes at peak, mean stays flat	Out of capacity	Active backends past the core count; lock and scheduling contention	Transaction-mode pooler, cap the active pool
Storage grows faster than data	Need a bigger volume, or a shard	Bloat from autovacuum unable to keep up	Per-table autovacuum settings, raise the cost limit, pg_repack
A nightly job went from 40 minutes to 6 hours	The table got too big	The plan flipped when a row estimate crossed a threshold	ANALYZE, extended statistics, an index for the join
CPU pinned on simple queries	Need more cores	Sequential scans chosen because random_page_cost is still 4.0	Planner settings for solid-state storage, partial indexes
Writes slow, reads fine	Write throughput ceiling	Too many indexes on the hot table, no heap-only-tuple path	Drop unused indexes, unindex the hot column, lower fillfactor
Replica lag climbs during batch jobs	Replication cannot keep up	One very large transaction serializing apply on the replica	Chunk the write, throttle the batch, commit more often

What sharding actually costs, stated plainly

None of this is an argument that sharding is wrong. It is an argument that it is expensive and permanent, and that the bill is paid mostly by people who were not in the room when the decision was made. Here is what changes on the day the second shard goes live.

• Cross-shard joins stop being joins. They become application code, with their own pagination, their own failure modes, and no query planner helping.
• Transactions across shards need two-phase commit or a saga. Either way you now own a distributed correctness problem forever, including its partial-failure states.
• Global uniqueness is no longer enforceable by the database. Email addresses, external identifiers, invoice numbers: the constraint moves to your code, where it is a race condition rather than a guarantee.
• Schema migrations run N times with partial-failure states in between, and every deploy now has a state where shards disagree about the schema.
• Rebalancing a hot shard is a data-movement project with a live correctness risk, and hot shards are not hypothetical. Real keys are skewed.
• Backup and point-in-time recovery become a coordination problem. Restoring one cluster to a moment is routine. Restoring several to the same consistent moment is not the same operation.
• Every observability query fans out. "How many orders did we take yesterday" is now a distributed query you have to write and maintain.
• Tooling degrades. Migration frameworks, admin interfaces, reporting tools, and local development environments all assumed one database, and most of them were right to.

When sharding is the right answer

There is a real case, and it has a shape. A clean partition key present in nearly every query and never crossed. Write volume genuinely past what one WAL stream absorbs, measured rather than assumed. A dataset larger than any instance you can rent. Or a contractual requirement to keep certain customers' data physically separate, which is a compliance answer rather than a performance one and is often satisfied by separate databases rather than a sharded one.

Multi-tenant products with a strong tenant identifier are the honest case. The tenant key does the routing, cross-tenant queries barely exist, and extensions like Citus turn most of the distribution work into configuration rather than a rewrite. If that describes your data model, sharding early is defensible precisely because it is cheap while the system is small. If the key is not clean, and usually it is not, sharding means building a distributed system by accident and discovering the requirements one incident at a time.

Score it the way you would score any expensive, irreversible architecture decision: weights set before anyone sees the numbers, so the rubric decides instead of ratifying a conclusion someone already reached.

60 and above: shard, and start with the tooling and the migration path rather than the key, because the key is the easy part. 35 to 59: climb the ladder first and re-score in two quarters with the measurements you did not have the first time. Below 35: the ladder is the entire answer and a shard key would be a costly way to avoid reading an execution plan.

A two-week headroom audit

Two weeks is enough because every expensive unknown here is measurable inside it. Whether the box is contended or saturated is measurable. Whether the working set fits in RAM is measurable. Whether the top statements have fixable plans is answered by fixing three and looking at the graph. What is not measurable in two weeks is opinion, and opinion is what turns a tuning problem into a twelve-month re-platform.

Do these before you write a shard key

• pg_stat_statements enabled, and the top ten by total execution time reviewed with plans
• A transaction-mode pooler in front, with the active server pool sized to the core count
• random_page_cost and effective_cache_size set for the hardware you actually run on
• Unused indexes identified across a full business cycle and dropped
• Per-table autovacuum settings on every table above roughly fifty million rows
• Bloat and frozen XID age on a dashboard with alert thresholds someone owns
• Analytics and reporting moved off the primary
• The largest table partitioned, with retention automated and pruning verified in EXPLAIN
• A written headroom number: months of runway at current growth, and which wall arrives first

Bottom line

Sharding is a real technique with a narrow, honest case. Most systems that reach for it are somewhere between two and fifty times away from a single instance's actual ceiling, and the distance is made of unfixed queries, an unbounded connection count, indexes nobody audited, and vacuum settings that have not been touched since the schema had ten thousand rows in it. Those are cheap to fix, reversible, and they compound. Climb the ladder in order, measure between rungs, and write down the headroom number so the next conversation about scale starts from evidence instead of a whiteboard.

Frequently asked questions