Database Selection Skill

Purpose

Choose the right database for the job by analyzing data access patterns first, then matching to the appropriate storage paradigm.

Step 1 — Analyze Data Access Patterns

Answer these questions before looking at any database:

1. What is the read/write ratio? (read-heavy → optimize for reads; write-heavy → optimize for writes)
2. What queries will be run? (simple key lookups? complex joins? full-text search? aggregations?)
3. What is the data structure? (flat records? nested documents? relationships? graphs? time-series?)
4. What consistency is required? (strong ACID? eventual? per-entity?)
5. What is the scale? (GB? TB? PB? queries/sec?)
6. What are the access patterns? (random access? sequential scans? range queries? geo queries?)

The access pattern determines the database type. Never choose a database before answering these.

Step 2 — Database Type Selection

Relational (SQL) — Default Choice

Use when:

• Data has clear relationships and structure
• Need ACID transactions (financial, medical, inventory)
• Complex queries with joins, aggregations, reporting
• Schema is relatively stable

Top choices: PostgreSQL (default), MySQL, CockroachDB (distributed)

PostgreSQL is the safe default for most applications. Only deviate with a clear reason.

Document Store

Use when:

• Data is hierarchical/nested and read together
• Schema varies significantly per record
• High write throughput with flexible structure
• No complex cross-document joins needed

Top choices: MongoDB, Firestore, DynamoDB (with document access patterns)

Avoid when: you frequently need to query across document fields or join documents

Key-Value Store

Use when:

• Simple lookups by a known key
• Session storage, caching, feature flags
• Extremely high throughput, low latency requirements
• Data has no relationships

Top choices: Redis (in-memory), DynamoDB (persistent), Memcached (pure cache)

Wide-Column Store

Use when:

• Massive scale (billions of rows)
• High write throughput with time-series or event data
• Known, predictable query patterns (no ad-hoc queries)
• Need geographic distribution

Top choices: Cassandra, HBase, Bigtable

Search Engine

Use when:

• Full-text search with ranking/relevance
• Faceted filtering (e-commerce, catalogs)
• Log analysis and observability

Top choices: Elasticsearch, OpenSearch, Typesense (simpler), Meilisearch

Note: search engines are usually a secondary store — primary data lives in SQL/document DB

Graph Database

Use when:

• Data is fundamentally relational with many hops (social graphs, fraud detection, recommendations)
• Query patterns traverse relationships: "friends of friends who also bought X"

Top choices: Neo4j, Neptune (AWS), Memgraph

Time-Series Database

Use when:

• Data is timestamped and queried by time range
• Metrics, IoT sensor data, financial tick data
• High ingest rate with time-based aggregations

Top choices: InfluxDB, TimescaleDB (Postgres extension), Prometheus (metrics only)

Step 3 — Comparison Output Format

Always present the recommendation as:

Recommended: [Database]

• Why: [2–3 specific reasons tied to the access patterns identified]
• Schema sketch: show the main tables/collections/keys
• Indexing strategy: which fields to index and why
• Scaling approach: how it scales as data grows

Runner-up: [Database]

• Would choose if: [specific condition that would flip the decision]

Multi-Database Architectures

It is normal and correct to use multiple databases. Common patterns:

State the primary store first, then justify each additional store separately.

Decision Checklist

• Access patterns analyzed before choosing
• Consistency requirements defined (ACID vs eventual)
• Scale target stated (GB/TB, queries/sec)
• Backup and disaster recovery approach addressed
• Migration strategy from any existing database considered
• Team familiarity with chosen database assessed
• Managed service vs. self-hosted decision made