A team wants an ephemeral database per pull request, seeded from a 4 TB production schema of about 900 tables. Roughly how large is a referentially consistent subset that supports the functional test suite, how long does creating one take, and which assumption dominates the estimate?

The assumptions, stated

• Size concentrates in a few tables. In most transactional schemas, three to six event-like tables hold 90-95% of the bytes and the other 890 tables are small. Assume 95% in five tables.
• The suite exercises entities, not volume. A functional suite typically needs on the order of 1,000 to 10,000 root entities (customers, accounts, orders), not millions.
• Each root entity drags a closure of related rows through foreign keys. Ten to fifty rows per root across child tables is a normal fan-out; take 30.
• Reference data comes whole. Currency tables, product catalogues, country lists: copy them entirely, because subsetting them breaks joins and saves nothing.

The arithmetic

Reference and configuration tables: the 5% outside the big tables is about 200 GB, and most of that is still history rather than reference data. Call genuine reference data 1-5 GB.

Entity closure: 5,000 roots × 30 rows × roughly 1 KB per row is about 150 MB.

Indexes and overhead roughly double the logical size, and the schema itself (900 tables, indexes, constraints) costs a few hundred megabytes before any data.

Total: on the order of 2-8 GB, so between 500× and 2,000× smaller than production. At that size a per-PR database restores from a prepared snapshot in under two minutes with copy-on-write storage, or five to fifteen minutes from a logical dump.

Which assumption dominates the error

The size of genuine reference data. The entity closure is small under any plausible fan-out; it would take a fan-out of 500 rows per root to reach even 2.5 GB. But if one "reference" table turns out to be a 300 GB product-pricing history that three tests join against, the estimate is wrong by two orders of magnitude and the whole design fails. Measure that one number before committing, by listing every table the suite touches with its size, which is a query, not a project.

What the number rules in and out

At 2-8 GB, per-PR databases are clearly viable: a snapshot restored per run costs pennies and removes the shared-environment contention that makes suites flaky. It rules out the common compromise of three shared masked environments, which exists only because people assume a test database must be production-sized.

What it does not rule in: performance testing, query-plan validation, or anything that depends on data volume. A planner choosing a sequential scan over 5,000 rows tells you nothing about its choice over 400 million, and a subset environment that people believe is representative of performance is worse than none, because it produces confident wrong answers.

The part the arithmetic hides

Referential consistency is the hard engineering, not the size. Following foreign keys is mechanical; the relationships that are not declared — an identifier in a JSON column, a soft reference by business key, a table joined only in application code — are what break the subset, and they are found by running the suite, not by reading the schema. Budget the first implementation in weeks, and expect the subset definition to need maintenance whenever the schema changes, which is the ongoing cost people forget when they compare it against a nightly clone.

Common weak answers

• "Just clone production nightly." It gives the right data and the wrong properties: one shared copy, hours of restore time, contention between teams, and every obligation that attaches to production personal data now attaching to the test estate.
• "Take the top 5,000 rows of each table." Fast and useless. Independent per-table limits break every foreign key, so the suite fails on missing parents rather than on defects.
• Quoting a size without measuring the reference tables. The estimate above is dominated by one unknown, and a plan that does not name that unknown is a guess presented as arithmetic.