How to Generate Test Data for PostgreSQL Databases

How to Generate Test Data for PostgreSQL Databases

When a test suite runs against an empty or stale database, the results are meaningless. Real‑world bugs surface only when the data looks like production: referential integrity, edge‑case values, volume, and distribution all matter. This guide walks through the decisions you’ll make, shows a concrete worked example, and highlights the failure modes that bite teams most often.

---

Prerequisites

Item	Why it matters	Quick verification
PostgreSQL ≥ 13	Modern functions (gen_random_uuid, generate_series, jsonb_build_object) simplify data creation.	SELECT version();
Schema ownership	You need CREATE, INSERT, TRUNCATE on target tables.	\dt+ in psql shows privileges.
Test‑data policy	Decide whether data must be anonymized, synthetic, or a subset of production.	Documented in your QA handbook.
CI/CD access	Automated pipelines must be able to spin a temporary DB or run migrations before data load.	Check pipeline YAML for services: or docker-compose.
Tooling baseline	Decide if you’ll use pure SQL, a scripting language, or a dedicated generator.	List available runtimes (Python 3.11+, Node 18+, etc.).

If any of these are missing, stop and resolve them first. A missing privilege or an outdated PG version will turn a 10‑minute data‑gen job into a day‑long debugging session.

---

Choosing a Generation Strategy

Strategy	Strengths	Weaknesses	Typical use‑case
Built‑in SQL functions (generate_series, random, md5, gen_random_uuid)	Zero dependencies, runs inside the DB, easy to version‑control as migration scripts.	Limited to relational shapes; complex business rules become verbose.	Small‑to‑medium schemas, CI pipelines that already run psql.
pg_dump + anonymization	Starts from a real snapshot, preserves statistics, indexes, and constraints.	Requires a production‑like dump; anonymization scripts can be brittle.	Teams with a staging dump and strict data‑privacy rules.
Custom scripts (Python/PL/pgSQL)	Full programming power, libraries like Faker, factory_boy, psycopg2.	Adds runtime dependency; script maintenance overhead.	Complex domain logic, need for external APIs (e.g., address validation).
Dedicated test‑data generators (e.g., QA3’s free test data generator at /tools/test-data-generator)	Declarative YAML/JSON config, built‑in referential integrity, schema‑aware, CI‑friendly.	Learning curve for config syntax; may not cover every exotic type out‑of‑the‑box.	Teams that want repeatable, reviewable data specs without writing code.

Decision checklist

• Do you have a production dump you can safely anonymize? → pg_dump path.
• Is the schema stable and relatively small (< 200 tables)? → Pure SQL or generator.
• Do you need to encode business rules (e.g., “order total = sum(line_items) + tax”)? → Custom script or generator with expression support.
• Is the team comfortable maintaining YAML/JSON specs? → Dedicated generator.

Pick one primary approach; you can always fall back to a secondary method for edge tables.

---

Worked Example: Generating Realistic Order Data

1. Schema snapshot

CREATE TABLE customers (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       TEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE products (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku         TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    price_cents INT NOT NULL CHECK (price_cents > 0)
);


CREATE TABLE orders (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id     UUID NOT NULL REFERENCES customers(id),
    placed_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    status          TEXT NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
    total_cents     INT NOT NULL CHECK (total_cents >= 0)
);


CREATE TABLE order_items (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id    UUID NOT NULL REFERENCES orders(id),
    product_id  UUID NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price_cents INT NOT NULL CHECK (unit_price_cents > 0)
);

Goal: 10 000 customers, 5 000 products, 50 000 orders, 150 000 order_items – enough to exercise pagination, indexing, and aggregation queries.

---

2. Using QA3’s free test data generator

1. Install / run the generator (Docker image qa3io/test-data-gen:latest).
2. Create a spec file order-spec.yaml:

version: 1
tables:
  customers:
    count: 10000
    columns:
      email:      "{{unique.email}}"
      full_name:  "{{name.fullName}}"
      created_at: "{{date.past('5y')}}"
  products:
    count: 5000
    columns:
      sku:        "{{unique.bothify('SKU-####-??')}}"
      name:       "{{commerce.productName}}"
      price_cents: "{{number.between(100, 50000)}}"
  orders:
    count: 50000
    columns:
      customer_id: "{{ref.customers.id}}"
      placed_at:   "{{date.between('2022-01-01','2024-12-31')}}"
      status:      "{{random.element(['new','paid','shipped','cancelled'])}}"
      total_cents: "{{expr 'order_items.sum(unit_price_cents * quantity)'}}"
  order_items:
    count: 150000
    columns:
      order_id:       "{{ref.orders.id}}"
      product_id:     "{{ref.products.id}}"
      quantity:       "{{number.between(1,5)}}"
      unit_price_cents: "{{ref.products.price_cents}}"

3. Run

docker run --rm -v $(pwd):/spec qa3io/test-data-gen:latest \
  generate --spec /spec/order-spec.yaml --dsn "postgres://user:pwd@host:5432/testdb"

The generator resolves references ({{ref.customers.id}}), evaluates expressions ({{expr ...}}), and streams batched COPY statements for speed. Typical run time on a modest VM: ≈ 45 seconds.

---

3. Pure‑SQL alternative (no external tool)

-- 1. Customers
INSERT INTO customers (email, full_name, created_at)
SELECT
    md5(random()::text || i::text) || '@example.com',
    initcap(md5(random()::text)) || ' ' || initcap(md5(random()::text)),
    (now() - (random() * interval '5 years'))::timestamptz
FROM generate_series(1, 10000) AS i;


-- 2. Products
INSERT INTO products (sku, name, price_cents)
SELECT
    'SKU-' || lpad(i::text,4,'0') || '-' || substr(md5(random()::text),1,2),
    'Product ' || i,
    (100 + random() * 49900)::int
FROM generate_series(1, 5000) AS i;


-- 3. Orders (needs customer_id)
WITH cust AS (SELECT id FROM customers),
     ord AS (
       INSERT INTO orders (customer_id, placed_at, status, total_cents)
       SELECT
         (SELECT id FROM cust ORDER BY random() LIMIT 1),
         (date '2022-01-01' + (random() * (date '2024-12-31' - date '2022-01-01')))::timestamptz,
         (ARRAY['new','paid','shipped','cancelled'])[floor(random()*4)+1],
         0  -- placeholder, will update later
       FROM generate_series(1, 50000)
       RETURNING id
     )
SELECT 1; -- just to finish CTE


-- 4. Order items (needs order_id, product_id)
WITH ord AS (SELECT id FROM orders),
     prod AS (SELECT id, price_cents FROM products)
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents)
SELECT
    (SELECT id FROM ord ORDER BY random() LIMIT 1),
    (SELECT id FROM prod ORDER BY random() LIMIT 1),
    (1 + floor(random()*5))::int,
    (SELECT price_cents FROM prod ORDER BY random() LIMIT 1)
FROM generate_series(1, 150000);


-- 5. Recalculate order totals
UPDATE orders o
SET total_cents = sub.total
FROM (
    SELECT order_id, sum(unit_price_cents * quantity) AS total
    FROM order_items
    GROUP BY order_id
) sub
WHERE o.id = sub.order_id;

Notes

• generate_series drives row counts.
• Random FK selection uses ORDER BY random() LIMIT 1; for > 10 k rows this is acceptable but not optimal.
• The final UPDATE guarantees referential consistency for total_cents.
• Execution time on the same VM: ≈ 2 minutes (mostly the UPDATE).

---

4. Comparing the two runs

Metric	QA3 generator	Pure SQL
Lines of spec / script	45 YAML lines	~120 SQL lines
Runtime (10 k cust, 5 k prod, 50 k ord, 150 k items)	45 s	120 s
Referential integrity	Built‑in, declarative	Manual UPDATE step
Extensibility (new column)	Add one line in YAML	Edit multiple CTEs
Version control friendliness	Diffable YAML	Diffable SQL (larger)
Learning curve	Low (config syntax)	Medium (SQL tricks)

Pick the approach that matches your team’s comfort level and CI constraints.

---

Validation & Quality Checks

Generating data is only half the job; you must prove the data is usable.

Check	How to implement	Frequency
Row counts	SELECT count(*) FROM each_table;	Every run
FK integrity	SELECT count(*) FROM orders o LEFT JOIN customers c ON o.customer_id=c.id WHERE c.id IS NULL;	Every run
Domain constraints	CHECK constraints already enforce; add SELECT * FROM orders WHERE total_cents < 0;	Every run
Distribution sanity	SELECT status, count(*) FROM orders GROUP BY status; – expect roughly even spread.	Every run
Statistical similarity	Compare pg_stats histograms of generated vs. production (if you have a sanitized dump).	Periodic (e.g., weekly)
Performance smoke test	Run a representative query (e.g., SELECT * FROM orders WHERE placed_at > now() - interval '30 days' LIMIT 100;) and assert < 200 ms.	CI gate
Anonymization audit	Grep for real email patterns, PII regexes.	Every run if using production dump.

Automate these checks in a post‑generation script that exits non‑zero on any failure. The script becomes a gate in your pipeline before the test suite starts.

---

Common Failure Modes & Mitigations

Failure mode	Symptom	Root cause	Mitigation
FK violations	INSERT errors or orphan rows after generation.	Random FK selection without guarantee of existence.	Use generator’s ref syntax or pre‑load FK tables into CTE arrays.
Check constraint breaches	total_cents < 0 or quantity = 0.	Expression logic error or missing CHECK.	Validate expressions in a dry‑run (--dry-run flag) before real load.
Data skew	95 % of orders have status new.	Uniform random on enum without weighting.	Add weight map: {{random.weighted(['new':0.1,'paid':0.6,'shipped':0.2,'cancelled':0.1])}}.
Performance collapse	Generation takes > 30 min, CI times out.	Row‑by‑row INSERT loops, missing COPY.	Batch with COPY FROM STDIN or generator’s bulk mode; disable indexes/FK temporarily, rebuild after.
Schema drift	New column discount_cents added to orders but not in spec.	Spec not updated after migration.	Add a CI step that diffs information_schema.columns against spec; fail if mismatch.
PII leakage	Real email addresses appear in test DB.	Used production dump without anonymization.	Run anonymization script before any dump enters CI; enforce via policy check.
Non‑deterministic flakiness	Tests pass locally but fail in CI due to different random seed.	No seed control.	Pass a fixed seed (--seed 42) to generator or SET SEED 0.12345; in SQL.

Document each failure mode in your team’s runbook; the table above can be copied verbatim.

---

Checklist for Production‑Ready Test Data

• Schema lock – migration applied, no pending DDL.
• Generation spec – version‑controlled, reviewed, matches current schema.
• Seed control – fixed seed or deterministic generator mode.
• Bulk load – COPY/generator bulk mode, indexes disabled during load.
• Post‑load validation – row counts, FK integrity, domain checks, distribution sanity.
• Performance baseline – smoke query latency recorded and asserted.
• Anonymization proof – PII scan passes.
• CI integration – generation step runs before test suite, fails fast on validation errors.
• Rollback plan – TRUNCATE … RESTART IDENTITY CASCADE; scripted for quick cleanup.
• Documentation – README with run commands, seed value, expected volumes.

Tick every box before you consider the data set “ready for QA”.

---

Next Steps

1. Pick a strategy today (SQL, script, or generator) based on the decision checklist.
2. Create a minimal spec for one core domain (e.g., orders + order_items).
3. Run the generator (or SQL script) against a throwaway database in your CI pipeline.
4. Add the validation script as a required job step.
   5