How to Choose the Right Database

Use the same workload for every candidate: create an order with five items, reserve inventory exactly once, read the customer’s last 20 orders, search products by text, and retain a receipt PDF. Assume 100 writes and 1,000 reads per second at peak, one primary region, and a requirement that a paid order never appear unpaid because of a stale read.

PostgreSQL fits order and inventory transactions because constraints and atomic updates protect invariants. A document store can return an order aggregate conveniently, but cross-document inventory updates need transactions or a redesigned boundary. A key-value store can serve known-key order status, but “last 20 by customer” requires an explicit index. A search engine improves product discovery, and object storage owns receipt bytes; neither should become the authority for payment state.

The result is often a small portfolio, not one winner: one authoritative transactional store plus derived search/cache views and object storage. Consistency and access requirements decide which copy is authoritative; data shape only suggests representation.

Relational databases store data in tables, can enforce referential integrity, and support transactions across related records. Those mechanisms are useful when invariants such as “inventory cannot be reserved twice” matter more than optimizing one isolated access path.

Use a relational model when the domain maps to constrained records and joins: user accounts, orders, invoices, and inventory. ORMs can help application integration, but generated queries, migrations, and transaction boundaries still need review.

Columnar databases flip the storage model: instead of storing rows together, they store columns together. That makes aggregations — SUM, AVG, COUNT over millions of rows — dramatically faster and cheaper because the engine reads only the columns you query.

These are analytics engines, not application databases. Do not use them for OLTP workloads with frequent single-row updates. They shine in data warehouses, BI dashboards, log analytics, and batch reporting pipelines.

Not everything is a table row. Images, videos, PDFs, backups, ML model artifacts, and raw log files are unstructured blobs — binary objects accessed by key, not by SQL query.

Object storage is designed for durable, high-scale object access, but every service has quotas, request costs, consistency semantics, and regional failure modes. Use it as the system of record for receipt bytes, then reference an object key—not a permanent public URL—from transactional metadata.

Full-text search is a specialized problem. Users type keywords, expect relevance-ranked results in milliseconds, and want fuzzy matching, synonyms, and faceted filters. SQL LIKE queries cannot do this at scale.

Search engines build inverted indexes — mapping every word to the documents containing it. They power product catalogs, knowledge bases, log exploration, and site-wide search bars.

Semi-structured data does not fit rigid tables but still has identifiable keys. Session tokens, user preferences, feature flags, shopping carts, and rate-limit counters all map naturally to key-value access patterns.

In-memory layers can absorb repeated reads in front of an authoritative store. Their latency depends on network, payload, commands, persistence, and load; benchmark the actual path. Redis is one common choice, but it does not remove the need to define stale-read and eviction behavior.

When a single key is not enough, richer data models may fit. Document databases store aggregate-shaped records with evolving schemas; wide-column stores distribute partition-key-driven datasets with tunable consistency.

Document stores suit aggregate reads; wide-column stores suit workloads designed around known partition keys. Pick from measured query and consistency needs, not popularity or a named company’s scale.

Some workloads benefit from dedicated indexes and execution models. Graph databases traverse relationships; time-series databases partition and compress timestamped data; ledgers preserve append-oriented audit evidence; geospatial engines index location. A specialized engine can improve a representative query, but it adds another service to secure, migrate, back up, and monitor.

SQL recursive queries and partitioned time-series tables can remain adequate at meaningful scale. Move only after a reproducible workload shows unacceptable latency, cost, or operational complexity—not because the data has a graph or timestamp in it.

In the checkout workload, the search projection falls 12 minutes behind after its change-data-capture consumer loses credentials. Customers can still pay, but newly added products disappear from search. The root cause is not the search engine’s data model; it is a broken replication boundary between the authoritative database and a derived index.

Detect the incident with projection-lag and consumer-error alerts. Keep checkout reads on the transactional store, restore credentials, and replay events from the last committed offset into a versioned index. Compare document counts and sampled product versions, then atomically switch the search alias. If the log cannot replay, rebuild from an authoritative snapshot and reconcile updates written during the rebuild.

Run an equivalent recovery drill for each candidate: kill a node, revoke a credential, fill a quota, and restore a backup. A database that wins a schema debate but fails your recovery-time objective is the wrong operational choice.