Database Selection: A Workload and Operations Decision Guide

Select a database from access patterns, transaction boundaries, consistency and operating constraints. Includes an illustrative decision matrix and validation worksheet.

Describe the work before naming the database

A database decision starts with the reads, writes and invariants the system must support. “Large scale,” “real time” and “flexible schema” need concrete definitions before they can distinguish candidates.

Collect representative queries, expected concurrency, data growth, skew and retention. Specify what must commit together, which reads may be stale and what the application does during a failure.

Start with the fewest stores that meet those requirements. Each additional store adds another data model, security boundary, backup, upgrade and recovery process. A specialized system can justify that cost, but a feature list alone does not establish the case.

For an existing database with a known performance problem, first use the database scaling guide. Replacing an engine and fixing an access path are different decisions.

Separate hard requirements from preferences

A familiar query language may be a preference. Keeping a business invariant within one supported transaction can be a hard requirement. Make that distinction before assigning weights to a scorecard.

| Requirement | Evidence to capture | |---|---| | Read shape | Keys, filters, joins, ordering, page size and result freshness for each important request. | | Write shape | Insert/update/delete behavior, bursts, contention and the records that must change atomically. | | Data lifecycle | Retention, corrections, deletion, archival and reconstruction requirements. | | Performance | An application-derived budget under a stated data shape and concurrency. | | Failure behavior | Acceptable unavailable operations, acknowledged-write loss and recovery time. | | Operating constraints | Skills, region, extensions, access controls, upgrade options and exit path. |

A candidate that fails a required control should not win because it scores well on convenience. Mark it as unsupported or requiring an explicit product change.

Distinguish an unknown from a failure. “We have not tested restore” needs a test, not an invented positive score. Keep assumptions visible in the decision record.

Compare capabilities without category shortcuts

A relational database can store time-series data. PostgreSQL has declarative partitioning, and the TimescaleDB documentation describes a PostgreSQL extension for time-oriented workloads. Evaluate plain tables, partitioning and extensions against ingestion, range queries, retention and operational constraints before ruling out the relational approach.

Likewise, document databases are not categorically unsuitable for transactions. MongoDB documents multi-document transactions for supported replica-set and sharded deployments. Check transaction scope, read/write concerns and restrictions for the intended version.

Key-based access does not automatically require a separate key-value database. A relational index may satisfy the requirement. A specialized store becomes a candidate when its access model and operating characteristics meet a demonstrated need.

Consistency also varies within one product. DynamoDB's read-consistency documentation distinguishes table and local-secondary-index reads from global-secondary-index reads. A feature supported on the base table may not hold for the lookup path an application actually uses.

Treat full-text search, document flexibility and analytical aggregation in the same way: define the missing behavior, test candidates and include synchronization cost if the chosen store is a derived copy.

Worked decision: a multi-tenant order service

Consider a hypothetical business application with these requirements:

• Creating an order and its line items must commit together.
• The service lists recent orders for a tenant and retrieves one order by ID.
• Support staff need occasional filtered reporting.
• The team already operates PostgreSQL and has no measured capacity failure.
• A future search feature is proposed, but its relevance and latency requirements are not yet agreed.

This example supports a bounded evaluation, not a general product ranking.

| Candidate | Decision for this scenario | |---|---| | PostgreSQL as the system of record | Evaluate first because the transaction and query shapes fit the existing operating model. Validate indexes, isolation and recovery rather than assuming familiarity proves capacity. | | MongoDB as the system of record | Keep as a viable alternative if the document model reduces demonstrated complexity. Test cross-document transactions and reporting; do not reject it on a false “no transactions” rule. | | Key-value-oriented design | Require a concrete key/index model for every critical request and verify the needed transaction and read semantics. Query changes may require new indexes or projections. | | Separate search store | Defer until relevance, filtering or performance requirements justify a derived index and its synchronization/rebuild obligations. |

The provisional decision is to test the existing relational design first. It changes if the evidence shows an unmet requirement or if a candidate reduces enough implementation and operating cost to justify migration.

An external payment remains outside the order database's local transaction. Choosing a database with transactions does not make an API call to another system atomic with that commit. Record the coordination and recovery design separately.

Run a decision-focused validation exercise

Build the smallest representative dataset and workload that can reject a bad choice. Include large tenants, uneven keys, corrections and retention operations. A tiny uniform dataset may conceal the behavior that matters.

For the order example:

1. Verify all-or-nothing order creation and concurrent updates under the chosen isolation and retry rules.
2. Check recent-order pagination for duplicates or omissions when timestamps tie.
3. Measure the agreed latency distribution while writes, reads and relevant background tasks run together.
4. Exercise a duplicate request, connection loss after commit and unavailable read target.
5. Restore a backup into an isolated environment and verify records, permissions and application behavior.
6. Rehearse a schema change while compatible old and new application versions coexist.

Record engine and extension versions, topology, settings, dataset shape, test method and results. Keep the exact environment with the findings; a managed product name alone is not enough to reproduce a benchmark.

Test operational work as well. If one candidate needs a specialist to recover it, identify that dependency and its availability before treating recovery as solved.

Decide who will operate it

Managed services can remove some infrastructure work while leaving the application team responsible for data modeling, access, query behavior and recovery requirements. The exact split depends on the service.

Use an operating worksheet rather than an unsupported rule that almost everyone should choose managed hosting:

| Operating question | Decision evidence | |---|---| | Required engine features | Supported extensions, versions and configuration controls in the selected service and region. | | Recovery | Backup retention, tested restore procedure and the failure scenarios covered by replicas versus backups. | | Change control | Upgrade schedule, rollback limits and responsibility for compatibility testing. | | Visibility | Available logs, query diagnostics, audit records and access restrictions. | | Capacity and cost | Representative workload estimate, expected growth and material cost drivers. | | Exit | Export format, transfer limits, downtime constraints and a verified migration path. |

Self-hosting is an operating commitment. Assign patching, upgrades, backups, alert response and restore testing to people with capacity to do the work. Existing servers do not make those responsibilities free.

Keep additional stores deliberate

When a search index or analytical store is derived from the system of record, specify how it receives updates, deletions and permission changes. Track lag and expose it where it affects the user.

A rebuild must not resurrect deleted or unauthorized records from an older snapshot. Align the backfill boundary with subsequent changes and test access behavior before switching consumers.

Choose which system is authoritative for each business decision. If both accept independent edits to the same fact, conflict resolution becomes part of the product. Do not let that happen accidentally through an integration.

Record the decision and its revisit condition

Keep the selected candidate, rejected alternatives, unresolved assumptions, validation results and operating owner in one decision record. Add the condition that would justify another review, such as a new cross-region write requirement or an access pattern the current model cannot meet within budget.

Bring that record and representative workload to a technology stack evaluation. The next step should resolve a named uncertainty, not repeat a generic database comparison.