High-Volume Database Test Data for Performance Testing
High‑Volume Database Test Data for Performance Testing
Performance tests only reveal real bottlenecks when the data looks and behaves like production. A 10 GB table with uniform random strings will not stress the same indexes, partitioning, or caching layers as a 10 GB table that mirrors the cardinality, skew, and referential depth of live data. This guide walks through the decisions, tooling, and validation steps needed to create high‑volume, realistic test data sets that keep performance results trustworthy.
1. Why the Data Shape Matters
| Symptom | Root cause in test data | What you actually observe |
|---|---|---|
| Query plans differ between staging and prod | Uniform distribution vs. heavy‑tailed key frequencies | Index seeks turn into scans, parallelism changes |
| Memory grants explode | All rows have the same column width | TempDB spills, sort/ hash warnings |
| Lock contention appears only in prod | Missing foreign‑key chains or cascade paths | Deadlocks, blocking chains not reproduced |
| Partition pruning fails | Partition key values not aligned with boundary ranges | Full‑partition scans, higher I/O |
If the test data does not reproduce cardinality, null‑rate, value‑range, correlation, and referential depth, the optimizer will choose a different plan and the performance numbers become meaningless.
2. Defining the Generation Requirements
Before picking a tool, write down the non‑functional requirements that the data set must satisfy.
| Requirement | Typical metric | How to capture it |
|---|---|---|
| Target volume | Rows per table, total GB | SELECT SUM(pgsize) FROM pg_class … or sp_spaceused |
| Column statistics | Distinct count, histogram buckets, null % | pg_stats, DBCC SHOW_STATISTICS |
| Cross‑column correlation | Pearson / mutual information between customer_id and region | Custom script or pg_stats_ext |
| Referential integrity depth | Max FK chain length, orphan ratio | Schema graph walk |
| Temporal realism | Date distribution, seasonality | Histogram on created_at |
| Change‑rate simulation | % rows updated per hour | CDC logs or audit tables |
| Masking / compliance | PII columns must be synthetic | Data‑classification policy |
Document these in a Data Generation Spec (a short markdown file checked into version control). The spec becomes the contract between QA, DBAs, and developers.
3. Choosing a Generation Strategy
| Strategy | When it shines | Main trade‑offs |
|---|---|---|
| Production copy + masking | Very complex schemas, existing data already realistic | Requires secure pipeline, storage for full copy, masking latency |
| Subset + synthetic augmentation | Large prod DB, need only a slice (e.g., last 30 days) | Subset must preserve FK closure; augmentation logic can be brittle |
| Pure synthetic generation | Green‑field projects, strict compliance, CI‑friendly | Must model every statistical property manually |
| Hybrid (template + generator) | Re‑usable across environments, need deterministic seeds | Tooling maturity varies; schema changes require template updates |
Decision checklist
- Do you have a recent, sanitized production dump?
- Is the schema stable enough to maintain templates?
- What is the acceptable lead time for a fresh data set?
- Do you need deterministic output for regression comparison?
- Are there regulatory constraints that forbid any production‑derived rows?
If you answer yes to the first two and no to the last, a subset‑then‑augment approach usually gives the best ROI. For pure CI pipelines, a template‑driven synthetic generator is the only fully automated path.
4. End‑to‑End Generation Workflow
┌─────────────────────┐
│ 1. Schema intake │ → ER diagram, FK graph, column stats
└───────┬─────────────┘
│
┌───────▼─────────────┐
│ 2. Spec authoring │ → Data Generation Spec (markdown)
└───────┬─────────────┘
│
┌───────▼─────────────┐
│ 3. Tool selection │ → Evaluate against checklist
└───────┬─────────────┘
│
┌───────▼─────────────┐
│ 4. Prototype run │ → 1 % volume, validate stats
└───────┬─────────────┘
│
┌───────▼─────────────┐
│ 5. Scale‑up run │ → Full volume, parallel workers
└───────┬─────────────┘
│
┌───────▼─────────────┐
│ 6. Validation suite │ → Row counts, FK closure, histograms
└───────┬─────────────┘
│
┌───────▼─────────────┐
│ 7. Publish artifact │ → Versioned dump / container image
└─────────────────────┘
Each step produces an artifact that can be reviewed in a pull request, giving traceability from spec to final data set.
5. Worked Example: E‑Commerce Order Domain
5.1 Domain snapshot
| Table | Rows (prod) | Size | Key columns | Notable skew |
|---|---|---|---|---|
customers | 2 M | 1.2 GB | customer_id (PK) | 80 % in 3 regions |
orders | 15 M | 9 GB | order_id (PK), customer_id (FK) | 70 % placed in last 90 days |
order_items | 45 M | 18 GB | order_id (FK), product_id (FK) | 5 % of products = 60 % of lines |
products | 500 k | 0.4 GB | product_id (PK) | 10 % discontinued |
5.2 Spec excerpt (markdown)
# Data Generation Spec – Orders Domain
## Volume targets
- customers: 2,000,000 rows
- orders: 15,000,000 rows
- order_items: 45,000,000 rows
- products: 500,000 rows
## Column statistics (sample)
### customers.region
- distinct: 12
- distribution: US‑East 35%, US‑West 25%, EU‑Central 20%, APAC 15%, Other 5%
### orders.order_date
- range: 2022‑01‑01 → 2024‑12‑31
- histogram: 70% in last 90 days, 20% in prior 9 months, 10% older
### order_items.quantity
- min 1, max 50, mean 2.3, 95th pct 8
5.3 Prototype with QA3’s free test data generator
The generator at /tools/test-data-generator lets you paste the spec (or a simplified JSON) and instantly produces a CSV/COPY script for each table. For the prototype we ask for 1 % of target rows.
# 1. Export spec to JSON (qa3-cli can do this)
qa3 spec export --file spec.md --out spec.json
# 2. Run generator for 1% volume
qa3 generate \
--spec spec.json \
--scale 0.01 \
--out ./proto_data \
--format csv \
--workers 4
Result (≈ 150 k customers, 150 k orders, 450 k items).
5.4 Validation of prototype
| Check | Query | Expected | Pass? |
|---|---|---|---|
| Row counts | SELECT COUNT(*) FROM customers; | 150 000 | ✅ |
| FK closure | SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id=c.customer_id WHERE c.customer_id IS NULL; | 0 | ✅ |
| Region histogram | SELECT region, COUNT(*) FROM customers GROUP BY region; | ±5 % of spec | ✅ |
| Order‑date skew | SELECT date_trunc('month',order_date) AS m, COUNT(*) FROM orders GROUP BY m ORDER BY m DESC LIMIT 3; | 70 % in last 3 months | ✅ |
| Item‑per‑order avg | SELECT AVG(cnt) FROM (SELECT order_id, COUNT(*) cnt FROM order_items GROUP BY order_id) t; | 3.0 ±0.2 | ✅ |
If any check fails, adjust the spec (e.g., add a correlation rule between customer.region and order_date) and re‑run the prototype. The generator is deterministic when you pass --seed 42, so the same spec always yields identical output.
5.5 Scale‑up to full volume
qa3 generate \
--spec spec.json \
--scale 1.0 \
--out ./full_data \
--format copy \
--workers 16 \
--seed 42
- COPY format streams directly into
psql \copyorbcpfor minimal overhead. - 16 workers saturate a 32‑core DB host; monitor
pg_stat_activityto avoid lock storms on FK indexes. - Total wall‑time on a modest 8 vCPU / 32 GB VM: ~45 minutes for 68 M rows.
5.6 Full‑volume validation (automated)
-- 1. Row counts
SELECT 'customers' AS tbl, COUNT(*) FROM customers
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'products', COUNT(*) FROM products;
-- 2. FK integrity (run in parallel per FK)
SELECT COUNT(*) FROM orders o
LEFT JOIN customers c ON o.customer_id=c.customer_id
WHERE c.customer_id IS NULL;
-- 3. Histogram comparison (using width_bucket)
SELECT width_bucket(order_date, '2022-01-01'::date, '2025-01-01'::date, 36) AS bucket,
COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;
Wrap the queries in a CI job that fails if any metric deviates >5 % from the spec. Store the JSON output as an artifact for audit.
6. Tool Landscape – What to Evaluate
| Tool | License | Generation model | CI/CD native | Schema drift handling | Typical use‑case |
|---|---|---|---|---|---|
| QA3 Test Data Generator | Free (web) / Paid API | Template + statistical spec | CLI, GitHub Action | Spec versioned with code | Fast prototype → full run |
| dbt‑seed + macros | Apache‑2.0 | SQL‑based transforms | dbt run | dbt packages | Teams already on dbt |
| Synthesized (commercial) | Proprietary | ML‑learned distributions | SDK, Airflow | Auto‑detect drift | High‑fidelity PII masking |
| DataFactory (Azure) | Pay‑as‑you‑go | Mapping data flows | ADF pipelines | Parameterised datasets | Cloud‑native ETL |
| Custom Python + Faker | MIT | Code‑first | Any orchestrator | Manual | One‑off, highly bespoke |
Evaluation rubric (score 1‑5)
| Criterion | Weight | QA3 | dbt‑seed | Synthesized | DataFactory | Custom |
|---|---|---|---|---|---|---|
| Spec‑driven determinism | 0.25 | 5 | 4 | 4 | 3 | 3 |
| Parallel bulk load | 0.20 | 5 | 3 | 5 | 4 | 4 |
| FK‑aware generation | 0.15 | 5 | 4 | 5 | 3 | 3 |
| CI/CD integration | 0.15 | 5 | 5 | 4 | 5 | 4 |
| Cost (license + infra) | 0.15 | 5 | 5 | 2 | 3 | 5 |
| Learning curve | 0.10 | 4 | 3 | 3 | 3 | 2 |
| Weighted total | 1.00 | 4.85 | 3.85 | 3.85 | 3.55 | 3.55 |
Numbers are illustrative; run your own proof‑of‑concept before committing.
7. Validation Checks That Prevent Silent Drift
| Check | Implementation tip | Frequency |
|---|---|---|
| Row‑count parity | SELECT COUNT(*) FROM <table> vs. spec | Every generation run |
| FK closure | EXCEPT between child FK column and parent PK | Every generation run |
| Column‑level histograms | width_bucket / percentile_cont | Every generation run |
| Cross‑column correlation | corr(col_a, col_b) or mutual information script | Nightly on staging |
| Partition alignment | Verify partition_key values fall in expected ranges | After each schema change |
| Checksum of data files | sha256sum on exported CSVs | Archive for reproducibility |
| Performance baseline | Run a fixed query set (e.g., 10‑query suite) and capture EXPLAIN ANALYZE | After each data refresh |
Automate the first four in the CI pipeline; the latter three can be a nightly job that posts a Slack summary.
8. Common Pitfalls & Mitigations
| Pitfall | Why it hurts | Mitigation |
|---|---|---|
| Ignoring FK cycles | Generator inserts parent after child → FK violation | Topologically sort tables; use DEFERRABLE INITIALLY DEFERRED constraints during load |
| Uniform random for skewed columns | Optimizer picks wrong plan, cache behavior unrealistic | Encode histogram or CDF in spec; use weighted samplers |
| Single‑threaded COPY | Load takes hours, blocks CI | Parallelize per table or per partition; use `COPY ... FROM PROGRAM 'cat file.gz |
| No versioned spec | Schema change silently breaks data shape | Store spec in repo; gate generation on spec‑diff test |
| Masking after load | Extra pass doubles I/O, may break indexes | Generate masked values directly; if post‑mask required, do it in‑place with UPDATE ... SET col = gen_mask(col) |
| **Assuming production statistics are |
Read more
GraphQL Test Data Generation for Queries and Mutations
A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Postman Test Data Generation with CSV and JSON Files
A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Generate API Test Data Directly from an OpenAPI Specification
A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.