Database Selection

A decision framework for choosing between relational, distributed SQL, key-value, document, wide-column, search, time-series, graph, analytical and vector stores.

What it is

Database selection matches data shape, access patterns, consistency needs, scale and operational constraints to a storage technology. Most real systems use several stores (polyglot persistence), with one designated system of record per data domain.

Use the Database Selection Guide to practise.

Decision framework

  1. What is the system of record? Business-critical data with invariants usually belongs in a relational or distributed SQL store.
  2. What are the top queries? Key lookups, joins, aggregations, full-text, graph traversals, similarity?
  3. Consistency per operation? Strong for money/stock/uniqueness; eventual elsewhere.
  4. Scale and growth? Writes/s, data size, regions.
  5. Operational model? Managed/serverless vs self-managed; team skills.
  6. Cost model? Provisioned vs on-demand; storage vs IO pricing.
Category Choose for Examples
Relational Transactions, joins, constraints, reporting on operational data PostgreSQL, MySQL, Aurora, AlloyDB, Azure SQL
Distributed SQL Relational + horizontal or multi-region strong consistency Spanner, Aurora DSQL, CockroachDB, YugabyteDB
Key-value / document Massive scale with known access patterns, flexible schema DynamoDB, Cosmos DB, MongoDB, Firestore
Wide-column Very high write rates, time-ordered data per key Cassandra, ScyllaDB, Bigtable
In-memory Caching, counters, sessions, leaderboards Redis/Valkey
Search Full-text, relevance, faceting, logs OpenSearch, Elasticsearch, Azure AI Search
Time-series Metrics, IoT telemetry, retention and downsampling TimescaleDB, InfluxDB, Timestream
Graph Relationship-heavy queries Neo4j, Neptune, Spanner Graph
Analytical Large scans and aggregations BigQuery, Snowflake, Redshift, ClickHouse, Databricks
Vector Semantic similarity for AI/RAG pgvector, OpenSearch k-NN, Vertex AI Vector Search, Pinecone, Qdrant
flowchart TD
  Q1{Need multi-row ACID<br/>transactions and joins?} -- yes --> Q2{Write scale beyond one primary<br/>or multi-region writes?}
  Q2 -- no --> RDB[(Relational DB)]
  Q2 -- yes --> DSQL[(Distributed SQL)]
  Q1 -- no --> Q3{Access mostly by key<br/>at very high scale?}
  Q3 -- yes --> KV[(Key-value / document /<br/>wide-column)]
  Q3 -- no --> Q4{Query type?}
  Q4 -- full text --> SE[(Search engine)]
  Q4 -- aggregations over history --> OLAP[(Warehouse / lakehouse)]
  Q4 -- relationships --> GR[(Graph DB)]
  Q4 -- similarity --> VEC[(Vector index)]

Real-world example

An online marketplace: orders, payments and seller balances in PostgreSQL (Aurora) for ACID; catalog in DynamoDB for scale with item attributes as documents; OpenSearch for search, fed by DynamoDB Streams; Redis for sessions and carts; S3 + Iceberg tables queried by Athena/Spark for analytics; pgvector for semantic product search during early stages.

Advantages and disadvantages of polyglot persistence

Advantages Disadvantages
Each workload uses the best tool More technologies to secure, back up and operate
Independent scaling Data synchronization and consistency between stores
Better performance and cost More skills required

Architect-level considerations

[!ARCHITECT] Default to a managed relational database unless a requirement rules it out; it is the most flexible store for unknown future queries. Choose NoSQL when access patterns are well known and scale or latency demand it, and document the access patterns that justify the key design.

[!EA] Maintain an approved data-store catalog with support tiers. Each new store type needs operational ownership, backup policy and security baseline.

[!CTO] The cost of a database choice is mostly in people and migration risk, not licences. Prefer fewer, well-run platforms.

Interview questions and answers

When would you pick DynamoDB over PostgreSQL?

When access patterns are known and key-based, scale is large or spiky, you want serverless operations with predictable single-digit millisecond latency, and you do not need ad-hoc joins. Keep PostgreSQL when queries evolve, relationships are rich or multi-entity transactions are frequent.

How would you keep a search index consistent with the primary database?

Change data capture from the database (Debezium, DynamoDB Streams, change streams) into an indexing pipeline, idempotent upserts with version checks, periodic reconciliation, and acceptance that search is eventually consistent.

Is "use MongoDB because the schema is flexible" a good reason?

Rarely on its own. PostgreSQL JSONB offers flexible documents with relational guarantees. Choose a document database when the data is naturally self-contained, accessed by key, and needs its scaling and operational model.