Database Choice: The Rapid Growth Constraint Matrix
Choose a datastore using business invariants, access patterns, recovery evidence, workload tests, and a migration decision with an explicit point of no return.
trigger="A new workload or a measured constraint requires a datastore decision." owner="The engineering owner accountable for the application's data correctness and recovery." participants={["Database engineer", "Application owner", "Platform operator", "Security and data owners", "Finance partner"]} prerequisites={[ "Business invariants, representative queries, data distribution, and growth scenarios.", "Consistency, retention, geographic, security, and recovery requirements with owners.", "A safe benchmark environment and an inventory of the current system's constraints." ]} outputs={[ "A constraint matrix and decision record with rejected options and remaining uncertainty.", "A reproducible benchmark and failure-recovery report for the shortlisted design.", "A migration, reconciliation, and cutover plan where an existing store is being replaced." ]} doneWhen={[ "The candidate satisfies the hard correctness and recovery constraints in tests.", "Performance and cost evidence includes representative skew, peaks, and failure conditions.", "The operator can restore usable data with the required identities and keys.", "Any cutover has a tested return path for new writes or an explicitly approved forward-only gate." ]} />
Decide what must remain true
Start with the data's business meaning. An account balance, a search result, and a cached product description have different consistency and recovery needs. A technology that serves one well is not automatically the best home for all three.
This playbook supports one operational datastore decision. It is not a vendor ranking or a reason to replace a functioning system because user counts have grown. User count alone does not describe transactions, query complexity, item size, active working set, or key skew.
The accountable owner writes the invariant first: which changes must commit together, which reads must reflect a prior write, which errors can be retried, and what data loss is acceptable after a failure. Keep those requirements separate from preferences such as an existing team's familiarity with a language.
1. Build the constraint matrix
The application owner lists actual access patterns, including administrative, support, reporting, retention, and recovery operations. Record the read/write mix, transaction boundaries, required ordering, result size, expected concurrency, and the most active tenants or keys.
| Constraint | Evidence to collect | Candidate must demonstrate | | --- | --- | --- | | Atomic business change | Invariants and concurrent-operation examples | Correct transaction or conditional-write behavior | | Read consistency | Read-after-write and stale-read requirements | Documented behavior under the selected configuration | | Query shape | Filters, joins, searches, aggregates, pagination | Predictable plans for important paths | | Distribution | Hot keys, tenant skew, item sizes, growth scenarios | Capacity without unacceptable hotspot behavior | | Recovery | Required recovery time and acceptable data loss | Restore and failover exercise | | Data governance | Access, residency, deletion, retention, key ownership | Enforceable and testable controls | | Operating cost | Infrastructure, support, labor, and migration effort | Reproducible model with assumptions |
Mark hard constraints as pass, fail, or unresolved. Do not average a failed correctness requirement into an otherwise attractive score. Soft preferences can be weighted, but the team must agree the weights before seeing the result.
Gate: no candidate advances with an unowned requirement or an unresolved hard constraint disguised as a likely future feature.
2. Test whether the current store is the problem
Before a migration, the database engineer identifies the limiting resource and workload. Check expensive query shapes, lock waits, connection pressure, storage latency, memory, contention, data skew, and application retries. A new database may reproduce an inefficient access pattern with new operational risks.
Evaluate a query rewrite, appropriately designed index, resource adjustment, or bounded read offload against the same acceptance criteria. Caching adds invalidation and stale-read behavior; a replica introduces consistency and failover considerations. Neither is a universal first step.
When reviewing PostgreSQL indexes, zero observed scans are an investigation signal, not deletion authority. Inspect the index's constraints, uniqueness role, definition, statistics coverage, resets, replica usage, and infrequent maintenance or reporting paths. Confirm the observation period includes relevant business cycles.
The PostgreSQL statistics documentation describes cumulative counters and collection behavior. The operator must understand which server and period a counter represents. Keep the recreation definition and assess rebuild time, locking, storage headroom, and write impact before an approved removal.
Likewise, a sequential scan is not automatically a missing-index defect. Large result sets or small tables can make one appropriate. Inspect the query plan and measured workload. Execution-based plan analysis can run the query, so review side effects and environment safety before using it.
3. Shortlist by capability, then verify the product configuration
A relational engine can suit multi-record invariants and varied SQL access. A document engine can suit bounded document aggregates. A managed key-value service can suit explicit key-based access patterns. A cache or specialized index can serve a derived view when its source and rebuild behavior are defined.
These categories overlap. MongoDB documents multi-document transactions; describing its transactions as simply absent or unusable would be misleading. Evaluate the selected version, topology, transaction scope, and workload rather than deciding from a broad category label.
For DynamoDB, review the exact access and consistency requirements, indexes, partition-key distribution, capacity behavior, and quotas. Do not assume on-demand mode removes all capacity planning or that one capacity mode is always cheaper.
The DynamoDB SLA specifies availability commitments and service-credit conditions. It is not an end-to-end p99 latency guarantee at arbitrary scale. Measure latency from the application, including network, retries, serialization, and the actual consistency mode.
For Redis or any memory-oriented store, establish whether it is disposable derived data or an authoritative record. Verify persistence, replication, eviction, failover, and restart behavior for the actual deployment. A fast steady-state response does not prove the required durability.
4. Keep the authority boundary visible
"type": "svg-architecture", "title": "Separate authoritative state from derived views", "nodes": [ ], "links": [ ], "caption": "Adding a specialized store creates a synchronization responsibility. Define which system is authoritative and how stale, missing, or duplicated updates are detected." }} />
A second database is justified only when its benefit exceeds the additional synchronization, access-control, backup, deletion, and on-call work. For every copied field, define the owner, freshness expectation, conflict behavior, and rebuild process.
A change-data-capture pipeline is not automatically free of application or schema implications. Test schema evolution, connector restart, source-log retention, deletion propagation, and recovery after the checkpoint can no longer be resumed.
5. Run a reproducible workload and failure test
The database engineer creates a test manifest with dataset shape, scale, skew, query mix, client concurrency, versions, topology, storage settings, and warm-up method. Synthetic or de-identified data can be appropriate if it preserves important distribution and access properties. Do not copy sensitive production data merely to make a benchmark realistic.
Measure application-visible latency distributions, throughput, errors, retries, saturation, and cost at expected and stressed demand. Choose stress scenarios from capacity and recovery requirements, not a mandatory traffic multiplier.
Test concurrent conflicting writes, failed clients, dependency timeouts, node or zone loss where relevant, and restored backups. Compare results with the written invariant. A fast response carrying stale or inconsistent business state is not a successful benchmark.
Repeat significant measurements and record limits. Benchmarking one read endpoint does not certify the whole application or prove future capacity indefinitely.
6. Price the operating model, not just storage
Finance reviews compute or request charges, indexes, backups, replicas, data transfer, support, licensing, and minimum commitments. Include engineering effort for schema changes, upgrades, monitoring, incidents, migration, and decommissioning.
Use current dated rates for the intended region and service configuration. Model uncertain growth and key skew as scenarios. Keep an unverified forecast labeled as such, and distinguish migration overlap cost from the eventual operating state.
The platform owner identifies the skills and coverage needed to operate the candidate. A managed service transfers some tasks to the provider, but application data modeling, access policies, recovery testing, and cost control remain owned work.
7. Design migration as a state transition
Do not start with uncoordinated application dual writes. If one write succeeds and the other fails, the stores diverge. Choose a controlled change stream, transactional publication mechanism, or an explicitly designed write coordinator, with reconciliation.
"type": "flow", "title": "Database cutover gates", "steps": [ ], "caption": "An old database that no longer receives writes is stale, not a ready rollback target. Returning authority requires a tested synchronization or reconciliation procedure." }} />
The migration owner defines a consistent backfill and checkpoint procedure so that concurrent updates and deletions are neither missed nor overwritten. Compare business aggregates and sampled records, but also verify invariants that simple row counts cannot detect.
Before moving writes, fence old writers and drain or account for in-flight work. Record the authoritative position and confirm readers, jobs, integrations, and support tools use the intended store.
To return after new writes have begun, use tested reverse replication or a controlled write pause and reconciliation process. Resolve conflicts and schema compatibility before restoring old write authority. If the team cannot provide that path, mark the cutover as forward-only and obtain explicit approval before crossing it.
8. Preserve evidence and define decommissioning
Decision, accountable owner, and workload:
Hard constraints and accepted tradeoffs:
Candidate versions, topology, and primary references:
Benchmark manifest, results, and known limits:
Cost basis, scenarios, and operating responsibilities:
Migration checkpoints and writer-fencing procedure:
Return path for post-cutover writes:
Forward-only gate and required approval:
Stop conditions and reconciliation owner:
Backup restore evidence and retention decision:
Decommission prerequisites and final sign-off:The data owner approves decommissioning only when reconciliation, retention, rollback-window, and recovery requirements are satisfied. A fixed number of quiet days is not enough. Preserve necessary metadata and approved backups without retaining sensitive data indefinitely by default.
9. Design authorization, deletion, and recovery for every copy
A new database often creates a second representation before it becomes authoritative. That copy needs its own access boundary, tenant separation, encryption and key recovery, audit evidence, retention behavior and incident response. Do not grant migration workers unrestricted production access merely because they are temporary.
Derive organization and user scope from trusted identity. Verify authorization at the service or data boundary responsible for the resource. If the new store cannot enforce the full policy, document the application control and a defense-in-depth option. Test cross-tenant identifiers, revoked roles, administrative paths, exports and background jobs. A successful filtered query does not prove that every entry point enforces the filter.
Propagate deletion and retention decisions deliberately. A tombstone, event or reconciliation record may be needed so a late backfill or replay cannot resurrect removed data. Record how backups and restored environments reapply approved deletions. A migration snapshot copied into a test account remains governed data and needs an owner, access limit and destruction decision.
Recovery assets must include more than database bytes. Preserve compatible schema and application versions, infrastructure definitions, keys or their approved recovery mechanism, network access, identities and required external configuration. For an in-memory or cache-oriented system, examine persistence and restart behavior using the selected product documentation. Redis, for example, documents distinct persistence options and tradeoffs; the chosen setting must match the role of the data rather than a generic expectation that Redis is either always durable or always disposable.
Run restore tests in an isolated, authorized environment. Verify business records, constraints, relationships, access policy, deletion state and application behavior. Measure from the declared recovery event until the service owner accepts useful data. A provider reporting a completed restore does not establish application recovery.
If a security or deletion test fails, stop traffic expansion and preserve the evidence. Restrict the affected copy, repair policy or data, and rerun the relevant cases. Do not conceal the gap by reducing the benchmark dataset or excluding difficult tenants.
10. Conduct a candidate decision rehearsal
Prepare one workload package and give it to the teams advocating the current store, the leading candidate and a smaller intervention. The package includes invariants, query and write traces, data shape, tenant skew, growth scenarios, security requirements, recovery objectives, provider constraints and the accepted cost basis. All options use the same package.
Each team returns:
- the proposed data model and authoritative boundary;
- how each important access pattern executes;
- consistency and transaction behavior for the written invariants;
- capacity and hotspot assumptions;
- authorization, retention and deletion controls;
- backup, restore and regional failure behavior;
- migration and coexistence sequence;
- operating ownership and support needs; and
- expected and downside cost with dated inputs.
Run the same benchmark and failure cases. Where a feature is unavailable in the test edition or configuration, record it as unresolved rather than substituting a marketing claim. Where the current store fails, determine whether the cause is architectural, configurational, or an inefficient application pattern that every candidate would inherit.
Use hard gates before weighted preferences. A candidate that violates a required invariant, approved location or recovery objective does not win through lower cost or higher average throughput. For the remaining candidates, compare tradeoffs and uncertainty. Preserve the dissenting view when reviewers interpret evidence differently.
End with one of four decisions: retain and repair the current store, run a narrower experiment, approve a bounded migration, or stop the initiative because no candidate justifies the transition. A decision to retain is useful when it closes a costly technology debate with evidence.
11. Establish day-two operating acceptance
Before write cutover, the receiving team demonstrates routine and failure operations. They should identify the deployed topology and versions, inspect saturation and replication, rotate a credential, respond to a slow or unavailable dependency, locate backup evidence, restore a representative dataset, and explain the authority and reconciliation procedure.
Define signals that lead to decisions. Query latency needs workload and plan context. Replication lag needs a freshness and failover consequence. Connection saturation needs admission and administrative-reserve behavior. Storage growth needs retention, index and capacity context. Dashboards without owners and response thresholds do not create an operating model.
Document maintenance: upgrades, schema changes, index creation, compaction or vacuum behavior, backup verification, capacity expansion and provider lifecycle. Test at least one version transition before assuming a managed service makes upgrades transparent. Record mixed-version and client compatibility where applicable.
Rehearse an unknown write outcome. The client times out after the database may have committed. The operator must query or reconcile the operation using stable identity before retrying. Include an external event if the workflow publishes one. A database transaction does not automatically cover a message broker or payment provider.
Set an operating-cost review after representative production use. Compare the expected request, storage, transfer, replica, backup and support model with observed billing and engineering effort. Include migration cleanup and parallel-running cost. Do not declare the candidate economical from the benchmark cluster alone.
The receiving owner accepts the service only when the evidence, runbook, access and support capacity are usable without the migration authors. Any residual exception has a scope, accountable owner, compensating control and review or expiry condition.
Schedule the next architecture review against a material trigger such as new transaction semantics, a changed data-location obligation, sustained hotspot behavior, provider deprecation, failed recovery evidence, or a cost model outside its accepted range. Preserve the original matrix and results so the next decision can distinguish a changed workload from an unclosed migration gap.
"The matrix names business invariants and treats hard failures as disqualifying.", "The existing system's bottleneck has evidence, not a user-count assumption.", "Vendor capabilities and SLA language are checked for the selected configuration.", "Index changes account for constraints, statistics scope, infrequent work, and recreation.", "Benchmarks cover skew, concurrency, failure, restore, and application-visible behavior.", "Every derived store has authority, freshness, and reconciliation rules.", "Cutover fences writers and can account for every accepted write.", "A stale old database is never presented as an immediately usable rollback target." ]} />
This decision pack supports a domain review. It is not production approval, and the selected database still requires workload-specific security, operational, and recovery acceptance.