Read Replicas → Application-Level Sharding
Very HighHorizontally distributing a PostgreSQL dataset across N independent shards so that write throughput and storage scale linearly with shard count: permanently changing the data access model, eliminating cross-shard ACID transactions, and making the shard key the most consequential and irreversible architectural decision in the system.
Topology Changes
From
PostgreSQL Primary with Read Replicas
To
Horizontally Sharded Database
Topology Mutations
The dataset is divided across N shards. Each shard is an independent PostgreSQL instance with its own primary, replica, connection pool, and operational management. The shard key determines which shard owns each row: all rows for a given shard key value live on the same shard.
Operational Impact
Cross-shard queries (aggregations, joins, admin reports spanning all users) require scatter-gather: query all N shards and merge results in the application. These queries are O(N) in cost and grow more expensive as shard count increases.
Application-level logic (or a middleware layer like Vitess or Citus coordinator) maps each query to the correct shard based on the shard key value. The router is in the critical path for every database operation.
Operational Impact
Shard routing bugs: incorrect key extraction, off-by-one hash modulo: cause silent data placement errors. Rows land on the wrong shard, making them invisible to queries using the correct shard calculation.
Operations touching rows on multiple shards cannot use a single database transaction. Two-phase commit across shards is theoretically possible but practically unusable at high throughput: the blocking phase dramatically degrades throughput. Sagas with compensating transactions are the operational model.
Operational Impact
Business operations that previously updated multiple tables in a single transaction : debit one account, credit another: now require saga coordination with explicit compensation logic for partial failure. Every cross-entity operation must be designed for partial failure.
Migration Stages
Identify the shard key. Audit every query in the application to determine whether it includes the shard key in its WHERE clause. Queries that do not include the shard key must scatter-gather across all shards: enumerate and accept these as permanent scatter queries, or redesign them. Document every cross-shard join that must be eliminated. This step is research and design: no production changes.
Implement shard routing in the application: all database calls go through a routing layer that accepts a shard key and returns the correct database connection. Initially, all routing maps to shard 0 (the existing single database). This allows the routing logic to be deployed and validated in production before any data is moved.
Provision N shard instances (primary + replica each). Validate replication, connection pooling, monitoring, and failover procedures on each shard. All shards are empty at this stage. Production traffic still routes entirely to the single database (shard 0).
Application writes to both the original single database and the target shard simultaneously. This validates shard routing correctness: the sharded dataset should grow to match the single database. Run consistency checks. Monitor for routing errors (rows on wrong shard).
Migrate all existing rows from the single database to their correct shard via batch backfill jobs. Run checksums and row count validation per shard. This is the most time-consuming stage: large datasets require weeks of controlled backfill with rate limiting to avoid impacting production write performance.
Gradually shift read traffic from the single database to the sharded cluster: 10%, 25%, 50%, 100% over multiple weeks. Monitor query latency and error rates at each step. After reads are fully on the sharded cluster, cut writes. Decommission single database after 30+ days of stable sharded operation.
Migration Risks
Cross-shard joins: queries that previously fetched rows from multiple tables via JOIN : must be replaced entirely. These queries are not possible in the sharded model without scatter-gather. Analytics queries that span all shards will have O(N) cost growth.
Mitigation
Eliminate all cross-shard joins before cutover: replace with async denormalization, application-level assembly, or move to a separate analytics store (ClickHouse, Snowflake) for cross-entity queries. Accept that some query patterns are permanently incompatible with sharding.
A shard key with non-uniform distribution creates hotspot shards: one shard receives a disproportionate share of writes while others are idle. The hotspot shard becomes the new bottleneck, defeating the purpose of sharding. Resharding requires another full data migration.
Mitigation
Measure shard key value distribution before choosing. UUIDs and high-cardinality integers distribute evenly with consistent hashing. Avoid shard keys based on geographic region, account type, or any attribute with uneven business distribution.
Resharding: changing the number of shards or the shard key: after the system is live requires a complete data migration of the same magnitude as the original sharding migration. There is no online resharding without significant application downtime or a prolonged dual-cluster operation.
Mitigation
Choose shard count conservatively larger than current need: overshard initially (e.g., 16 shards when 4 would suffice today) to allow shard reassignment without full resharding as data grows. Use consistent hashing to minimize data movement when adding shards.
Coupling Changes
Each shard is an independent database: a shard failure affects only the subset of data on that shard
Consequence
Blast radius of a single database failure is reduced from 100% of data to 1/N of data
N independent databases multiply operational surface area: monitoring, patching, and incident response scale with shard count
Consequence
Platform investment required to manage N databases grows with shard count: this is not optional automation
Application code is now tightly coupled to the shard key model: every data access path must be shard-key-aware
Consequence
New features must be designed around the shard key constraint from the outset; retrofitting is expensive
Consistency Model Changes
- ·Single-shard operations retain full PostgreSQL ACID guarantees within the shard boundary
- ·Cross-shard operations have no ACID guarantees: they are eventually consistent via saga pattern
- ·There is no global sequence or global transaction ID: operations across shards cannot be totally ordered
- ·Distributed unique constraints are not enforceable at the database level: application must enforce global uniqueness via a coordination service or hash-based allocation
Rollback Risks
- ·Sharding is not reversible: once the single database is decommissioned, returning to a single database requires a full data migration from N shards into one
- ·Application shard routing code cannot be removed without a full re-architecture of the data access layer
- ·Cross-shard join replacements (async denormalization, analytics store) represent permanent architectural changes that cannot be rolled back independently