Quality is not optional. It's our standard. Free QA tools for testers and developers.

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

SymptomRoot cause in test dataWhat you actually observe
Query plans differ between staging and prodUniform distribution vs. heavy‑tailed key frequenciesIndex seeks turn into scans, parallelism changes
Memory grants explodeAll rows have the same column widthTempDB spills, sort/ hash warnings
Lock contention appears only in prodMissing foreign‑key chains or cascade pathsDeadlocks, blocking chains not reproduced
Partition pruning failsPartition key values not aligned with boundary rangesFull‑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.

RequirementTypical metricHow to capture it
Target volumeRows per table, total GBSELECT SUM(pgsize) FROM pg_class … or sp_spaceused
Column statisticsDistinct count, histogram buckets, null %pg_stats, DBCC SHOW_STATISTICS
Cross‑column correlationPearson / mutual information between customer_id and regionCustom script or pg_stats_ext
Referential integrity depthMax FK chain length, orphan ratioSchema graph walk
Temporal realismDate distribution, seasonalityHistogram on created_at
Change‑rate simulation% rows updated per hourCDC logs or audit tables
Masking / compliancePII columns must be syntheticData‑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

StrategyWhen it shinesMain trade‑offs
Production copy + maskingVery complex schemas, existing data already realisticRequires secure pipeline, storage for full copy, masking latency
Subset + synthetic augmentationLarge prod DB, need only a slice (e.g., last 30 days)Subset must preserve FK closure; augmentation logic can be brittle
Pure synthetic generationGreen‑field projects, strict compliance, CI‑friendlyMust model every statistical property manually
Hybrid (template + generator)Re‑usable across environments, need deterministic seedsTooling 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

TableRows (prod)SizeKey columnsNotable skew
customers2 M1.2 GBcustomer_id (PK)80 % in 3 regions
orders15 M9 GBorder_id (PK), customer_id (FK)70 % placed in last 90 days
order_items45 M18 GBorder_id (FK), product_id (FK)5 % of products = 60 % of lines
products500 k0.4 GBproduct_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

CheckQueryExpectedPass?
Row countsSELECT COUNT(*) FROM customers;150 000✅
FK closureSELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id=c.customer_id WHERE c.customer_id IS NULL;0✅
Region histogramSELECT region, COUNT(*) FROM customers GROUP BY region;±5 % of spec✅
Order‑date skewSELECT 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 avgSELECT 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 \copy or bcp for minimal overhead.
  • 16 workers saturate a 32‑core DB host; monitor pg_stat_activity to 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

ToolLicenseGeneration modelCI/CD nativeSchema drift handlingTypical use‑case
QA3 Test Data GeneratorFree (web) / Paid APITemplate + statistical specCLI, GitHub ActionSpec versioned with codeFast prototype → full run
dbt‑seed + macrosApache‑2.0SQL‑based transformsdbt rundbt packagesTeams already on dbt
Synthesized (commercial)ProprietaryML‑learned distributionsSDK, AirflowAuto‑detect driftHigh‑fidelity PII masking
DataFactory (Azure)Pay‑as‑you‑goMapping data flowsADF pipelinesParameterised datasetsCloud‑native ETL
Custom Python + FakerMITCode‑firstAny orchestratorManualOne‑off, highly bespoke

Evaluation rubric (score 1‑5)

CriterionWeightQA3dbt‑seedSynthesizedDataFactoryCustom
Spec‑driven determinism0.2554433
Parallel bulk load0.2053544
FK‑aware generation0.1554533
CI/CD integration0.1555454
Cost (license + infra)0.1555235
Learning curve0.1043332
Weighted total1.004.853.853.853.553.55

Numbers are illustrative; run your own proof‑of‑concept before committing.


7. Validation Checks That Prevent Silent Drift

CheckImplementation tipFrequency
Row‑count paritySELECT COUNT(*) FROM <table> vs. specEvery generation run
FK closureEXCEPT between child FK column and parent PKEvery generation run
Column‑level histogramswidth_bucket / percentile_contEvery generation run
Cross‑column correlationcorr(col_a, col_b) or mutual information scriptNightly on staging
Partition alignmentVerify partition_key values fall in expected rangesAfter each schema change
Checksum of data filessha256sum on exported CSVsArchive for reproducibility
Performance baselineRun a fixed query set (e.g., 10‑query suite) and capture EXPLAIN ANALYZEAfter 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

PitfallWhy it hurtsMitigation
Ignoring FK cyclesGenerator inserts parent after child → FK violationTopologically sort tables; use DEFERRABLE INITIALLY DEFERRED constraints during load
Uniform random for skewed columnsOptimizer picks wrong plan, cache behavior unrealisticEncode histogram or CDF in spec; use weighted samplers
Single‑threaded COPYLoad takes hours, blocks CIParallelize per table or per partition; use `COPY ... FROM PROGRAM 'cat file.gz
No versioned specSchema change silently breaks data shapeStore spec in repo; gate generation on spec‑diff test
Masking after loadExtra pass doubles I/O, may break indexesGenerate 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.