End to End: Preparing Bookstore for 10x Growth — Measure, Decide, Apply, Verify

Version note: The examples in this post were run with PostgreSQL 18.7 (two containers: a primary and an async streaming replica), .NET 10, Npgsql 10.0.3 and Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 in a single-machine lab. The SQL Server measurements of earlier posts were on SQL Server 2025 (CU10). The load test is a lab-scale verification; it doesn't replace a real capacity test.

Guess First

Here is the final shape of what we built over the series for Bookstore, the online bookstore used as the shared example across this site's series: a PostgreSQL primary, an async streaming replica, a router that sends reads to the replica or the primary based on lag and the user's last write (post 06), a retry strategy that waits out failover, and order writes with an idempotency key (post 05). Under a load that mimics a campaign day (about 75 orders and 1,300 "My orders" reads per second), we kill the primary with docker kill at second 20 and promote the replica 6 seconds later.

Guess: in these 60 seconds, how many orders fail, how many reads fail, and how many orders that customers were told "your order has been received" are lost?

window   | writes ok/err | reads ok/err | replica% | read p99 ms | write p99 ms
  0-  5s |    361/0     |   6214/0     |     100% |         9.9 |        144.5
  5- 10s |    385/0     |   6752/0     |     100% |         3.3 |          5.0
 10- 15s |    383/0     |   6565/0     |     100% |         3.9 |          8.2
 15- 20s |    360/0     |   6630/0     |     100% |         6.6 |          8.0
 20- 25s |      0/0     |   6780/0     |     100% |         3.7 |          0.0
 25- 30s |    248/0     |   6917/0     |      24% |         2.8 |       7050.2
 30- 35s |    383/0     |   6836/0     |       0% |         5.9 |         22.6
 35- 40s |    384/0     |   6868/0     |       0% |         4.6 |          5.3
 40- 45s |    384/0     |   6837/0     |       0% |         6.3 |         22.6
 45- 50s |    377/0     |   6760/0     |       0% |         7.4 |         25.0
 50- 55s |    379/0     |   6520/0     |       0% |         6.6 |         16.0
 55- 60s |    326/0     |   5323/0     |       0% |        18.9 |         34.7
total: writes ok=3970 failed=0, reads ok=79002 failed=0, read p99=6.5 ms
longest gap between committed writes: 7.1 s
acknowledged orders: 3970, found on the current primary: 3970, lost: 0

Zero, zero, zero. When the primary died, writes stopped for 7.1 seconds but none returned an error; the retries waited for the new primary. Reads never stopped: while there was no primary the replica kept being read, and after the promotion the router moved reads to the new primary. All 3,970 acknowledged orders are on the new primary. Comparing this with the first attempt in post 05 (98 failed orders, 50 lost) shows what the series built. In this post we connect all the steps from start to finish: starting measurements, the target, the gain and cost of every rung, recording the decisions, and verifying with the same metrics.

The Starting Point: Measurements and a Target

In post 01 we started with three numbers. 1,290 orders a day in the last full quarter; the product team's target is 10x in a year, about 12,900 orders a day. About 653 bytes per order including indexes, so about 3.1 GB of new data a year at 10x growth. And a measurable target, the SLO: "the My orders query stays under 50 ms at p99 in 5-minute windows, also at a campaign day's peak load".

That day the "My orders" query ran without an index: 186.9 requests per second with 64 clients, p99 653 ms, 13 times the target. The database wasn't slow; one query was wrong. The ladder's first rung is always this: don't decide to scale before you measure.

Rung by Rung: Gain and Cost

In every post we climbed one rung and measured the gain in the same lab:

The order matters. The index was a single command and cut p99 more than 100-fold; sharding takes months and affects every query. You climb a rung only when the previous one has hit a measured limit. The rungs Bookstore's 10x growth needed were: the index, the pool budget and PgBouncer, the archive job, a replica and a read router, partitioning the orders table. According to the estimate in post 09, even at 10x growth the write load is far below what a single server can carry; sharding isn't part of this path.

Recording Decisions: ADRs

Every rung is an architecture decision: a trade-off was chosen, alternatives were rejected, a cost was accepted. Six months later, the answer to "why did we add the replica, and why not sync?" should live in an ADR. Bookstore's repo has a record for every rung under docs/adr/. Post 06's decision, in a form close to Nygard's template:

# 4. Serve "My orders" and catalog reads from a read replica with lag-aware routing

Status: Accepted (2026-11)

## Context
Orders are expected to grow 10x in a year (1,290/day -> ~12,900/day). SLO: the "My orders" database
query stays under 50 ms at p99 in 5-minute windows, also at campaign peaks. Reads are ~95% of
database calls. The primary also serves checkout, payments and stock, and must keep headroom for them.

## Decision
- Add one async streaming replica; use it for reads that tolerate a few seconds of staleness.
- BookstoreReadContext (no tracking, no SaveChanges) on Npgsql "prefer-standby"; writes on "primary".
- ReadRouter sends a read to the primary when the replica is unreachable, lags more than 2 s, or has
  not replayed the user's last write LSN.
- Checkout, payment, stock and "decide-then-write" reads always stay on the primary.

## Consequences
- Read-your-writes holds for the writing user; other users may see data up to 2 s old.
- When the replica falls behind, all reads go to the primary: the primary is sized for the full read load.
- Two pools per API instance: the connection budget counts both against the primary.
- Long reports move to a separate reporting database (logical replication), not to this replica.

## Verification
- 5-minute load at campaign peak: read p99 < 50 ms, 0 errors (see post 10 measurements).
- Quarterly failover drill: write gap and lost acknowledged orders are recorded.

The record is in English like the code, because it lives in the same repo and is reviewed together with the code in pull requests. The most important section is "Verification": it states up front which measurement tells us whether the decision worked. The details of writing ADRs and the templates are in the Architecture Decisions series' post on writing ADRs.

Verification: Measuring the SLO Under Load

The measurement the decision record promises is a 5-minute load without failover: 4 writers each place an order every 50 ms, and 16 readers each read a random customer's "My orders" list every 10 ms. Every write has an idempotency key and retries, as in post 05, and every read goes through post 06's router:

await using BookstoreReadContext read = readFactory.Create(null, out target);
await read.Orders
    .Where(o => o.CustomerId == customerId)
    .OrderByDescending(o => o.CreatedAt)
    .Take(20)
    .ToListAsync();

window   | writes ok/err | reads ok/err | replica% | read p99 ms | write p99 ms
  0- 60s |   4568/0     |  80728/0     |     100% |         2.9 |          6.7
 60-120s |   4584/0     |  80484/0     |     100% |         3.4 |          6.5
120-180s |   4560/0     |  79754/0     |     100% |         5.3 |         15.8
180-240s |   4593/0     |  80463/0     |     100% |         3.7 |          6.3
240-300s |   4529/0     |  79657/0     |     100% |         4.4 |         12.0
total: writes ok=22834 failed=0, reads ok=401086 failed=0, read p99=4.0 ms
longest gap between committed writes: 0.1 s
acknowledged orders: 22834, found on the current primary: 22834, lost: 0
pg1 max client connections 21
pg2 max client connections 18

Read p99 over the 5-minute window is 4.0 ms; the target is 50 ms. The write side runs at about 76 orders per second, a rate equivalent to 6.5 million orders a day; about 500 times the 10x target (12,900 a day). All reads were served by the replica and the primary only dealt with writes. Connection counts are within post 02's budget: at most 21 client connections on the primary and 18 on the replica.

This is a lab verification, not a capacity test. A real load test is run with production-like data size, a real traffic mix (catalog, search, cart, checkout, reports), an explicit arrival rate, and with results read correctly against coordinated omission. All of that is in the Performance and Load Testing series: workload modeling, arrival-rate scenarios with k6, and interpreting results. What this series hands over to that test is three things: the SLO sentence, the metrics to measure (read and write p99, error count, replica lag, connection counts) and the failure scenarios to test.

The Failover Drill

The table at the top of the post was the output of a game day. As promised in post 05, the drill was defined with measurable questions, and each one was measured:

• How many seconds did writes stop, that is, what was the measured RTO? The longest gap between two consecutive successful commits was 7.1 seconds: the 6 seconds from the primary's death to the promotion, plus a retry interval.
• How many requests returned errors? Zero. Writes waited with retries; in those 7 seconds write p99 rose to 7,050 ms. That sets the upper bound for the HTTP timeout and how long a user waits.
• How many acknowledged orders were lost, that is, what was the measured RPO? Zero, but that was luck: the async replica had caught up when the primary died. In post 05, 50 orders were lost while the replica's network was cut; guaranteeing zero RPO takes sync replication and at least two replicas (post 04).
• What happened to reads? No errors at all. While there was no primary, the replica kept serving stale but consistent data; after the promotion the router couldn't find a replica and handed reads to the new primary.

The drill showed one more thing: after the promotion no replica was left and the whole read load landed on the primary (replica share 0% in the last 30 seconds, read p99 up to 18.9 ms). The first job after a failover is building a new replica; until then the primary must carry the whole load. A tool such as Patroni brings the old primary back as a replica, but there was no Patroni in this lab and we promoted by hand.

The Restore Drill

A replica isn't a backup (post 05): a DELETE run by mistake on the primary is applied to the replica too. The second half of the drill is showing that we can really come back from a backup. We restored a base backup taken from the new primary with pg_basebackup into an empty container and compared counts:

gosu postgres pg_basebackup -h pg2 -U replicator -D "$PGDATA" -X stream -c fast

restored and accepting connections in 3 s
 in_recovery | orders  | last_order | keyed_orders
-------------+---------+------------+--------------
 f           | 1028272 |    1028286 |        28272

On the source the same query returned 1028272 | 1028286 | 28272. A 698 MB cluster came up in 3 seconds on the same machine; in production the backup comes from storage in another region and the size is hundreds of GB, so it takes hours. That time is exactly what needs measuring, because it's the RTO of DR. Returning to a specific moment (point-in-time recovery) needs a continuous WAL archive in addition to the base backup; we didn't set up a WAL archive in the lab. Bookstore's target was set in post 05: for DR, an RPO of five minutes and an RTO of one hour. The restore drill should measure every quarter whether that target is realistic.

Checklist

The items we check before saying Bookstore is ready for 10x growth:

• Connection budget: instances × pool size × number of pools (write and read) stays under the primary's max_connections; calculated with the HPA's maximum pod count (02).
• Replica lag alert: when lag passes 2 seconds the router falls back to the primary and an alert fires; "share of reads served by the replica" is watched too (04, 06).
• Alert on unused replication slots, and max_slot_wal_keep_size (04).
• Partition maintenance job: the next 3 months are open; alert if the furthest partition is less than 2 months away; check for half-finished detaches; ANALYZE ONLY for the parent (07, 08).
• Archive retention: months older than 3 years are in the archive; how long the archive is kept is agreed in writing with the legal team (03, 07, 08).
• Retry and idempotency: all write endpoints take an idempotency key; the retry strategy recognises the errors seen during failover; the HTTP timeout is longer than the retry time (05).
• Failover and restore drills: every quarter, before the campaign season; the measured RTO and RPO are recorded.
• An ADR for every rung, with a verification measurement in it.

In Production

Don't stop measuring. The SLO measurement in this post shouldn't be a one-off test but a metric computed continuously in production over 5-minute windows. If per-query time and wait distribution as in post 01, connection counts and replica lag sit on the same dashboard, the data is ready when it's time for the next rung.

Let measurements decide the ladder's next rung. If the primary's CPU, WAL volume, disk write latency and the growth rate of the biggest tables are tracked, you find the time for vertical scaling, a second replica or sharding from trends, not guesses.

Run the drill with the real components. In the lab we triggered failover by hand; in production Patroni, SQL Server's AG listener, DNS TTLs, load balancer health checks and alerts are part of the drill too. Whether they all work together only shows in a real drill.

Plan campaigns ahead: before a big campaign, the pools' minimum size, filling the cache ahead of time and the number of replicas should be set from the load test's results.

Common Pitfalls

Climbing a rung without measuring: the answer to post 01's "the database is slow" complaint was a single index. Sharding or a bigger server would have solved the same problem at a higher price.

Looking at averages: the SLO is defined on p99; in the drill with failover the read average barely moved, but write p99 went up to 7 seconds.

Assuming zero loss with an async replica: nothing was lost in this drill, 50 orders were lost in post 05. The difference was the replica's state at that moment, not the architecture.

Not recording decisions: if why the replica and the router were set up this way isn't written down, at the first big lag someone moves all reads to the primary "temporarily" and nobody moves them back.

Testing failover but not restore: a replica doesn't recover data deleted by mistake; without knowing the restore time, the DR target is a guess.

A Small Experiment

★☆☆ Repeat the drill with synchronous_standby_names = 'pg2'. How much does write p99 rise in normal operation, and does the write gap change during failover?

★★☆ Add a second replica to the drill and make reads go to the remaining replica instead of falling back to the primary during the promotion. Compare read p99 in the last 30 seconds and the connection count on the primary.

★★★ Set up a WAL archive for Bookstore (archive_mode and archive_command to a folder), make a DELETE mistake and use point-in-time recovery to go back to one second before the mistake. Compare the RTO you measure with post 05's DR target (one hour).

This is where the series ends. We started by measuring Bookstore's database, climbed the ladder's rungs one by one and paid the cost of each in the same lab. The next step is outside this series: the details of load testing are in the Performance and Load Testing series, the theory of replication and consistency in the Distributed Systems Fundamentals series, and system-level decisions in the System Design series.