Database Scaling Strategies: From 10K to 100M Users

Scaling Stages Overview: The Growth Ladder

Each user-growth stage has one dominant bottleneck and one cheap first fix. Jump stages (adding sharding at 50K users) buys complexity without solving your actual problem. Diagnose first, then apply the fix matched to your stage.

The pattern is consistent: boring fixes first. Instagram ran 90M+ photos on a handful of Postgres instances with aggressive indexing and application-level sharding done late and deliberately. Nobody's p99 was ever saved by a distributed database they didn't understand yet.

Indexing Done Right

A composite index follows one rule: equality columns first (any order), then range/sort column last. An index that can serve equality filters plus an ORDER BY turns a 500ms sort-scan into a 2ms index scan. Over-indexing then taxes every INSERT/UPDATE/DELETE — each extra index adds roughly 10–15% to write cost.

The composite index rule

-- Query: find orders for a customer in a date range, newest first
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'shipped'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC;

-- Correct: two equality columns, then the range/sort column LAST
CREATE INDEX idx_orders_cust_status_created
    ON orders (customer_id, status, created_at DESC);

-- Wrong: range column in the middle kills the rest of the index.
-- After created_at is range-scanned, status/customer_id can't be used.
CREATE INDEX idx_orders_wrong ON orders (customer_id, created_at, status);

Why order matters: a B-tree index works like a phone book sorted by (last name, first name). You can find all "Smith, J*" instantly, but you cannot efficiently find all people whose first name is "Jamie". Equality columns define contiguous ranges; only one range column can ride along at the tail.

Covering indexes: skip the table entirely

-- Hot dashboard query, runs 800 times/sec
SELECT status, COUNT(*) FROM orders
WHERE customer_id = 42 GROUP BY status;

-- Index-only scan: every referenced column lives in the index,
-- zero heap fetches. ~1-3ms instead of ~40ms.
CREATE INDEX idx_orders_cust_covering
    ON orders (customer_id) INCLUDE (status);

-- Postgres: verify with EXPLAIN (ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
SELECT status, COUNT(*) FROM orders WHERE customer_id = 42 GROUP BY status;
-- Look for: Index Only Scan ... Heap Fetches: 0

Reading EXPLAIN in 60 seconds

• Seq Scan on a big table + filter: missing index (fine under ~1K rows)
• Rows estimate ≫ actual rows: stale statistics — run ANALYZE; bad plans follow
• Nested Loop × high row counts: planner expects few rows but gets thousands — usually stats or type mismatch (int vs bigint, or casting away the index with functions like WHERE lower(email) = ...)
• Sort node on large input: extend the index to cover ORDER BY

The over-indexing penalty

• Every index must be updated per write: 12 indexes on a hot table ≈ up to 13x write amplification on the indexed columns
• Bulk loads slow down dramatically — drop indexes before big migrations, rebuild after
• Find unused indexes after 30 days of stats: pg_stat_user_indexes where idx_scan = 0 — drop them

Query Optimization: The Cheapest Scaling There Is

Kill the N+1, stop using SELECT *, and replace OFFSET pagination past page ~10 with keyset (cursor) pagination. These three fixes routinely cut database load by 5–10x without touching infrastructure.

The N+1 problem

// BAD: 1 query for posts + 1 query PER post for comments
const posts = await db.post.findAll({ limit: 20 });
for (const post of posts) {
    post.comments = await db.comment.findAll({ where: { postId: post.id } });
}
// Total: 21 queries, ~21 x 3ms round trips = 63ms+ network alone

// FIX 1: eager loading (join or two-query strategy)
const posts = await db.post.findAll({
    limit: 20,
    include: [db.comment],   // ORM issues 2 queries total, joins in memory
});

// FIX 2: manual batch IN — often faster than a giant JOIN
const ids = posts.map(p => p.id);
const comments = await db.comment.findAll({ where: { postId: { [Op.in]: ids } } });
// Then group comments by postId in application code

Detect N+1s: log all SQL in dev, alert on any request issuing > 20 similar queries. Most ORMs have a built-in counter (ActiveRecord::Base.connection queries, Django AssertNumQueries, Sequelize logging).

Why SELECT * hurts

• Drags every column across the wire including multi-KB text/JSONB blobs you never read
• Defeats covering indexes — forces heap fetches even when an index could answer the query
• Silently breaks when someone adds a column; breaks PREPAREd statement plan caching in some drivers
• Selecting 5 of 30 columns typically cuts transfer and memory by 60%+ on wide tables

Pagination at depth: OFFSET collapses, keyset doesn't

-- OFFSET: reads and discards all skipped rows.
-- Page 5000 (OFFSET 100000): scans 100K+ rows to return 20. ~800ms.
SELECT * FROM feed_posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- Keyset/cursor: jumps straight into the index. Constant ~2ms at any depth.
-- Cursor encodes (created_at, id) of the last item on the current page.
SELECT * FROM feed_posts
WHERE (created_at, id) < ('2026-08-01T10:00:00Z', '987654')
ORDER BY created_at DESC, id DESC
LIMIT 20;

Read Replicas: Cheap Reads, With Strings Attached

Replicas multiply read throughput but lag the primary by anywhere from 10ms to tens of seconds under load. Any feature reading its own writes must be pinned to the primary or use version tokens — otherwise you ship the classic "I posted a comment and it disappeared" bug.

What replication actually gives you

• Streaming replication (Postgres): replica applies WAL with typical lag of 50ms–2s; under heavy bulk writes it can spike to 30s+
• Route ~80–90% of traffic (dashboards, feeds, search) to replicas; keep writes and read-after-write paths on primary
• Replicas also fail independently — a long analytical query on a replica no longer blocks OLTP writes
• They do NOT help if the load is one hot row (counter updates, flash-sale inventory): all writes still serialize on the primary

Read-your-writes: session pinning

// After any write, pin subsequent reads in this session to the primary
// for a window longer than observed max lag (e.g., 3s).
class RoutingDataSource {
    async execute(req, queryFn) {
        const pinned = req.session.pinnedUntil > Date.now();
        const target = pinned ? this.primary : this.replicaPool.random();
        return queryFn(target);
    }
}
// Set session.pinnedUntil = Date.now() + 3000 immediately after any UPDATE/INSERT.

Version tokens: precise, less wasteful

// Client sends X-Version-Token header it got back with its last write.
// Token carries the primary's LSN/watermark at write time.

app.get('/api/posts/:id', async (req, res) => {
    const token = req.headers['x-version-token'];
    if (token && !replicaCaughtUpTo(token)) {
        // Replica hasn't applied this write yet — fail over to primary.
        return queryPrimary(req.params.id);
    }
    return queryReplica(req.params.id);
});

When replicas don't help

• Single hot row: 5K increments/sec on one counter row — replicas replicate the contention too. Fix with in-memory counters flushed periodically or sharded counters (row split into N sub-counters)
• Write-heavy workload: 80% writes means replicas mostly idle-wait on WAL
• Lag-sensitive features: inventory availability, balances — treat as primary-only or redesign around eventual consistency explicitly

Caching Layers: The Biggest Win and the Classic Trap

A well-placed cache converts a 50ms DB query into a ~1ms Redis GET — but only invalidation is hard. Cache immutable-ish data aggressively with TTLs, invalidate event-driven on writes, and always guard hot keys against stampedes with locks or probabilistic early refresh.

Redis vs Memcached

Invalidation strategies

• TTL only: simplest; staleness bounded by TTL (e.g., 300s). Fine for product pages, wrong for permissions/balances
• Write-through: update DB and cache in the same operation; reads never miss, writes pay the cache tax. Good when reads vastly outnumber writes (> 10:1)
• Event-based: DB commit publishes user.updated:{id}; consumers delete keys. Correctness without coupling; needs outbox/CDC plumbing

Cache stampede protection (mutex/lock pattern)

async function getWithStampedeProtection(key, ttlSec, loader) {
    let value = await redis.get(key);
    if (value) return JSON.parse(value);

    // Try to acquire a short-lived lock so ONE worker rebuilds the key
    const lockKey = `lock:${key}`;
    const acquired = await redis.set(lockKey, workerId, 'NX', 'EX', 10);
    if (!acquired) {
        // Someone else is rebuilding: briefly poll instead of hammering the DB
        for (let i = 0; i < 20; i++) {
            await sleep(50);
            value = await redis.get(key);
            if (value) return JSON.parse(value);
        }
        throw new Error('cache_rebuild_timeout');
    }

    try {
        value = await loader();                       // expensive DB call, once
        await redis.set(key, JSON.stringify(value), 'EX', ttlSec);
        return value;
    } finally {
        await redis.del(lockKey);
    }
}

// Belt-and-braces extras:
// - Stale-while-revalidate: serve slightly-stale copy while rebuilding
// - Probabilistic early expiry (XFetch): refresh before TTL as popularity grows

Hit-rate math: why 95% is the magic neighborhood

If a DB query costs ~50ms and a cache hit ~1ms, average latency = (hitRate × 1ms) + ((1 − hitRate) × 50ms).

Going 80%→90% halves latency; going 90%→95% halves DB load again. Chasing the last 3% usually means caching low-value long-tail keys — watch hit rate per key-class, not just global.

Connection Pooling & Limits

Databases are not web servers: every connection is a process/thread costing roughly 1–10MB RAM, and context-switching above a few hundred active connections degrades everyone. Put a pooler in front, size pools small (cores × 2 is the classic starting point), and never let app servers open connections per request.

Why max_connections kills you

• Postgres default max_connections = 100; each backend is an OS process (~2–10MB, plus work_mem per sort/hash)
• 40 app pods × 20 connections = 800 demanded vs 100 available → "sorry, too many clients already" during deploys
• Even raising the limit fails: 1,000 active Postgres backends spend more time context-switching than querying — throughput DROPS as connections rise

PgBouncer modes

Sizing guidance

# Pool size heuristic (HikariCP formula, works for most OLTP):
pool_size ≈ cores * 2 + effective_spindle_count

# Modern SSD/NVMe boxes: cores * 2 is a fine start.
# 8-core DB -> pool of ~16-20 ACTIVE server connections. That's it.

# PgBouncer transaction mode:
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
default_pool_size = 20          ; real server connections
max_client_conn = 2000          ; cheap client-side slots
reserve_pool_size = 5
server_idle_timeout = 60

# Verify saturation before growing:
# SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
# If 'active' rarely hits default_pool_size, the pool is NOT the bottleneck.

• App-side pools (HikariCP, pgx pool, Sequelize pool) should be sized so pods × appPoolSize ≤ max_client_conn, not ≤ DB connections
• Add acquireTimeout/queue limits — a bounded wait with fast failure beats a 30s hang
• Watch pgbouncer_stats: cl_waiting > 0 sustained means raise default_pool_size or (better) reduce query time

Vertical vs Horizontal: Bigger Box Is Underrated

Until roughly 64 vCPUs / 512GB+ RAM / NVMe storage, a bigger single instance beats distribution on every axis except fault tolerance. Scale vertically as far as the budget allows; split only when writes exceed what one machine commits durably — that's around 10K–30K durable transactions/sec on modern cloud hardware for Postgres-class databases.

When the bigger box wins

• No distributed complexity: joins, foreign keys, transactions, backups all keep working exactly as documented
• RAM absorbs the working set: hot data fully cached means disk latency leaves the equation entirely — p99 drops 10–50x
• One knob: resize takes minutes on managed platforms; sharding migrations take quarters
• Cheaper per unit of throughput than operating a fleet plus its coordination layer

Realistic ceilings (single instance, cloud NVMe, ballpark)

The point where you actually must split

• Writes exceed one machine's durable commit rate and batching/debouncing can't shave them below it
• Maintenance operations (vacuum, backup restore, major-version upgrade) exceed your acceptable downtime window
• Failure blast radius is unacceptable: one region outage taking down ALL customers is a business decision, not just technical

Partitioning & Sharding: Know the Difference

Partitioning splits one table inside one instance (zero-downtime, transparent to queries); sharding splits data across many machines (real distribution, real pain). Exhaust partitioning first — it solves maintenance and prune-scan problems without touching your consistency model.

Partitioning: within one instance

-- Range partitioning by month: the workhorse
CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id BIGINT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_07 PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- Queries with a date filter prune to 1 partition instead of scanning years.
-- Dropping old data = DROP TABLE events_2026_07 (instant, no DELETE bloat).
-- Other schemes: LIST (region, tenant_tier), HASH (even spread by user_id).

Sharding: across instances

Each shard owns a disjoint slice of rows and runs on its own machine. The router maps each query to shard(s) via the shard key.

Shard key selection rules

• High cardinality: user_id (millions of values), not country (195 values) or tenant where one tenant might hold 40% of rows
• Even distribution: hash(user_id), not raw timestamps (every write hits "today's" shard) and not monotonically increasing IDs
• Query isolation: nearly every query must include the key. If 30% of queries lack it, those fan out to all shards — your p99 becomes your worst shard
• Co-locate related data: orders, payments, and notifications keyed by user_id live on the same shard → single-shard transactions still work

Resharding pain (why choosing right the first time matters)

// Resharding = double-write + backfill + dual-read + cutover.
// Typical timeline for a 2TB dataset: 2-4 months of engineering.

// Phase 1: dual-write to old + new layout (new one may lag; tolerate)
await legacyDb.write(row);
await newShardMap.write(row).catch(logDualWriteFailure); // never block prod

// Phase 2: backfill historical rows chunk-by-chunk (batch 1K rows/sec)

// Phase 3: dual-read with comparison (read old, read new, diff, alert)
// Phase 4: flip reads to new, keep writes mirrored for rollback window
// Phase 5: stop legacy writes; retire old tables

Avoid cross-shard transactions

• Design entities so one user's data lands on one shard (co-location)
• Where impossible, use sagas/outbox patterns instead of 2PC — see the Saga pattern guide
• Global uniqueness: don't rely on auto-increment; use UUIDv7 or snowflake IDs (timestamp-prefix sortable)
• Cross-shard queries (analytics, admin search) belong in a replica/warehouse path, not OLTP shards

Checklist: signs you ACTUALLY need sharding

• ☐ Write TPS exceeds ~10K–30K durable commits/sec on the largest instance you're willing to run
• ☐ Data volume makes maintenance (backup, vacuum, version upgrade) exceed operational windows
• ☐ You've exhausted: indexes, replicas, caching (≥95% hit rate), partitioning, archiving
• ☐ Multi-tenant isolation requirements demand physical separation (compliance, noisy neighbors)
• ☐ You can name your shard key AND ≥90% of hot queries include it
• ☐ You have (or will hire) engineers to own routing, rebalancing, and per-shard monitoring

SQL vs NoSQL: Decision Framework, Not Hype

Choose by access pattern and integrity requirements, not benchmarks or fashion. Relational databases handle far more scale than their reputation suggests — Postgres happily serves millions of users — while document stores win on flexible schemas and known-key access. Many mature stacks end up polyglot: one source-of-truth relational DB plus purpose-built stores at the edges.

Polyglot persistence reality check

• Every additional store = another failure mode, backup story, security audit, and on-call mental model
• The common winning shape: Postgres as source of truth → CDC (Debezium/logical replication) → derived stores (search index, analytics warehouse, cache)
• "NoSQL scales better" is mostly obsolete folklore: Postgres with Citus/Vitess-style sharding, or managed equivalents, covers most NoSQL headline numbers
• Default answer for a new product: relational + Redis. Deviate when a concrete access pattern proves the default wrong

Cost Optimization: Scaling Without Burning Money

The biggest database cost lever isn't instance size — it's not storing or querying data you don't need hot. Tier storage, archive cold data, and match managed tiers to workload shape. Numbers below are ballpark cloud pricing (AWS RDS/GCP Cloud SQL class, mid-2026) — treat as ±30% and check current rates.

Managed DB tiers (ballpark, monthly)

Storage tiering & cold archival

• Hot (NVMe/gp3): last 30–90 days, ~$0.10–0.19/GB-mo — everything users touch daily
• Cool (object storage): 90–365 days, ~$0.02/GB-mo — export monthly partitions to Parquet; query via Athena/Spectrum only when needed
• Cold/archive: 1yr+, ~$0.004/GB-mo (Glacier Deep Archive class) — compliance dumps, restore in hours; accept it
• Tiering a 2TB events table (90% cold) saves roughly $350/mo in storage AND keeps VACUUM/backups fast — the speed win often outweighs the dollar win
• Right-size reserved/savings plans once usage is stable 60+ days: 30–40% off committed compute

-- Find the tables worth archiving (Postgres):
SELECT relname AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup AS rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
-- Anything > 50GB with append-only semantics is an archiving candidate.

• Delete test/debug data and orphaned rows on schedule; bloat inflates every downstream cost
• Compress: TOAST-aware column types (Postgres COMPRESSION lz4), or move large blobs to S3 and store URLs — DB rows shrink 10–100x
• Cross-region read replicas double-to-triple cost; confirm someone actually uses them before standing them up