Database Scaling Strategies

Diagnose database limits, compare scaling options, budget connections, and roll out changes with workload evidence, consistency checks, and recovery gates.

trigger="Database latency, saturation, cost, or an approaching capacity limit threatens an agreed service objective." owner="The database or backend lead accountable for the workload's data correctness and performance." participants={["Application owner", "Platform engineer", "Database operator", "Product owner", "Security or data-governance representative"]} prerequisites={[ "Representative query, transaction, connection, and resource measurements, including peak periods.", "A documented consistency contract, supported database version, and recovery requirements.", "A repeatable workload test with representative data distribution and a verified recovery path." ]} outputs={[ "A bottleneck hypothesis supported by a baseline and query evidence.", "An option decision record covering performance, correctness, operations, and cost.", "A staged rollout plan with stop conditions, rollback limits, and acceptance results." ]} doneWhen={[ "The chosen change meets the agreed workload objective under representative load.", "Connection, correctness, failover, and cold-start tests pass their explicit gates.", "The operator can reverse the change or execute the documented forward-recovery procedure.", "Ownership, capacity triggers, and remaining constraints are recorded." ]} />

Start with the constraint, not a scaling ladder

There is no universal sequence that says to buy a larger database first, optimize later, and eventually shard. A missing access path, excessive connection churn, a hot row, and sustained storage pressure need different interventions. Increasing instance size can create useful headroom, but it may leave the underlying workload defect untouched.

This playbook helps a team choose and verify the smallest change that addresses its measured constraint. Examples use relational database concepts and refer to PostgreSQL where behavior is engine-specific. Managed services, other engines, and deployed versions may impose different limits.

The result is a decision supported by test evidence. It is not a promise that one architecture supports a particular user count. Concurrent work, transaction shape, data distribution, and recovery obligations matter more than registered users.

1. Define the workload and collect a baseline

The application owner lists the operations that matter: interactive reads, writes, reports, background jobs, imports, and maintenance. Capture their arrival rate, concurrency, transaction duration, result size, and data growth. Include tenant skew and batch schedules, not just an average over a quiet day.

Choose acceptance measures before testing a change. Examples include latency percentiles for a named transaction, completion time for a reporting job, tolerated replica freshness, and a cost boundary for the workload. The product owner approves these targets. Avoid generic CPU or cache-hit thresholds presented as universal rules.

| Signal | Question to investigate | Evidence to collect | | --- | --- | --- | | Query latency | Is execution slow, or is time spent waiting for a connection or lock? | Traces, pool wait, query timings, and lock waits | | CPU and reads | Which operations consume resources? | Query fingerprints, plans, buffer and storage metrics | | Write latency | Are commits, contention, or storage the limit? | Transaction duration, lock graph, log and storage telemetry | | Connections | Is demand useful work or idle/churning sessions? | Active, idle, waiting, and rejected sessions by client | | Growth | Which tables, indexes, and tenants grow fastest? | Size trends, retention, and access patterns | | Replica behavior | Can replay keep up under load? | Apply lag, conflicts, retained logs, and read latency |

Retain the configuration, software versions, test dataset characteristics, and observation window with the baseline. Sanitize query examples and traces before sharing them. Production values can contain customer data even when the query text looks operational.

2. Test a bottleneck hypothesis

The database engineer writes one falsifiable explanation, such as: “The list endpoint scans a growing table because its filter and sort cannot use the existing index.” Predict what should change if the hypothesis is right, then compare the same workload before and after the intervention.

An execution plan is evidence, not a verdict. A scan may be appropriate when an operation needs a large proportion of a table. An index can reduce reads while increasing write work, storage, and maintenance. Measure the tradeoff across affected operations, not only the query used to justify it.

PostgreSQL's EXPLAIN documentation distinguishes estimated plans from measured execution. EXPLAIN ANALYZE executes the statement and introduces measurement overhead. Run side-effecting statements only in an approved safe environment; wrapping a statement in a transaction is not a universal safeguard against external effects triggered by functions.

Change one major variable at a time where practical. Preserve the comparison data and explain unavoidable differences, such as a changed dataset or background workload. If a test uses a warm cache but production regularly starts cold, add the cold-path test before accepting the result.

3. Compare options against the actual bottleneck

| Option | Useful when evidence shows | Added responsibility | | --- | --- | --- | | Query, index, or transaction changes | Avoidable work, long lock holding, or inefficient access | Write overhead, plan changes, and safe schema rollout | | Connection management | Pool waits, session churn, or excess concurrency | Shared budgets and compatibility testing | | Larger instance or storage change | A relevant resource is saturated | Cutover behavior, cost, and remaining single-writer limits | | Read replicas | Eligible reads compete with primary work | Freshness-aware routing and replica operations | | Cache | Repeated reads can reuse an acceptable representation | Invalidation, isolation, eviction, and cold-path capacity | | Table partitioning | Pruning or lifecycle operations match the partition key | Query compatibility, maintenance, and partition management | | Sharding | A single database remains a measured write, size, or isolation constraint | Routing, cross-shard operations, rebalancing, and recovery | | Separate analytical store | Reporting harms the transactional workload | Data movement, reconciliation, and freshness disclosure |

Partitioning within one database is not automatically sharding across independent databases. Likewise, a read replica does not generally remove the primary's write bottleneck. Reject an option when it does not address the observed constraint, even if it is widely used.

Document the operational cost as well as infrastructure spend. A new store needs access control, backups, monitoring, schema ownership, on-call procedures, and data-lifecycle rules. Do not introduce another database solely to avoid understanding the existing query path.

4. Budget connections across the deployment

Set a server-side connection budget with the database owner. Account for platform-reserved connections, administration, replication where applicable, migration jobs, background workers, autoscaling, and old/new instances overlapping during deployment. Verify which clients reach the database directly and which share a pooler.

For example, suppose an operator has established a usable application-and-operations ceiling of 160 connections for a tested configuration. Reserving 32 for background clients and 28 for operations and deployment headroom leaves 100 for the web tier. If that tier can reach ten simultaneously connected instances, a direct pool cap of ten per instance fits this allocation. These are illustrative numbers, not sizing recommendations. All client paths still need inventory and load testing.

"type": "svg-architecture", "title": "A deployment-wide connection budget", "nodes": [ ], "links": [ ], "caption": "Client sessions and database connections are different budgets when a pooler is present. Inventory any clients that bypass it." }} />

More accepted connections can increase contention rather than throughput. Also bound pool acquisition time, request deadlines, and queued work. Monitor connection utilization and wait alongside successful throughput to identify whether queuing is protecting the database or hiding an overloaded service.

For PostgreSQL, review connection settings and reserved slots. If using PgBouncer, test the chosen pooling mode against application behavior. PgBouncer's feature matrix documents compatibility differences, including session-dependent features. Confirm behavior against the actual pooler and driver versions.

5. Treat replicas and caches as consistency decisions

The application owner classifies read operations before routing them away from the writer. A search result may tolerate an approved delay; a permission check or a confirmation after a write may not. PostgreSQL streaming replication is asynchronous by default, and its durability and visibility behavior depends on configuration. Use the engine's replication documentation rather than assuming every replica sees the latest commit.

Caching has a separate consistency problem. Updating a database and cache in application code is not automatically one atomic operation. Partial failures and concurrent writers can leave different values. Label the chosen strategy, define its tolerated staleness, and test invalidation and recovery. A write-through label alone does not prove strong consistency.

Before combining both techniques, ask where cache fills originate. A lagging replica can populate a newly created cache entry with old data. Entry age alone then understates the age of the underlying state. See Read Replicas vs Caching for routing contracts and failure tests.

Load-test cache bypass and replica withdrawal with admission control enabled. A fast normal path is not a safe design if losing it overwhelms the writer.

6. Gate partitioning and sharding carefully

For partitioning, verify that common predicates can benefit from the chosen key and that retention or maintenance operations become simpler. Test planning overhead, constraints, index behavior, and operational procedures on the deployed engine. Partitioning is not a substitute for an access pattern that matches the workload.

Before sharding, the technical owner must answer:

  • What is the routing key, and how does the design handle large or unusually active tenants?
  • Which uniqueness checks, joins, and transactions cross shard boundaries?
  • How are migrations, backup schedules, point-in-time recovery, and schema versions coordinated?
  • How does a tenant move between shards without losing or duplicating writes?
  • What protects against stale routing during a move?
  • Who owns reconciliation if a cutover stops halfway?

Do not approve sharding until a representative migration and recovery exercise has usable evidence. Record which operations become eventually consistent or require application coordination. If the product cannot accept those changes, revisit the design instead of hiding them in implementation details.

7. Roll out with an explicit recovery path

Use an expand-and-contract approach for incompatible schema or data-path changes: introduce compatible structures, backfill with checkpoints, compare results, switch bounded traffic, and retire the old path only after the rollback window closes. Define the exact compatibility period with application and database owners.

| Failure during rollout | Immediate action | Recovery evidence | | --- | --- | --- | | Latency or lock regression | Stop expansion; reverse the traffic or compatible code change | Baseline behavior returns without unresolved transactions | | Pool exhaustion | Cap callers and restore the last safe allocation | Administrative access and critical operations remain available | | Stale or mismatched reads | Remove the affected read path | Correctness probes and sampled comparisons pass | | Backfill interruption | Pause at a durable checkpoint | Restart does not duplicate or omit records | | Dual-write divergence | Stop the cutover and identify the authoritative record | Reconciliation accounts for affected keys | | Shard move interrupted | Follow the recorded routing and write-fencing state | One authoritative write path and verified data ownership |

A traffic rollback does not undo committed writes. Dropping a new column or restoring an old snapshot may lose data produced after cutover. When reversal is no longer safe, use the documented forward-recovery and reconciliation procedure. Test backup restoration independently; a backup job's success does not prove application recovery.

8. Rehearse one query change under a competing workload

The database engineer owns this exercise; the application owner supplies the transaction contract. Consider a hypothetical order-history endpoint that filters by tenant and sorts by creation time. Its tail latency rises during an export job. The team suspects an inefficient access path, but connection waiting and storage contention are competing explanations. No result in this example represents a customer measurement.

Build a permitted test dataset with the relevant tenant-size distribution, historical depth and result sizes. A uniform dataset can hide the largest tenant's scan cost. Preserve the production query shape, including parameters and pagination, while removing identifying values. Capture the endpoint's pool wait separately from server execution time so one improvement cannot conceal a different bottleneck.

First run the endpoint alone, then with the export job and normal writes. The operator records completed operations, errors, lock waits, storage activity and resource limits for each run. Use the same load-generator capacity and arrival model for the comparison. If the generator slows when the service slows, it may reduce offered load and make overload look less severe. Report offered work, completed work and rejected work together.

The engineer then tests the proposed access-path change against the same cases. A candidate index must support the actual predicates and ordering; including more columns can increase its size and write overhead. Compare order creation and update behavior as well as the list endpoint. If the export still dominates storage work, the experiment may justify scheduling or isolating that workload instead of adding another index.

| Rehearsal case | Required decision evidence | | --- | --- | | Small and large tenants | Query plans, returned records and timing explain whether the change helps the relevant distribution | | Concurrent export and writes | Interactive gains do not hide unacceptable write latency, job delay or lock contention | | Cold start or cache bypass | The database can serve the approved degraded workload without an uncontrolled retry surge | | Deployment overlap | Old and new application instances remain within the aggregate connection allocation | | Interrupted index operation | The operator can identify incomplete state and follow the approved repair procedure |

For PostgreSQL, consult CREATE INDEX before planning the production operation. A concurrent build has different locking behavior and additional restrictions; a failed build can leave an invalid index that still requires attention. “Concurrent” does not mean free of resource cost or safe to run without a change plan. Verify index validity after completion and schedule any cleanup through the database owner.

Gate: retain the change only when the targeted operation improves under the agreed conditions and the other protected operations remain acceptable. If the candidate merely moves waiting from the database into the connection pool, record that outcome and revise the hypothesis.

9. Prove failover, restore and reconciliation separately

The operations lead owns recovery testing, with the data owner defining what counts as a complete business record. High availability, backup restoration and data reconciliation answer different questions. A standby taking over successfully does not demonstrate that an accidentally deleted record can be recovered, and a restored database starting does not demonstrate that external effects match its contents.

Choose an isolated, authorized rehearsal environment and record its differences from production. Do not point recovered workers at production queues or payment endpoints. Restore the required database state and confirm that the application can read it using the intended roles, extensions and configuration. Exercise a known set of synthetic transactions before and after the recovery point, then account for their expected presence or absence.

PostgreSQL's continuous archiving and point-in-time recovery documentation describes the base-backup and archived-log requirements. The recovery record must identify the backup, available log range and selected recovery target. A missing archive segment is a recovery gap even when the latest backup job reported success.

For a promotion or endpoint change, test connection retirement and reconnection, stale DNS or routing information, and the prevention of competing writers. An application timeout can leave the outcome of a committed transaction unknown. Use the operation identifier and authoritative state to resolve it before retrying an action with external effects. Record which operations are duplicate-safe and which require an operator's decision.

Do not use row counts as the sole reconciliation test. Check primary identifiers, critical relationships, business totals and representative field values. For partitioned or sharded data, verify that every key belongs to the intended owner after the exercise. Retained logs and comparison results need the same access and retention controls as other operational evidence.

The evidence package records elapsed recovery time, the interval of data potentially lost, failed checks, required manual steps and the person accepting the remaining limits. If an exercise misses the required recovery objective, reduce the release scope or fix the recovery path before adding a new topology dependency. End the rehearsal by proving that temporary resources and credentials are retired without deleting required evidence.

10. Use a scaling decision record

Workload and business objective:
Database / version / deployment configuration:
Baseline window and representative data characteristics:
Measured bottleneck and competing explanations:
Chosen option / alternatives rejected:
Consistency and security impact:
Connection and concurrency budget:
Experiment configuration and comparison evidence:
Rollout stages / owner / stop condition:
Rollback limit and forward-recovery procedure:
Reconciliation checks and results:
Cost and operational responsibilities:
Capacity trigger for the next review:

Keep the record with the runbook and include its configuration version in change requests. After the change, observe a representative production cycle, including scheduled jobs and maintenance. Record any difference between the test and production behavior instead of presenting a laboratory result as a production guarantee.

Completion checklist

"The workload objective and baseline are explicit, and the bottleneck hypothesis has supporting evidence.", "The chosen option improves the targeted constraint without hiding a correctness or write-cost regression.", "Connection budgets include maximum replicas, background work, direct clients, and deployment overlap.", "Replicas and caches have per-operation consistency and fallback rules.", "Schema changes, backfills, and routing changes have tested stop and recovery procedures.", "Data reconciliation and application-level restore checks pass.", "The owner has recorded remaining limits and the conditions that trigger another capacity review." ]} />

For a workload-specific assessment, Ampity's scalability and performance analysis is the related service. The useful engagement output is a measured constraint, an accepted change, and evidence that the resulting system remains correct.

Primary references

Use the documentation for the deployed versions when turning the decision record into commands. The examples here do not establish a universal server size, connection formula, or scaling threshold.