Database Scaling Patterns: A Measured Path from Queries to Shards

Choose database scaling changes from workload evidence, consistency requirements and recovery limits. Includes a query-plan decision record and a shard-skew example.

Identify the constrained operation

A slow application does not establish that the database needs more instances. The delay might be a connection-pool queue, a blocked transaction, a poor access path or an overloaded downstream dependency.

Start with a user operation and its latency or completion requirement. Trace time waiting for a connection separately from time executing a query. Group work by query shape, tenant and transaction type so a fleet average does not hide one expensive path.

This guide covers scaling an existing data system. If the workload needs a different data model or query capability, use the database selection guide to evaluate that decision.

Collect evidence before choosing a pattern

Observe representative load, including bursts, maintenance and uneven tenant sizes. Record the dataset, index definitions, engine version, settings and concurrency used in any test.

| Evidence | Question it helps answer | |---|---| | Query plan, actual rows and buffer activity | Is the engine doing unnecessary work, or is the result itself expensive to produce? | | Lock waits and transaction duration | Is contention limiting progress despite available CPU? | | Pool wait time and active connections | Is work queuing before execution, and would more concurrency make contention worse? | | CPU, storage latency and memory pressure | Which resource is constrained during the affected operation? | | Replication progress and reader errors | Can a read target satisfy its freshness and availability contract? | | Traffic and data by key or tenant | Would distributing work create balanced partitions? |

Use PostgreSQL's EXPLAIN documentation as an engine-specific example. Planner costs are estimates, not milliseconds. EXPLAIN ANALYZE executes the query, so use an appropriate test environment and account for side effects and load before running it.

A sequential scan is not automatically a defect. Reading a large fraction of a table may make it a reasonable plan. Compare estimated and actual row counts, selectivity and the work needed to satisfy the requested result.

Worked decision: a tenant's recent orders

Consider a hypothetical endpoint that returns the latest 50 orders for one tenant. It filters by tenant ID and sorts by creation time and order ID. The current index covers creation time but not tenant ID.

The investigation finds that the plan reads many unrelated tenants' rows before returning the requested page. The design candidate is a B-tree beginning with tenant ID, followed by creation time and order ID in an order that supports the query. The final field gives pagination a deterministic tie-breaker.

PostgreSQL's multicolumn-index guidance explains how leading equality constraints affect the scan. The planner still chooses a path from the actual data and available alternatives.

Use this decision record:

| Decision field | Evidence or action for this example | |---|---| | Requirement | Return the correct tenant's recent orders within the endpoint budget under expected concurrency. | | Hypothesis | The access path scans unrelated rows because the index does not match the tenant filter. | | Candidate | Test the matching composite index and a bounded page query. | | Comparison | Run equivalent requests against the same data shape, including the largest tenant and cold-cache conditions. | | Guardrails | Check insert/update cost, index size, build impact and other queries sharing the table. | | Acceptance | Correct results and the agreed latency distribution, without unacceptable write or operating cost. | | Revisit | If resource saturation persists after reducing unnecessary work, test additional capacity or an eligible read replica. |

This is an investigation worksheet, not a benchmark result. No speedup follows merely from adding an index. Index creation and removal also need an engine-specific rollout plan.

If locking is the real bottleneck, a larger machine may leave it unchanged. If a valid working set exceeds memory or storage throughput, additional capacity may be the most direct option. Choose from the measured constraint rather than forcing every system through the same scaling ladder.

State the consistency requirement separately

More memory does not change transaction isolation. A read replica does not automatically serve the latest committed value. Write-through caching does not create a transaction across a cache and its backing database.

Name the property the application needs:

Read-your-writes means a caller can observe its completed update.
A bounded-staleness requirement limits how old an acceptable result may be.
Transaction isolation controls which concurrent effects a transaction can observe.
Durability after acknowledgement depends on persistence, replication and the specified failure model.

PostgreSQL's isolation documentation distinguishes isolation levels and retry requirements. Keep those settings in the application contract instead of labeling an entire topology “strongly consistent.”

For example, an order-confirmation response may need an authoritative read while a historical dashboard can accept a known replication delay. One database can serve both through deliberately different read paths.

Use replicas and caches with failure rules

PostgreSQL streaming replication is asynchronous by default. Its standby documentation distinguishes receiving, durably writing and applying changes. Acknowledgement by a standby does not always mean a query there can already see the write.

For freshness-sensitive requests, read from the authoritative owner or use a supported version/progress check before selecting a replica. A fixed delay after writing is a guess unless the contract tolerates its failures. During failover, account for fencing the old writer, connection retries and the possibility of unreplicated acknowledged writes under the configured durability policy.

A cache needs equivalent precision. Define identity, authorization scope, expiry, invalidation and the behavior when it is unavailable. A missed invalidation or a delayed fill can expose an older value after an update. Version checks or another coordinated mechanism may be needed for the required contract.

| Pattern | Boundary to test | |---|---| | Read replica | Lag, read-after-write behavior, stale connections and failover capacity. | | Cache-aside | Stale fills, tenant-safe keys, invalidation loss and a surge of misses after restart. | | Write-through cache | Partial failure between stores, bypassing writers and the acknowledgement rule. | | Asynchronous write-behind | Loss of queued writes, ordering, recovery and the durability promised to callers. |

Judge cache value by avoided backend work and acceptable response behavior. A high hit rate on cheap requests can coexist with an overloaded database serving expensive misses. Test whether the database survives cache loss before depending on the cache for normal operation.

Test shard skew before distributing writes

Partitioning within one database and sharding across independent nodes solve different placement problems. A partitioned table can improve pruning or retention operations without adding write capacity on another machine.

For sharding, map the proposed key to actual traffic and data. High key cardinality does not guarantee balanced demand.

Suppose an illustrative workload has 12,000 writes per second and one tenant produces 40% of them. That tenant contributes 12,000 × 0.40 = 4,800 writes per second. Four shards would average 3,000 if perfectly balanced, but hashing tenant ID cannot split that tenant's 4,800 writes across shards.

Possible responses include isolating the tenant, subdividing its data under a different key, or changing a hot aggregate. Each changes routing or transaction boundaries. Adding shards alone does not resolve a single hot key.

Before adoption, test:

Largest-tenant placement and hot-key behavior.
Queries and transactions that cross shards.
Global uniqueness and identifier generation.
Routing-map availability and stale routing.
Resharding with concurrent writes, catch-up and writer fencing.
Recovery when only some shards or their message consumers are restored.

Changing a hash-modulo shard count can remap many keys. Use the selected system's migration mechanism and validate ownership transitions; do not treat resharding as an arithmetic configuration change.

Avoid capability and geography shortcuts

Database category labels hide material differences. MongoDB supports multi-document transactions on supported replica-set and sharded deployments. Redis transactions also exist, with semantics that differ from relational rollback. Compare the documented engine, topology and configuration rather than a “transactions: yes/no” category table.

A multi-region design must specify write ownership, quorum or acknowledgement rules, conflict behavior and what happens during a network partition. Nearby replicas may improve eligible reads while writes still wait for a distant owner or quorum. Multiple writers require a supported coordination or conflict-resolution model.

Instance maxima, licenses and managed-service features change. Check the intended edition and region during capacity planning. None of those product limits replaces a production-shaped benchmark or recovery exercise.

Put the scaling decision into a release plan

Record the operation being improved, baseline, candidate, consistency contract, rollout boundary and rollback conditions. Test restored capacity as well as peak capacity: a design that meets its target only while every replica is healthy may not meet the availability requirement.

Bring a representative query plan, traffic distribution and failure scenario to a scalability and performance assessment. Decide whether to reduce work, add resources or distribute ownership from that evidence.