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
| Rung | Typical gain | Complexity added | Reversible? | Time to implement |
|---|---|---|---|---|
| 1. Query/index optimization | 10-1000x on the affected queries | Low | Yes | Hours to days |
| 2. Vertical scaling | 2-8x | None (cost only) | Yes | Minutes to hours |
| 3. Connection pooling | 2-5x under high concurrency | Low | Yes | Days |
| 4. Caching | 5-100x read offload on hot keys | Medium (invalidation) | Mostly | Days to weeks |
| 5. Read replicas | Linear read scaling (N replicas ≈ N x reads) | Medium (lag) | Yes | Days |
| 6. Functional partitioning | Splits load by domain | Medium (no cross-DB joins) | Hard | Weeks to months |
| 7. Table partitioning | Faster maintenance, pruning on huge tables | Low to medium | Hard | Weeks |
| 8. Sharding | Linear write scaling | Very high | Very hard | Months |
| 9. Distributed SQL | Sharding without app-level routing | High (latency, cost, new ops) | Very hard | Months |
| CQRS / read models | Reads served from purpose-built stores | High (eventual consistency) | Hard | Weeks 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:
| Metric | How to get it (PostgreSQL) | Why it matters |
|---|---|---|
| Read/write ratio | pg_stat_database (tup_returned + tup_fetched vs tup_inserted + tup_updated + tup_deleted) | Replicas and caches only help reads |
| Top queries by total time | pg_stat_statements ordered by total_exec_time | Usually 5-10 queries are 80% of load |
| Working set vs. RAM | pg_statio_user_tables cache hit ratio; table and index sizes | If the hot set fits in RAM, vertical scaling is very effective |
| Peak concurrency | pg_stat_activity count by state at peak | Determines 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:
| Problem | Detection | Fix |
|---|---|---|
| Missing index | Seq Scan on a large table in EXPLAIN (ANALYZE, BUFFERS); high seq_tup_read in pg_stat_user_tables | Add a B-tree / composite / partial index matching the predicate and sort |
| Wrong column order in composite index | Index exists but plan uses Filter after Index Scan | Equality columns first, then range, then sort |
| N+1 queries | Same query shape with thousands of calls per request in pg_stat_statements | Batch with WHERE id = ANY($1) or join; see N+1 Queries |
| Over-fetching | SELECT * on wide rows; large shared read buffers | Select needed columns; covering index with INCLUDE |
| Bad estimates | Estimated rows vs actual rows off by 100x | ANALYZE, raise statistics target, extended statistics |
Deep OFFSET pagination | Cost grows linearly with page number | Keyset pagination (WHERE (created_at, id) < ($1, $2)) |
| Unused/duplicate indexes | idx_scan = 0 in pg_stat_user_indexes | Drop 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_statementsall 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.
| Resource | Upgrade helps when | Upgrade does nothing when |
|---|---|---|
| RAM | Cache hit ratio < 99%, working set > shared_buffers + OS cache | Working set already fits |
| CPU cores | Many concurrent queries, CPU saturated, no single hot lock | One slow query dominates; lock contention |
| Storage IOPS/throughput | High iowait, shared read dominates buffers | Everything already in RAM |
| Network | Replication or bulk export saturates NIC | Rarely 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 for | Breaks |
|---|---|---|
| Session | Entire client session | Nothing, but little multiplexing |
| Transaction | One transaction | Session state: SET, advisory locks, LISTEN, (historically) prepared statements |
| Statement | One statement | Multi-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.
| Decision | Options | Guidance |
|---|---|---|
| Pattern | Cache-aside, read-through, write-through, write-behind | Cache-aside is the default; see Caching Strategies |
| Invalidation | TTL, explicit delete on write, CDC-driven | TTL + delete-on-write covers most cases; see Cache Invalidation |
| Stampede protection | Locking, request coalescing, early refresh | Required for any key with > 100 req/s; see Thundering Herd |
| What to cache | Hot entities, expensive aggregates, rendered fragments | Not: 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.
| Anomaly | Example | Mitigation |
|---|---|---|
| Read-your-writes violation | User updates their profile, page reloads, old name shows | Route 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 violation | Refresh shows a comment, next refresh doesn't (hit a more-lagged replica) | Sticky replica per session |
| Causal violation | Reply visible before the message it replies to | Read both from the same replica, or from primary |
| Stale decision | Inventory 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 capability | Replacement |
|---|---|
Cross-domain JOIN | Application-side joins, denormalized copies, or a read model (CQRS) |
| Cross-domain ACID transactions | Sagas, outbox pattern, idempotent consumers |
| Foreign keys across domains | Application-level validation, eventual consistency |
| One backup/restore point | Coordinated 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;
| Benefit | Why |
|---|---|
| Partition pruning | WHERE occurred_at > now() - interval '7 days' touches 1-2 partitions, not 24 |
| Cheap retention | DROP a partition instead of DELETE millions of rows (no bloat, no vacuum storm) |
| Faster vacuum and index builds | Each partition is small; old partitions are frozen and never vacuumed again |
| Hot data stays in cache | Recent 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 gain | What you lose or must build |
|---|---|
| Write throughput ≈ N x one server | Cross-shard joins and transactions |
| Dataset size ≈ N x one server | Global unique constraints and secondary indexes |
| Smaller blast radius per shard | Simple 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"]
| Factor | Single-node Postgres/MySQL | Distributed SQL |
|---|---|---|
| Single-row write latency (same region) | ~1-2 ms | Several ms (consensus round trip) |
| Multi-region writes | Async replicas only, one writable region | Native, with latency = inter-region RTT |
| Horizontal write scaling | No (needs app-level sharding) | Yes, automatic range splitting |
| Cross-shard transactions | N/A or manual | Built-in, with contention costs |
| Compatibility | Full | High but incomplete (extensions, some features, planner behavior) |
| Cost per unit of throughput | Low | Higher (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 when | Avoid 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 expensive | Users need read-your-writes everywhere |
| Analytics queries are hurting OLTP latency | Team lacks streaming/CDC operational experience |
| Multiple consumers need different views of the same data | One 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.
| Scenario | Rough ceiling |
|---|---|
| Simple primary-key reads, 32 vCPU, data in RAM | 100k+ queries/s |
| Mixed OLTP (indexed reads/writes, small transactions), 32 vCPU | 10k-30k transactions/s |
Single-row inserts with synchronous_commit = on, one NVMe volume | Tens 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 off | Hundreds of GB, or whenever retention deletes hurt |
| Comfortable single-node dataset | Low 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
| Mistake | Consequence | Better |
|---|---|---|
| Sharding before measuring | Months of work for a problem one index would fix | Rung 1 first, with pg_stat_statements evidence |
| Adding replicas for a write-bound primary | Zero gain; replicas add replication load | Find what is writing; partition or shard |
| Cache with no stampede protection | Hot key expiry takes the database down | Coalescing, jittered TTL, early refresh |
Routing all SELECTs to replicas | Read-your-writes bugs, stale write decisions | Decide per endpoint; LSN- or time-based stickiness |
| Each app pod with a 50-connection pool | Hundreds of idle connections, memory exhaustion | Central pooler; small per-pod pools |
| Partitioning on a column queries don't filter by | Every query scans every partition | Partition on the dominant filter (usually time or tenant) |
Shard key with hot spots (e.g. country) | One shard at 90%, others idle | High-cardinality key aligned to access pattern |
| "We'll be web-scale" premature distributed SQL | Higher latency and cost for a workload one node handles | Prove the single-node ceiling first |
Capacity Planning Checklist
Before proposing any rung above 5, write down:
- Current peak read QPS, write QPS, dataset size, and growth rate per month.
- Top 20 queries by total time, with their plans — and confirmation they are optimized.
- Headroom on the current instance (CPU, IOPS, connections, WAL rate) at peak.
- Months until the ceiling at current growth, for each resource.
- The next rung's cost: engineering time, operational burden, and what capabilities it removes (joins, transactions, constraints).
- 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
- Indexing Deep Dive and Query Planning & Optimization — rung 1
- Connection Pooling — rung 3
- Caching Strategies — rung 4
- Replication — rung 5
- Sharding — rung 8
- NewSQL and Multi-Region Database — rung 9
- CQRS Deep Dive — read models
- Database CPU at 100% playbook — when the problem is happening right now
"Every database scaling technique trades a capability for capacity. Spend capabilities slowly — you rarely get them back."