Choosing the Right Database for 2018

Key Takeaways

• Choose a database by its access patterns, transaction boundaries, and operational requirements—not whether it carries a SQL or NoSQL label.
• Compare specific systems and deployment configurations. Database families don't provide universal performance or consistency guarantees.
• Plan for realistic growth and schema evolution, but include the cost of migrations and premature complexity.
• Validate your shortlist with representative queries, concurrent traffic, recovery exercises, and a total-cost estimate.

Choosing the right database affects your application's performance, scalability, and maintainability. Choose the system whose data model, query capabilities, correctness guarantees, and operating costs fit your actual workload. SQL versus NoSQL is a useful starting vocabulary, not a decision rule.

How to Choose a Database: Start With Requirements

Before comparing products, write a short requirements document. Separate requirements you must satisfy from preferences you can negotiate.

What will you do with the data?

Describe operations, not just entities. “We store customers and orders” tells you less than “checkout must reserve stock and create an order together.”

• Traffic: What is the read/write mix, peak request rate, expected concurrency, and distribution of traffic across customers or keys?
• Query shapes: Do you need key lookups, filtered lists, joins, aggregations, relationship traversals, time windows, or similarity searches?
• Correctness: Which changes must succeed together? Which rules must never be violated, even under concurrent requests?
• Latency: Set targets for individual operations, including p95 and p99 latency—the response-time thresholds covering 95% and 99% of requests.
• Freshness and availability: Must a user immediately see a completed write? What should happen when a replica, network link, or region fails?

For each important query, record filters, ordering, result size, frequency, and required freshness. These become your evaluation cases.

How much will this data change over time?

Distinguish changing values, changing structure, and growing volume. Estimate record sizes, retention, working-set size—the data accessed frequently—and likely growth over a planning horizon your business can justify.

Also ask how you'll introduce fields, backfill historical records, remove obsolete data, and support old and new application versions during deployment. Don't buy a complex distributed architecture solely because the application might eventually become large.

Database Types and Use Cases

These families describe modeling and access patterns, not mutually exclusive product boundaries. The Microsoft data-model guide and AWS database-selection guide provide useful starting points. Treat the following as shortlist guidance, not a ranking.

Online transaction processing (OLTP) handles application operations such as checkout. Online analytical processing (OLAP) answers broader questions such as revenue by region over several years. BigQuery uses columnar storage optimized for analytical queries; that doesn't make it interchangeable with an operational checkout database. See our OLTP versus OLAP comparison for the workload distinction.

Likewise, MongoDB and Cassandra aren't interchangeable “unstructured data” stores. Cassandra uses query-driven modeling and does not support joins. A device-event workload queried by device and time could justify evaluating it; arbitrary cross-customer reporting needs a separate assessment. Our HBase versus Cassandra comparison explores two wide-column systems.

SQL vs NoSQL: Compare Capabilities, Not Slogans

For an application with interconnected business records, evolving queries, and cross-record rules, I'd start by evaluating a relational database such as PostgreSQL, MySQL, or SQL Server. That's a practical default for this workload, not a claim that relational systems always win.

Semi-structured data doesn't automatically require a document database. For example, PostgreSQL supports JSON storage and indexing with jsonb, so relational records and flexible attributes can coexist. Compare the resulting queries and indexes against a document design.

Flexible Documents Still Need a Schema Strategy

MongoDB allows documents in a collection to have different fields and types, and supports database-level schema validation. That flexibility doesn't remove application contracts or migration work.

A product catalog with varying attributes is a reasonable document-model candidate. Decide what belongs together, validate shared fields, and test searches across product types. Use backward-compatible changes: introduce the new representation, support both forms during rollout, backfill, and remove the old form after readers have migrated. Our MongoDB schema-design questions help frame those modeling decisions.

Transactions Come From the Database, Not the Mapper

MongoDB supports multi-document transactions on replica sets and sharded clusters. Its documentation also warns that distributed transactions generally cost more than single-document writes; transaction support isn't a replacement for effective modeling.

Mongoose is a Node.js object document mapper (ODM) that provides schemas and modeling conveniences. Its transaction helpers wrap MongoDB transaction facilities; they don't manufacture database guarantees. Evaluate mapper ergonomics separately from storage semantics, as discussed in our Mongoose versus native-driver guide.

Specify the Guarantees Your Application Needs

“We need consistency” is too vague to evaluate. Turn it into questions about observable behavior:

• Atomicity: Must several changes commit together or not at all? Creating an order and reserving stock may define one transaction boundary.
• Isolation: What can concurrent transactions observe or interfere with? Test competing reservations, not just sequential requests.
• Read consistency: Can a read return an older value after a successful write? Does the answer change for secondary indexes or replicas?
• Durability: Which failures must an acknowledged write survive? Check persistence and replication settings.
• Availability: During failures, which operations must remain usable, and with what latency or freshness compromises?

These are separate dimensions. PostgreSQL's Serializable isolation makes committed transactions behave as though they ran one at a time, but applications must handle serialization failures and retries. By contrast, a transaction's atomicity alone doesn't establish its isolation behavior.

Read guarantees can also vary within a product. DynamoDB supports strongly consistent reads on tables and local secondary indexes, but not global secondary indexes. Evaluate the exact read path and configuration, rather than assigning guarantees to “SQL” or “NoSQL.”

Let Real Queries Drive Schema and Index Choices

Suppose you need “the latest 20 paid orders for one customer.” Here's a self-contained PostgreSQL example to run in an empty test database using a SQL client. It's an illustration, not a benchmark.

CREATE TABLE orders (
    order_id bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL,
    total_cents bigint NOT NULL CHECK (total_cents >= 0)
);

CREATE INDEX orders_customer_status_recent_idx
    ON orders (customer_id, status, created_at DESC, order_id DESC);

INSERT INTO orders
    (order_id, customer_id, status, created_at, total_cents)
VALUES
    (1, 42, 'paid', '2026-10-01T10:00:00Z', 2500),
    (2, 42, 'paid', '2026-10-02T10:00:00Z', 1800),
    (3, 42, 'pending', '2026-10-03T10:00:00Z', 900);

SELECT order_id, created_at, total_cents
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

The index puts equality filters first, followed by the requested ordering. PostgreSQL's documentation explains multicolumn index filtering and how indexes can satisfy ordering. The order ID breaks timestamp ties deterministically.

Now change the requirement to “daily paid revenue across all customers.” Don't assume the same index or database architecture remains the best fit. Test that aggregation separately, and include index-maintenance costs in write tests.

Inspect realistic PostgreSQL queries with EXPLAIN (ANALYZE, BUFFERS). It executes the query and reports execution information; it isn't a replacement for measuring application latency under concurrency. A tiny table may legitimately use a sequential scan instead of your index.

Evaluate Vector Search as Another Workload

For semantic search, embeddings represent content as numeric vectors that can be compared for similarity. You don't automatically need a separate database: pgvector adds exact and approximate nearest-neighbor search to PostgreSQL, while MongoDB Vector Search supports vector retrieval alongside document data.

Start by testing vector support in your existing general-purpose database when it could avoid another data pipeline and operating surface. Shortlist a dedicated vector database when independent capacity, retrieval features, or measured performance justify another service.

Compare recall—how many true nearest neighbors you retrieve—against exact-search results. Also test filtered searches, tenant isolation, update and deletion freshness, index-build time, memory usage, and concurrent transactional traffic. Approximate search trades exactness for speed; benchmark the quality you actually need, not latency alone.

Evaluate Operations, Total Cost, and Your Exit Plan

Recovery and Team Readiness

Set a recovery point objective (RPO), the acceptable data-loss window, and a recovery time objective (RTO), the acceptable time to restore service. AWS's recovery-objective guidance explains how to connect these targets to business impact.

Then rehearse restoring a production-sized backup into an isolated environment. AWS recommends periodic recovery tests to verify backup integrity and procedures. Measure replication failover and client reconnection separately; neither a replica nor a backup configuration proves that your application can recover.

For each candidate, check managed-service availability in your required regions, supported features, upgrade procedures, monitoring, security controls, and on-call ownership. Verify encryption, least-privilege access, audit logging, and applicable data-location requirements. Microsoft's data-store preparation checklist covers these operational and governance dimensions.

Cost and Migration Risk

Estimate total cost of ownership: compute, primary storage, indexes, replicas, backups, data transfer, licensing or service charges, monitoring, and engineering labor. Compare normal demand, peak demand, and the next likely growth stage—not introductory pricing.

Before committing, test export and re-import of representative data. Check query compatibility, extensions, proprietary APIs, and service-specific features rather than assuming that a familiar protocol makes migration easy.

For an existing application, budget for backfills, synchronization, validation, cutover, and rollback. If you propose dual writes, specify how partial failures and retries will be reconciled. AWS's cutover guidance illustrates why downtime tolerance and changes made after cutover affect the recovery plan.

A Repeatable Database Selection Process

1. Define acceptance criteria. Write down critical queries, transaction boundaries, latency and freshness targets, recovery objectives, and budget.
2. Shortlist a few candidates. Include a familiar system that plausibly meets the requirements. Reject candidates that fail non-negotiable guarantees before benchmarking.
3. Build representative models. Include realistic relationships, indexes, document sizes, retention, skewed keys, and expensive queries.
4. Test realistic load. Measure throughput, p95/p99 latency, errors, retries, resource use, and cost under mixed reads and writes. Include cold starts or cold caches where relevant.
5. Exercise operations. Restore backups, trigger controlled failover, rehearse an upgrade, and test exportability.
6. Record the decision and revisit triggers. State assumptions and trade-offs. Reassess when query patterns, recovery needs, growth, or operating costs change.

My recommendation: choose the simplest system that meets the measured requirements with credible headroom. Add another database only when a distinct workload earns the extra synchronization and operational work.

Common Mistakes

• Using “read-heavy,” “unstructured,” or “scalable” as a complete requirement.
• Comparing one system's default configuration against another's carefully tuned deployment.
• Benchmarking only averages, tiny datasets, or uniform key distributions.
• Treating flexible documents as permission to skip validation and compatibility planning.
• Optimizing for hypothetical growth while ignoring recovery, staffing, and migration costs.

FAQ

How do I choose the right database for my application?

List your important queries, transaction boundaries, latency targets, data volume, and recovery requirements. Shortlist systems that meet those requirements, then compare representative workloads, operational behavior, and total cost.

When should I choose a relational database instead of a NoSQL database?

Start by evaluating a relational database when joins, constraints, interconnected records, and multi-record transactions are central to the application. Compare specific alternatives rather than assuming that every NoSQL database lacks transactions or every relational database fits your workload.

Are NoSQL databases always more scalable than SQL databases?

No. Scalability depends on the specific system, data distribution, access patterns, correctness requirements, and deployment. Test your workload and scaling path instead of inferring capacity from the database category.

Does MongoDB have a schema?

MongoDB has a flexible document model: documents in a collection can differ in fields and types. It supports database-level schema validation, and applications still need data contracts and a schema-evolution strategy.

What should I benchmark before choosing a database?

Benchmark representative queries and mixed reads and writes using realistic data sizes, skew, and concurrency. Measure tail latency, throughput, errors, retries, resource consumption, and cost; test backup restoration and failover as separate acceptance exercises.

Further Reading

• Microsoft: Understand data models — database families and access patterns.
• AWS: Choosing a database service — workload and operational selection criteria.
• PostgreSQL: Multicolumn indexes — matching indexes to filters.
• PostgreSQL: Transaction isolation — concurrency guarantees and retries.
• MongoDB: Schema validation — enforcing document contracts.
• MongoDB: Transactions — transaction scope and considerations.
• pgvector documentation — exact and approximate vector search.
• MongoDB Vector Search documentation — vector indexes and retrieval.