Choosing the Right Database for Your Architecture

By NTh Hai - 8 min read

Picking a database is one of the earliest architectural decisions that is hard to undo. Teams often start from brand familiarity (“we always use Postgres”) or trend pressure (“everyone is on NoSQL”). A better approach starts from access patterns and consistency needs, then maps those to a store that fits.

Start With Questions, Not Products

Before you open a vendor comparison page, answer:

  1. What is the unit of consistency? A single row? An order plus its line items? A user’s entire profile graph?
  2. How do you look data up? Primary key, secondary filters, full-text, geospatial, time range?
  3. What is the write shape? Append-only events, frequent updates to the same row, bulk analytics loads?
  4. What happens when two writers conflict? Last-write-wins, merge, reject, or human resolve?
  5. What is the retention and query horizon? Hot last 30 days vs. years of history?

These answers constrain the design more than any benchmark chart.

Relational Databases Still Win Often

PostgreSQL, MySQL, and similar engines remain the default for good reason:

  • Strong consistency and mature transactions
  • Rich querying with joins, constraints, and indexes
  • Excellent tooling for migrations, backups, and observability
  • Enough flexibility via JSON columns when you need semi-structured fields

Choose relational when relationships are real, invariants matter (inventory, money, permissions), and you want the database to enforce integrity — not just store blobs.

A common modern pattern is Postgres as the system of record, with specialized stores beside it for search or cache — not instead of it.

Document Stores: When the Document Is the Aggregate

Document databases fit when your primary aggregate is naturally a document: a content page, a product catalog entry, a form submission. You read and write most of the aggregate together, and cross-document joins are rare.

Watch for:

  • Growing need for multi-document transactions
  • Reporting that wants to slice across many fields
  • Unbounded arrays inside documents that wreck update performance

If you find yourself reinventing joins in application code constantly, you may have forced a relational problem into a document box.

Key-Value and Cache Layers

Redis and similar systems shine for:

  • Session and rate-limit state
  • Idempotency keys
  • Hot read caches in front of a slower primary store
  • Short-lived coordination (locks, queues light enough for the job)

They are poor systems of record for durable business data unless you deliberately design for persistence, eviction, and failover. Architecture-wise, treat cache as derived data. If the cache vanishes, the primary must still be correct.

Specialized Engines Have a Place

| Need | Typical fit | | --- | --- | | Full-text search relevance | OpenSearch / Elasticsearch / Postgres FTS for lighter cases | | Time-series metrics | Timescale, VictoriaMetrics, cloud TSDB | | Analytical aggregates | Columnar warehouse / OLAP | | Graph traversals | Graph DB — or relational with careful modeling if depth is shallow |

Polyglot persistence is valid; accidental polyglot (five databases because five tutorials) is not. Each new store multiplies operational cost: backups, IAM, monitoring, failure modes.

A Decision Shortcut

  • Strict invariants + relationships → relational
  • Document-shaped aggregates, flexible schema → document (or relational + JSON)
  • Extreme read QPS on simple keys → key-value / cache in front of primary
  • Search relevance or analytics → specialized index/warehouse fed from the primary

When unsure, prefer the boring choice that your team can operate at 3 a.m. Clever data platforms do not help if nobody can restore them.

Closing Thought

Solution architecture for data is less about crowning a “best database” and more about matching consistency, access patterns, and operational reality. Start from how the business reads and writes truth; the product name comes last.