Load Testing With an Empty Database? Here's Your Problem
By Mikhail Shytsko, Founder at Seedfast · · Updated
You can polish the k6 scripts, tune every Gatling scenario, and arm the Locust swarm, but with only 47 rows in the database behind them, every number they collect is fiction.
Two weeks of work go into a load testing suite covering login, search, checkout, and reporting, with think times, ramp-up curves, and error thresholds modeled. The run comes back clean, showing P99 under 200ms, zero errors, and 3,000 requests per second, so the release ships on those numbers. Production then tells a different story, where search crawls to 8 seconds, the reporting endpoint times out, and the checkout flow that cleared 3,000 RPS in the test can barely hold 400, all of it aimed at a database that could never answer the question the load test was asking.
- An empty or near-empty database makes every load-test number fiction: query plans, buffer cache, and connection-pool dynamics are all data-dependent, so a system measured at 50 rows behaves nothing like one at 5 million.
- The four numbers that move most: throughput typically drops 3–5x, P99 latency rises 10–50x, missing indexes surface, and the connection pool saturates — none of which an empty database reveals.
- Realistic data volume matters more than any refinement to the load-test script, so your existing k6/Gatling/Locust scenarios end up hitting a production-shaped database.
- Dropping Seedfast into the pipeline as a step before the load test requires no script changes, since the foreign-key-valid volume it generates comes straight from your live schema.
Most load tests run against trivially small datasets, an open secret in performance engineering. Nobody wants to spend a week building a data-generation pipeline when the goal is to test application performance, so teams take shortcuts:
- Use the default dev database with 20 rows per table
- Run migrations and insert a handful of fixture records
- Copy a small CSV extract from production
- Skip data setup entirely and hope the application layer is the bottleneck
Databases don't behave the same way at 50 rows and 5 million; the difference is fundamental, not approximate. At scale the query planner makes different decisions and once-ignored indexes become critical, while disk I/O patterns change and buffer cache hit rates collapse. Connection pool behavior shifts too, and nested-loop joins give way to hash joins.
PostgreSQL's query planner uses table statistics — row counts, value distributions, null fractions — to choose execution plans. At 100 rows almost every query gets a sequential scan, because reading the whole table costs less than an index lookup's random I/O, so the planner correctly decides the index isn't worth it.
At 10 million rows, the same query gets an index scan, a bitmap heap scan, or a parallel sequential scan, depending on selectivity, while a missing index produces a sequential scan that reads gigabytes where an index would have read kilobytes.
-- Illustrative timings (the shape of the change, not a benchmark of any hardware):
-- With 100 rows: Seq Scan, 0.2ms (whole table fits in cache)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- With 10M rows, no index: Seq Scan, 4,200ms
-- With 10M rows, with index: Index Scan, 0.3ms
Read those two plans in sequence. A customer lookup that returns in 0.2ms against 100 cached rows becomes a 4,200ms sequential scan at 10 million rows with no supporting index, dropping back to 0.3ms only once the index exists, so a load test that never leaves the first line measures a plan production will never run.
PostgreSQL's shared buffer cache works well when the working set fits in memory, and with a small database that condition always holds, so everything is cached, every query hits memory, latency stays sub-millisecond, and throughput is bounded only by CPU.
Once the working set outgrows cache size at production scale, queries begin hitting disk and latency spikes while throughput drops, and the degradation isn't linear; it falls off a cliff the moment the cache hit rate crosses below a threshold. The mapping below is illustrative of that cliff shape, not a benchmark of any specific hardware:
Database size: 50MB → Cache hit rate: 99.9% → Avg query: 0.5ms
Database size: 5GB → Cache hit rate: 97% → Avg query: 2ms
Database size: 50GB → Cache hit rate: 78% → Avg query: 45ms
The 0.5ms from your load test becomes 45ms in production, and a P99 that read 200ms turns into 2,000ms, leaving the capacity model you built off by an order of magnitude.
With a small database, every query completes in microseconds and connections return to the pool almost instantly, so 20 connections can serve thousands of concurrent requests, each occupied for a tiny fraction of a second.
At production scale queries take longer, connections stay checked out for milliseconds instead of microseconds, and the pool starts queuing. Each added millisecond lets more requests stack up, raising pool pressure and pushing the system into a feedback loop that doesn't exist at small scale. The figures below assume query time dominates the connection hold time, a simplification that shows the direction, not a literal throughput ceiling:
Small DB: Query time 0.5ms → 20 connections handle 40,000 req/s
Large DB: Query time 50ms → 20 connections handle 400 req/s
→ Requests start queuing at 400 req/s
→ Queue adds latency → more queuing → cascade
This is the mechanism that turns a test showing comfortable headroom into a production system that hits a wall.
With small tables the planner ignores indexes entirely, and it doesn't matter because sequential scans on small tables are fast, so your load test never exercises index performance at all.
At scale, missing indexes become the dominant performance factor, and compound indexes matter as well, since a query filtering on customer_id and status may use an index on customer_id yet still scan thousands of rows for the status filter without a compound index. The bugs that only real test data catches walk through this same index-at-scale failure from the application side.
Join strategies change as well, since small tables get nested loop joins while large tables get hash or merge joins, and those carry completely different memory requirements, CPU profiles, and performance characteristics.
-- Small tables: Nested Loop Join, 0.5ms
-- Both tables fit in cache, inner loop is trivial
SELECT o.*, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';
-- Large tables: Hash Join, 180ms (or worse)
-- Hash table may spill to disk, changing performance profile entirely
Every performance engineering team has hit at least one of these. Some are hitting all four.
This is the most common and most dangerous of the four. The database holds whatever the migration scripts created, empty tables with correct schemas, and the load test runs fast because there's nothing to query.
# "We tested it with k6, performance is fine"
k6 run load-test.js
# ✓ P99 < 200ms
# ✓ Error rate < 0.1%
# ✓ Throughput > 2000 RPS
# (against a database with 0 rows in every table)
What runs here isn't a load test; it only shows how fast the application can hand back rows that aren't there.
This one is slightly better, since someone ran the dev seed script, so there are now 10-20 rows per table and the queries return data. Even so, the performance profile matches an empty database, because every table still fits in a single disk page.
The responsible engineer exports a CSV from production, scrubs it for PII (hopefully), and writes an import script, three days of work for a snapshot already a month stale. Half the foreign keys are broken because the export missed related tables, and the script breaks on the next schema change with nobody left to update it.
# The "proper" approach that nobody maintains
psql staging_db -c "\COPY users FROM 'users_sanitized.csv' CSV HEADER"
psql staging_db -c "\COPY orders FROM 'orders_sanitized.csv' CSV HEADER"
# ERROR: insert or update on table "orders" violates foreign key constraint
# (because the users CSV was from Monday and orders CSV was from Wednesday)
This is the most seductive anti-pattern, and the reasoning goes: "Our bottleneck is CPU-bound application logic, not the database. So database size doesn't matter."
Sometimes it's true, but never for long. Optimize the application layer and the database becomes the bottleneck, by which point your load tests have baselined against an empty database, so you can't measure the improvement accurately.
Teams that switch to realistic-data load testing keep hitting the same five issues, which come up repeatedly in practice, not in a lab benchmark.
The endpoint that cleared 3,000 RPS on an empty database sustains 600-1,000 RPS with realistic data, because the bottleneck was always the database and empty tables kept it invisible.
Average latency rises modestly while tail latency explodes, since an occasional uncached or connection-starved query adds seconds to the P99, where your SLA lives, not on the average.
Nearly every schema past 20 tables hides at least one missing index that only matters at scale, and realistic data triggers slow-query logs that stayed silent at 50 rows. Missing indexes are one of six bug classes that only real test data catches — alongside N+1 queries, pagination off-by-ones, and timeout cascades.
Pool sizing tuned to sub-millisecond queries collapses when queries take 10-50ms, and the real fix is usually the queries, not a bigger pool, since more connections just push contention into PostgreSQL's backends.
At small scale, background jobs (reports, exports, analytics) finish instantly and leave request queries alone; at production scale they compete for the same connections, buffer cache, and I/O, contention an empty-database load test never surfaces.
Before running your load test, populate the database with realistic data at the volume you expect in production.
# Seed production-scale data for a load test
seedfast seed --scope "seed 500,000 users with profiles, 2,000,000 orders spread across the last 12 months, payments for each order, and product reviews for 10% of orders"
Seedfast reads your schema and generates foreign-key-valid data with realistic distributions: timestamps cluster during business hours, status fields carry realistic ratios, amounts follow natural distributions. It adapts to migrations as they land, so a new column flows into the next run instead of breaking the way the hand-written CSV importer above does.
Seeding Plan:
public.users — 500,000 records
public.profiles — 500,000 records
public.products — 5,000 records
public.orders — 2,000,000 records
public.order_items — 5,800,000 records
public.payments — 2,000,000 records
public.reviews — 200,000 records
Total: 11,005,000 records across 7 tables
Approve? (Y/n)
Now run the same k6/Gatling/Locust scripts you already have; nothing about them changes, except that this time the database actually has data in it.
# Same test, dramatically different results
k6 run load-test.js
The numbers will come out different, and that's the point, because the new ones sit much closer to what production will experience. The figures below are illustrative of the shape of the change (throughput down, tail latency up, pool saturated), not a benchmark of any specific application:
Empty DB 500K Users
P50 Latency 12ms 45ms
P99 Latency 85ms 890ms
Throughput 3,200 RPS 780 RPS
Error Rate 0.01% 2.3%
DB Connections Used 4/20 20/20 (saturated)
An error rate like that is what pool saturation produces, and a P99 that climbs like that is what a missing index on a hot query looks like. Both surface only against a realistically sized database, which Seedfast generates from your own schema, so the test runs against production-shaped data, not an empty table, and you can run your first seed.
One-off load tests help, but the real value comes from running them automatically, seeding the database as a pipeline step ahead of each test. The CI/CD database seeding guide walks through the full setup.
name: Load Test
on:
schedule:
- cron: '0 2 * * 1' # Weekly Monday 2AM
workflow_dispatch:
jobs:
load-test:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:16
env:
POSTGRES_DB: loadtest
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
ports:
- 5432:5432
steps:
- uses: actions/checkout@v4
- name: Run migrations
run: npm run migrate
env:
DATABASE_URL: postgresql://postgres:postgres@localhost:5432/loadtest
- name: Seed realistic data
run: |
seedfast seed \
--scope "seed 200,000 users with orders and payments" \
--output plain
env:
SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
DATABASE_URL: postgresql://postgres:postgres@localhost:5432/loadtest
- name: Run load test
run: k6 run --out json=results.json load-tests/main.js
env:
BASE_URL: http://localhost:3000
DATABASE_URL: postgresql://postgres:postgres@localhost:5432/loadtest
- name: Check thresholds
run: |
# Fail the pipeline if P99 exceeds threshold
python scripts/check-load-test-results.py results.json \
--p99-max 500 \
--error-rate-max 1.0
load-test:
stage: performance
services:
- postgres:16
variables:
POSTGRES_DB: loadtest
DATABASE_URL: postgresql://postgres:postgres@postgres:5432/loadtest
script:
- npm run migrate
- seedfast seed --scope "seed 200,000 users with orders and payments" --output plain
- k6 run load-tests/main.js
rules:
- if: $CI_PIPELINE_SOURCE == "schedule"
- if: $CI_PIPELINE_SOURCE == "web"
The most powerful pattern is to seed fresh data and run load tests nightly, comparing each run with the last so performance regressions surface before they reach production.
#!/bin/bash
# nightly-load-test.sh
# Fresh database every run
dropdb --if-exists loadtest && createdb loadtest
npm run migrate
# Seed consistent volumes
seedfast seed \
--scope "seed 100,000 users with orders, payments, and activity logs" \
--output plain
# Run load test with consistent parameters
k6 run \
--out json=results/$(date +%Y-%m-%d).json \
load-tests/main.js
# Compare with yesterday's results
python scripts/compare-results.py \
results/$(date -d yesterday +%Y-%m-%d).json \
results/$(date +%Y-%m-%d).json \
--regression-threshold 15
| Goal | Minimum volume | What it reveals |
|---|---|---|
| Basic query plans | 10,000+ rows | Whether indexes are being used |
| Cache behavior | Data > 25% of shared_buffers | Real-world cache hit rates |
| Connection pool sizing | Enough to make queries take >5ms | Pool saturation points |
| Join performance | 100,000+ rows per joined table | Hash join vs. nested loop thresholds |
| End-to-end realism | Match production row counts | Realistic full-pipeline performance |
Start with roughly 10x your current test data, so 100 rows per table becomes 1,000, then run the load test and step up to 10,000 and 100,000, watching for the volume where the performance profile changes, since that's where your production issues hide.
# Progressive scaling approach
seedfast seed --scope "seed 1,000 users with orders and payments"
k6 run load-test.js # Baseline
# Drop and re-seed at higher volume
seedfast seed --scope "seed 10,000 users with orders and payments"
k6 run load-test.js # Look for non-linear degradation
seedfast seed --scope "seed 100,000 users with orders and payments"
k6 run load-test.js # This is usually where things get interesting
The volume where latency stops scaling linearly is where your architecture has a bottleneck, and finding that inflection point is the whole purpose of load testing with realistic data. Re-seeding between volume tiers takes minutes, not the three-day CSV marathon; in our internal runs, Seedfast generated about 1M foreign-key-valid rows on a 20-table SaaS schema in roughly 3.5 minutes (local Postgres, single run). For the highest volumes, the large-volume seeding guide covers batching and scope tuning.
Load testing without realistic data is performance theater. It generates charts and reports and clears the compliance checkbox, yet none of that predicts how the system behaves in production, where "P99 under 200ms" on an empty database routinely becomes "P99 at 2 seconds," which is the difference between a clean launch and a 2AM incident.
You already invest real effort in writing load test scenarios, tuning parameters, and analyzing results. The one missing piece is the data; once the database holds production-shaped volume, those same tests start telling you the truth.
seedfast seed --scope "seed production-scale data across all tables"
Then run the test you already have, and this time the results it hands back will be real ones.
Why does an empty database make load test results inaccurate?
Database performance is data-dependent. With a near-empty table, PostgreSQL's planner picks a sequential scan because the table is cheaper to read whole, every read is served from the buffer cache, and connections return to the pool in microseconds. At production volume the same query may need an index scan or hash join, reads start hitting disk, and connections stay checked out long enough to saturate the pool. So an empty-database load test measures how fast your application returns near-empty result sets, not how it handles production query load.
How much test data do I need for a realistic load test?
Match production row counts when you can; when you cannot, start at roughly 10x your current test volume and scale up in tiers (1,000 → 10,000 → 100,000 → 1,000,000), running the load test at each step. Index usage shows up around 10,000 rows, cache behavior once the data exceeds about 25% of shared_buffers, and join-strategy changes around 100,000 rows per joined table. Watch for the volume where latency stops scaling linearly, since that inflection is where your bottleneck lives.
Does database size really change PostgreSQL query plans?
Yes — the plan is chosen from what the statistics say about the data, so the same query that gets a sequential scan at 100 rows may get an index scan, a bitmap heap scan, or a parallel scan at 10 million, and a missing index turns into a scan that reads gigabytes. Join strategy shifts with size as well, from nested loops toward hash or merge joins with very different memory and CPU profiles. Run EXPLAIN ANALYZE on realistic volumes to see the plan production will actually run.
Can I just copy production data into my load test environment instead?
It's a common shortcut with two separate problems. First, whether you're even allowed to move real production records into a test environment is a compliance question, often restricted under regimes like HIPAA or GDPR, so check with your own compliance team, not a load-test guide. Second, it's brittle, since production dumps drift from the schema and break on the next migration, and partial exports leave foreign keys dangling. Seedfast addresses the second problem by generating foreign-key-valid data from your schema, so no real customer records enter the load-test pipeline. It isn't an anonymization or compliance tool, and it doesn't make copying prod data safe so much as remove the reason to copy it at all.
How do I add database seeding to a load testing CI pipeline?
To add database seeding to a load-testing CI pipeline, add a seed step after migrations and before the load test. Set SEEDFAST_API_KEY in the environment to run the CLI non-interactively (no confirmation prompt), pass your volume as a plain-English --scope, then run your existing k6, Gatling, or Locust scenario against the seeded database. The CI/CD database seeding guide shows the full GitHub Actions and GitLab CI setup.
- Small Data, Big Lies: 6 Bugs Only Real Test Data Catches — the same volume blind spot from the application side: the bug classes that stay hidden until your tables hold real volume
- PostgreSQL Test Data: A Syntax Cookbook — the raw SQL patterns for generating volumes and realistic distributions by hand
- ALTER TABLE, 5 Million Rows, and the Deploy That Took Down the Site — what happens when a migration meets production-scale data
- Enterprise Database Test Data: What It Actually Looks Like — realistic relational test data across a large, multi-table schema
- Get started with Seedfast — connect your database and run your first schema-aware seed
Get Started | Documentation | Pricing
Seedfast turns your live schema into production-scale test data with valid relationships and realistic volumes, and there are no seed scripts left for you to maintain.