Data Systems
Choosing the Right Database
Database selection is one of the most consequential architectural decisions. A wrong choice can limit your system for years. Learn a systematic approach to evaluating database technologies.
- Trade-offs — Every database optimizes for specific access patterns
- Requirements — Your data model and query patterns drive the choice
- Evolution — Systems often use multiple database types (polyglot persistence)
There is no "best" database—only the best database for your specific requirements.
The Database Selection Framework
Use this decision framework to systematically evaluate database options:
Step 1: Understand Your Data Model
Step 2: Map Requirements to Database Types
| Requirement Pattern | Recommended Database |
|---|---|
| Complex queries with JOINs | PostgreSQL, MySQL |
| Flexible document schema | MongoDB, CouchDB |
| Simple key-value lookups | Redis, DynamoDB |
| Relationship traversal | Neo4j, Amazon Neptune |
| Full-text search | Elasticsearch, Solr |
| Time-series data | InfluxDB, TimescaleDB |
| High write throughput | Cassandra, ScyllaDB |
| Strong consistency | Spanner, CockroachDB |
| Global distribution | DynamoDB Global Tables, Cosmos DB |
Step 3: Evaluate the CAP Trade-offs
Step 4: Consider Operational Characteristics
| Factor | Questions to Ask |
|---|---|
| Team expertise | Does the team know this database? |
| Community | Is there good documentation and support? |
| Managed services | Can we use a managed version (RDS, DynamoDB)? |
| Cost | What's the total cost of ownership (license, ops, storage)? |
| Migration | How hard is it to migrate away if needed? |
The Decision Matrix
Use this matrix to compare database options:
Common Anti-Patterns
- One database for everything — Trying to force a single database to handle all access patterns
- Premature optimization — Choosing a complex database when a simple one suffices
- Ignoring operational complexity — Choosing a database the team can't operate
- Copy-paste architecture — Using what worked at a previous company without considering different requirements
Practice Exercises
-
Database Selection: For a ride-sharing app (like Uber), choose the right database for each data type: user profiles, ride history, real-time location, payment transactions. Justify each choice.
-
Trade-off Analysis: Compare PostgreSQL and MongoDB for an e-commerce product catalog. What are the trade-offs in terms of schema flexibility, query performance, and scalability?
-
Polyglot Design: Design a social media system using at least 3 different databases. Explain why each database is the right choice for its specific use case.
-
Migration Planning: Your team currently uses MySQL but needs to handle 10x more write throughput. Evaluate the options: scale vertically, add read replicas, shard, or migrate to Cassandra.
What to Learn Next
-> SQL Deep Dive PostgreSQL, MySQL, indexing strategies, and query optimization.
-> NoSQL Deep Dive Document, key-value, column-family, and graph databases.
-> NewSQL and Distributed SQL Spanner, CockroachDB, and the next generation of SQL databases.
-> Databases SQL vs NoSQL, indexing, replication, and sharding fundamentals.
-> Database Indexing B-trees, hash indexes, and indexing strategies.
-> Data Partitioning Sharding strategies, consistent hashing, and partition keys.