PostgreSQL database subsetting: a small copy that still joins

What subsetting is

A subset is a smaller database that keeps the structure of the original and a selected part of its rows. Selecting rows with a plain LIMIT or a random sample does not work in a relational schema: an order without its customer violates a foreign key, and the restore fails.

How Subveil chooses the rows

You name one or more root tables and choose their starting rows. Subveil then grows the selection in two directions so that every foreign key in the copy is satisfied:

• Down: rows that reference a selected row are added. A tenant brings its users, their orders, and so on.
• Up: rows that a selected row references are added, and so on up the chain. An order brings its customer and the customer's country. Going up never goes back down, so a parent that was pulled in does not drag in all of its other children.

Self-references such as parent_id are followed in both directions.

Choose roots with where or scale

Each root uses exactly one of where, a SQL condition that selects rows, or scale, a fraction between 0 and 1. A scale root picks a deterministic sample by hashing each row, so the same data gives the same sample on every run.

subset:  roots:    - table: public.tenants      where: "id = 'acme'"    - table: public.patients      scale: 0.05  full_tables:    - public.currencies  exclude_tables:    - public.audit_events

• full_tables are copied completely. Use them for small lookup tables such as currencies or countries.
• exclude_tables are skipped, and their structure must already exist on the target. Exclude only tables that nothing else depends on, such as logs, or the pull fails when the transaction commits.
• With no roots, every table is copied in full.

Load order and circular foreign keys

Subveil sorts tables topologically from the foreign-key graph, so a parent is always loaded before its children. Some schemas have cycles, for example employees.manager_id pointing back at employees, or two tables that reference each other. A cycle has no valid linear order, so Subveil breaks it at one foreign key and restores it afterwards:

• If every column of the key allows NULL, the column loads as NULL and a second pass sets the real value.
• If the constraint is DEFERRABLE, the check is postponed to the end of the transaction.
• If it is NOT NULL and not deferrable, Subveil makes the constraint deferrable on the target inside the same transaction, so a failed pull rolls that change back too. The source is never changed.

The second pass matches rows by primary key, so a table that needs a back-filled column must have one.

See the plan before you copy

plan reads only the schema and shows the load order, tables that can load at the same time and any circular keys. --dry-run reports how many rows a pull would copy.

$ subveil plan --schemas public$ subveil pull --config subveil.yaml --dry-run

Frequently asked questions

Can the subset contain broken foreign keys?

No. The selection is closed over every foreign key, and the target is loaded in a single transaction, so a violation rolls everything back instead of leaving a partial copy.

Is the sample random?

A scale root samples deterministically by hashing each row, so the same data gives the same sample on every run. A where root selects exactly the rows its condition matches.

How big will the copy be?

Run subveil pull --dry-run. It reports how many rows each table would copy before anything is written.

Does subsetting also mask personal data?

In Subveil the two happen in the same pull: rows are selected by the subset rules and masked by the transformers in the same run. See the guide on anonymizing a PostgreSQL database.