Databases

Databases in System Design

In system design and high-level architecture (HLD), databases form the backbone of any system.
Choosing the right database depends on data structure, query patterns, scalability, and consistency requirements.

---

🧭 Database Decision Tree (Conditional Flow)

---

🏷️ Quick Reference Table

---

1. Relational Databases (SQL)

• Structure: Tables (rows & columns), fixed schema, ACID properties
• Strengths: Strong consistency, complex queries, joins, transactions
• Weaknesses: Harder to scale horizontally, rigid schema
• When NOT to use: If you need flexible schema, massive horizontal scaling, or handle unstructured data.
• Popular Managed Options: AWS RDS, Azure SQL, Google Cloud SQL
• Performance/Scalability: Vertical scaling, some support for read replicas and sharding.
• Cost: Can be expensive at scale, especially for licensing (Oracle, SQL Server).

Use Cases

• Banking, ERP systems, e-commerce orders, financial transactions

Examples

• PostgreSQL, MySQL, Oracle, SQL Server

Diagram

---

2. Key-Value Databases

• Structure: Simple key → value mapping
• Strengths: Extremely fast, simple retrieval, easy to scale horizontally
• Weaknesses: Limited query functionality, no relationships
• When NOT to use: If you need complex queries or relationships.
• Popular Managed Options: AWS DynamoDB, Azure Table Storage, Google Cloud Datastore
• Performance/Scalability: Excellent horizontal scaling, in-memory options (Redis).
• Cost: Pay-per-request (DynamoDB), memory cost (Redis).

Use Cases

• Caching, session storage, shopping carts, leaderboards

Examples

• Redis, DynamoDB, Memcached

Diagram

---

3. Document Databases

• Structure: JSON-like documents, flexible schema
• Strengths: Hierarchical data, flexible, easy to update, semi-structured
• Weaknesses: Less efficient for joins, eventual consistency in some cases
• When NOT to use: If you need complex joins or strict schema.
• Popular Managed Options: MongoDB Atlas, Firebase Firestore, AWS DocumentDB
• Performance/Scalability: Good horizontal scaling, sharding.
• Cost: Storage + throughput, can be expensive at scale.

Use Cases

• CMS, product catalogs, IoT apps, user profiles

Examples

• MongoDB, Firestore, CouchDB

Diagram

---

4. Wide-Column Databases

• Structure: Columns grouped in families for scalable analytics
• Strengths: Scalable, optimized for analytics, high write throughput
• Weaknesses: Complex schema design, not ideal for transactional workloads
• When NOT to use: If you need ACID transactions or simple queries.
• Popular Managed Options: Google Bigtable, Azure Cosmos DB (Cassandra API), AWS Keyspaces
• Performance/Scalability: Excellent horizontal scaling, partitioning.
• Cost: Pay-per-use, can be high for large clusters.

Use Cases

• Big data analytics, recommendation engines, time-series-like workloads

Examples

• Cassandra, HBase, Bigtable

Diagram

---

5. Graph Databases

• Structure: Nodes and edges
• Strengths: Complex relationship queries, traversals, flexible schema
• Weaknesses: Slower for bulk inserts, not ideal for analytics
• When NOT to use: If you have simple, flat data or need high write throughput.
• Popular Managed Options: Neo4j Aura, AWS Neptune, Azure Cosmos DB (Gremlin API)
• Performance/Scalability: Good for traversals, can be hard to scale for massive graphs.
• Cost: Pay-per-node/edge, can be high for large graphs.

Use Cases

• Social networks, recommendations, fraud detection, knowledge graphs

Examples

• Neo4j, ArangoDB, JanusGraph

Diagram

---

6. Time-Series Databases

• Structure: Indexed by timestamp
• Strengths: Optimized for sequential data, compression, fast range queries
• Weaknesses: Narrow use case, not for general-purpose data
• When NOT to use: If you need complex relationships or non-time-based queries.
• Popular Managed Options: InfluxDB Cloud, AWS Timestream, Timescale Cloud
• Performance/Scalability: High write throughput, retention policies.
• Cost: Pay-per-ingest, storage.

Use Cases

• IoT, monitoring, financial tick data, metrics

Examples

• InfluxDB, TimescaleDB, Prometheus

Diagram

---

7. Search Databases

• Structure: Indexed text for full-text search
• Strengths: Full-text search, scoring, faceting, analytics
• Weaknesses: Not transactional, eventual consistency
• When NOT to use: For transactional or relational data.
• Popular Managed Options: Elastic Cloud, AWS OpenSearch, Azure Cognitive Search
• Performance/Scalability: Scales horizontally, sharding.
• Cost: Storage + compute, can be high for large indexes.

Use Cases

• E-commerce search, logs, document search

Examples

• Elasticsearch, Solr, OpenSearch

Diagram

---

8. Vector Databases

• Structure: High-dimensional vectors
• Strengths: Semantic/AI queries, similarity search, ANN (approximate nearest neighbor)
• Weaknesses: Still evolving, not for transactional data
• When NOT to use: For traditional queries or transactional workloads.
• Popular Managed Options: Pinecone, Weaviate Cloud, Milvus Cloud
• Performance/Scalability: Optimized for vector search, horizontal scaling.
• Cost: Pay-per-vector, storage.

Use Cases

• AI embeddings, semantic search, recommendation, image/audio similarity

Examples

• Pinecone, Weaviate, Milvus

Diagram

---

9. Multi-Model Databases

• Structure: Supports multiple paradigms (doc + graph + key-value)
• Strengths: Flexible, can handle complex apps with mixed requirements
• Weaknesses: Added complexity, may not be best-in-class for any one model
• When NOT to use: If you only need one data model.
• Popular Managed Options: ArangoDB Oasis, Azure Cosmos DB, OrientDB Cloud
• Performance/Scalability: Varies by model, generally good.
• Cost: Pay-per-model, storage.

Use Cases

• Complex apps, SaaS, hybrid data needs

Examples

• ArangoDB, OrientDB, Cosmos DB

Diagram

---

10. Ledger/Blockchain Databases

• Structure: Immutable, append-only, cryptographically verifiable
• Strengths: Tamper-proof, auditable, traceable
• Weaknesses: Slower writes, complex to manage
• When NOT to use: For high-throughput or general-purpose data.
• Popular Managed Options: Amazon QLDB, Hyperledger Fabric, BigchainDB
• Performance/Scalability: Slower than traditional DBs, optimized for audit.
• Cost: Pay-per-write, storage.

Use Cases

• Audit logs, financial ledgers, supply chain, compliance

Examples

• Amazon QLDB, Hyperledger, BigchainDB

Diagram

---

11. Embedded Databases

• Structure: Local, file-based, lightweight
• Strengths: No server needed, fast, portable
• Weaknesses: Not distributed, limited concurrency
• When NOT to use: For distributed or multi-user systems.
• Popular Managed Options: SQLite, RocksDB, Realm
• Performance/Scalability: Excellent for local, limited for distributed.
• Cost: Free/open source.

Use Cases

• Mobile apps, IoT devices, desktop software

Examples

• SQLite, RocksDB, Realm

Diagram

---

12. NewSQL Databases

• Structure: Relational, distributed, SQL interface
• Strengths: ACID + horizontal scaling, global distribution
• Weaknesses: Newer, less mature ecosystem
• When NOT to use: If you don’t need global scale or distributed SQL.
• Popular Managed Options: Google Spanner, CockroachDB Cloud, Yugabyte Cloud
• Performance/Scalability: Excellent horizontal scaling, global consistency.
• Cost: Pay-per-node, storage.

Use Cases

• Global apps, SaaS, fintech, distributed transactions

Examples

• CockroachDB, Google Spanner, YugabyteDB

Diagram

📝 Summary Table of Database Types

---

🏆 Real-World Scenario Recommendations

---

✅ How to Choose: Checklist

• Do you need strong consistency and complex queries? → Relational (SQL)
• Do you need high throughput, simple lookups? → Key-Value
• Do you need flexible, evolving schema? → Document
• Do you need analytics on huge datasets? → Wide-Column
• Do you need to model complex relationships? → Graph
• Do you need to store time-stamped metrics? → Time-Series
• Do you need full-text search? → Search
• Do you need AI/ML similarity search? → Vector
• Do you need multiple models in one DB? → Multi-Model
• Do you need immutable, auditable records? → Ledger/Blockchain
• Do you need local/mobile/embedded storage? → Embedded
• Do you need distributed SQL with global scale? → NewSQL

Tip: For most modern systems, a polyglot persistence approach (using multiple DBs for different needs) is common!

Links:

• PostgreSQL Docs
• MongoDB Docs
• Redis Docs
• Cassandra Docs
• Neo4j Docs
• Elasticsearch Docs
• InfluxDB Docs
• Pinecone Docs
• CockroachDB Docs

---