Referentially Consistent Subset

Test environments default to one of two bad answers. Either a copy of production, which is large, slow to provision, shared between teams and carries every obligation that attaches to real personal data — or hand-built fixtures, which are small and fast and describe a world that does not exist, with three customers who all have clean addresses and no history.

Subsetting is the third answer: take the shape of production and a tiny fraction of its rows, chosen so that every foreign key still resolves. The result behaves like the real schema under test — the same constraints, the same joins, the same awkward optional fields — at a size that fits in a container that lives for the length of a pull request.

Why it matters

The size reduction is larger than people expect, and it changes what is architecturally possible. In a typical transactional schema, three to six event-like tables hold 90-95% of the bytes, and a functional suite needs none of that volume: it needs on the order of 5,000 root entities and their closures. At roughly 30 related rows per root and about 1 KB per row, that is around 150 MB before indexes — so a 4 TB production database subsets to something on the order of 2-8 GB, between 500× and 2,000× smaller.

At that size a database per pull request restores from a prepared snapshot in under two minutes. That removes the shared-environment contention that makes suites flaky, and it removes the need for the three shared masked environments most organisations run, which exist mainly because everyone assumed a test database had to be production-sized.

Implementation patterns

• Start from root entities and follow declared foreign keys in both directions: the customer, their orders, the order lines, and also the products those lines reference.
• Copy reference data whole. Currency codes, country lists, product catalogues, configuration tables. Subsetting them breaks joins and saves little, since they are small by nature.
• Select roots deliberately, not randomly. Include the awkward ones: the customer with 400 orders, the account in every lifecycle state, the record with unusual characters in the name. A random sample of a long-tailed distribution contains none of them.
• Transform as you extract, not afterwards, so personal data never lands in the test store in the first place. Keep the transformation consistent so joins on a masked key still work.
• Materialise as a snapshot, restore per run. Copy-on-write storage or a prepared image makes provisioning a seconds-long operation instead of a data pipeline.
• Verify by running the suite, then add whatever was missing to the subset definition. That loop is how undeclared relationships get discovered.

Industry example

This is a well-established commercial category rather than a single company's invention: database virtualisation and subsetting products have existed since the early 2010s precisely because regulated organisations could not keep cloning production into test, and open-source tools now do the same for common relational engines. The pattern's adoption tracks two pressures — data protection rules that made masked production copies a liability, and per-change ephemeral environments that made large test databases impractical.

Failure scenarios

• Undeclared relationships break the subset: an identifier inside a JSON column, a soft reference by business key, a join that exists only in application code. The extractor cannot see them and the suite fails on missing parents.
• The per-table row limit shortcut: taking the first 5,000 rows of every table independently, which breaks every foreign key and produces a database that fails on startup.
• Reference data that is not reference data: a 300 GB pricing history classified as a lookup table, which breaks the size estimate by two orders of magnitude.
• Subset drift: the schema evolves, the subset definition does not, and the extract silently stops including a new required table.
• Performance conclusions drawn from the subset: a planner choosing a sequential scan over 5,000 rows tells you nothing about 400 million, and the confident wrong answer is worse than no answer.
• Masking that breaks determinism: the same customer masked differently in two tables, so joins on the masked key return nothing.

Trade-offs

The maintenance cost is the one teams underestimate: the subset definition is code that tracks schema change, and when nobody owns it, it rots quietly and the suite starts failing for reasons nobody can explain.

When not to use it

Not for performance, capacity or query-plan work — those need production volume, and the right answer there is a restored production-sized environment used rarely, or measurement in production itself.

Not for a small system, either. If the whole database is 2 GB, subsetting is machinery with no purpose: copy it. The practice earns its cost roughly above the point where a full copy stops fitting on a developer's machine or a CI runner, which in practice means tens of gigabytes and up.

Interview question

Q: You want a database per pull request for a 4 TB schema with 900 tables. Estimate the size of the subset you need, say which assumption you would verify before committing to the approach, and describe how you would discover the relationships your extractor cannot see.

What a strong answer covers: the arithmetic — root entities times fan-out times row size, with reference data copied whole — landing in the low single-digit gigabytes; the recognition that reference-data size is the dominant unknown and is a query rather than a project; discovery by running the suite and iterating on the definition, since undeclared relationships are invisible to schema inspection; deliberate selection of awkward roots over random sampling; transformation during extraction rather than after; and the explicit statement that the subset is not valid for performance conclusions.

Quick check

Quiz: Why does taking the first 5,000 rows of every table fail? — Independent per-table limits break foreign keys, so children reference parents that were not extracted; the subset must be built by following relationships from selected root entities.

Flashcard: What dominates the size estimate for a test subset? — Reference data copied whole, not the entity closure. The closure is megabytes under any plausible fan-out; a single misclassified "lookup" table can be hundreds of gigabytes.