Realistic test data

By the end of this chapter the lab database holds a million orders over fifty thousand customers, generated deterministically; query plans use the indexes from chapter 04; and you have CSV feeder files of real IDs ready for Gatling in chapter 06. You also have a chosen reset strategy, because a load test that writes must leave the data it found — or the next run is measuring a different system.

Why small datasets lie

A thousand-row orders table makes every code path look fast, for reasons that have nothing to do with your code:

• PostgreSQL stops using indexes. Below a few thousand pages, the planner correctly prefers sequential scans — reading the whole table is faster than index lookups when the whole table is a handful of disk pages. A missing index is invisible in a small lab and catastrophic in production.
• Everything is cached. Fifty thousand rows of orders is a few hundred MB at most; the entire working set fits in shared_buffers and the OS page cache. Disk behaviour — the thing that dominates real-world tail latency — never happens.
• N+1 stays hidden. One extra query per row × 20-row pages against a warm, tiny database costs single-digit milliseconds. The same code at production scale saturates the connection pool.
• Pagination depth is fake. OFFSET 20 and OFFSET 200000 are different operations; only a large table shows it.

The dataset must therefore be large enough that (a) indexes beat scans decisively, and (b) the working set meaningfully exceeds what any single cache can hold. Example assumption: 50,000 customers, 1,000,000 orders, ~3,000,000 order items — roughly a few GB on disk. Large enough to exercise indexes and deep pagination, small enough to seed in under a minute on a laptop.

Enable pg_stat_statements first

Chapter 09 needs query-level evidence. pg_stat_statements requires a shared library preload, so add a command to the postgres service in compose.yaml:

1
  postgres:
2
    image: postgres:18-alpine
3
    command: >
4
      postgres
5
      -c shared_preload_libraries=pg_stat_statements
6
      -c pg_stat_statements.track=all
7
    environment:
8
      ...

Then recreate the container: docker compose up -d --force-recreate postgres.

The seed script

db/init/02-seed.sql runs once on an empty volume, after 01-schema.sql. It generates data with generate_series — deterministic, fast, and free of external tools. The distributions are deliberate, not random noise: read on.

1
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
2

3
-- 50,000 customers
4
INSERT INTO customers (name, email, created_at)
5
SELECT 'Customer ' || i,
6
       'customer' || i || '@example.com',
7
       now() - (i || ' minutes')::interval
8
FROM generate_series(1, 50000) AS i;
9

10
-- 1,000,000 orders.
11
-- Customer assignment is skewed: i % N concentrates ~10% of orders on
12
-- ~1% of customers, the way real "power users" concentrate traffic.
13
-- Status distribution (deterministic): 40% DELIVERED, 25% SHIPPED,
14
-- 15% PAID, 12% PLACED, 8% CANCELLED.
15
INSERT INTO orders (customer_id, status, total_amount, created_at, updated_at)
16
SELECT
17
    CASE WHEN i % 10 = 0 THEN 1 + (i % 500)      -- hot customers
18
         ELSE 1 + (i % 50000) END,               -- the long tail
19
    CASE WHEN i % 100 < 40 THEN 'DELIVERED'
20
         WHEN i % 100 < 65 THEN 'SHIPPED'
21
         WHEN i % 100 < 80 THEN 'PAID'
22
         WHEN i % 100 < 92 THEN 'PLACED'
23
         ELSE 'CANCELLED' END,
24
    (10 + (i % 5000) / 10.0)::numeric(12,2),
25
    now() - ((i % 525600) || ' minutes')::interval,  -- spread over ~1 year
26
    now() - ((i % 525600) || ' minutes')::interval
27
FROM generate_series(1, 1000000) AS i;
28

29
-- ~3 items per order on average (1..5)
30
INSERT INTO order_items (order_id, sku, quantity, unit_price)
31
SELECT o.id,
32
       'SKU-' || (o.id % 10000),
33
       1 + (o.id % 5),
34
       (5 + (o.id % 2000) / 10.0)::numeric(12,2)
35
FROM orders o
36
JOIN generate_series(1, 3) AS n ON true;
37

38
ANALYZE;

Three decisions worth copying:

• Deterministic over random. i % 100 patterns give the same data on every fresh volume, so run sheets in chapter 02 can compare row counts and expect identical plans. random() would make each seeded database a slightly different system.
• Skew is a feature. The i % 10 = 0 rule makes 500 customers carry ~10% of all orders — because real customer distributions are skewed, and a uniform distribution makes every “customer search” artificially cheap (a typical customer has ~18 orders; a hot one has ~200 — ten pages deep instead of one).
• ANALYZE at the end. The planner’s row estimates are empty until statistics exist; without this, the first queries after seeding get plans chosen for an empty table.

Apply it — wipe and reseed:

1
docker compose down -v && docker compose up -d
2
docker compose exec postgres psql -U orders -d orders -c \
3
  "SELECT (SELECT count(*) FROM customers) AS customers,
4
          (SELECT count(*) FROM orders)     AS orders,
5
          (SELECT count(*) FROM order_items) AS items;"

Expect 50000 | 1000000 | 3000000. Measured on this lab’s machine: ~85 seconds total (the items insert is the long pole) — a minute or two is normal; if it takes far longer, check that 01-schema.sql and 02-seed.sql are the only files in db/init/ and that the volume really was empty.

Prove the indexes work — before load arrives

Do not wait for a load test to learn whether the planner uses your indexes:

1
EXPLAIN (ANALYZE, BUFFERS)
2
SELECT id, customer_id, status, total_amount, created_at
3
FROM orders
4
WHERE customer_id = 42 AND status = 'DELIVERED'
5
ORDER BY created_at DESC
6
LIMIT 20;

You should see an Index Scan or Bitmap Heap Scan using idx_orders_customer_status, not Seq Scan. A Seq Scan here — with a million rows — means something is wrong before Gatling ever runs: wrong WHERE clause shape, stale statistics, or a datatype mismatch. Fix it now; diagnosing it mid-load-test wastes the run.

Feeder files: real IDs for the load generator

Gatling requests need real identifiers — inventing customerIds produces 404s and empty pages that make the workload unrealistically cheap. Export real IDs into CSVs that chapter 06’s feeders will cycle through:

1
mkdir -p src/gatling/resources/data
2

3
docker compose exec -T postgres psql -U orders -d orders -c \
4
  "\copy (SELECT id AS customer_id FROM customers ORDER BY id) TO STDOUT WITH CSV HEADER" \
5
  > src/gatling/resources/data/customers.csv
6

7
docker compose exec -T postgres psql -U orders -d orders -c \
8
  "\copy (SELECT id AS order_id FROM orders ORDER BY id) TO STDOUT WITH CSV HEADER" \
9
  > src/gatling/resources/data/orders.csv
10

11
# PLACED orders only: PATCH transitions must start from a live state
12
docker compose exec -T postgres psql -U orders -d orders -c \
13
  "\copy (SELECT id AS order_id, customer_id FROM orders WHERE status = 'PLACED' ORDER BY id) \
14
   TO STDOUT WITH CSV HEADER" \
15
  > src/gatling/resources/data/placed_orders.csv

A million-row orders.csv is fine on disk, but a feeder with a million entries is wasteful — chapter 06 uses circular() feeders, so 100,000 IDs is plenty; trim with head -100001 if you like.

Reset and isolation between runs

Write traffic mutates the database; three runs against a drifting dataset measure the drift too. Pick one strategy per run and record it on the run sheet:

The template trick deserves a highlight: CREATE DATABASE orders_clean TEMPLATE orders; once, then CREATE DATABASE orders_run2 TEMPLATE orders_clean; before each run — a filesystem-level copy, far faster than re-seeding. Point the app at it with SPRING_DATASOURCE_URL=jdbc:postgresql://localhost:5432/orders_run2 ./gradlew bootRun.

Trade-off: reserved write sets are the least disruptive but the easiest to get subtly wrong — a PATCH that flips a PLACED order to PAID shrinks the PLACED pool across runs. The placed_orders.csv feeder combined with a template reset is the honest combination: write traffic mutates a copy you discard.

Milestone check: row counts match, EXPLAIN shows an index scan on the customer+status search, and three CSVs exist under src/gatling/resources/data/. Chapter 06 writes the simulations that consume them.