How to generate realistic test data for PostgreSQL

Realistic test data is synthetic data that behaves like production: referentially intact across foreign keys, shaped like your real column distributions, and coherent across related columns. The fastest way to get it is to read the schema itself, not to configure generators by hand. This guide shows the five things that separate realistic data from random rows, and how Weavori automates each one.

Why hand-written seed scripts fail

Most teams have a seed script that generates mock data for local and staging databases. It works at first, and then production data gets messy in ways the script never modeled.

"The problem is that production data is messy and it's very difficult to replicate that with mock data." : Neosync founders, Show HN: Neosync, May 2024

The failure modes are consistent: dangling foreign keys when fixtures drift, uniform random values that don't exercise real query plans, and broken rows (a customer predating their own signup, a paid order with no paid_at). Each one is a bug that ships to production because the test environment lied.

Step 1: Start from the schema, not your fixtures

The schema is the source of truth. Tables, column types, foreign keys, check constraints, enums, defaults, everything a realistic generator needs is already declared there.

Weavori connects to any PostgreSQL database (or accepts pasted CREATE TABLE statements via DDL paste mode), introspects the schema, and infers a generator for every column from semantic rules: first_name becomes a name, zip becomes a ZIP, a status enum samples its own values. No YAML, no DSL, nothing to maintain when the schema changes.

Step 2: Keep every foreign key intact

Referential integrity is the first thing tests notice. A broken FK produces false failures, or worse, false passes when a join silently returns nothing.

Weavori resolves the foreign-key dependency graph and generates parents before children, streaming rows so memory stays flat. Every generated row references a real parent row: guaranteed by construction, not checked after the fact. Composite primary keys and multi-level chains (users → orders → order_items) are handled in dependency order.

Step 3: Match production distributions

Uniform random data is unrealistic data. If 70% of your real status values are active, a flat random generator gives you ~33% each, and the query planner picks different indexes, different joins, different plans.

Weavori samples PostgreSQL's column statistics (pg_stats) and weights generated values accordingly. A status column that is 70% active stays 70% active. What drives the mechanism is repetition rather than type: any column with a most-common-value list inherits its real frequencies, so a rating dominated by 5s or a quantity clustered on 1 and 2 comes out skewed the same way. A continuous column where most values appear once has no such list to read, so it falls back to a plausible range for its name and type until you declare a shape: --distribution "users.age=normal:mean=35,stddev=10", zipf over a ranked set, or weighted for explicit label weights. The models are in the generate reference. This is the difference between data that looks real in a screenshot and data that behaves like production under load.

Step 4: Enforce cross-column coherence

Realistic data is internally consistent. Customers don't predate their own signups. Orders placed in January aren't shipped in the previous year. A paid_at timestamp exists exactly when status = 'paid': and is NULL otherwise.

Weavori detects these patterns from your schema, temporal ordering between timestamp columns, conditional nullability, CHECK constraints, and enforces them per row. This is the layer that catches the bugs seed scripts and naive generators never see, because the generated data honors the relationships between columns, not just each column in isolation.

The same relationship is what makes a UNIQUE constraint testable at all. Deriving email from first_name and last_name reproduces the collisions a real signup flow produces: The Postgres migration that passed every test runs that experiment end to end.

Step 5: Automate it in CI

Manual test data is data that rots. The realistic-data workflow ends in a pipeline: every push regenerates a clean, production-shaped database for tests.

Weavori is built for this: API keys for headless auth, plain/json output for logs, standardized exit codes for pipeline branching, and a schema cache so repeat runs skip introspection. --seed closes the last gap: the same seed produces byte-identical rows, and the seed used is printed at the start of every run, so a job that fails on the fourteenth try can be replayed exactly. Licenses validate offline, so CI runners on restricted networks don't phone home. See the CI/CD integration guide for GitHub Actions, GitLab, CircleCI, and Jenkins examples.

Synthetic generation vs masking real data

Two jobs get confused: producing fresh data, and scrubbing existing data. They have different inputs and different guarantees. Weavori does both from the same binary and the same foreign-key graph, so the choice is about what you need in the target database rather than which tool you have to install.

Three things are worth understanding before you pick.

Masking keeps the messiness, and generation cannot invent it. A scrubbed copy still holds the duplicate email your legacy import created, the empty ZIP codes, and the order whose paid_at predates its created_at. Generated data satisfies the rules you declared; it cannot violate them in the specific way production does. If the defect you are chasing depends on a row that exists, copy the row.

Generation produces scenarios that have not happened yet. You cannot mask a customer into a market you have not entered, a volume you have not reached, or a constraint violation your last migration has not caused. A masked slice is only ever as large and as strange as what you already have.

The hard part of masking is the join, not the value. Most attempts are UPDATE statements over columns, and the moment one touches a key it orphans every child row beneath it. The shipped design is bijective: one distinct replacement per distinct original, uniqueness preserved, NULL left as NULL, and a foreign-key child takes the replacement its parent row received, so the graph stays connected after the scrub. Columns that carry no identity, keys, serials, numerics, booleans, are not touched at all.

That determinism deserves precision, because it is the part most masking pages blur. A substitution that is consistent and unique is pseudonymization, not irreversible anonymization: the mapping is stable and reproducible from the seed you hold. For a staging or demo database that is usually exactly the property you want, since anything weaker breaks joins and makes a reproduced bug impossible to reproduce twice. If an obligation requires data that can never be mapped back, check that requirement against what consistent substitution provides rather than assuming the flag name settles it.

If your constraint is that no real data may leave your network, generation remains the stronger position: nothing is copied or scrambled because nothing real is touched. That is now a choice you are making rather than a limitation you are living with.

Greenmask and PostgreSQL Anonymizer stay the right answer for different tastes: transformation rules declared as database labels, custom per-column SQL transforms, and scrubbing a single cluster in place instead of copying between two. weavori sync is on the Pro plan; the flags are in the sync reference.

A masked slice also sits outside the artifact and recipe split from Backups are for data you cannot regenerate: it needs production access to produce, so it inherits artifact-class handling, and it holds no PII, so it can live comfortably inside a sandbox an agent is allowed to destroy.

If you want the masked-slice mechanics, the join trap a naive UPDATE script creates, and how --subset keeps a small copy referentially honest, that is the whole subject of A referentially intact slice of production, without the PII.

The tool landscape in 2026

Different tools fit different workflows. Honest breakdown of the options teams evaluate alongside Weavori:

For full pricing, feature-by-feature comparisons, and who each tool is honestly best for, see the comparison hub.

If your schema lives behind an ORM, the framework guides cover that stack's own seeding commands and where they break: Prisma, Drizzle, TypeORM, Django, Rails, and Laravel.

How fast is it?

Realistic doesn't have to mean slow. Weavori streams generation with constant memory, so scale doesn't trade off against correctness:

• 100,000 rows across 14 tables in 12.4 seconds
• 25,000 rows in 2.1 seconds; 2,500 rows in 384 ms
• Typical schemas (under 200 tables) complete in under 2 minutes
• Stress-tested against a 500 GB benchmark database

Summary

Realistic PostgreSQL test data comes from five practices, in order:

1. Start from the schema: introspection beats hand-configured fixtures
2. Guarantee FK integrity by construction: parents before children, always
3. Sample real distributions: pg_stats frequencies carry over to every column whose values repeat, and --distribution shapes the ones that do not
4. Enforce cross-column coherence: timestamps, conditionals, and CHECKs per row
5. Automate in CI: API keys, exit codes, offline licensing, cached introspection

Weavori does all five in one command:

The preview shows the full generation plan before a single row is written, estimate rows, spot generator choices, then run. Start with the quickstart, and if you're weighing it against another tool, the comparison hub has the honest trade-offs.

No database yet? You can generate from pasted CREATE TABLE statements with DDL paste mode.