c. Replica & Pooling

During the sale, for every fan who holds a seat, dozens look at the seat map. Those reads do not need the primary: a read replica — a copy of the database kept up to date by streaming the primary's write-ahead log (WAL) — can serve them. Two costs come with it: the copy is slightly behind (replication lag), and you now route queries to two places. This step also adds PgBouncer, because every replica of every service opening its own pool of connections is how PostgreSQL runs out of connections.

1. Streaming replication

sequenceDiagram
    autonumber
    participant I as inventory
    participant P as db (primary)
    participant R as db-replica (hot standby)
    Note over R: first start: pg_basebackup copies the primary
    I->>P: BEGIN; UPDATE seats ...; COMMIT
    P->>P: write WAL record, flush
    P-->>I: COMMIT ok (asynchronous replication: does not wait for R)
    P->>R: stream WAL
    R->>R: replay (after recovery_min_apply_delay)
    I->>R: SELECT availability (readOnly tx)
    R-->>I: result, possibly a few ms old

The primary needs a user allowed to replicate. This script runs once, when the primary's volume is created:

infra/postgres/10-replication.sh
#!/bin/bash
# Runs once, when the primary's data directory is created (docker-entrypoint-initdb.d).
# Creates the user the replica connects with and allows replication connections.
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-SQL
    CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '${REPLICATION_PASSWORD:-replicator}';
    -- pg_rewind (step 4) needs it; ALTER SYSTEM keeps it in the data directory, where pg_rewind looks
    ALTER SYSTEM SET wal_log_hints = on;
SQL
echo "host replication replicator all scram-sha-256" >> "$PGDATA/pg_hba.conf"

The replica clones the primary on its first start and then follows it:

infra/postgres/replica-entrypoint.sh
#!/bin/bash
# Starts a streaming replica of "db". The first time, it clones the primary with
# pg_basebackup; -R writes standby.signal + primary_conninfo, so PostgreSQL starts
# as a read-only hot standby that keeps applying the primary's WAL.
set -e
if [ ! -s "$PGDATA/PG_VERSION" ]; then
  echo "cloning the primary..."
  until pg_basebackup -h db -U replicator -D "$PGDATA" -R -X stream -c fast -P; do
    echo "primary not ready yet, retrying in 2 s"; sleep 2
  done
  chmod 0700 "$PGDATA"
fi
# REPLICA_DELAY (e.g. 2s) makes the replica apply changes late: replication lag on demand;
# cluster_name is the name the primary sees (synchronous_standby_names, step 4)
exec postgres -c hot_standby=on -c recovery_min_apply_delay="${REPLICA_DELAY:-0}" -c cluster_name=db-replica

Recreate the database volume

10-replication.sh only runs on an empty data directory. Your db-data volume exists since lab 1, so recreate it (Flyway seeds everything again on startup): docker compose down -v before the first up of this lab. Remember to chmod +x infra/postgres/*.sh.

2. PgBouncer

Each PostgreSQL connection is an operating-system process with its own memory; a few hundred are fine, thousands are not. Count the clients: 3 catalog replicas × 10 + auth × 10 + inventory × 20 × 2 pools… and in lab 10 Kubernetes will autoscale replicas. PgBouncer accepts many cheap client connections and multiplexes them onto a small pool of real ones.

infra/pgbouncer/pgbouncer.ini
; PgBouncer: thousands of client connections, a few dozen real PostgreSQL connections.
[databases]
boxoffice = host=db port=5432 dbname=boxoffice

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = boxoffice

; transaction pooling: a server connection is lent for ONE transaction, then returned
pool_mode = transaction
; real connections to PostgreSQL per (database, user) pair
default_pool_size = 20
; client connections PgBouncer accepts (all services and replicas together)
max_client_conn = 1000
; protocol-level prepared statements work in transaction mode (PgBouncer >= 1.21)
max_prepared_statements = 200
ignore_startup_parameters = extra_float_digits,options

infra/pgbouncer/userlist.txt

"boxoffice" "boxoffice"

Pool mode	A server connection is lent for…	Breaks
`session`	the whole client connection	nothing, but saves little
`transaction`	one transaction	session state: `SET`, session advisory locks, `LISTEN`, temp tables
`statement`	one statement	multi-statement transactions

Transaction pooling is what makes PgBouncer effective — and why Flyway does not go through it: migrations use session-level locks. inventory therefore has two hosts: DB_HOST=pgbouncer for the application, DB_MIGRATION_HOST=db for Flyway.

3. Compose

docker-compose.yaml
name: boxoffice

# settings shared by the three catalog replicas (a YAML anchor)
x-catalog: &catalog
  build: ./services/catalog
  image: boxoffice-catalog
  environment:
    DB_HOST: db
    DB_PORT: 5432
    DB_NAME: ${DB_NAME:-boxoffice}
    DB_USER: ${DB_USER:-boxoffice}
    DB_PASSWORD: ${DB_PASSWORD:-boxoffice}
    DB_POOL_SIZE: ${DB_POOL_SIZE:-10}
    REDIS_HOST: redis
    REDIS_PORT: 6379
    CACHE_ENABLED: ${CACHE_ENABLED:-true}
    CACHE_TTL_EVENT: ${CACHE_TTL_EVENT:-60s}
    CACHE_TTL_LIST: ${CACHE_TTL_LIST:-10s}
    CACHE_STAMPEDE_PROTECTION: ${CACHE_STAMPEDE_PROTECTION:-false}
  depends_on:
    db:
      condition: service_healthy
    redis:
      condition: service_healthy
  deploy:
    resources:
      limits:
        cpus: "1.0"
        memory: 768M
  healthcheck:
    test: ["CMD-SHELL", "wget -qO- http://localhost:8082/actuator/health/readiness || exit 1"]
    interval: 10s
    timeout: 3s
    start_period: 40s
    retries: 3

services:

  db:
    image: postgres:17
    command: ["postgres", "-c", "max_connections=${DB_MAX_CONNECTIONS:-100}"]
    environment:
      POSTGRES_DB: ${DB_NAME:-boxoffice}
      POSTGRES_USER: ${DB_USER:-boxoffice}
      POSTGRES_PASSWORD: ${DB_PASSWORD:-boxoffice}
      REPLICATION_PASSWORD: ${REPLICATION_PASSWORD:-replicator}
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
      # runs only when the volume is new: creates the "replicator" user
      - ./infra/postgres/10-replication.sh:/docker-entrypoint-initdb.d/10-replication.sh:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $${POSTGRES_USER} -d $${POSTGRES_DB}"]
      interval: 5s
      timeout: 3s
      retries: 10

  # read-only streaming replica of db (hot standby)
  db-replica:
    image: postgres:17
    user: postgres
    entrypoint: ["/usr/local/bin/replica-entrypoint.sh"]
    environment:
      PGPASSWORD: ${REPLICATION_PASSWORD:-replicator}     # used by pg_basebackup
      REPLICA_DELAY: ${REPLICA_DELAY:-0}                   # e.g. 2s: artificial lag
    ports:
      - "5433:5432"
    volumes:
      - replica-data:/var/lib/postgresql/data
      - ./infra/postgres/replica-entrypoint.sh:/usr/local/bin/replica-entrypoint.sh:ro
    depends_on:
      db:
        condition: service_healthy
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U boxoffice -d boxoffice"]
      interval: 5s
      timeout: 3s
      retries: 20

  pgbouncer:
    image: edoburu/pgbouncer:v1.26.0-p0
    ports:
      - "6432:6432"
    volumes:
      - ./infra/pgbouncer/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro
      - ./infra/pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt:ro
    depends_on:
      db:
        condition: service_healthy

  redis:
    image: redis:8-alpine
    # a pure cache: bounded memory, evict least-recently-used keys, no persistence
    command: ["redis-server", "--maxmemory", "256mb", "--maxmemory-policy", "allkeys-lru",
              "--save", "", "--appendonly", "no"]
    ports:
      - "6379:6379"
    healthcheck:
      test: ["CMD", "redis-cli", "ping"]
      interval: 5s
      timeout: 3s
      retries: 10

  # three identical replicas; no "ports:" — only the edge is reachable from outside
  catalog-1:
    <<: *catalog
    hostname: catalog-1
  catalog-2:
    <<: *catalog
    hostname: catalog-2
  catalog-3:
    <<: *catalog
    hostname: catalog-3

  inventory:
    build: ./services/inventory
    environment:
      DB_HOST: ${INVENTORY_DB_HOST:-pgbouncer}      # writes go through PgBouncer...
      DB_PORT: ${INVENTORY_DB_PORT:-6432}
      DB_MIGRATION_HOST: db                         # ...migrations straight to the primary
      DB_REPLICA_HOST: ${DB_REPLICA_HOST:-db-replica}
      DB_REPLICA_PORT: 5432
      DB_NAME: ${DB_NAME:-boxoffice}
      DB_USER: ${DB_USER:-boxoffice}
      DB_PASSWORD: ${DB_PASSWORD:-boxoffice}
      DB_POOL_SIZE: ${INVENTORY_POOL_SIZE:-20}
      INVENTORY_LOCKING: ${INVENTORY_LOCKING:-pessimistic}
      INVENTORY_HOLD_TTL: ${INVENTORY_HOLD_TTL:-PT10M}
    ports:
      - "8083:8083"            # published for experiments; clients go through the edge
    depends_on:
      db:
        condition: service_healthy
      db-replica:
        condition: service_healthy
      pgbouncer:
        condition: service_started
    deploy:
      resources:
        limits:
          cpus: "1.0"
          memory: 768M
    healthcheck:
      test: ["CMD-SHELL", "wget -qO- http://localhost:8083/actuator/health/readiness || exit 1"]
      interval: 10s
      timeout: 3s
      start_period: 40s
      retries: 3

  auth:
    build: ./services/auth
    environment:
      DB_HOST: db
      DB_PORT: 5432
      DB_NAME: ${DB_NAME:-boxoffice}
      DB_USER: ${DB_USER:-boxoffice}
      DB_PASSWORD: ${DB_PASSWORD:-boxoffice}
      JWT_ISSUER: ${JWT_ISSUER:-boxoffice-auth}
      JWT_TTL: ${JWT_TTL:-PT1H}
      ADMIN_EMAIL: ${ADMIN_EMAIL:-admin@boxoffice.dev}
      ADMIN_PASSWORD: ${ADMIN_PASSWORD:-boxoffice-admin}
    depends_on:
      db:
        condition: service_healthy
    deploy:
      resources:
        limits:
          cpus: "1.0"
          memory: 768M
    healthcheck:
      test: ["CMD-SHELL", "wget -qO- http://localhost:8081/actuator/health/readiness || exit 1"]
      interval: 10s
      timeout: 3s
      start_period: 40s
      retries: 3

  gateway:
    build: ./services/gateway
    environment:
      AUTH_URL: http://auth:8081
      INVENTORY_URL: http://inventory:8083
      AUTH_JWKS_URI: http://auth:8081/auth/.well-known/jwks.json
      JWT_ISSUER: ${JWT_ISSUER:-boxoffice-auth}
    ports:
      - "8080:8080"          # published for experiments; clients use the edge (port 80)
    depends_on:
      - auth
      - inventory
      - catalog-1
      - catalog-2
      - catalog-3
    deploy:
      resources:
        limits:
          cpus: "1.0"
          memory: 768M
    healthcheck:
      test: ["CMD-SHELL", "wget -qO- http://localhost:8080/actuator/health/readiness || exit 1"]
      interval: 10s
      timeout: 3s
      start_period: 40s
      retries: 3

  edge:
    image: nginx:1.30-alpine
    ports:
      - "80:80"
    volumes:
      - ./infra/nginx/nginx.conf:/etc/nginx/nginx.conf:ro
    depends_on:
      - gateway

volumes:
  db-data:
  replica-data:

chmod +x infra/postgres/*.sh
docker compose down -v
docker compose up --build -d
docker compose exec db psql -U boxoffice -c "SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication"
docker compose exec db-replica psql -U boxoffice -c "SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS lag"

The lag column is the time since the replica replayed the last transaction: while the primary is idle it keeps growing even though the replica is fully up to date. Compare sent_lsn and replay_lsn on the primary for the real distance.

docker compose exec db psql -U boxoffice -c "SELECT client_addr, state FROM pg_stat_replication" client_addr | state
-------------+-----------
172.22.0.4 | streamingdocker compose exec db-replica psql -U boxoffice -c "INSERT INTO inventory.holds VALUES (gen_random_uuid(), 1, 'x', 'ACTIVE', now(), now())"ERROR: cannot execute INSERT in a read-only transaction

4. Routing reads to the replica

services/inventory/src/main/java/boxoffice/inventory/DataSourceConfig.java
package boxoffice.inventory;

import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;

/**
 * Two connection pools — primary and replica — behind one DataSource.
 * A transaction marked @Transactional(readOnly = true) gets a replica connection;
 * every other transaction gets a primary connection. The choice happens when the
 * transaction runs its first statement (that is what "lazy" means here).
 */
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("inventory.datasource.primary")
    HikariDataSource primaryDataSource() {
        return new HikariDataSource();
    }

    @Bean
    @ConfigurationProperties("inventory.datasource.replica")
    HikariDataSource replicaDataSource() {
        return new HikariDataSource();
    }

    @Bean
    @Primary
    DataSource dataSource(HikariDataSource primaryDataSource, HikariDataSource replicaDataSource) {
        var routing = new LazyConnectionDataSourceProxy(primaryDataSource);
        routing.setReadOnlyDataSource(replicaDataSource);     // Spring Framework 6.2+
        return routing;
    }
}

LazyConnectionDataSourceProxy hands out a placeholder connection when a transaction starts and fetches the real one at the first statement — by then Spring knows whether the transaction is readOnly, and picks the pool. The rule becomes an annotation:

Method	Annotation	Goes to	Why
`SeatQueries.availability`, `seats`	`@Transactional(readOnly = true)`	replica	browsing; a few ms of staleness is fine
`HoldService.hold`, `confirm`, `release`	`@Transactional`	primary	writes
`HoldService.find`	`@Transactional`	primary	read-your-writes: the fan just created this hold
`SeatQueries.doubleBookedSeats`	`@Transactional`	primary	an audit must not be stale

Both pools publish metrics (hikaricp.connections.active{pool=primary|replica}): check them while a test runs, curl -s 'localhost:8083/actuator/metrics/hikaricp.connections.active?tag=pool:replica' | jq.

5. Experiments

Replica offload. Run the read load against the replica, then point the "replica" pool at the primary and run it again — no code change:

load/availability.js
// Lab 6: read traffic on the seat map during the sale. These GETs are readOnly
// transactions, so they run on the replica (or on the primary, if you point
// DB_REPLICA_HOST at it to compare).
//   k6 run -e RATE=200 load/availability.js
import http from 'k6/http';
import { check } from 'k6';
import { BASE_URL } from './lib.js';

const RATE = Number(__ENV.RATE || 200);

export const options = {
  scenarios: {
    readers: {
      executor: 'constant-arrival-rate', rate: RATE, timeUnit: '1s', duration: __ENV.DURATION || '1m',
      preAllocatedVUs: Math.max(20, RATE), maxVUs: Math.max(200, RATE * 10),
    },
  },
  summaryTrendStats: ['avg', 'med', 'p(95)', 'p(99)', 'max'],
  thresholds: {
    http_req_failed: ['rate<0.01'],
    'http_req_duration{name:availability}': ['p(99)<1000'],
    'http_req_duration{name:section}': ['p(99)<1000'],
  },
};

export default function () {
  if (Math.random() < 0.5) {
    const res = http.get(`${BASE_URL}/inventory/events/1/availability`, { tags: { name: 'availability' } });
    check(res, { 'availability is 200': (r) => r.status === 200 });
  } else {
    const section = `S${String(1 + Math.floor(Math.random() * 30)).padStart(2, '0')}`;
    const res = http.get(`${BASE_URL}/inventory/events/1/seats?section=${section}`, { tags: { name: 'section' } });
    check(res, { 'section is 200': (r) => r.status === 200 });
  }
}

k6 run -q -e RATE=200 -e DURATION=30s load/availability.js > /dev/null   # warm-up: the seat-map queries are still cold
k6 run -e RATE=200 load/availability.js                     # reads on db-replica
DB_REPLICA_HOST=db docker compose up -d --wait inventory    # --wait: until the recreated inventory is healthy
k6 run -q -e RATE=200 -e DURATION=30s load/availability.js > /dev/null   # warm-up: a new JVM
k6 run -e RATE=200 load/availability.js                     # reads on the primary
docker compose up -d --wait inventory                       # back to the replica

Keep docker stats open: the CPU moves from db to db-replica. The seat-map latency barely changes — a replica does not make a query faster, it adds capacity and keeps reads from competing with the writes of the sale.

Read-your-writes. A fan holds a seat and immediately reloads the seat map, served by the replica:

load/read-your-writes.js
// Lab 6: hold a seat, then immediately look at the seat map (served by the replica).
// Does the fan see their own seat as HELD? With replication lag, sometimes not.
//   REPLICA_DELAY=1s docker compose up -d db-replica   (artificial lag)
//   bash scripts/reset-inventory.sh && k6 run load/read-your-writes.js
import http from 'k6/http';
import { check } from 'k6';
import { Rate } from 'k6/metrics';
import { BASE_URL, loginFans, authHeaders } from './lib.js';

http.setResponseCallback(http.expectedStatuses(200, 201, 204, 409));

const stale = new Rate('stale_reads');

export const options = {
  vus: 10,
  iterations: 200,
  thresholds: { stale_reads: ['rate>=0'] },
};

export function setup() {
  const seats = http.get(`${BASE_URL}/inventory/events/2/seats?section=S01`).json();
  return { tokens: loginFans(10), seatIds: seats.map((s) => s.id) };
}

export default function (data) {
  const token = data.tokens[__VU % data.tokens.length];
  const seatId = data.seatIds[Math.floor(Math.random() * data.seatIds.length)];
  const hold = http.post(`${BASE_URL}/inventory/holds`, JSON.stringify({ eventId: 2, seatIds: [seatId] }), authHeaders(token));
  if (hold.status !== 201) {
    return;                                          // seat already taken: try another one
  }
  // read-after-write: the seat map comes from the replica
  const map = http.get(`${BASE_URL}/inventory/events/2/seats?section=S01`).json();
  const seat = map.find((s) => s.id === seatId);
  stale.add(seat.status !== 'HELD');
  check(seat, { 'my seat shows as HELD': (s) => s.status === 'HELD' });
  // and from the primary (GET /holds/{id} is not readOnly): always up to date
  const mine = http.get(`${BASE_URL}/inventory/holds/${hold.json('id')}`, authHeaders(token));
  check(mine, { 'my hold is visible on the primary': (r) => r.status === 200 });
  http.del(`${BASE_URL}/inventory/holds/${hold.json('id')}`, null, authHeaders(token));
}

bash scripts/reset-inventory.sh && k6 run load/read-your-writes.js    # lag ~ milliseconds
REPLICA_DELAY=1s docker compose up -d db-replica && sleep 10
bash scripts/reset-inventory.sh && k6 run load/read-your-writes.js    # lag of 1 second
REPLICA_DELAY=0 docker compose up -d db-replica

Even with no artificial delay, stale_reads is not zero: in a test run for this lab it was about 6% — the replica on the same machine is only milliseconds behind, but the fan's next request arrives within milliseconds too. With REPLICA_DELAY=1s it was 98%: almost no fan saw their own hold on the seat map, although GET /inventory/holds/{id} (primary) shows it. In production, lag grows with write volume, network distance and long queries on the replica — exactly during a flash sale.

Ways out: read your own recent writes from the primary (what find does), route a user to the primary for a few seconds after they write ("sticky" reads), or wait until the replica has replayed the write's LSN (log sequence number).

PgBouncer. Count connections during the flash sale, with and without the pooler:

# during load/flash-sale.js:
docker compose exec db psql -U boxoffice -c "SELECT usename, application_name, count(*) FROM pg_stat_activity GROUP BY 1, 2"
docker compose exec -e PGPASSWORD=boxoffice db psql -h pgbouncer -p 6432 -U boxoffice pgbouncer -c "SHOW POOLS"

# without PgBouncer:
INVENTORY_DB_HOST=db INVENTORY_DB_PORT=5432 docker compose up -d inventory

In a test run for this lab, pg_stat_activity showed about 60 application connections: 30 from the three catalog replicas, 10 from auth, and 20 from PgBouncer — which served all of inventory's client connections. SHOW POOLS showed cl_active 20 against sv_active + sv_idle + sv_used ≤ 20.

Push it: restart with DB_MAX_CONNECTIONS=80 INVENTORY_POOL_SIZE=40 docker compose up -d. Through PgBouncer everything works (PostgreSQL still sees at most 20 connections from it). Directly against db (add INVENTORY_DB_HOST=db INVENTORY_DB_PORT=5432 to that same command), 30 + 10 + 40 connections, plus the idle ones PgBouncer still keeps open, exceed 80 (boxoffice is a superuser, so the reserved superuser slots do not help): PostgreSQL refuses every new connection with FATAL: sorry, too many clients already (docker compose logs db). inventory's pool stops growing short of 40 (31 in a test run for this lab), and every other new client is refused too — psql and scripts/reset-inventory.sh included. A pooler turns "too many clients" into queueing (cl_waiting). Back to the defaults afterwards: docker compose up -d --wait.

In the interview

"Add read replicas" is only an answer for read-heavy load that tolerates staleness. Say which reads go to replicas, how you handle read-your-writes, and what happens to writes — replicas do nothing for them; that needs partitioning (class 6) or less work per write.

Next: 3. Indexes and EXPLAIN.