Scaling databases: queries, replicas and sharding
Find what limits the database, then choose between query fixes, caching, read replicas and sharding.
Find the expensive query
An order list becomes slow as customers accumulate history. The obvious fixes are a bigger database, a cache or read replicas. But the page may be issuing one query per customer, reading unused columns or waiting behind a long transaction. Diagnose the work first; otherwise the same inefficiency follows the data into a more complicated setup.
Collect query frequency and duration, read volume, connection waits, locks and disk use. A 200 ms query repeated ten thousand times can consume more than an occasional five-second report. Break down the workload by journey and customer.
For PostgreSQL, EXPLAIN ANALYZE compares planner estimates with actual rows and timings. Large differences help expose inaccurate assumptions about data. ANALYZE executes the query, so inspecting modifying statements requires deliberate care and an appropriate environment.
Read the execution plan
An order page loads 50 rows, then fetches each customer separately: 51 database calls, a familiar N+1. Batched loading, an appropriate JOIN and a bounded result set can help more than a larger server. The improvement also keeps paying off as traffic grows.
Choose indexes for actual filters, ordering and data distribution. An index need not serve every query; scanning a large portion sequentially can be cheaper. Check read performance, write overhead, index size and maintenance after the change.
A transaction waiting for an external service holds resources during someone else's work. Revisit its boundary, then explicitly preserve workflow integrity outside it. Simply making a transaction shorter does not solve the new partial-failure cases.
PostgreSQL’s EXPLAIN documentation separates planner estimates from observed execution. Its index guidance also stresses the cost of maintaining indexes. Test the affected reads and writes together instead of treating a new index as free capacity.
How long cached data stays valid
Delivery reference data is easier to cache than the last available item. Define acceptable staleness, update behavior and misses for each object. If a displayed price is old, decide which price checkout will confirm.
Estimate database load without the cache. If it serves 90% of reads, losing it can increase database read traffic roughly tenfold at unchanged arrivals. This is a calculation for the example, not a universal cache property. Bound refresh concurrency, spread expiration and decide when an old response is acceptable.
For price data, display and order confirmation are separate decisions. A page may show a value within an agreed freshness window, while checkout verifies current terms. If the price changed, the customer sees the new total before committing. This makes caching part of the workflow rather than merely a TTL setting.
Read replicas and delayed updates
An asynchronous replica receives changes later. A customer saving an address and reading it immediately can see the previous value on a lagging replica. That looks like a lost write even when the primary saved it. Route this path to the primary or establish another explicit visibility condition.
Synchronous replication has distinct acknowledgement modes. Receiving a record and applying it are different guarantees. Stricter confirmation can add latency and dependence on replica availability. Choose it for required operations rather than treating consistency as one setting.
Read replicas offload reads; they do not split the write workload. We watch replication lag, replay capacity and failover behavior. A replica is not a backup either: an accidental deletion can reach every copy quickly, so recovery needs its own test.
The PostgreSQL standby documentation explains the replication trade-off: asynchronous copies can lag, while stronger synchronous acknowledgement can delay commits. For an address-change screen, specify where the immediate follow-up read goes before routing all reads to replicas.
Partitioning or sharding
Partitioning divides a logical table into pieces. Monthly event partitions can simplify removing old periods and reduce the data visited by a weekly query. Queries must permit pruning for that benefit. Dividing a table does not imply placing it on independent servers.
Sharding distributes data among databases. A company key can keep common operations local, but one large company may overload its shard. Another key may balance load while turning ordinary reports into cross-shard queries.
Before moving data, resolve identifiers, uniqueness, cross-shard transactions, queries without the key, rebalancing and recovery. If these operations dominate the product, distribution may cost more than the expected capacity gain.
Measure the change
Compare the representative workload before and after: latency, throughput, contention, cost and failure headroom. Changing queries, hardware and placement simultaneously without attribution makes the result hard to explain.
The migration needs data completeness checks, write cutover rules and a response to mismatches. Rollback must account for writes that arrived after switching; restoring the old schema may no longer be enough. We are finished when the team can operate and recover the new setup, not when the copy command succeeds.
- Capture the slow order-list path with realistic history sizes. Separate connection wait, query execution and application calls; do not blame the largest table by default.
- Remove N+1 calls and unnecessary rows or columns first. Compare the plan and timings, then check that any index leaves enough capacity for ordinary writes.
- If caching is still useful, state the freshness window and test a cold cache. Confirm checkout against current data and show customers any changed terms.
- Send lag-tolerant reports to replicas only after checking replay delay. Keep immediate reads after important writes on a path with the required visibility.
- For a migration, compare IDs, totals and business relationships, not only row counts. Record write cutover and how new writes would survive a rollback.
- Use partitioning for demonstrated pruning or history-retention needs. Consider sharding only after mapping hot customers, cross-shard operations, rebalancing and recovery ownership.
Keep reading
Scaling services: when more instances do not help
Why a service can be slow with idle CPU, how extra instances overload the database and what to check before autoscaling.
Metrics and alerts: what to collect and when to page
Which metrics a service needs, how to read latency and error budgets, and how to make an alert lead to action.