On this page

Database Scaling

"The database is slow" is the most common scaling complaint in backend systems, and the most commonly over-solved. Teams jump to sharding when a missing index would have bought them two years. Others add a cache when the real problem is 400 idle connections thrashing the server. Each scaling technique has a cost in complexity that is paid forever, so the order in which you apply them matters more than any individual technique.

This page is a ladder: climb one rung at a time, and only when you have evidence that the current rung is exhausted. Each rung lists what it buys, what it costs, and the signals that you have outgrown it. The deep mechanics live in dedicated pages — this page links to them rather than repeating them.

The Ladder at a Glance

flowchart TB
    R1["1. Query and index optimization"] --> R2["2. Vertical scaling"]
    R2 --> R3["3. Connection pooling"]
    R3 --> R4["4. Caching"]
    R4 --> R5["5. Read replicas"]
    R5 --> R6["6. Functional partitioning"]
    R6 --> R7["7. Table partitioning"]
    R7 --> R8["8. Sharding"]
    R8 --> R9["9. Distributed SQL / NewSQL"]
    R5 -.-> R10["CQRS / read models<br/>(sideways: different read shape)"]
    R4 -.-> R10

    style R1 fill:#d1fae5,stroke:#059669
    style R2 fill:#d1fae5,stroke:#059669
    style R3 fill:#d1fae5,stroke:#059669
    style R4 fill:#fef3c7,stroke:#d97706
    style R5 fill:#fef3c7,stroke:#d97706
    style R6 fill:#fef3c7,stroke:#d97706
    style R7 fill:#fef3c7,stroke:#d97706
    style R8 fill:#fee2e2,stroke:#dc2626
    style R9 fill:#fee2e2,stroke:#dc2626
    style R10 fill:#e0e7ff,stroke:#4f46e5
RungTypical gainComplexity addedReversible?Time to implement
1. Query/index optimization10-1000x on the affected queriesLowYesHours to days
2. Vertical scaling2-8xNone (cost only)YesMinutes to hours
3. Connection pooling2-5x under high concurrencyLowYesDays
4. Caching5-100x read offload on hot keysMedium (invalidation)MostlyDays to weeks
5. Read replicasLinear read scaling (N replicas ≈ N x reads)Medium (lag)YesDays
6. Functional partitioningSplits load by domainMedium (no cross-DB joins)HardWeeks to months
7. Table partitioningFaster maintenance, pruning on huge tablesLow to mediumHardWeeks
8. ShardingLinear write scalingVery highVery hardMonths
9. Distributed SQLSharding without app-level routingHigh (latency, cost, new ops)Very hardMonths
CQRS / read modelsReads served from purpose-built storesHigh (eventual consistency)HardWeeks to months

The one-line rule

Rungs 1-3 are things every production database should have anyway. Rungs 4-5 are standard for read-heavy systems. Anything from rung 6 up should be preceded by a written capacity analysis that shows the lower rungs are exhausted.

Know Your Workload First

Every decision below depends on four numbers. Measure them before you move:

MetricHow to get it (PostgreSQL)Why it matters
Read/write ratiopg_stat_database (tup_returned + tup_fetched vs tup_inserted + tup_updated + tup_deleted)Replicas and caches only help reads
Top queries by total timepg_stat_statements ordered by total_exec_timeUsually 5-10 queries are 80% of load
Working set vs. RAMpg_statio_user_tables cache hit ratio; table and index sizesIf the hot set fits in RAM, vertical scaling is very effective
Peak concurrencypg_stat_activity count by state at peakDetermines whether pooling is the bottleneck
-- Top 10 queries by cumulative time: start every scaling discussion here
SELECT
  round(total_exec_time::numeric / 1000, 1)                  AS total_s,
  calls,
  round(mean_exec_time::numeric, 2)                          AS mean_ms,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct,
  left(regexp_replace(query, '\s+', ' ', 'g'), 100)          AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Buffer cache hit ratio per table (want > 99% for OLTP hot tables)
SELECT relname,
       heap_blks_hit * 100.0 / nullif(heap_blks_hit + heap_blks_read, 0) AS hit_pct,
       pg_size_pretty(pg_total_relation_size(relid))                     AS total_size
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

If one query is 40% of total time, you do not have a scaling problem. You have a query problem.

Rung 1: Query and Index Optimization

The cheapest, highest-leverage rung, and the one most often skipped. A single missing index on a hot path can make a 16-core database look saturated at 500 queries per second.

What it buys. A sequential scan of a 50M-row table takes seconds; an index lookup takes well under a millisecond. Fixing N+1 query patterns turns 101 round trips into 2. These are 10-1000x gains on affected queries — no infrastructure change can compete.

The checklist:

ProblemDetectionFix
Missing indexSeq Scan on a large table in EXPLAIN (ANALYZE, BUFFERS); high seq_tup_read in pg_stat_user_tablesAdd a B-tree / composite / partial index matching the predicate and sort
Wrong column order in composite indexIndex exists but plan uses Filter after Index ScanEquality columns first, then range, then sort
N+1 queriesSame query shape with thousands of calls per request in pg_stat_statementsBatch with WHERE id = ANY($1) or join; see N+1 Queries
Over-fetchingSELECT * on wide rows; large shared read buffersSelect needed columns; covering index with INCLUDE
Bad estimatesEstimated rows vs actual rows off by 100xANALYZE, raise statistics target, extended statistics
Deep OFFSET paginationCost grows linearly with page numberKeyset pagination (WHERE (created_at, id) < ($1, $2))
Unused/duplicate indexesidx_scan = 0 in pg_stat_user_indexesDrop them: they cost write throughput and RAM
-- Before: 2.3 s, Seq Scan on orders (48M rows)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 8812 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- Fix: equality columns first, then sort column; INCLUDE makes it index-only
CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC)
  INCLUDE (total_cents);

-- After: 0.08 ms, Index Only Scan, 4 buffers

Deep dives: Indexing Deep Dive, Query Planning & Optimization, PostgreSQL Performance Tuning. When the database is already on fire, follow the Database CPU at 100% playbook.

Signals you've outgrown this rung:

  • The top 20 queries in pg_stat_statements all use appropriate indexes and have plans you cannot improve.
  • Load is spread across many queries rather than concentrated in a few.
  • CPU or I/O is saturated at a throughput consistent with "every query is already efficient."

Rung 2: Vertical Scaling

Buy a bigger machine. It is unglamorous, it requires no code changes, and it is astonishingly far-reaching. A single modern server — 64-192 vCPUs, 512 GB to several TB of RAM, NVMe storage with hundreds of thousands of IOPS — can run a well-tuned PostgreSQL or MySQL at tens of thousands of transactions per second and hold multi-terabyte datasets with the hot set in memory.

xychart-beta
    title "Illustrative: cost vs. capacity on managed Postgres"
    x-axis ["4 vCPU", "8", "16", "32", "64", "96"]
    y-axis "Relative monthly cost" 0 --> 26
    bar [1, 2, 4, 8, 16, 24]
    line [1, 2, 4, 8, 16, 24]

Within a cloud instance family, cost scales roughly linearly up to the largest sizes — the "superlinear cost" argument against vertical scaling is mostly a hardware-purchasing argument, not a cloud one. What does not scale linearly is benefit: lock contention, WAL write throughput, and single-threaded parts of the engine (one query uses one core unless it parallelizes) cap the return.

ResourceUpgrade helps whenUpgrade does nothing when
RAMCache hit ratio < 99%, working set > shared_buffers + OS cacheWorking set already fits
CPU coresMany concurrent queries, CPU saturated, no single hot lockOne slow query dominates; lock contention
Storage IOPS/throughputHigh iowait, shared read dominates buffersEverything already in RAM
NetworkReplication or bulk export saturates NICRarely the bottleneck for OLTP

The failover cost of big boxes

A bigger primary means a bigger blast radius and slower recovery: a 4 TB database takes much longer to rebuild a replica for, and a restart with a cold cache can take many minutes to warm. Pair vertical scaling with at least one hot standby and test failover time explicitly.

Signals you've outgrown this rung:

  • You are on (or one step from) the largest instance your provider offers.
  • Doubling the instance size last time gave you less than 1.3x throughput — you are bound by contention, not resources.
  • Write volume (WAL generation) is near what one disk subsystem can sustain.
  • Dataset growth will exceed the largest instance's storage or memory within 12-18 months.

Rung 3: Connection Pooling

Many "the database is overloaded" incidents are actually "the database has too many connections." In PostgreSQL every connection is a process with its own memory (several MB to tens of MB once it has touched data), and throughput falls as active connections climb past roughly 2-4x the core count because of context switching and lock contention.

flowchart LR
    subgraph APP["120 app pods x 20 conns each"]
        A1["pod 1"]
        A2["pod 2"]
        A3["..."]
        A4["pod 120"]
    end
    PB["PgBouncer<br/>(transaction mode)<br/>2400 client conns"]
    DB[("PostgreSQL<br/>max 64 server conns<br/>32 cores")]
    A1 --> PB
    A2 --> PB
    A3 --> PB
    A4 --> PB
    PB -->|"64 server connections"| DB

Sizing rule of thumb. Start the server-side pool at roughly cores x 2 (plus a few for maintenance) and measure; the right number depends on how much time queries spend waiting on I/O. Little's Law gives you the requirement: connections needed ≈ throughput x mean query time. At 8,000 queries/s with a 4 ms mean, you need about 32 busy connections — not 2,400.

Pool mode (PgBouncer)Connection held forBreaks
SessionEntire client sessionNothing, but little multiplexing
TransactionOne transactionSession state: SET, advisory locks, LISTEN, (historically) prepared statements
StatementOne statementMulti-statement transactions

Recent PgBouncer versions support protocol-level prepared statements in transaction mode, which removed the biggest historical caveat. Full mechanics and failure modes: Connection Pooling and Connection Pool Tuning.

Signals you've outgrown this rung:

  • Server-side pool is sized correctly, clients rarely wait for a connection, and the database is still saturated on CPU or I/O.
  • Pool wait time is high because queries are slow, not because the pool is small — go back to rung 1, or move up.

Rung 4: Caching

Caching removes reads from the database entirely. For read-heavy workloads with skewed access (a small fraction of keys gets most of the traffic), a cache in front of the database can absorb 90-99% of reads.

sequenceDiagram
    participant App
    participant Cache as Redis
    participant DB as PostgreSQL

    App->>Cache: GET product:42
    alt hit (≈ 0.2-0.5 ms)
        Cache-->>App: cached JSON
    else miss
        Cache-->>App: nil
        App->>DB: SELECT ... WHERE id = 42 (≈ 1-5 ms)
        DB-->>App: row
        App->>Cache: SET product:42 EX 300
    end

What it costs. Invalidation correctness, a second system to operate, cold-start behavior (a cache flush can send 100% of traffic to a database sized for 5%), and thundering herds when a hot key expires.

DecisionOptionsGuidance
PatternCache-aside, read-through, write-through, write-behindCache-aside is the default; see Caching Strategies
InvalidationTTL, explicit delete on write, CDC-drivenTTL + delete-on-write covers most cases; see Cache Invalidation
Stampede protectionLocking, request coalescing, early refreshRequired for any key with > 100 req/s; see Thundering Herd
What to cacheHot entities, expensive aggregates, rendered fragmentsNot: data that must be read-your-writes consistent, per-user data with low reuse
// Cache-aside with request coalescing and jittered TTL
import { Redis } from 'ioredis';
const redis = new Redis(process.env.REDIS_URL!);
const inflight = new Map<string, Promise<unknown>>();

export async function cached<T>(key: string, ttlSec: number, load: () => Promise<T>): Promise<T> {
  const hit = await redis.get(key);
  if (hit !== null) return JSON.parse(hit) as T;

  // Coalesce concurrent misses for the same key within this process
  const existing = inflight.get(key);
  if (existing) return existing as Promise<T>;

  const p = (async () => {
    try {
      const value = await load();
      const jitter = Math.floor(ttlSec * 0.1 * Math.random());   // avoid synchronized expiry
      await redis.set(key, JSON.stringify(value), 'EX', ttlSec + jitter);
      return value;
    } finally {
      inflight.delete(key);
    }
  })();
  inflight.set(key, p);
  return p;
}

Signals you've outgrown this rung:

  • Cache hit ratio is already > 90% and the database is still saturated — the remaining load is writes or uncacheable reads.
  • Access pattern is uniform (no hot set), so hit ratio stays low regardless of cache size.
  • Invalidation bugs are causing user-visible staleness and the team is spending more time on cache correctness than features.

Rung 5: Read Replicas

Replicas copy the primary's changes (via WAL streaming in PostgreSQL, binlog in MySQL) and serve read-only queries. Read capacity scales roughly linearly with replica count; write capacity does not change at all — every replica must apply every write.

flowchart LR
    APP["Application"] -->|"writes +<br/>read-your-writes reads"| P[("Primary")]
    APP -->|"other reads"| LB["Read router /<br/>replica pool"]
    P -->|"WAL stream"| R1[("Replica 1")]
    P -->|"WAL stream"| R2[("Replica 2")]
    P -->|"WAL stream"| R3[("Replica 3<br/>analytics")]
    LB --> R1
    LB --> R2

Replication Lag Is a Product Decision

Asynchronous replicas are behind the primary by some amount — typically milliseconds, occasionally seconds or minutes during bulk writes, long-running queries on the replica, or network issues. Every read routed to a replica is a read of possibly stale data.

AnomalyExampleMitigation
Read-your-writes violationUser updates their profile, page reloads, old name showsRoute the user's reads to the primary for N seconds after a write; or wait for the replica to reach the write's LSN
Monotonic reads violationRefresh shows a comment, next refresh doesn't (hit a more-lagged replica)Sticky replica per session
Causal violationReply visible before the message it replies toRead both from the same replica, or from primary
Stale decisionInventory check on replica says "in stock"Never make write decisions from replica reads; check on primary inside the write transaction
// LSN-based read-your-writes: after a write, remember the primary's WAL position
// and only use a replica that has replayed at least that far.
import { Pool } from 'pg';
const primary = new Pool({ connectionString: process.env.PRIMARY_URL });
const replica = new Pool({ connectionString: process.env.REPLICA_URL });

export async function writeAndGetLsn(sql: string, params: unknown[]): Promise<string> {
  const client = await primary.connect();
  try {
    await client.query(sql, params);
    const { rows } = await client.query('SELECT pg_current_wal_lsn()::text AS lsn');
    return rows[0].lsn;          // store in the user's session / cookie
  } finally {
    client.release();
  }
}

export async function readAfter(lsn: string | undefined, sql: string, params: unknown[]) {
  if (lsn) {
    const { rows } = await replica.query(
      'SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn AS caught_up', [lsn],
    );
    if (!rows[0].caught_up) return primary.query(sql, params);   // fall back to primary
  }
  return replica.query(sql, params);
}

Monitor lag in bytes and in time; alert on both:

-- On the primary
SELECT application_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication;

War Story (illustrative)

The double refund

A payments team moved "read-only" endpoints to replicas, including the endpoint that listed refundable charges. During a nightly batch job, replica lag reached 40 seconds. A support agent issued a refund, refreshed, still saw the charge as refundable (the replica had not caught up), and issued it again. The refund write path trusted the list it had been given rather than rechecking state on the primary. The fix had two parts: the refund transaction re-validated the charge with SELECT ... FOR UPDATE on the primary, and any endpoint that fed a write decision was moved back to the primary. Replicas are for reads that can tolerate staleness — and someone has to decide, per endpoint, which reads those are.

Mechanics of sync vs async replication, failover, and topologies: Replication. Cross-region replicas: Multi-Region Database.

Signals you've outgrown this rung:

  • The primary is saturated by writes; adding replicas does nothing for it (and each replica adds a WAL sender).
  • Replicas cannot keep up with replay (replay is largely single-threaded in PostgreSQL), so lag grows during peaks.
  • Too many endpoints need read-your-writes consistency, so most reads end up on the primary anyway.

Rung 6: Functional Partitioning

Split the database by domain: users and auth in one database, orders in another, analytics events in a third. Each database is smaller, has its own resource budget, and can be scaled independently. This often happens naturally when moving to services.

flowchart TB
    subgraph BEFORE["Before: one database"]
        DB1[("monolith DB<br/>users, orders, catalog,<br/>events, sessions")]
    end
    subgraph AFTER["After: split by domain"]
        U[("identity DB<br/>users, sessions")]
        O[("orders DB<br/>orders, payments")]
        C[("catalog DB<br/>products, prices")]
        E[("events store<br/>append-only, columnar")]
    end
    BEFORE --> AFTER

What it buys. The noisiest workload (often event logging or sessions) stops competing with the critical one. Individual databases go back to fitting comfortably on rung 2. Each domain can choose a different engine — sessions into Redis, events into a columnar store.

What it costs:

Lost capabilityReplacement
Cross-domain JOINApplication-side joins, denormalized copies, or a read model (CQRS)
Cross-domain ACID transactionsSagas, outbox pattern, idempotent consumers
Foreign keys across domainsApplication-level validation, eventual consistency
One backup/restore pointCoordinated backups, or accept per-domain recovery points

How to do it safely: identify tables with few cross-domain joins (query logs tell you), move writes behind a domain API first, then replicate the tables to the new database (logical replication or CDC), switch reads, switch writes, and finally drop the old tables. Schema-change mechanics are in Database Migrations.

Signals you've outgrown this rung:

  • A single domain's tables are too large or too write-heavy for one server on their own.
  • There is no more natural seam to split along — the remaining load is one big table.

Rung 7: Table Partitioning

Split one huge table into child tables inside the same database, by range (time), list (region, tenant tier), or hash. The query planner prunes partitions that cannot match, and maintenance becomes per-partition.

CREATE TABLE events (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id   bigint      NOT NULL,
  occurred_at timestamptz NOT NULL,
  kind        text        NOT NULL,
  payload     jsonb       NOT NULL,
  PRIMARY KEY (occurred_at, id)               -- partition key must be in the PK
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_11 PARTITION OF events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE events_2026_12 PARTITION OF events
  FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

CREATE INDEX ON events (tenant_id, occurred_at DESC);   -- created on each partition

-- Retention becomes a metadata operation instead of a multi-hour DELETE
ALTER TABLE events DETACH PARTITION events_2025_11 CONCURRENTLY;
DROP TABLE events_2025_11;
BenefitWhy
Partition pruningWHERE occurred_at > now() - interval '7 days' touches 1-2 partitions, not 24
Cheap retentionDROP a partition instead of DELETE millions of rows (no bloat, no vacuum storm)
Faster vacuum and index buildsEach partition is small; old partitions are frozen and never vacuumed again
Hot data stays in cacheRecent partitions' indexes fit in RAM

Partitioning is not sharding

All partitions live on the same server, sharing its CPU, RAM, and WAL. Partitioning makes a huge table manageable and some queries faster; it does not add write capacity. Queries that do not filter on the partition key get slower, because they must scan every partition. Use tools like pg_partman to automate partition creation so inserts never fail for a missing future partition.

Signals you've outgrown this rung:

  • Total write throughput, not table size, is the bottleneck.
  • The table is partitioned and maintenance is easy, but the server is still saturated.

Rung 8: Sharding

Split data across multiple independent database servers by a shard key, so each server holds a fraction of the rows and handles a fraction of the writes. This is the only rung that scales writes horizontally on a traditional RDBMS — and the one with by far the highest permanent cost.

flowchart TB
    APP["Application"] --> ROUTER["Shard router<br/>(app library, Vitess, Citus coordinator)"]
    ROUTER -->|"tenant_id hash 0-255"| S1[("Shard 1")]
    ROUTER -->|"256-511"| S2[("Shard 2")]
    ROUTER -->|"512-767"| S3[("Shard 3")]
    ROUTER -->|"768-1023"| S4[("Shard 4")]
    S1 --> S1R[("replica")]
    S2 --> S2R[("replica")]
    S3 --> S3R[("replica")]
    S4 --> S4R[("replica")]
What you gainWhat you lose or must build
Write throughput ≈ N x one serverCross-shard joins and transactions
Dataset size ≈ N x one serverGlobal unique constraints and secondary indexes
Smaller blast radius per shardSimple backups, schema migrations across N databases
Rebalancing / resharding tooling
Hot-shard handling (one tenant bigger than a shard)

The decision that matters is the shard key. Good keys appear in nearly every query (so each query goes to one shard), have high cardinality, and spread load evenly. For multi-tenant SaaS, tenant_id is almost always right. For consumer apps, user_id usually is. A shard key chosen badly is close to unfixable later.

Before choosing, estimate: if the largest tenant/user is more than ~10-20% of one shard's capacity, you need a plan for splitting hot keys from day one.

Strategies (hash, range, directory), resharding, cross-shard queries, and production systems (Vitess, Citus, MongoDB, DynamoDB): Sharding. Partitioning schemes in general: Data Partitioning.

Signals you've outgrown (or should skip) this rung:

  • Too many queries are cross-shard, so the router fans out and latency suffers.
  • You need cross-shard ACID transactions for core flows.
  • The team's operational burden (resharding, per-shard migrations) is consuming a significant fraction of engineering time — distributed SQL may cost less overall.

Rung 9: Distributed SQL / NewSQL

Systems like CockroachDB, YugabyteDB, Google Spanner, and TiDB shard automatically into ranges, replicate each range with consensus (Raft/Paxos), and offer serializable or snapshot-isolated distributed transactions behind a SQL interface — PostgreSQL-compatible or MySQL-compatible wire protocols.

flowchart LR
    C["SQL client"] --> G["Any node<br/>(gateway)"]
    G --> RA["Range A<br/>leaseholder"]
    G --> RB["Range B<br/>leaseholder"]
    RA -->|"Raft"| RA2["Range A replica"]
    RA -->|"Raft"| RA3["Range A replica"]
    RB -->|"Raft"| RB2["Range B replica"]
    RB -->|"Raft"| RB3["Range B replica"]
FactorSingle-node Postgres/MySQLDistributed SQL
Single-row write latency (same region)~1-2 msSeveral ms (consensus round trip)
Multi-region writesAsync replicas only, one writable regionNative, with latency = inter-region RTT
Horizontal write scalingNo (needs app-level sharding)Yes, automatic range splitting
Cross-shard transactionsN/A or manualBuilt-in, with contention costs
CompatibilityFullHigh but incomplete (extensions, some features, planner behavior)
Cost per unit of throughputLowHigher (3x replication, more nodes)

Choose distributed SQL when you need horizontal write scaling and cross-entity transactions, or multi-region writes with strong consistency — and you would otherwise build that yourself on top of sharding. Do not choose it to fix slow queries; a bad query is slower on a distributed database, because it may now touch many nodes.

Details and system comparisons: NewSQL. Geographic topologies: Multi-Region Database. General engine choice: Database Selection Guide.

Sideways Rung: CQRS and Read Models

Sometimes the problem is not volume but shape: the write model is normalized for correctness, but reads need joins across ten tables, full-text search, aggregations over millions of rows, or a feed per user. No index fixes a query that is fundamentally the wrong shape for a row store.

Command Query Responsibility Segregation separates the model you write to from the models you read from. Writes go to the transactional database; changes flow (via CDC or domain events) into read models built for specific queries.

flowchart LR
    CMD["Commands<br/>(place order)"] --> W[("Write DB<br/>PostgreSQL, normalized")]
    W -->|"CDC / outbox<br/>(Debezium, Kafka)"| BUS["Event stream"]
    BUS --> P1["Projector"] --> RM1[("Search index<br/>Elasticsearch")]
    BUS --> P2["Projector"] --> RM2[("Customer dashboard<br/>denormalized table")]
    BUS --> P3["Projector"] --> RM3[("Analytics<br/>ClickHouse")]
    Q["Queries"] --> RM1
    Q --> RM2
    Q --> RM3
Use CQRS read models whenAvoid when
Reads need a fundamentally different shape (search, aggregates, feeds)Simple CRUD with modest read load
Read:write ratio is very high and reads are expensiveUsers need read-your-writes everywhere
Analytics queries are hurting OLTP latencyTeam lacks streaming/CDC operational experience
Multiple consumers need different views of the same dataOne extra index or a materialized view would do

A lighter step before full CQRS: materialized views refreshed on a schedule (REFRESH MATERIALIZED VIEW CONCURRENTLY) or summary tables maintained by triggers. These give you a read model without a second system.

Deep dives: CQRS Deep Dive and Projections.

Decision Flowchart

flowchart TD
    START(["Database is a bottleneck"]) --> Q1{"Top queries in pg_stat_statements<br/>have efficient plans?"}
    Q1 -->|"No"| FIX1["Rung 1: fix queries,<br/>add indexes, kill N+1"]
    FIX1 --> START
    Q1 -->|"Yes"| Q2{"Connections > 4x cores<br/>or pool waits high?"}
    Q2 -->|"Yes"| FIX3["Rung 3: pooler,<br/>right-size pools"]
    FIX3 --> START
    Q2 -->|"No"| Q3{"Larger instance available<br/>and last upgrade scaled well?"}
    Q3 -->|"Yes"| FIX2["Rung 2: scale up"]
    Q3 -->|"No"| Q4{"Load mostly reads?"}
    Q4 -->|"Yes"| Q5{"Hot set / skewed<br/>access?"}
    Q5 -->|"Yes"| FIX4["Rung 4: cache"]
    Q5 -->|"No"| Q6{"Reads tolerate<br/>staleness?"}
    Q6 -->|"Yes"| FIX5["Rung 5: read replicas"]
    Q6 -->|"No, or wrong shape"| FIX10["CQRS read models /<br/>materialized views"]
    Q4 -->|"No: write-bound"| Q7{"Clear domain seam<br/>splitting the load?"}
    Q7 -->|"Yes"| FIX6["Rung 6: functional<br/>partitioning"]
    Q7 -->|"No"| Q8{"Pain is one huge table's<br/>maintenance/retention?"}
    Q8 -->|"Yes"| FIX7["Rung 7: table partitioning"]
    Q8 -->|"No: raw write throughput"| Q9{"Need cross-entity transactions<br/>or multi-region writes?"}
    Q9 -->|"No; clean shard key"| FIX8["Rung 8: shard<br/>(Vitess / Citus / app-level)"]
    Q9 -->|"Yes"| FIX9["Rung 9: distributed SQL"]

Numbers to Anchor Decisions

Order-of-magnitude figures for a well-tuned PostgreSQL on current cloud hardware. Your workload will differ; use these to sanity-check plans, not as promises.

ScenarioRough ceiling
Simple primary-key reads, 32 vCPU, data in RAM100k+ queries/s
Mixed OLTP (indexed reads/writes, small transactions), 32 vCPU10k-30k transactions/s
Single-row inserts with synchronous_commit = on, one NVMe volumeTens of thousands/s with group commit; bound by WAL fsync
Useful active connections~2-4x vCPU before throughput degrades
Table size where partitioning starts paying offHundreds of GB, or whenever retention deletes hurt
Comfortable single-node datasetLow single-digit TB (bounded more by backup/restore and replica rebuild time than by query speed)
Async replica lag, healthy< 100 ms; spikes to seconds under bulk writes
Cache hit latency (Redis, same AZ)0.2-0.5 ms vs 1-5 ms for an indexed DB query

Common Mistakes

MistakeConsequenceBetter
Sharding before measuringMonths of work for a problem one index would fixRung 1 first, with pg_stat_statements evidence
Adding replicas for a write-bound primaryZero gain; replicas add replication loadFind what is writing; partition or shard
Cache with no stampede protectionHot key expiry takes the database downCoalescing, jittered TTL, early refresh
Routing all SELECTs to replicasRead-your-writes bugs, stale write decisionsDecide per endpoint; LSN- or time-based stickiness
Each app pod with a 50-connection poolHundreds of idle connections, memory exhaustionCentral pooler; small per-pod pools
Partitioning on a column queries don't filter byEvery query scans every partitionPartition on the dominant filter (usually time or tenant)
Shard key with hot spots (e.g. country)One shard at 90%, others idleHigh-cardinality key aligned to access pattern
"We'll be web-scale" premature distributed SQLHigher latency and cost for a workload one node handlesProve the single-node ceiling first

Capacity Planning Checklist

Before proposing any rung above 5, write down:

  1. Current peak read QPS, write QPS, dataset size, and growth rate per month.
  2. Top 20 queries by total time, with their plans — and confirmation they are optimized.
  3. Headroom on the current instance (CPU, IOPS, connections, WAL rate) at peak.
  4. Months until the ceiling at current growth, for each resource.
  5. The next rung's cost: engineering time, operational burden, and what capabilities it removes (joins, transactions, constraints).
  6. The exit: how you would reverse the change if it does not work.

If step 4 says "more than 18 months," the right answer is usually to do the cheaper rungs now and revisit next year.

Further Reading


"Every database scaling technique trades a capability for capacity. Spend capabilities slowly — you rarely get them back."