How to Choose a Database
Quick answer: How to Choose a Database
How to Choose a Database is covered here as a practical system design article. A decision tree for selecting the right database based on your data model, access patterns, consistency needs, and scale requirements. Use it to turn a broad system design idea into an interview-ready checklist with practical examples and links to deeper topics.
Focus on the problem being solved, the main design choice, the failure mode, and the tradeoff you would defend in front of a senior engineer. This page is part of the DosiBridge system design knowledge graph, so it links related concepts, interview problems, tradeoffs, and references instead of leaving the topic isolated.
How to Choose a Database
"Which database should I use?" is one of the most common system design questions, and the answer is always "it depends." This guide provides a decision framework based on your actual requirements rather than hype or familiarity.
Start With Your Data Model
The shape of your data is the strongest signal for database selection.
Structured data with relationships: If your data has well-defined schemas with relationships between entities (users have orders, orders have items, items belong to categories), start with a relational database (PostgreSQL, MySQL). SQL databases enforce schema consistency, support complex joins, and provide ACID transactions. PostgreSQL is the default choice for most applications — it handles JSON, full-text search, and geospatial data well enough that you often do not need a second database.
Document-oriented data: If your data is hierarchical, schema-flexible, or naturally fits into self-contained documents (user profiles with nested preferences, product catalogs with variable attributes), consider MongoDB or DynamoDB. Document databases excel when you read and write entire documents and rarely need cross-document joins.
Key-value lookups: If your access pattern is primarily "get value by key" with no complex queries — session storage, feature flags, user preferences — a key-value store like Redis or DynamoDB is ideal. Redis adds the benefit of sub-millisecond latency since it operates in memory.
Wide-column / time-series data: If you write enormous volumes of data with a time component (metrics, logs, IoT sensor data, event streams), consider Cassandra, ScyllaDB, or a purpose-built time-series database like InfluxDB or TimescaleDB. These databases optimize for high write throughput and time-range queries.
Graph data: If your core queries traverse relationships (social networks, recommendation engines, fraud detection), a graph database like Neo4j provides efficient traversals that would require expensive recursive joins in SQL.
Full-text search: If your primary use case is searching text (product search, log analysis), Elasticsearch or OpenSearch provide inverted indexes optimized for text queries. These are typically used alongside a primary database, not as a replacement.
Decision Tree
- Do you need ACID transactions across multiple entities? If yes, use a relational database (PostgreSQL, MySQL). DynamoDB supports transactions within a single table; Spanner supports them globally.
-
Is your read pattern primarily key-based lookups? If yes and you need speed, use Redis. If you need durability and scale, use DynamoDB.
-
Is your write volume extremely high (>100K writes/sec)? If yes, consider Cassandra, ScyllaDB, or DynamoDB. Relational databases struggle at extreme write volumes on a single node.
-
Do you need flexible schemas? If your schema changes frequently or varies between records, document databases (MongoDB) or wide-column stores (Cassandra) provide more flexibility than relational databases.
-
Is your data time-series? Use TimescaleDB (if you want SQL) or InfluxDB (purpose-built). Cassandra also works well for time-series with proper partition key design.
- Do you need full-text search? Add Elasticsearch alongside your primary database. Do not use it as your source of truth.
Common Mistakes
Choosing based on scale you do not have. PostgreSQL handles millions of rows and thousands of queries per second on a single node. Most startups will never outgrow it. Do not pick Cassandra because you might need it someday — pick it when you actually need horizontal write scaling.
Using one database for everything. A relational database is a great default, but using it for full-text search, caching, and message queuing leads to a slow, overloaded system. Use the right tool for each job.
Ignoring operational complexity. Running a Cassandra cluster requires significant operational expertise. A managed service (DynamoDB, Cloud Spanner, managed PostgreSQL) trades cost for operational simplicity.
Picking a database because of one feature. MongoDB has flexible schemas, but if you also need transactions across collections, that flexibility comes at a cost. Evaluate the full set of requirements, not one standout feature.
The Default Recommendation
If you are unsure, start with PostgreSQL. It is the most versatile database available: relational with ACID, JSON support for semi-structured data, full-text search for basic search needs, LISTEN/NOTIFY for simple pub/sub, and a mature ecosystem of tools and extensions. Add specialized databases only when PostgreSQL demonstrably cannot handle a specific workload.
Original DosiBridge notes for How to Choose a Database
This section is written specifically for this page, not copied from a general system-design checklist. The useful way to study How to Choose a Database is to connect the concept to a data path where correctness, access pattern, and future migrations matter more than benchmark numbers. That framing changes the design discussion from definitions to operating choices.
What makes this topic distinct
- Practical Lesson: For How to Choose a Database, decide the practical lesson before choosing tools; otherwise the design looks clean but fails when how changes.
- Migration Story: For How to Choose a Database, decide the migration story before choosing tools; otherwise the design looks clean but fails when to changes.
- Production Symptom: For How to Choose a Database, decide the production symptom before choosing tools; otherwise the design looks clean but fails when choose changes.
- Decision Checklist: For How to Choose a Database, decide the decision checklist before choosing tools; otherwise the design looks clean but fails when a changes.
- Rollback Plan: For How to Choose a Database, decide the rollback plan before choosing tools; otherwise the design looks clean but fails when database changes.
Production review question
If a team adopts How to Choose a Database, what observable signal proves it helped instead of only making the architecture diagram more fashionable?
Practical metric to track
Track how-database health as a named dashboard or review item: combine one user-facing metric, one saturation metric, and one correctness metric. For How to Choose a Database, this keeps the discussion grounded in behavior that can be measured after deployment instead of generic advice that sounds good in an interview but never gets verified.