Database Sharding and Read-Replication: Scaling PostgreSQL Beyond 10TB

What to fix before you shard

Most databases described as needing sharding actually need indexing and query work. A sequential scan over a large table, an unindexed foreign key or an accidental cross join can consume more resource than the entire rest of the workload combined.

Next, partition the largest tables by time or by a natural key. Native declarative partitioning keeps queries fast on recent data, makes retention a metadata operation rather than a mass delete, and keeps vacuum and index maintenance tractable.

Then pool connections. PostgreSQL allocates a process per connection, so hundreds of application connections from many replicas cause context switching and memory pressure that masquerade as slow queries. A transaction-level pooler in front of the database usually delivers a large, cheap improvement.

• Fix missing indexes and inefficient queries first.
• Partition large tables by time or natural key for retention and maintenance.
• Introduce a connection pooler before scaling instance size.
• Measure before assuming the database is the bottleneck.

Read replicas and replication lag

Read replicas distribute read load and provide a failover target, but they do not increase write throughput. They also introduce replication lag, which means a read immediately after a write may not see that write.

Applications must handle this explicitly. Route read-after-write operations to the primary, or use a session-level guarantee that pins a user's reads to the primary for a short window after their write. Silently routing everything to replicas produces confusing defects that appear only under load.

Replica count is not free. Each replica adds write amplification on the primary, consumes network bandwidth and adds a failover candidate that must be monitored. Three replicas plus a synchronous standby is a common, sufficient topology for most workloads.

Sharding and shard key selection

Sharding splits data across independent databases, each with its own storage and write capacity. It is the only technique that genuinely removes the write ceiling, and it does so by moving routing, transactions and joins into the application.

The shard key determines everything. It must distribute writes evenly, keep frequently joined data together, and match the dominant query pattern. A key with a hot tenant creates one overloaded shard while others idle; a key that scatters related rows turns every join into a cross-shard operation.

Avoid keys that require resharding soon. A hash of a stable entity identifier usually distributes well; a date-based key concentrates all current writes on one shard. Where a natural key is not suitable, consider a directory service that maps entities to shards, so rebalancing is a mapping change rather than a migration.

• Distribute writes evenly and keep joined data co-located.
• Match the shard key to the dominant access pattern.
• Prefer hashed stable identifiers over date-based keys.
• Consider a mapping service so rebalancing is a data change.

Operating a sharded system

Sharding changes operations fundamentally. Backups, migrations and schema changes must be applied across every shard consistently, and a migration that runs for minutes on one database runs for hours across sixty.

Cross-shard queries and transactions need explicit handling: scatter-gather queries for reads and sagas or two-phase patterns for writes. Both are slower and more failure-prone than their single-node equivalents and should be designed rather than discovered.

Finally, capacity planning becomes per-shard. Monitor the most loaded shard rather than the average, because the average hides exactly the imbalance that causes incidents.

• Automate schema migration across all shards with verification.
• Design scatter-gather reads and saga-based writes explicitly.
• Monitor the most loaded shard, not the mean.
• Plan rebalancing before it is urgent, with a tested procedure.