Database Design for Scalability: SQL, NoSQL, and Polyglot Persistence

TLDR: There is no "best database." Each type optimises for a specific access pattern. SQL wins for complex queries and ACID transactions. Document stores win for flexible schema and nested data. Key-value stores win for speed. Time-series databases win for metrics. Graph databases win for traversals. Production systems use several simultaneously — polyglot persistence. Choose based on access pattern, not hype.

This is the final post in the Phase 2 HLD series. We covered API Design at Scale in the previous post — your services have APIs. Now let's design what they read from and write to.

Part 1: Why Database Choice Is an Architectural Decision

Most code can be refactored. A wrong database choice can't be refactored in an afternoon.

Changing a database:
  ├─ Migrate all existing data (terabytes?)
  ├─ Rewrite all data access code
  ├─ Change the data model (from relational to document?)
  ├─ Handle dual-write during migration (consistency window)
  └─ Coordinate with all downstream consumers

Cost: Months of engineering time, high risk, outage potential.

The access pattern, consistency requirement, and scale you design for today determine which databases make sense. Getting this wrong is expensive.

Polyglot Persistence in Action

Real-world e-commerce platform might use:

Part 2: Relational Databases (SQL) — The Default

What Makes SQL, SQL

Relational databases store data in tables with rows and columns. Relationships are expressed via foreign keys and joined at query time.

-- Schema (normalized)
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  total DECIMAL(10, 2),
  status VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders(id),
  product_id UUID,
  quantity INT,
  unit_price DECIMAL(10, 2)
);

-- Retrieve orders with line items in one query
SELECT o.id, o.total, o.status, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 'user-123'
ORDER BY o.created_at DESC;

When SQL Wins

ACID transactions across tables:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
  INSERT INTO audit_log (event, amount) VALUES ('transfer', 100);
COMMIT;
-- Either all three happen, or none. Money never disappears.

No other database type gives you this guarantee across multiple entities without significant complexity.

Ad-hoc queries and reporting:

-- Business analytics impossible in key-value stores
SELECT
  date_trunc('month', created_at) AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value
FROM orders
WHERE status = 'completed'
  AND created_at >= NOW() - INTERVAL '12 months'
GROUP BY month
ORDER BY month DESC;

Schema enforcement: SQL enforces types, constraints, foreign keys at the database level. Invalid data cannot be inserted.

SQL Limitations

Vertical scaling ceiling:

Single machine limits:
  CPU: ~128 cores
  RAM: ~8TB
  Disk: ~100TB NVMe
  Connections: ~10K concurrent

Beyond this → sharding (complex) or read replicas (reads only).

Schema rigidity:

-- Adding a column to a 500M-row table:
ALTER TABLE users ADD COLUMN preferences JSONB;
-- Locks table for minutes on Postgres (before Postgres 11)
-- Zero-downtime requires: add nullable first, backfill, add NOT NULL

Joins at scale are expensive:

-- Works at 10K rows:
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

-- At 100M users + 1B orders: full table scan, minutes of execution
-- Requires: indexes on user_id, query optimisation, materialised views

When to Use SQL

• Complex queries with JOINs and aggregations
• ACID transactions across multiple tables
• Financial data, ledgers, inventory with strict consistency
• Small-to-medium data (< 1TB) on a single node
• Standard CRUD apps where schema is known upfront

Best choices: PostgreSQL (most feature-complete), MySQL (widest ecosystem), Amazon Aurora (managed, MySQL/Postgres compatible).

Part 3: Document Stores — Flexible Schema

What Document Stores Are

Document databases store data as self-contained JSON (or BSON) documents. No schema required. Related data is embedded rather than joined.

// One document = one order (MongoDB)
{
  "_id": "order-456",
  "user": {
    "id": "user-123",
    "name": "Alice",
    "email": "alice@example.com"
  },
  "items": [
    {"product_id": "p-1", "name": "Widget A", "qty": 2, "price": 9.99},
    {"product_id": "p-2", "name": "Widget B", "qty": 1, "price": 24.99}
  ],
  "shipping_address": {
    "street": "123 Main St",
    "city": "Bengaluru",
    "country": "IN"
  },
  "status": "shipped",
  "total": 44.97,
  "created_at": "2026-07-01T10:00:00Z"
}

No JOINs. One document fetch returns the complete order with items and user info.

When Document Stores Win

Varied or evolving schema:

// Product catalog — each product has different attributes
{"_id": "laptop-1", "name": "...", "ram": "16GB", "cpu": "M3", "ports": ["USB-C", "HDMI"]}
{"_id": "shirt-1", "name": "...", "size": "L", "color": "blue", "material": "cotton"}
{"_id": "book-1", "name": "...", "isbn": "...", "author": "...", "pages": 320}

SQL requires either a wide table (mostly NULLs) or an EAV anti-pattern. Documents handle this naturally.

High read throughput on embedded data:

SQL: Fetch order + items + user info
  → 2-3 JOINs or 3 separate queries

MongoDB: Fetch order document
  → Single document read (embedded items + user snapshot)
  → Faster, simpler, no JOIN overhead

Hierarchical / nested data:

// Comment thread with nested replies
{
  "_id": "post-1",
  "comments": [
    {
      "id": "c1", "author": "alice", "text": "Great post!",
      "replies": [
        {"id": "c1r1", "author": "bob", "text": "Agreed!"}
      ]
    }
  ]
}

SQL needs a recursive CTE or a separate table with a parent_id. Documents make this native.

Document Store Limitations

No transactions across documents (MongoDB < 4.0):

Update order status AND decrement inventory:

SQL:  One transaction, atomic ✓

MongoDB pre-4.0:
  ├─ Update order document
  ├─ Update inventory document
  └─ Network failure between them → inconsistent ✗

MongoDB 4.0+ has multi-document transactions, but with a performance cost.

Duplication is the trade-off:

// User's name embedded in every order document
{"order_id": "1", "user": {"name": "Alice", ...}}
{"order_id": "2", "user": {"name": "Alice", ...}}
{"order_id": "3", "user": {"name": "Alice", ...}}

User changes name → must update thousands of order documents.
In SQL: update one row in users table.

Poor for ad-hoc queries across documents:

// Aggregation across documents requires pipeline stages
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$user.id", total_spent: { $sum: "$total" } } },
  { $sort: { total_spent: -1 } },
  { $limit: 10 }
]);
// Works, but complex. SQL GROUP BY is simpler.

When to Use Document Stores

• Product catalogs with variable attributes
• Content management systems (articles, pages, metadata)
• User profiles with optional fields
• Hierarchical / nested data (comments, configurations)
• Rapid iteration (schema evolves as product evolves)

Best choices: MongoDB, Amazon DocumentDB, Couchbase.

Part 4: Key-Value Stores — Speed First

What Key-Value Stores Are

The simplest data model: a hash map. Given a key, get a value. Nothing else.

SET user:123:session "eyJhbGciOiJIUzI1NiJ9..."  EX 3600
GET user:123:session → "eyJhbGciOiJIUzI1NiJ9..."

SET rate_limit:api-key-xyz 0
INCR rate_limit:api-key-xyz → 1
EXPIRE rate_limit:api-key-xyz 60

HSET product:456 name "Widget A" price "9.99" stock "50"
HGET product:456 price → "9.99"

Operations are O(1). Latency is sub-millisecond. Throughput is millions of operations per second.

When Key-Value Stores Win

Session management:

User logs in:
  SET session:{session_id} {user_id, roles, expiry} EX 1800
  
User makes request:
  GET session:{session_id} → user context in <1ms
  
User logs out:
  DEL session:{session_id}
  
No DB query per request. Redis handles millions of sessions.

Rate limiting:

-- Redis Lua script (atomic rate limit check)
local key = KEYS[1]
local limit = tonumber(ARGV[1])
local window = tonumber(ARGV[2])

local current = redis.call('INCR', key)
if current == 1 then
  redis.call('EXPIRE', key, window)
end

if current > limit then
  return 0  -- rate limited
else
  return 1  -- allowed
end

Leaderboards with sorted sets:

ZADD leaderboard 1000 "user:alice"
ZADD leaderboard 850 "user:bob"
ZADD leaderboard 1200 "user:charlie"

ZREVRANGE leaderboard 0 9 WITHSCORES
→ charlie:1200, alice:1000, bob:850

ZREVRANK leaderboard "user:alice" → 1 (0-indexed, so rank 2)

Key-Value Limitations

No query flexibility:

SQL: SELECT * FROM users WHERE city = 'Bengaluru' AND age > 25
Redis: ??? (can't query by value — only by exact key)

Workaround: Maintain secondary indexes manually
  SADD city:bengaluru user:123 user:456
  SADD age:25 user:123

  But: you maintain these yourself, they can drift.

Value size limits: Redis values max at 512MB. Not suitable for large blobs.

No relationships: Key-value stores don't know that user:123 and order:456 are related. You manage relationships in application code.

When to Use Key-Value Stores

• Session management
• Caching (query results, computed values, API responses)
• Rate limiting and quota counters
• Real-time leaderboards
• Feature flags
• Distributed locks (SETNX pattern)

Best choices: Redis (richest data structures), DynamoDB (serverless, scales to any size), Memcached (pure cache, simpler).

Part 5: Time-Series Databases — Metrics at Scale

What Time-Series Databases Are

Optimised for sequential, timestamped data. Write-heavy, time-ordered, typically append-only.

-- TimescaleDB (Postgres extension):
SELECT
  time_bucket('1 minute', time) AS minute,
  avg(cpu_usage) AS avg_cpu,
  max(memory_usage) AS peak_memory
FROM server_metrics
WHERE server_id = 'prod-api-1'
  AND time >= NOW() - INTERVAL '1 hour'
GROUP BY minute
ORDER BY minute;

Why Time-Series DBs Outperform SQL for Metrics

Inserting 1M rows/second of metrics:

PostgreSQL:
  ├─ Row-oriented storage (all columns per row written together)
  ├─ B-tree index updates on every insert
  └─ At 1M rows/s: disk IO saturated, latency spikes

InfluxDB / TimescaleDB:
  ├─ Column-oriented storage (each metric compressed separately)
  ├─ Time-based partitioning (only recent data actively written)
  ├─ Automatic data retention (old data compressed or deleted)
  └─ At 1M rows/s: designed for this workload

Storage compression example:

CPU metric values: [78, 79, 79, 80, 80, 80, 79, 78]

SQL row store:  8 rows × 16 bytes = 128 bytes
InfluxDB delta compression: Δ values [+1, 0, +1, 0, 0, -1, -1]
  → ~20 bytes (6x compression typical for metrics)

When to Use Time-Series Databases

• Application performance monitoring (CPU, memory, latency)
• Business metrics (DAU, revenue, funnel rates)
• IoT sensor data
• Financial tick data (stock prices)
• Any data where the primary query is "show me this metric over time"

Best choices: InfluxDB (purpose-built), TimescaleDB (Postgres + time-series), Prometheus (metrics + alerting), Amazon Timestream (managed).

Part 6: Graph Databases — Relationship Traversals

What Graph Databases Are

Data is stored as nodes (entities) and edges (relationships). Optimised for traversing connections.

// Neo4j Cypher query: "Find products bought by people who bought what Alice bought"
MATCH (alice:User {name: "Alice"})-[:PURCHASED]->(p:Product)
      <-[:PURCHASED]-(other:User)-[:PURCHASED]->(rec:Product)
WHERE NOT (alice)-[:PURCHASED]->(rec)
RETURN rec.name, COUNT(other) AS frequency
ORDER BY frequency DESC
LIMIT 10;

In SQL, this is a self-join on a bridge table — complex and slow at scale. In Neo4j, graph traversal is native and fast even with billions of relationships.

When Graph Databases Win

Use case            SQL approach            Graph approach
─────────────────────────────────────────────────────────
Fraud detection     50-table JOINs          Traverse shared nodes
Social network      Recursive CTEs          Native traversal
Recommendation      Collaborative filter    Pattern matching
Access control      Role hierarchy JOINs    Permission paths
Knowledge graphs    EAV anti-pattern        Property graphs

Fraud detection example:

// Find accounts sharing IPs with known fraudulent accounts
MATCH (fraud:Account {is_fraud: true})-[:USED_IP]->(ip:IP)
      <-[:USED_IP]-(suspect:Account)
WHERE suspect.is_fraud IS NULL
RETURN suspect.id, COUNT(ip) AS shared_ips
ORDER BY shared_ips DESC;

SQL equivalent requires: join accounts → ip_usage → accounts, filter, deduplicate. With millions of accounts, it's impractical.

When to Use Graph Databases

• Social networks (followers, connections, recommendations)
• Fraud detection (shared identifiers, behaviour patterns)
• Knowledge graphs
• Access control hierarchies (RBAC with nested roles)
• Network topology (dependency graphs, infrastructure maps)

Best choices: Neo4j (most mature), Amazon Neptune (managed), TigerGraph (performance at scale).

Part 7: Polyglot Persistence — Using Multiple Databases

Real production systems use multiple databases, each for what it does best.

Example: E-Commerce Platform

Feature                  Database              Why
──────────────────────────────────────────────────────────────
User accounts            PostgreSQL            ACID, joins, reporting
Orders & payments        PostgreSQL            ACID transactions required
Product catalog          MongoDB               Variable attributes per category
Product search           Elasticsearch         Full-text, faceted search
Sessions / auth tokens   Redis                 Sub-ms reads, TTL, no SQL needed
Rate limiting            Redis                 Atomic INCR, EXPIRE
Recommendation engine    MongoDB or Neo4j      Graph traversal or flexible docs
Metrics / analytics      InfluxDB              Time-series, write-heavy
Audit logs               Elasticsearch         Full-text search, retention policies

How Services Own Their Databases

In a microservices architecture, each service owns its database. No sharing.

Order Service          → PostgreSQL (orders, order_items)
User Service           → PostgreSQL (users, auth)
Product Service        → MongoDB (product catalog)
Search Service         → Elasticsearch (search index, populated via events)
Session Service        → Redis (sessions, rate limits)
Analytics Service      → InfluxDB + data warehouse (metrics, reporting)

Event flow:
  ProductUpdated event
    → Search Service updates Elasticsearch index
    → Analytics Service updates product view counters

Each service: full autonomy over its store.
No other service can write to its database directly.

Part 8: Sharding vs. Replication

When a single database node can't handle the load, you scale out.

Replication (Scale Reads)

Copy data to multiple nodes. Reads can go to any replica. Writes still go to the primary.

┌──────────────┐
│   Primary    │  ← All writes
│   (leader)   │
└──────┬───────┘
       │ Replication
  ┌────┴────┐
  ▼         ▼
┌──────┐  ┌──────┐
│Repli-│  │Repli-│  ← Reads distributed here
│ca 1  │  │ca 2  │
└──────┘  └──────┘

Result:
  ├─ Write throughput: unchanged (still 1 primary)
  ├─ Read throughput: 3x (3 nodes)
  └─ Availability: primary fails → replica promoted

Replication lag:

Primary writes: user.email = "alice@new.com" at T=0
Replica 1 receives: at T=5ms
Replica 2 receives: at T=8ms

Client reads from Replica 2 at T=6ms:
  → Gets old email (replication lag)
  → Stale read for 2ms

Fix: Read from primary for strong consistency
     Read from replica for eventual consistency (acceptable for most reads)

Sharding (Scale Writes)

Distribute data across multiple primary nodes. Each node owns a subset of the data.

Sharding by user_id:

Shard 1: user_id 0000000 – 3333333   (Node A)
Shard 2: user_id 3333334 – 6666666   (Node B)
Shard 3: user_id 6666667 – 9999999   (Node C)

Request for user_id = 5000000:
  → Shard router: falls in Shard 2 → route to Node B

Result:
  ├─ Write throughput: 3x (3 primaries)
  ├─ Storage: 3x (data distributed)
  └─ Complexity: high (cross-shard queries, rebalancing)

Sharding strategies:

Range sharding:
  ├─ user_id 0–3M → Shard 1
  └─ Hotspot risk: new users all go to Shard 3 (write imbalance)

Hash sharding:
  ├─ shard = hash(user_id) % num_shards
  └─ Uniform distribution, but range queries span shards

Directory sharding:
  ├─ Lookup table: user_id → shard_id
  └─ Flexible, but lookup table is a bottleneck

Consistent hashing (best for dynamic clusters):
  ├─ Hash ring: nodes placed at positions on a ring
  ├─ Key hashed → nearest node clockwise
  └─ Adding/removing nodes: minimal data movement

Cross-shard queries are painful:

-- "Top 10 users by order count" in a sharded system:

SELECT user_id, COUNT(*) as orders
FROM orders
GROUP BY user_id
ORDER BY orders DESC
LIMIT 10;

Sharded: Must query ALL shards, aggregate results in application.
  Shard 1 → top 10
  Shard 2 → top 10
  Shard 3 → top 10
  Merge → true top 10

No push-down. Application does merge sort. High latency.

Replication vs. Sharding Decision

Problem                     Solution
────────────────────────────────────────────────
Read throughput bottleneck  Replication (add replicas)
Write throughput bottleneck Sharding (add primaries)
Storage limit               Sharding (data split across nodes)
High availability           Replication (failover)
Complex cross-shard queries Avoid sharding, use read replicas

Rule: Start with replication. Shard only when write throughput or storage forces it.

Part 9: Schema Evolution & Migrations

The Challenge

Your schema at v1 (100M rows):
  CREATE TABLE orders (id UUID, user_id UUID, total DECIMAL);

Business needs v2:
  ADD COLUMN coupon_code VARCHAR(50);
  ADD COLUMN discount_amount DECIMAL;
  DROP COLUMN notes;  ← risky

Running ALTER TABLE on 100M rows:
  ├─ Full table rewrite (Postgres, MySQL)
  ├─ Locks table for minutes (or hours)
  └─ Zero-downtime? Needs careful approach.

Zero-Downtime Migration Strategy

Expand → Migrate → Contract (3-phase approach):

Phase 1: EXPAND — add new column, keep old

  ALTER TABLE orders ADD COLUMN coupon_code VARCHAR(50);
  -- Old column 'notes' still exists
  -- New column is nullable (no default required)
  -- Application writes to BOTH old and new columns
  -- Application reads from old column (new is empty)

Phase 2: MIGRATE — backfill data

  UPDATE orders SET coupon_code = extract_coupon(notes)
  WHERE coupon_code IS NULL AND notes IS NOT NULL;
  -- Run in batches of 1000 rows to avoid lock contention
  -- Application now reads from new column if populated, falls back to old

Phase 3: CONTRACT — drop old column

  ALTER TABLE orders DROP COLUMN notes;
  -- All data migrated, no reads of old column
  -- Safe to remove

Migration tooling:

Flyway (Java):
  V1__create_orders.sql
  V2__add_coupon_code.sql
  V3__drop_notes_column.sql

  flyway migrate → applies pending migrations in order
  Tracks applied migrations in flyway_schema_history table

Liquibase (XML/YAML):
  changeSet id="2" author="ravi":
    addColumn tableName="orders":
      column name="coupon_code" type="VARCHAR(50)"

Document Store Schema Evolution

Easier (no lock) but requires application-level handling:

// Old document shape:
{ _id: "order-1", user: "alice", total: 49.99 }

// New document shape:
{ _id: "order-2", user_id: "user-123", user_name: "alice", total: 49.99 }

// Application handles both versions:
function getUsername(order) {
  return order.user_name || order.user;  // handle both v1 and v2
}

// Lazy migration: update documents on read
function getOrder(id) {
  const order = db.orders.findOne({_id: id});
  if (!order.user_id && order.user) {
    order.user_id = lookupUserId(order.user);
    order.user_name = order.user;
    db.orders.replaceOne({_id: id}, order);
  }
  return order;
}

No downtime. Documents migrate as they're accessed. Old and new shapes coexist.

Part 10: Decision Matrix

Database Type       Access Pattern              Consistency  Scale Model
──────────────────────────────────────────────────────────────────────────
PostgreSQL          Relational, JOIN-heavy       ACID         Read replicas
                    Transactions, reporting

MongoDB             Nested docs, flexible        Eventual     Auto-sharding
                    schema, catalog

Redis               Key lookup, counters,        None/soft    Cluster mode
                    session, cache, rate limit

Elasticsearch       Full-text search, facets,   Eventual     Shard across
                    log search                               nodes

InfluxDB            Time-series, metrics,        Eventual     Retention
                    monitoring                               policies

Neo4j               Graph traversal, paths,      ACID         Causal
                    recommendations              (raft)       clustering

DynamoDB            Single-table design,         Eventual     Serverless,
                    key-value at any scale       (tunable)    unlimited

The quick rule:

• Relational data with transactions → PostgreSQL
• Flexible schema, nested objects → MongoDB
• Cache, sessions, rate limits → Redis
• Search (full-text, autocomplete) → Elasticsearch
• Metrics and monitoring → InfluxDB or Prometheus
• Connections and traversal → Neo4j
• Infinite scale, simple queries → DynamoDB

Conclusion

No database does everything well. The engineers who make bad database choices are usually the ones who picked their favourite tool regardless of the problem.

Ask these four questions before choosing:

1. What is the access pattern? Point lookup? Range scan? Full-text? Graph traversal?
2. What consistency do I need? ACID across multiple records? Or eventual is fine?
3. What scale am I designing for? 10K rows? 10B rows? 1M writes/sec?
4. Who owns this data? One service or many?

Start with PostgreSQL. It handles 90% of cases well, supports JSONB for document-like flexibility, and has excellent tooling. Add Redis for caching and sessions. Add Elasticsearch when you need full-text search. Graduate to specialised stores only when you hit genuine limits.

Polyglot persistence is not an upfront decision — it's the result of hitting limits and choosing the right tool to solve each bottleneck.

Further Reading

• Designing Data-Intensive Applications by Martin Kleppmann — the definitive reference on database internals and trade-offs
• Database Internals by Alex Petrov — storage engines, B-trees, LSM trees
• Seven Databases in Seven Weeks by Eric Redmond — hands-on tour of database types
• PostgreSQL Documentation (postgresql.org) — indexes, JSONB, partitioning
• The Amazon DynamoDB Paper (2022) — single-table design at AWS scale