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
- What is the system of record? Business-critical data with invariants usually belongs in a relational or distributed SQL store.
- What are the top queries? Key lookups, joins, aggregations, full-text, graph traversals, similarity?
- Consistency per operation? Strong for money/stock/uniqueness; eventual elsewhere.
- Scale and growth? Writes/s, data size, regions.
- Operational model? Managed/serverless vs self-managed; team skills.
- 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.