system-design · intermediate

Types of Databases — Matching Engines to Access Patterns

The Central Question

Consider a modern enterprise platform (datalab.com) managing multiple distinct product workloads:

1. Financial Ledger: Executes multi-row balance transfers requiring 100% ACID linearizability.
2. User Session Store: Reads and writes 200,000 active user session tokens per second with sub-millisecond response goals.
3. Product Catalog: Stores 5,000,000 polymorphic items with deeply nested, highly variable JSON attribute schemas.
4. Social Graph Network: Queries 6-degree connection paths across 50,000,000 user relationship edges.
5. IoT Fleet Telemetry: Ingests 1,000,000 metric data points per second from global sensor hardware.

• Complex 6-hop social graph queries require 10-table SQL JOIN operations, taking 15 seconds per request.
• High-frequency IoT telemetry writes cause severe B-Tree index page splitting and disk write amplification.
• Storing polymorphic product attributes forces sparse, 200-column tables filled with empty NULL values.

This lesson answers one central question: How do engineers select and combine specialized database paradigms (Relational, Key-Value, Document, Wide-Column, Graph, Time-Series, Vector) based on query access patterns, data structural relationships, and write/read scale requirements?

The Polyglot Persistence Architecture

Modern platforms adopt Polyglot Persistence—the architectural practice of using distinct, specialized database engines for different service domains within a single unified platform:

flowchart TB
  subgraph Client Ingress Layer
    GW[API Gateway / Edge Proxy]
  end
subgraph Specialized Database Paradigm Tier
    GW -->|1. Financial Transfers| SQL[(Relational DB: PostgreSQL<br/>ACID Transactions)]
    GW -->|2. Fast Session Lookup| KV[(Key-Value Store: Redis<br/>In-Memory Sub-ms)]
    GW -->|3. Dynamic Product Catalog| Doc[(Document Store: MongoDB<br/>Polymorphic JSON)]
    GW -->|4. Metric Telemetry Ingestion| TS[(Time-Series DB: TimescaleDB<br/>Append-Only Time Ranges)]
    GW -->|5. Friend Recommendation| Graph[(Graph DB: Neo4j<br/>Index-Free Adjacency)]
  end

Figure 1: Polyglot persistence architecture routing specialized workload access patterns to custom database engines.

1. Relational Databases (RDBMS)

Relational databases structure data into rigid two-dimensional tables consisting of rows and columns, linked together by foreign key constraints and queried using SQL.

erDiagram
    USERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    USERS {
        bigint id PK
        string email
    }
    ORDERS {
        bigint id PK
        bigint user_id FK
        decimal total_amount
    }
    ORDER_ITEMS {
        bigint id PK
        bigint order_id FK
        string sku
        int quantity
    }

Figure 2: Entity-Relationship diagram illustrating relational schema constraints and foreign key linkages.

Core Characteristics

• Data Model: Structured tables with explicit schemas and primary/foreign keys.
• Storage Engine: Typically B-Tree indexes for fast point and range lookups.
• Transactional Guarantees: Strict multi-row ACID (Atomicity, Consistency, Isolation, Durability).
• Optimal Access Pattern: Structured relational schemas, financial ledgers, transactional ordering systems, complex SQL joins.
• Production Examples: PostgreSQL, MySQL, Oracle, CockroachDB.

2. Key-Value Stores

Key-Value stores operate as giant, distributed hash tables. Every data item is addressed by a unique string key that maps directly to an opaque binary blob or structured primitive.

flowchart LR
  subgraph Memory Index Ring
    Key1["session:usr_9012"] -->|Hash Lookup O(1)| Val1["{ user_id: 9012, role: 'admin' }"]
    Key2["rate_limit:ip_203"] -->|Hash Lookup O(1)| Val2["Counter: 14"]
    Key3["cache:product_402"] -->|Hash Lookup O(1)| Val3["JSON Payload (12 KB)"]
  end

Figure 3: Key-Value hash index providing sub-millisecond $O(1)$ key lookups.

Core Characteristics

• Data Model: Opaque Key $\rightarrow$ Value pairs.
• Storage Engine: In-memory hash maps backed by asynchronous disk snapshotting (RDB) or append logs (AOF).
• Access Speed: $O(1)$ constant time lookup latency ($< 1\text{ms}$).
• Optimal Access Pattern: Session caching, API rate limiting counters, user token validation, temporary leaderboards.
• Production Examples: Redis, Memcached, AWS DynamoDB (in Key-Value mode).

3. Document Databases

Document stores organize data into self-describing, semi-structured documents (typically JSON, BSON, or XML). Unlike relational databases, document databases do not enforce a fixed schema across all records in a collection.

{
  "_id": "prod_99182",
  "name": "Noise Cancelling Headphones",
  "price": 299.99,
  "attributes": {
    "bluetooth": "5.3",
    "battery_hours": 30,
    "color_options": ["black", "silver"]
  },
  "reviews_summary": { "rating": 4.8, "count": 1240 }
}

Core Characteristics

• Data Model: Hierarchical, nested JSON/BSON document collections.
• Schema Model: Schema-on-Read (polymorphic records coexist in the same collection).
• Optimal Access Pattern: E-commerce product catalogs, Content Management Systems (CMS), user profile preferences where fields vary per entity.
• Production Examples: MongoDB, Couchbase, Amazon DocumentDB.

4. Wide-Column (Column-Family) Stores

Wide-Column stores organize data into sparse tables where rows can contain millions of dynamic columns grouped into column families. Data is stored sequentially on disk by column family rather than by row.

flowchart TD
  subgraph Row-Oriented Storage (PostgreSQL)
    R1["Row 1: [ID:101, Name: Alice, Email: a@a.com, Age: 30]"]
    R2["Row 2: [ID:102, Name: Bob, Email: b@b.com, Age: 25]"]
  end
  
  subgraph Column-Oriented Storage (ClickHouse / Cassandra)
    C1["Name Column Block: [Alice, Bob]"]
    C2["Age Column Block: [30, 25]"]
    Note1["Reading average age scans ONLY the Age Column Block on disk!"]
  end

Figure 4: Comparing Row-Oriented storage against Column-Oriented disk layout.

Core Characteristics

• Data Model: Sparse column families indexed by row key and column name.
• Storage Engine: LSM-Tree (Log-Structured Merge-Tree) optimized for high write throughput.
• Optimal Access Pattern: High-volume write ingestion, analytical aggregations (SUM, AVG over billions of rows), user activity tracking logs.
• Production Examples: Apache Cassandra, ScyllaDB, Google Cloud Bigtable, ClickHouse.

5. Graph Databases

Graph databases represent data as Nodes (entities), Edges (relationships), and Properties (attributes). They use Index-Free Adjacency, storing direct pointer references between connected nodes.

flowchart LR
  UserA((User: Alice)) -->|FOLLOWS| UserB((User: Bob))
  UserB -->|PURCHASED| ItemX[Item: Laptop]
  UserA -->|LIVES_IN| CityY[City: Berlin]
  UserB -->|LIVES_IN| CityY
  
  style UserA fill:#d4edda,stroke:#28a745
  style UserB fill:#d4edda,stroke:#28a745
  style ItemX fill:#cce5ff,stroke:#004085

Figure 5: Graph node-edge topology executing pointer traversal for friend recommendations.

Core Characteristics

• Data Model: Nodes, Edges, and Properties graph traversal model.
• Traversal Mechanics: Index-Free Adjacency ($O(1)$ edge pointer traversal without index seeks).
• Optimal Access Pattern: Social networks, fraud detection networks, knowledge graphs, recommendation engines requiring multi-hop path traversal.
• Production Examples: Neo4j, Amazon Neptune, Memgraph.

6. Time-Series Databases

Time-Series databases are specialized engines optimized for handling sequences of values indexed by precise, append-only timestamps.

gantt
    title Time-Series Metric Ingestion Timeline (Timestamp + Values)
    dateFormat ss
    axisFormat %S
    section Server CPU Metric
    12:00:01 (CPU 42%) :active, m1, 01, 02
    12:00:02 (CPU 45%) :active, m2, 02, 03
    12:00:03 (CPU 88%) :crit, m3, 03, 04
    12:00:04 (CPU 91%) :crit, m4, 04, 05

Figure 6: High-frequency sequential timestamp metric stream.

Core Characteristics

• Data Model: (Timestamp, Metric Name, Tag Set, Numeric Value) tuples.
• Data Retention: Built-in downsampling, continuous aggregation, and automated Time-To-Live (TTL) expiration policies.
• Optimal Access Pattern: Infrastructure monitoring (Prometheus metrics), IoT sensor telemetry, stock ticker prices.
• Production Examples: InfluxDB, TimescaleDB, Prometheus.

Comprehensive Database Paradigm Comparison Matrix

Complete Worked Example: DataLab Multi-Engine Routing

Let's inspect how the DataLab platform (datalab.com) routes incoming application queries to specialized database engines based on endpoint requirements:

package main
import (
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"time"
)
type DataLabRouter struct {
	postgresDB PostgresStore // Relational: Transactions
	redisDB    RedisStore    // Key-Value: Sessions
	mongoDB    *MongoStore    // Document: Catalog
}
func (r DataLabRouter) ServeHTTP(w http.ResponseWriter, req http.Request) {
	ctx, cancel := context.WithTimeout(req.Context(), 2*time.Second)
	defer cancel()
switch req.URL.Path {
	case "/v1/session/lookup":
		// 1. Key-Value Access Pattern (< 1ms target)
		token := req.Header.Get("X-Session-Token")
		userSession, err := r.redisDB.GetSession(ctx, token)
		if err != nil {
			http.Error(w, "Session Expired", http.StatusUnauthorized)
			return
		}
		json.NewEncoder(w).Encode(userSession)
case "/v1/finance/transfer":
		// 2. Relational ACID Transaction Access Pattern
		var payload TransferPayload
		json.NewDecoder(req.Body).Decode(&payload)
		err := r.postgresDB.ExecuteACIDTransfer(ctx, payload.FromAcc, payload.ToAcc, payload.Amount)
		if err != nil {
			http.Error(w, "Transaction Aborted", http.StatusConflict)
			return
		}
		w.WriteHeader(http.StatusOK)
case "/v1/catalog/product":
		// 3. Polymorphic Document Access Pattern
		productID := req.URL.Query().Get("id")
		doc, _ := r.mongoDB.GetProductDoc(ctx, productID)
		json.NewEncoder(w).Encode(doc)
	}
}

Failure Modes and Engineering Mitigations

What You Should Remember

1. Match engine to access pattern: Select database types based on data structures (relational tables, key-value maps, JSON trees, graph edges) and read/write QPS demands.
2. Relational for strict ACID: Use relational databases (PostgreSQL) when multi-row ACID transactions and strict financial consistency are mandatory.
3. Key-Value for sub-millisecond lookup: Use key-value stores (Redis) for session tokens, caching, and rate limiting counters.
4. Graph for index-free adjacency: Use graph databases (Neo4j) when queries traverse multi-hop relationships (social graphs, fraud detection).
5. Columnar for analytics: Use wide-column/columnar stores (ClickHouse, Cassandra) for high-write telemetry ingestion and aggregate reporting queries.

Glossary of Terms

Practice Scenario and Self-Assessment

Architecture Scenario

1. User Accounts & Payments (Requires strict ACID consistency)
2. Driver GPS Location Telemetry (100,000 writes/sec append-only stream)
3. Active Driver-Rider Matching Graph (Requires multi-hop geographic proximity traversals)

1. Select the optimal database paradigm for each of the three workloads and justify your choices.
2. Formulate the architectural trade-offs of using a single PostgreSQL database versus a polyglot persistence design.

Interactive Self-Assessment

Graph databases use Index-Free Adjacency pointer hops (O(1)), bypassing expensive multi-table SQL index joins.

Graph databases run exclusively on quantum supercomputers.

B-Tree indexes are faster than memory pointer traversals.

Graph databases eliminate HTTPS TLS network handshakes.

Column-oriented storage reads only the 'age' column disk blocks, avoiding scanning un-needed row attributes.

Row-oriented storage compresses text data 10x more efficiently than column storage.

Column storage eliminates the need for SQL query syntax.

Column storage requires zero RAM memory to execute aggregations.

What to Learn Next

• Database Storage Architectures — B-Trees vs LSM-Trees: Discover the low-level disk layouts powering database engines.
• Database Scaling Strategies — Replicas, Shards, and Multiplexing: Learn how to scale database read and write capacity.
• SQL vs NoSQL — Trade-offs and Decision Frameworks: Revisit foundational relational versus non-relational trade-offs.

Track: Data, Storage and Messaging

Previous: SQL vs NoSQL — Choosing a Data Store

By Shubham Jain

All articles · Study paths

Shubham Jain · Learning Lab