Free lesson · System Design Fundamentals

Database Types

Medium12 min readsystem-designhlddistributed-systems

Lesson 12.1 - Database Types

The first lesson of Module 12.

"Which database should we use?" is simultaneously the most asked and worst answered question in system design. Engineers default to what they know - "use PostgreSQL" if they're a relational person, "use MongoDB" if they came from Node.js - without evaluating whether the database's strengths match the workload's requirements. The result: a relational database shoehorned into a workload that needs flexible schemas and horizontal scaling, or a document database struggling to enforce the relationships and transactions the application requires.

The truth is: there's no best database. There are eight major database categories, each optimized for a specific access pattern. Choosing the right one requires understanding what each category does well, what it does poorly, and what your workload actually needs. This lesson covers all eight categories, with concrete examples and a decision framework for choosing between them.

The Eight Database Categories

flowchart TD
    subgraph Relational["Relational (SQL)"]
        R["Tables with rows and columns\nStrong schema enforcement\nJOINs across tables\nACID transactions\n\nPostgreSQL, MySQL, SQL Server"]
    end
    
    subgraph Document["Document"]
        D["Flexible JSON/BSON documents\nNested data, no joins needed\nSchema-per-document\n\nMongoDB, CouchDB, Firestore"]
    end
    
    subgraph KV["Key-Value"]
        K["Simple key → value pairs\nFastest reads (O(1) lookup)\nNo queries beyond key lookup\n\nRedis, Memcached, DynamoDB*"]
    end
    
    subgraph WideColumn["Wide-Column"]
        W["Rows with dynamic columns\nColumn families for grouping\nDesigned for massive scale\n\nCassandra, HBase, Bigtable"]
    end
    
    subgraph Graph["Graph"]
        G["Nodes and edges (relationships)\nTraverse connections efficiently\nPattern matching queries\n\nNeo4j, Amazon Neptune, TigerGraph"]
    end
    
    subgraph TimeSeries["Time-Series"]
        T["Append-heavy, time-indexed\nAggregation over time windows\nAutomatic data retention/rollup\n\nInfluxDB, TimescaleDB, Prometheus"]
    end
    
    subgraph Search["Search / Full-Text"]
        S["Inverted indexes for text search\nFuzzy matching, relevance scoring\nFaceted search, aggregations\n\nElasticsearch, Solr, Meilisearch"]
    end
    
    subgraph Vector["Vector"]
        V["High-dimensional embeddings\nSemantic similarity search\nANN (Approximate Nearest Neighbor)\n\nPinecone, Weaviate, pgvector"]
    end

    style Relational fill:#34d399,stroke:#10b981,color:#000
    style Document fill:#fbbf24,stroke:#f59e0b,color:#000
    style KV fill:#a78bfa,stroke:#8b5cf6,color:#000
    style WideColumn fill:#fb7185,stroke:#f43f5e,color:#000

A diagram showing the eight major database categories. Relational databases store data in tables with rows and columns, enforce schemas strictly, support joins across tables, and provide ACID transactions. Examples: PostgreSQL, MySQL, SQL Server. Document databases store flexible JSON or BSON documents, support nested data without joins, and allow schema-per-document flexibility. Examples: MongoDB, CouchDB, Firestore. Key-value databases store simple key-to-value pairs with O(1) lookup - the fastest reads possible but no query capability beyond looking up by key. Examples: Redis, Memcached, DynamoDB in key-value mode. Wide-column databases store rows with dynamic columns grouped into column families, designed for massive horizontal scale across many nodes. Examples: Cassandra, HBase, Google Bigtable. Graph databases store nodes (entities) and edges (relationships), enabling efficient traversal of connections and pattern matching queries. Examples: Neo4j, Amazon Neptune, TigerGraph. Time-series databases are optimized for append-heavy, time-indexed data with efficient aggregation over time windows and automatic data retention policies. Examples: InfluxDB, TimescaleDB, Prometheus. Search databases use inverted indexes for full-text search with fuzzy matching, relevance scoring, and faceted search. Examples: Elasticsearch, Solr, Meilisearch. Vector databases store high-dimensional embeddings and support semantic similarity search using approximate nearest neighbor algorithms. Examples: Pinecone, Weaviate, pgvector. Each category is optimized for a specific access pattern. Choosing the right one requires matching the database's strengths to your workload's primary access pattern.

Each Category in Depth

Relational Databases (PostgreSQL, MySQL, SQL Server)

The workhorse of the software industry for 40+ years. Data is organized into tables (relations) with predefined schemas (columns with types). Rows represent records. Columns represent attributes. Relationships between tables are expressed through foreign keys and queried using JOINs.

What relational databases do best:

• Complex queries. SQL is incredibly expressive - JOINs across 5 tables, aggregations with GROUP BY, subqueries, window functions, CTEs. No other database category matches SQL's query flexibility.
• ACID transactions (12.3). Multiple operations (debit Account A, credit Account B) as an atomic unit - either all succeed or none do. Essential for financial systems, inventory management, and any workload where partial updates corrupt data.
• Data integrity. Schemas enforce types, NOT NULL constraints, unique constraints, foreign key relationships, and check constraints. The database prevents invalid data from entering - the application doesn't need to validate everything itself.
• Mature ecosystem. 40 years of tooling: ORMs (Hibernate, SQLAlchemy, Prisma), admin tools (pgAdmin, DBeaver), monitoring (pg_stat_statements), backup/restore, replication, and an enormous community.

What relational databases struggle with:

• Horizontal scaling. Sharding a relational database is hard - cross-shard JOINs become impossible or extremely expensive, foreign keys can't span shards, and distributed transactions add latency. Relational databases scale vertically (bigger machine) far more naturally than horizontally (more machines).
• Schema rigidity. Adding a column to a 500-million-row table requires an ALTER TABLE that can take minutes to hours (depending on the database). Schema changes in fast-moving applications create deployment friction.
• Hierarchical or nested data. Representing a deeply nested JSON structure (a product with variants, each with options, each with prices by region) in relational tables requires multiple JOINs. Document databases handle this natively.

When to choose relational: your workload has structured data with relationships between entities, needs complex queries (JOINs, aggregations), requires ACID transactions, and fits on a single machine (or a small number of read replicas). This is the default choice - start with PostgreSQL unless you have a specific reason not to. Most applications fit comfortably in a relational database.

Document Databases (MongoDB, CouchDB, Firestore)

Data is stored as documents - self-contained JSON (or BSON) objects that can have nested fields, arrays, and varying structures. Each document in a collection can have a different schema - one user document might have an address field; another might not.

What document databases do best:

• Flexible schemas. No ALTER TABLE to add a field - just start writing documents with the new field. Old documents don't need to change. This agility is valuable for rapidly evolving data models.
• Nested data. A user document contains their orders, each order contains its items, each item contains its product details - all in one document. One read retrieves everything, no JOINs needed. This matches how applications often think about data - as self-contained objects, not normalized tables.
• Horizontal scaling. MongoDB, CouchDB, and Firestore are designed for horizontal partitioning (sharding) - distribute documents across nodes by a shard key. Reads and writes for a single document hit one node - no cross-node coordination.

What document databases struggle with:

• Cross-document relationships. If User 42's order references Product 99, and you need to join user data with product data, document databases don't have JOINs. You either embed the product data in the order (denormalization - data duplication) or make a second query (application-level join). Denormalization works until the duplicated data changes and you must update every copy.
• Multi-document transactions. MongoDB added multi-document ACID transactions in version 4.0 (2018), but they're slower and more limited than relational transactions. If your workload requires frequent multi-record atomic operations, relational databases handle this more naturally.
• Complex aggregations. MongoDB's aggregation pipeline is powerful but less expressive and harder to learn than SQL. Complex analytical queries are better served by SQL.

When to choose document: your data is naturally hierarchical (user profiles with nested preferences, product catalogs with nested variants), schemas change frequently, and you rarely need cross-document JOINs or multi-document transactions.

Key-Value Databases (Redis, Memcached, DynamoDB*)

The simplest data model: a key (string) maps to a value (string, number, binary blob, or in Redis's case, rich data structures like lists, sets, sorted sets, and hashes). The primary operation is: given a key, return the value - O(1) lookup, the fastest possible read.

When to choose key-value: caching (M10-M11), session storage, configuration/feature flags, real-time leaderboards (Redis sorted sets), rate limiting counters (Redis INCR with EXPIRE), and any workload where access is primarily "get by key" with no query flexibility needed.

*DynamoDB is listed here with an asterisk because it's technically a "key-value and document" database - it supports both simple key-value access and richer document-style queries with secondary indexes. At its core, it's a key-value store with optional document features.

Wide-Column Databases (Cassandra, HBase, Bigtable)

Data is stored in rows identified by a partition key, with columns that can vary per row - you don't define all columns upfront. Columns are grouped into column families. Designed for massive horizontal scale - Cassandra clusters at Apple handle petabytes of data across thousands of nodes.

When to choose wide-column: write-heavy workloads at extreme scale (IoT sensor data, event logging, user activity tracking), time-series-like access patterns with a partition key (user_id + date), and workloads that need multi-datacenter replication with tunable consistency (Cassandra's tunable quorum reads/writes).

Graph Databases (Neo4j, Neptune, TigerGraph)

Data is stored as nodes (entities) and edges (relationships between entities). Queries traverse the graph - "find all friends of friends of Alice who work at Google and live in San Francisco." Relational databases can do this with recursive JOINs, but performance degrades rapidly with depth. Graph databases are optimized for deep traversals - they're fast regardless of how deep the relationship chain goes.

When to choose graph: social networks (friend recommendations, mutual connections), fraud detection (discovering suspicious transaction patterns), knowledge graphs (connected entities with rich relationships), recommendation engines based on user-item-user paths, and access control (role → permission → resource hierarchies).

Time-Series Databases (InfluxDB, TimescaleDB, Prometheus)

Optimized for append-heavy, time-indexed data - metrics, sensor readings, financial tick data, application logs. Data is written in time order and queried by time ranges ("show CPU usage for the last 24 hours, aggregated by minute"). Time-series databases compress sequential timestamps efficiently and support automatic data retention (delete data older than 90 days) and rollup (aggregate minute-level data into hour-level after 7 days).

When to choose time-series: monitoring and observability (M43 - Prometheus is a time-series database), IoT sensor data (temperature readings every second from 10,000 sensors), financial market data (trade executions with microsecond timestamps), and any workload where data is primarily appended in time order and queried by time range.

Search Databases (Elasticsearch, Solr, Meilisearch)

Built on inverted indexes - a data structure that maps every word to the documents containing it. Given a search query "wireless bluetooth headphones," the inverted index finds all documents containing those words in milliseconds, ranks them by relevance (TF-IDF, BM25), and returns the top results. Search databases also support fuzzy matching (handling typos), faceted search (filter by category, price range, brand), autocomplete, and highlighting.

When to choose search: full-text search across documents (product search, article search, log search), log analytics (the ELK stack - Elasticsearch + Logstash + Kibana), and any workload where users search by text and expect relevance-ranked, typo-tolerant results. Elasticsearch is NOT a primary database - it's a secondary index. Store your data in a primary database (PostgreSQL); replicate to Elasticsearch for search queries (via CDC - M9.4).

Vector Databases (Pinecone, Weaviate, Qdrant, pgvector)

Store high-dimensional vectors (embeddings) and support semantic similarity search - finding the vectors most similar to a query vector. Covered in depth in A10.5 (RAG and Vector Search). Use for: recommendation systems, semantic search, RAG pipelines for LLMs, image similarity, and anomaly detection.

The Decision Framework

flowchart TD
    Start["What's your primary\naccess pattern?"]
    
    Q1{"Need complex JOINs\nor ACID transactions?"}
    Q2{"Data is hierarchical/\nnested with flexible schema?"}
    Q3{"Primary access is\nget-by-key?"}
    Q4{"Relationships ARE\nthe data?"}
    Q5{"Time-indexed append-\nheavy data?"}
    Q6{"Full-text search\nwith relevance?"}
    Q7{"Semantic similarity\nsearch?"}
    Q8{"Extreme write scale\nacross many nodes?"}
    
    Rel["Relational\n(PostgreSQL)"]
    Doc["Document\n(MongoDB)"]
    KVa["Key-Value\n(Redis / DynamoDB)"]
    Gra["Graph\n(Neo4j)"]
    TS["Time-Series\n(InfluxDB)"]
    Sea["Search\n(Elasticsearch)"]
    Vec["Vector\n(Pinecone)"]
    WC["Wide-Column\n(Cassandra)"]
    
    Start --> Q1
    Q1 -->|"Yes"| Rel
    Q1 -->|"No"| Q2
    Q2 -->|"Yes"| Doc
    Q2 -->|"No"| Q3
    Q3 -->|"Yes"| KVa
    Q3 -->|"No"| Q4
    Q4 -->|"Yes"| Gra
    Q4 -->|"No"| Q5
    Q5 -->|"Yes"| TS
    Q5 -->|"No"| Q6
    Q6 -->|"Yes"| Sea
    Q6 -->|"No"| Q7
    Q7 -->|"Yes"| Vec
    Q7 -->|"No"| Q8
    Q8 -->|"Yes"| WC
    Q8 -->|"No"| Rel

    style Rel fill:#34d399,stroke:#10b981,color:#000
    style Doc fill:#fbbf24,stroke:#f59e0b,color:#000

A decision tree for choosing a database type. Start with the primary access pattern. Do you need complex JOINs or ACID transactions? If yes, use a relational database like PostgreSQL - it's the default choice and handles most workloads well. If no, is your data hierarchical or nested with flexible schemas? If yes, consider a document database like MongoDB. If no, is your primary access pattern get-by-key? If yes, use a key-value store like Redis or DynamoDB. If no, are relationships the core of your data (social graph, fraud patterns)? If yes, use a graph database like Neo4j. If no, is your data time-indexed and append-heavy (metrics, sensors)? If yes, use a time-series database like InfluxDB. If no, do you need full-text search with relevance ranking? If yes, use Elasticsearch. If no, do you need semantic similarity search (embeddings)? If yes, use a vector database. If no, do you need extreme write scale across many nodes? If yes, use a wide-column database like Cassandra. If nothing else fits, default to relational (PostgreSQL). The key insight: most applications should start with PostgreSQL. Only choose a specialized database when you have a specific access pattern that PostgreSQL handles poorly - and even then, PostgreSQL might be sufficient with the right schema and indexing.

The Polyglot Persistence Reality

Most production systems don't use one database - they use several, each for what it does best:

This is polyglot persistence - using the right database for each workload. The primary database (usually PostgreSQL) is the source of truth. Secondary databases (Elasticsearch, Redis, Neo4j) are specialized indexes kept in sync via CDC (M9.4) or application-level dual-writes.

The warning: every additional database adds operational complexity - another system to monitor, backup, scale, secure, and maintain. Don't add a specialized database until the primary database genuinely can't serve the workload. PostgreSQL with the right indexes, caching (M10-M11), and read replicas handles most workloads that engineers prematurely split across multiple databases.

A Brandon Story: The Database That Wasn't Right

A team I'd advised chose MongoDB for their e-commerce platform because "it's flexible and web-scale." Their data model: users, products, orders, payments, inventory. Classic relational data - entities with relationships, transactions required (decrement inventory atomically when an order is placed), and complex queries needed (sales reports joining orders with products and users).

For the first year, it worked - MongoDB handled the CRUD operations, and the team used application-level joins (query users, then query their orders, then query the products in those orders). But as the application grew, three problems emerged:

1. Application-level joins were slow. A sales report needed to join orders, products, and users. In PostgreSQL: one SQL query with two JOINs, 50ms. In MongoDB: three sequential queries plus in-memory merging in the application, 800ms. The application was doing the relational database's job - poorly.

2. Data integrity was fragile. Without foreign keys, the application was responsible for ensuring every product_id in an order actually referenced a real product. A bug in the product-deletion code deleted products that had active orders referencing them. The orders now referenced nonexistent products - broken data that a foreign key constraint would have prevented.

3. Inventory transactions were unreliable. The checkout flow needed to atomically: decrement inventory, create the order, and charge the payment. MongoDB's multi-document transactions (added in v4.0) were available but slower and less reliable than PostgreSQL's native transactions. Under high concurrency, the team saw occasional inventory oversells - two users buying the last item simultaneously.

The migration: they migrated to PostgreSQL. The sales report went from 800ms (3 queries + app-level join) to 45ms (one SQL query). Foreign keys prevented the orphaned-product bug. Native transactions eliminated the inventory oversell issue. The migration took 3 months and was the most expensive technical decision of their first two years - not because PostgreSQL was complicated, but because migrating a running application's database is always painful.

Lessons:

• "Flexible schema" sounds great until your data is actually relational. If your entities have relationships and you need JOINs and transactions, use a relational database.
• Application-level joins are a code smell - they mean your application is reimplementing what a database engine does natively, but worse.
• Foreign keys are not "old-fashioned constraints" - they're data integrity guarantees that prevent classes of bugs entirely.
• MongoDB is excellent for hierarchical, self-contained documents (CMS content, user profiles with nested preferences, event logs). It's poor for relational data with cross-entity transactions.
• Start with PostgreSQL. Migrate to a specialized database only when you've demonstrated that PostgreSQL can't serve your specific workload - not because a blog post said MongoDB is more modern.

Six Takeaways from This Lesson

1. Eight database categories, each optimized for a specific access pattern: relational (JOINs, ACID), document (flexible nested data), key-value (fast lookup), wide-column (write scale), graph (relationships), time-series (time-indexed), search (full-text), vector (similarity).

2. PostgreSQL is the default choice. Start here. Only choose a specialized database when you have a specific access pattern PostgreSQL handles poorly - and you've verified that with measurement.

3. The decision is driven by access pattern, not by marketing ("web scale," "modern," "flexible"). What are the primary queries? Do you need JOINs? Transactions? Full-text search? That determines the database.

4. Polyglot persistence - most production systems use multiple databases. PostgreSQL for transactions, Redis for caching, Elasticsearch for search. Each does what it does best.

5. Every additional database adds operational complexity. Don't add one until the primary database genuinely can't serve the workload.

6. Choosing the wrong database is expensive. Database migrations in running applications take months and introduce risk. Choose carefully upfront; validate the choice against your actual workload before committing.

Try It

Exercise: Warmup (15 minutes). For three applications you use daily (Instagram, Uber, Spotify), guess which database types they might use for different workloads. User accounts? Content feed? Search? Messaging? Map locations?

Exercise: Core (30 minutes). Design the database architecture for a social media platform. Identify: user profiles (which DB?), posts (which DB?), follower graph (which DB?), post search (which DB?), analytics/metrics (which DB?), direct messages (which DB?). Justify each choice using the decision framework.

Exercise: Stretch (45 minutes). Read Uber's blog post "Schemaless: Uber Engineering's Trip Database" (link below) - Uber built a custom database layer on top of MySQL. Why didn't they use PostgreSQL? Why not MongoDB? What access pattern drove their choice?

What's Next

Lesson 12.2 dives into the SQL vs NoSQL distinction - what actually separates them, why "NoSQL" is a misleading category name, and when each is genuinely the right choice.

Back to System Design Fundamentals