{"title":"Database Types","description":null,"content":"Every application needs to store data somewhere. But not all data is the same, and not all storage needs are equal. A social media app storing billions of posts has very different requirements from a banking system tracking account balances.\n\nThis diversity in data and access patterns has led to a rich ecosystem of database types, each optimized for specific use cases. Understanding these types is not just academic. The database you choose fundamentally shapes your application's architecture, performance characteristics, and operational complexity.\n\nIn this chapter, you will learn:\n\n- The major categories of databases and what makes each unique\n- Key characteristics and trade-offs of different database types\n- How to match database types to specific use cases\n- Real-world examples of where each type excels\n- How to think about database selection in system design interviews\n\nThis chapter provides the landscape. The following chapters will dive deeper into specific comparisons and concepts like ACID transactions and data modeling that cut across database types.\n\n---\n\n# Why Different Database Types Exist\n\nIn the early days of computing, relational databases dominated. Oracle, MySQL, PostgreSQL, SQL Server. They handled everything from inventory systems to websites. For many use cases, they still do.\n\nBut as the internet scaled, new problems emerged that relational databases struggled with:\n\n- **Massive scale:** Social networks with billions of users could not fit on a single machine\n- **Flexible schemas:** Agile development needed databases that did not require schema migrations for every change\n- **Specialized access patterns:** Full-text search, graph traversals, and time-series analytics needed specialized data structures\n- **Geographic distribution:** Global applications needed data replicated across continents with low latency\n\nThese pressures birthed the NoSQL movement and a proliferation of specialized databases. Today, the question is not \"which database\" but \"which databases\" since many systems use multiple types together.\n\n\n> **Interview Insight**\n>\n> When asked about database selection, do not jump to a specific technology. First, understand the data model, access patterns, scale requirements, and consistency needs. The right database type emerges from these requirements.\n\n\n---\n\n# The Database Landscape\n\n\n```mermaid\nflowchart TB\n DB[Databases]:::primary\n\n DB --> Relational[Relational
SQL]:::purple\n DB --> NoSQL[Non-Relational
NoSQL]:::orange\n\n Relational --> RDBMS[RDBMS
PostgreSQL, MySQL
Oracle, SQL Server]:::purple\n\n NoSQL --> KV[Key-Value
Redis, DynamoDB
Memcached]:::teal\n NoSQL --> Doc[Document
MongoDB, CouchDB
Firestore]:::green\n NoSQL --> Column[Wide-Column
Cassandra, HBase
ScyllaDB]:::orange\n NoSQL --> Graph[Graph
Neo4j, Neptune
TigerGraph]:::pink\n\n DB --> Specialized[Specialized]:::red\n\n Specialized --> TS[Time-Series
InfluxDB, TimescaleDB]:::red\n Specialized --> Search[Search
Elasticsearch
Solr]:::red\n Specialized --> Vector[Vector
Pinecone, Milvus
Weaviate]:::red\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef pink fill:#f783ac,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nLet us explore each category in detail.\n\n---\n\n# Relational Databases (RDBMS)\n\nRelational databases store data in tables with rows and columns. They use SQL (Structured Query Language) for queries and enforce schemas that define the structure of data. Relationships between tables are expressed through foreign keys.\n\n### Key Characteristics\n\n\n| Characteristic | Description |\n|----------------|-------------|\n| **Data Model** | Tables with rows and columns, strict schema |\n| **Query Language** | SQL with powerful joins, aggregations, subqueries |\n| **Consistency** | Strong consistency with ACID transactions |\n| **Schema** | Schema-on-write (enforced at insert time) |\n| **Scaling** | Primarily vertical, horizontal via read replicas or sharding |\n\n\n### How Data is Organized\n\n\n```mermaid\nflowchart TB\n subgraph Users[Users Table]\n U1[id: 1, name: Alice, email: alice@example.com]:::primary\n U2[id: 2, name: Bob, email: bob@example.com]:::primary\n end\n\n subgraph Orders[Orders Table]\n O1[id: 101, user_id: 1, total: $50]:::orange\n O2[id: 102, user_id: 1, total: $30]:::orange\n O3[id: 103, user_id: 2, total: $75]:::orange\n end\n\n U1 --> |Foreign Key| O1\n U1 --> |Foreign Key| O2\n U2 --> |Foreign Key| O3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n### Strengths\n\n- **ACID guarantees:** Transactions ensure data integrity, critical for financial systems\n- **Complex queries:** Joins across tables enable sophisticated analytics\n- **Mature ecosystem:** Decades of tooling, optimization, and operational knowledge\n- **Strong consistency:** Reads always return the latest committed data\n\n### Weaknesses\n\n- **Scaling limitations:** Sharding relational databases is complex and often sacrifices some SQL features\n- **Schema rigidity:** Schema changes can be painful and require migrations\n- **Not ideal for:** Hierarchical data, sparse data, or highly variable schemas\n\n### Popular Relational Databases\n\n\n| Database | Best For | Notable Users |\n|----------|----------|---------------|\n| **PostgreSQL** | Complex queries, extensibility, JSON support | Apple, Instagram, Spotify |\n| **MySQL** | Web applications, read-heavy workloads | Facebook, Twitter, Netflix |\n| **SQL Server** | Enterprise Windows environments | Stack Overflow, many Fortune 500 |\n| **Oracle** | Large enterprises, complex transactions | Banks, governments, airlines |\n\n\n### When to Choose Relational\n\n- Your data has clear relationships and benefits from joins\n- You need ACID transactions (financial systems, inventory)\n- Your schema is relatively stable\n- You need complex queries with aggregations and subqueries\n- Your scale fits on one server or a few replicas\n\n---\n\n# Key-Value Stores\n\nKey-value stores are the simplest database type. They store data as a collection of key-value pairs, like a giant hash map. You look up values by their key, and that is basically it.\n\n### Key Characteristics\n\n\n| Characteristic | Description |\n|----------------|-------------|\n| **Data Model** | Key-value pairs, value can be anything (string, JSON, binary) |\n| **Query Language** | Simple GET, PUT, DELETE operations by key |\n| **Consistency** | Varies (Redis: strong on single node, DynamoDB: configurable) |\n| **Schema** | Schema-less, value is opaque to the database |\n| **Scaling** | Excellent horizontal scaling through partitioning |\n\n\n### How Data is Organized\n\n\n```mermaid\nflowchart LR\n subgraph KVStore[Key-Value Store]\n K1[user:1001]:::primary --> V1[\"{name: 'Alice', age: 28}\"]:::orange\n K2[session:abc123]:::primary --> V2[\"{user_id: 1001, expires: ...}\"]:::orange\n K3[cache:product:555]:::primary --> V3[\"{name: 'Widget', price: 29.99}\"]:::orange\n K4[counter:pageviews]:::primary --> V4[\"1523847\"]:::orange\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n### Strengths\n\n- **Blazing fast:** O(1) lookups, often sub-millisecond latency\n- **Simple operations:** Easy to understand and use\n- **Horizontal scaling:** Partition by key for near-linear scalability\n- **Flexible values:** Store whatever you need in the value\n\n### Weaknesses\n\n- **Limited queries:** Cannot query by value, only by exact key\n- **No relationships:** No joins or references between entries\n- **No complex operations:** Aggregations require reading all data\n\n### Popular Key-Value Stores\n\n\n| Database | Best For | Notable Features |\n|----------|----------|------------------|\n| **Redis** | Caching, sessions, real-time leaderboards | In-memory, rich data types, pub/sub |\n| **Amazon DynamoDB** | Serverless, auto-scaling workloads | Fully managed, consistent performance |\n| **Memcached** | Simple caching | Distributed memory caching |\n| **etcd** | Configuration, service discovery | Strong consistency, used by Kubernetes |\n\n\n### When to Choose Key-Value\n\n- Caching frequently accessed data\n- Session storage\n- User preferences and settings\n- Shopping carts\n- Real-time leaderboards and counters\n- Any use case where you always know the key\n\n\n> **Interview Insight**\n>\n> Key-value stores are often the first line of defense before hitting a database. When discussing system design, consider adding a caching layer with Redis or Memcached to reduce load on your primary database.\n\n\n---\n\n# Document Databases\n\nDocument databases store data as semi-structured documents, typically JSON or BSON. Each document is self-contained and can have a different structure from other documents in the same collection.\n\n### Key Characteristics\n\n\n| Characteristic | Description |\n|----------------|-------------|\n| **Data Model** | JSON-like documents with nested structures |\n| **Query Language** | Rich query language on document fields |\n| **Consistency** | Configurable, often eventual consistency for distributed setups |\n| **Schema** | Schema-flexible (schema-on-read) |\n| **Scaling** | Horizontal scaling via sharding |\n\n\n### How Data is Organized\n\n\n```mermaid\nflowchart TB\n subgraph Collection[Products Collection]\n D1[\"{\n _id: 'prod_1',\n name: 'Laptop',\n specs: {cpu: 'i7', ram: '16GB'},\n reviews: [{user: 'alice', rating: 5}]\n }\"]:::primary\n\n D2[\"{\n _id: 'prod_2',\n name: 'Phone',\n specs: {screen: '6.1 inch'},\n colors: ['black', 'white']\n }\"]:::primary\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n```\n\n\nNotice how each document can have different fields. The laptop has `cpu` and `ram`, while the phone has `screen` and `colors`. This flexibility is a core strength of document databases.\n\n### Strengths\n\n- **Flexible schema:** Add fields without migrations, great for evolving data models\n- **Natural mapping:** Documents map directly to objects in most programming languages\n- **Nested data:** Embed related data in a single document, avoiding joins\n- **Developer productivity:** Less impedance mismatch between code and database\n\n### Weaknesses\n\n- **No joins:** Cross-document queries require multiple round trips or denormalization\n- **Potential data duplication:** Embedding data leads to redundancy\n- **Schema management:** Flexibility can become chaos without discipline\n\n### Popular Document Databases\n\n\n| Database | Best For | Notable Features |\n|----------|----------|------------------|\n| **MongoDB** | General purpose, flexible schemas | Aggregation pipeline, change streams |\n| **CouchDB** | Offline-first apps, sync | Multi-master replication |\n| **Amazon DocumentDB** | MongoDB-compatible, AWS managed | MongoDB API compatibility |\n| **Firestore** | Mobile and web apps | Real-time sync, offline support |\n\n\n### When to Choose Document\n\n- Your data is naturally hierarchical or nested\n- Schema evolves frequently\n- You want to avoid complex joins by embedding related data\n- Building content management systems, catalogs, or user profiles\n- Rapid prototyping where schema is not finalized\n\n---\n\n# Wide-Column Stores\n\nWide-column stores organize data by columns rather than rows. They excel at handling massive amounts of data across many servers with high write throughput.\n\n### Key Characteristics\n\n\n| Characteristic | Description |\n|----------------|-------------|\n| **Data Model** | Rows with dynamic columns, column families |\n| **Query Language** | CQL (Cassandra), HBase API |\n| **Consistency** | Tunable consistency (eventual to strong) |\n| **Schema** | Flexible columns within column families |\n| **Scaling** | Exceptional horizontal scaling, designed for it |\n\n\n### How Data is Organized\n\n\n```mermaid\nflowchart TB\n subgraph WideCols[Wide-Column Structure]\n subgraph Row1[Row Key: user_1]\n C1[name: Alice]:::primary\n C2[email: alice@ex.com]:::primary\n C3[last_login: 2024-01-15]:::primary\n C4[pref_theme: dark]:::primary\n end\n\n subgraph Row2[Row Key: user_2]\n C5[name: Bob]:::orange\n C6[phone: 555-1234]:::orange\n end\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\nNotice that `user_1` has `email`, `last_login`, and `pref_theme`, while `user_2` only has `name` and `phone`. Each row can have different columns.\n\n### Strengths\n\n- **Massive scale:** Built for petabytes of data across thousands of nodes\n- **High write throughput:** Append-only writes, no read-before-write\n- **Flexible columns:** Rows can have different columns without schema changes\n- **Geographic distribution:** Multi-datacenter replication built-in\n\n### Weaknesses\n\n- **Limited query flexibility:** Queries must follow primary key patterns\n- **No joins:** Data must be denormalized\n- **Operational complexity:** Running at scale requires expertise\n- **Eventual consistency:** Reads may return stale data\n\n### Popular Wide-Column Stores\n\n\n| Database | Best For | Notable Features |\n|----------|----------|------------------|\n| **Apache Cassandra** | High availability, write-heavy | Peer-to-peer, no single point of failure |\n| **Apache HBase** | Hadoop integration, analytics | Strong consistency, HDFS backend |\n| **ScyllaDB** | Cassandra-compatible, lower latency | C++ implementation, better performance |\n| **Google Bigtable** | Google-scale workloads | Managed service, inspiration for HBase |\n\n\n### When to Choose Wide-Column\n\n- Massive scale (billions of rows, petabytes of data)\n- High write throughput requirements\n- Time-series data, event logging, audit trails\n- Multi-datacenter deployments with tunable consistency\n- IoT data ingestion at scale\n\n\n> **Interview Insight**\n>\n> Wide-column stores like Cassandra are often mentioned for time-series data and messaging systems. If the interviewer mentions \"millions of writes per second\" or \"multi-region availability,\" consider wide-column stores.\n\n\n---\n\n# Graph Databases\n\nGraph databases model data as nodes (entities) and edges (relationships). They are optimized for traversing connections, making them ideal for social networks, recommendation engines, and fraud detection.\n\n### Key Characteristics\n\n\n| Characteristic | Description |\n|----------------|-------------|\n| **Data Model** | Nodes and edges with properties |\n| **Query Language** | Graph query languages (Cypher, Gremlin, SPARQL) |\n| **Consistency** | Typically ACID within a single graph |\n| **Schema** | Flexible, define node and edge types |\n| **Scaling** | Challenging, graph partitioning is hard |\n\n\n### How Data is Organized\n\n\n```mermaid\nflowchart LR\n A[Alice
Person]:::primary\n B[Bob
Person]:::primary\n C[Python
Skill]:::orange\n D[TechCorp
Company]:::green\n\n A --> |FRIENDS_WITH| B\n A --> |KNOWS| C\n B --> |KNOWS| C\n A --> |WORKS_AT| D\n B --> |WORKS_AT| D\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\nQueries naturally express traversals: \"Find all friends of Alice who know Python and work at the same company.\"\n\n### Strengths\n\n- **Relationship-centric:** Relationships are first-class citizens, not foreign keys\n- **Fast traversals:** Multi-hop queries are efficient, unlike SQL joins\n- **Intuitive modeling:** Matches how we think about connected data\n- **Pattern matching:** Find complex patterns in connected data\n\n### Weaknesses\n\n- **Not for everything:** Simple CRUD without relationships does not benefit\n- **Scaling challenges:** Graph partitioning is an open research problem\n- **Learning curve:** Graph query languages are different from SQL\n\n### Popular Graph Databases\n\n\n| Database | Best For | Notable Features |\n|----------|----------|------------------|\n| **Neo4j** | General graph use cases | Cypher query language, mature ecosystem |\n| **Amazon Neptune** | AWS managed graphs | SPARQL and Gremlin support |\n| **TigerGraph** | Large-scale analytics | Parallel graph processing |\n| **ArangoDB** | Multi-model (graph + document) | Flexible data model |\n\n\n### When to Choose Graph\n\n- Social networks (friends, followers, connections)\n- Recommendation engines (users who liked X also liked Y)\n- Fraud detection (find suspicious transaction patterns)\n- Knowledge graphs and semantic search\n- Network and IT operations (dependencies, impact analysis)\n- Supply chain and logistics optimization\n\n---\n\n# Specialized Databases\n\nBeyond the main categories, specialized databases optimize for specific data types and access patterns.\n\n### Time-Series Databases\n\nOptimized for timestamped data points, common in monitoring, IoT, and financial applications.\n\n\n```mermaid\nflowchart LR\n subgraph TimeSeries[Time-Series Data]\n TS1[10:00:01 - CPU: 45%]:::primary\n TS2[10:00:02 - CPU: 47%]:::primary\n TS3[10:00:03 - CPU: 46%]:::primary\n TS4[10:00:04 - CPU: 82%]:::orange\n end\n\n TimeSeries --> Agg[Aggregation:
Avg, Max, Percentiles
by time window]:::green\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n\n| Database | Best For |\n|----------|----------|\n| **InfluxDB** | Monitoring, metrics, IoT |\n| **TimescaleDB** | PostgreSQL-compatible time-series |\n| **Prometheus** | Kubernetes metrics, alerting |\n| **QuestDB** | High-performance financial data |\n\n\n**Use when:** You are storing metrics, sensor data, stock prices, or any time-ordered data that needs time-based aggregations.\n\n### Search Engines\n\nOptimized for full-text search, relevance ranking, and faceted navigation.\n\n\n```mermaid\nflowchart LR\n Query[Search: 'running shoes']:::primary\n\n Query --> Index[Inverted Index]:::purple\n Index --> R1[Result 1: Score 0.95]:::green\n Index --> R2[Result 2: Score 0.87]:::green\n Index --> R3[Result 3: Score 0.82]:::green\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**Use when:** Users need to search through text, you need faceted search (filter by category, price range), or you need log aggregation and analysis.\n\n### Vector Databases\n\nStore and search high-dimensional vectors, essential for AI applications like semantic search and recommendations.\n\n\n```mermaid\nflowchart LR\n Query[\"Query: 'comfortable office chair'\"]:::primary\n Embed[Embedding Model]:::orange\n Query --> Embed\n Embed --> Vector[\"[0.2, 0.8, -0.3, ...]\"]:::purple\n\n Vector --> Search[Vector Similarity Search]:::teal\n Search --> R1[\"Chair A (0.92 similarity)\"]:::green\n Search --> R2[\"Chair B (0.87 similarity)\"]:::green\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n\n| Database | Best For |\n|----------|----------|\n| **Pinecone** | Managed vector search, serverless |\n| **Milvus** | Open-source, large-scale |\n| **Weaviate** | Semantic search with modules |\n| **Qdrant** | High-performance, filtering |\n\n\n**Use when:** Building semantic search, recommendation systems, image similarity, or RAG (Retrieval-Augmented Generation) applications.\n\n---\n\n# Choosing the Right Database\n\n### Decision Framework\n\n\n```mermaid\nflowchart TD\n Start[Data Requirements]:::primary\n\n Start --> Q1{Structured data with
relationships and
complex queries?}\n\n Q1 -->|Yes| Relational[Relational
PostgreSQL, MySQL]:::purple\n Q1 -->|No| Q2{Simple key
lookups only?}\n\n Q2 -->|Yes| KV[Key-Value
Redis, DynamoDB]:::teal\n Q2 -->|No| Q3{Hierarchical/
nested documents?}\n\n Q3 -->|Yes| Doc[Document
MongoDB]:::green\n Q3 -->|No| Q4{Massive scale,
high write throughput?}\n\n Q4 -->|Yes| Column[Wide-Column
Cassandra]:::orange\n Q4 -->|No| Q5{Heavy relationship
traversals?}\n\n Q5 -->|Yes| Graph[Graph
Neo4j]:::pink\n Q5 -->|No| Specialized[Check Specialized:
Time-series, Search, Vector]:::red\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef pink fill:#f783ac,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\n### Quick Reference Table\n\n\n| Database Type | Best For | Not Ideal For |\n|---------------|----------|---------------|\n| **Relational** | ACID transactions, complex queries, structured data | Massive scale, schema flexibility |\n| **Key-Value** | Caching, sessions, simple lookups | Complex queries, relationships |\n| **Document** | Flexible schemas, nested data, rapid development | Heavy joins, strict consistency |\n| **Wide-Column** | Massive scale, high writes, time-series | Complex queries, strong consistency |\n| **Graph** | Relationship traversals, social networks | Simple CRUD, massive scale |\n| **Time-Series** | Metrics, IoT, monitoring | General-purpose storage |\n| **Search** | Full-text search, log analysis | Primary data storage |\n| **Vector** | Semantic search, AI embeddings | Traditional queries |\n\n\n### Polyglot Persistence\n\nModern systems often use multiple database types, each for what it does best:\n\n\n```mermaid\nflowchart TB\n App[Application]:::primary\n\n App --> PG[(PostgreSQL
User accounts
Orders)]:::purple\n App --> Redis[(Redis
Sessions
Caching)]:::orange\n App --> Mongo[(MongoDB
Product catalog)]:::green\n App --> ES[(Elasticsearch
Search)]:::teal\n App --> Cassandra[(Cassandra
Event logs)]:::pink\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef pink fill:#f783ac,stroke:#000,color:#000\n```\n\n\nThis approach is called **polyglot persistence**. It adds complexity but lets you use the right tool for each job.\n\n---\n\n# Summary\n\nThe database landscape has evolved from relational-database-for-everything to a rich ecosystem of specialized tools:\n\n- **Relational databases** remain the workhorse for structured data with complex queries and ACID requirements\n- **Key-value stores** provide blazing-fast lookups for caching and simple data\n- **Document databases** offer schema flexibility for evolving applications\n- **Wide-column stores** handle massive scale and high write throughput\n- **Graph databases** excel at relationship-heavy data and traversals\n- **Specialized databases** optimize for specific patterns like time-series, search, and vectors\n\nThe key insight is that database selection is not about finding the \"best\" database. It is about matching the database characteristics to your specific requirements. Often, the answer is multiple databases working together.","pageType":"system-design"}

Database Types

Ashish Pratap Singh

Get Premium