Seeding Postgres When Your Schema Has Foreign-Key Cycles

I have lost more afternoons than I would like to admit on this exact problem: a seed script that ran cleanly yesterday now crashes on its first INSERT, and the error message tells you something you already knew, namely that you have a chicken-and-egg dependency between two tables.

ERROR: insert or update on table "users" violates foreign key constraint "users_organization_id_fkey"
DETAIL: Key (organization_id)=(1) is not present in table "organizations".

The natural next move is to reorder the inserts, putting organizations first, except that organizations.owner_user_id is NOT NULL REFERENCES users(id), which means you cannot insert an organization without a user that does not exist yet. You are looking at a foreign-key cycle, and no ordering of plain INSERT statements can satisfy every NOT NULL REFERENCES at row-insertion time. The rest of this article walks through three working strategies for seeding a Postgres database that contains FK cycles, plus a decision table for picking the right one. Examples assume Postgres 18, which is the current stable as of mid-2026, but most of the reasoning ports cleanly to earlier versions and to other RDBMSes, with the caveats called out where they matter.

Two Flavors of Foreign-Key Cycles

The first thing worth understanding is that two distinct cycle shapes show up in real schemas, and the fix for each is slightly different.

The friendly kind is the self-referential cycle, which is what hierarchical data tends to produce. The classic example is an employees table with a manager_id REFERENCES employees(id) column: the CEO row has a NULL manager, but every other row points at another row in the same table. Self-references are easy to seed because the root row's reference can almost always be left nullable, and once that's done you insert top-down.

The harder kind is the multi-table cycle, where two or more tables point at each other through NOT NULL columns. The canonical case is bidirectional ownership between users and organizations, but real schemas often contain longer cycles that route through join tables, such as users → roles → permissions → resources → users. A four-hop cycle like that one will not yield to "just reorder the inserts" no matter how patient you are.

If you want to see exactly which cycles your schema contains, the Postgres catalog will tell you. The following recursive CTE walks pg_constraint, collects every cyclic path, and canonicalizes each cycle to a single representative row so you do not get one duplicate per starting node:

WITH RECURSIVE fk_graph AS (
  SELECT
    conrelid::regclass  AS from_table,
    confrelid::regclass AS to_table
  FROM pg_constraint
  WHERE contype = 'f'
),
walk AS (
  SELECT from_table AS start_table,
         from_table, to_table,
         ARRAY[from_table, to_table] AS path
  FROM fk_graph
  UNION ALL
  SELECT w.start_table,
         g.from_table, g.to_table,
         w.path || g.to_table
  FROM walk w
  JOIN fk_graph g ON g.from_table = w.to_table
  WHERE g.to_table <> ALL(w.path[2:])
)
SELECT path
FROM walk
WHERE to_table = start_table
  AND start_table = (SELECT MIN(t) FROM unnest(path) AS t);

The MIN predicate at the end picks the rotation that begins at the lexicographically smallest table, which is what produces one row per cycle rather than one row per node-on-cycle. Run this against any schema older than about a year, and there will almost certainly be a cycle you forgot you had introduced. I ran it last quarter against a 30-table schema I thought I knew well, and it returned six.

Why Naive INSERT Order Fails

The reason hand-written seed scripts feel solvable at first is that most schemas form a directed acyclic graph of foreign keys, and a DAG always admits a topological sort. Countries come before cities, cities before addresses, addresses before users, and as long as you insert in that order, everything resolves on the first pass.

The moment a cycle exists, the FK graph stops being a DAG, and no per-table ordering of inserts can satisfy every NOT NULL REFERENCES at row-insertion time. At least one row on the cycle has to reference a row that does not exist yet. This isn't a bug in your seed script; it's a property of the graph, and chasing it with another permutation of the insert order will produce the same error from a different table. You have three working ways out.

Strategy A: Two-Pass Insert With Nullable Columns

The first strategy is to give up the NOT NULL constraint on at least one edge of the cycle, insert both sides with that edge left NULL, and then close the loop in a second statement.

CREATE TABLE users (
  id              bigserial PRIMARY KEY,
  email           text NOT NULL UNIQUE,
  organization_id bigint  -- nullable on purpose
);

CREATE TABLE organizations (
  id              bigserial PRIMARY KEY,
  name            text NOT NULL,
  owner_user_id   bigint NOT NULL REFERENCES users(id)
);

ALTER TABLE users
  ADD CONSTRAINT users_org_fk
  FOREIGN KEY (organization_id) REFERENCES organizations(id);

With the nullable edge in place, the seed becomes a straightforward two-pass operation inside a transaction:

BEGIN;

INSERT INTO users (email, organization_id) VALUES ('[email protected]', NULL) RETURNING id;
-- -> 1
INSERT INTO organizations (name, owner_user_id) VALUES ('Acme', 1) RETURNING id;
-- -> 1

UPDATE users SET organization_id = 1 WHERE id = 1;

COMMIT;

The technique works on every relational database you are likely to touch, which is its main appeal, but the cost is real and easy to underestimate: you have just modelled a NOT NULL business invariant as nullable at the schema level, and you now have to enforce it somewhere else, whether in application code, in a CHECK constraint flipped on after the seeding is done, or in a deferred trigger that watches commits. Production schemas rarely tolerate that compromise, which is why Strategy A tends to live in ad-hoc dev databases where the cycle is incidental rather than load-bearing.

One Postgres 17 quality-of-life improvement worth knowing about is that MERGE ... RETURNING combined with the new merge_action() function makes the two-pass shape less verbose for bulk imports, because you can now route inserts and updates through a single MERGE and capture which path each row took. The underlying two-pass logic is unchanged, but for many real workloads the line count comes down by roughly half.

Strategy B: Deferred Constraints Inside a Transaction

Postgres offers a more elegant escape: you can declare a foreign key as deferrable, which postpones the constraint check from row-insertion time to transaction-commit time.

CREATE TABLE users (
  id              bigserial PRIMARY KEY,
  email           text NOT NULL UNIQUE,
  organization_id bigint NOT NULL
);

CREATE TABLE organizations (
  id            bigserial PRIMARY KEY,
  name          text NOT NULL,
  owner_user_id bigint NOT NULL REFERENCES users(id) DEFERRABLE INITIALLY IMMEDIATE
);

ALTER TABLE users
  ADD CONSTRAINT users_org_fk FOREIGN KEY (organization_id)
  REFERENCES organizations(id) DEFERRABLE INITIALLY IMMEDIATE;

The seed then collapses to a single pass inside a transaction that opts into deferred checking:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO organizations (id, name, owner_user_id) VALUES (1, 'Acme', 1);
INSERT INTO users (id, email, organization_id) VALUES (1, '[email protected]', 1);

COMMIT;  -- both rows exist by now, both FK checks pass

For day-to-day work, DEFERRABLE INITIALLY IMMEDIATE is the variant you want, because it leaves the constraint behaving exactly like a normal one in every transaction except those that explicitly call SET CONSTRAINTS ALL DEFERRED. The more aggressive INITIALLY DEFERRED defers every transaction by default, which sounds harmless until you realize that errors then surface at commit instead of at the offending statement, making real bugs much harder to chase down.

There is a piece of folklore in this area worth dismantling before it bites you: a foreign key declared DEFERRABLE INITIALLY IMMEDIATE does not pay a measurable runtime cost compared to a non-deferrable one. For FK constraints specifically, the check happens at end-of-statement or end-of-transaction in both modes, so there is no fast path you are giving up by marking it deferrable. The reason this confuses people is that UNIQUE and PRIMARY KEY constraints do behave differently when made deferrable, because their underlying index can no longer enforce uniqueness eagerly, so the perf-myth that applies legitimately to UNIQUE indexes gets generalized, incorrectly, to foreign keys. If your team is resisting DEFERRABLE on production tables because of a vague performance worry, the cure is a five-minute benchmark.

Two real caveats apply.

The smaller one is that only declared-deferrable constraints can be deferred, so if you forgot to write DEFERRABLE in the original migration, you have to alter the constraint after the fact, and that alteration requires SHARE ROW EXCLUSIVE on both tables. It isn't the end of the world for a planned maintenance window, but it isn't a no-op either.

The larger and more current caveat is a 2026 footgun that bit teams who adopted the new NOT ENFORCED toggle introduced in Postgres 18. In releases shipped before May 14, 2026, a foreign key declared DEFERRABLE INITIALLY DEFERRED would quietly start behaving as NOT DEFERRABLE after being toggled NOT ENFORCED and then back to ENFORCED. There was no error, no warning, no log line, just a constraint that no longer deferred when you expected it to, which made for some entertaining debugging sessions. The fix shipped in 18.4, 17.10, 16.14, 15.18, and 14.23, and the remediation after upgrading is to toggle any affected constraint NOT ENFORCED and back to ENFORCED one more time. Constraints declared INITIALLY IMMEDIATE, and constraints that have never been routed through NOT ENFORCED, were not affected, which means most production seeds escaped this entirely. Anyone who used NOT ENFORCED for bulk loads in late 2025 or early 2026 should re-verify, however, because the silent nature of the regression makes "we never noticed" the most likely failure mode.

The MySQL side of the story is shorter: InnoDB does not support deferred foreign keys at all. Your options there are Strategy A or a scoped SET FOREIGN_KEY_CHECKS = 0 inside a transaction, which is a heavier hammer than most teams are happy to swing in CI.

Strategy C: Generator-Driven Cycle Resolution

Strategies A and B both work, but they oblige you to hand-write the insert plan for every cycle, and that burden compounds as the schema grows. A team with half a dozen cycles, or with a cycle topology that shifts every few migrations, will eventually find that its seed script has become its own piece of brittle infrastructure that breaks in ways indistinguishable from the original problem, just in different places.

Three rough tiers of tooling cover this space, and it is worth knowing where each one sits before reaching for any of them.

At the bottom of the budget curve sit column-level generators such as Faker, Mockaroo, and generatedata.com, which produce realistic per-column values like names, emails, and ZIP codes but do not model the foreign-key graph at all. They hand you a CSV or a stream of INSERT statements and leave the cycle resolution to you, which is appropriate for what they do but also means that a Faker-driven pipeline still needs Strategy A or Strategy B underneath it as soon as cycles enter the picture.

A newer middle tier of schema-aware data generators treats the FK graph as a first-class input rather than asking you to script around it. Tools in this category read the schema directly from the database, work out a valid load order on their own, and emit a plan that handles the cycle for you, which means you do not have to write Strategy A or B by hand for every cycle in your schema. The category is still small and emerging — Neosync and Seedfast are two examples that take this approach in slightly different ways — and most of these tools originated in regulated-industry workflows where teams could not legally copy production data into development environments and hand-written seeds were not scaling across migrations, which is why they tend to assume that audience by default. The mechanical part of the problem — figuring out a valid load order — turns out to be the easy half. The harder and more consequential half is generating realistic values while keeping every other schema constraint satisfied at the same time, because a type-valid email such as [email protected] passes the column definition but renders a B2B SaaS test dataset useless even when the FK relationships are all intact, and the same trap applies to unique indexes, partial indexes, check constraints, generated columns, RLS policies, and any business invariants encoded in triggers.

One tier further up the budget curve, the enterprise test-data-management platforms that show up most often in procurement decks — Tonic.ai, Synthesized, and Delphix — take the inverted approach: they sit in front of a production database, anonymize or otherwise transform real data on the way out, and ship the result into lower environments through pipelines that are typically bought, audited, and operated by a dedicated platform team. The premise is the opposite of the schema-aware generators above, which assume you specifically do not want production data anywhere near dev or staging. Both approaches exist because both audiences exist, and choosing between them is usually a function of your compliance posture, your appetite for managing a TDM platform, and whether your annual tooling budget reaches six figures.

Adopting a schema-aware generator is a trade-off, not a default. It pays off when you have a non-trivial number of cycles, when CI rebuilds happen often enough that the seed plan needs to survive without supervision, and when several environments — local, CI, staging, demo — all need data with similar shape. It is overkill when you have a single cycle, a stable schema, and a small team, because in that situation Strategy B in fifteen lines of SQL will still be there working in two years. The enterprise tier becomes the right answer when the compliance question is settled in the opposite direction, namely that production data is the ground truth your downstream environments must look like, anonymization is the only legally defensible path to lower environments, and your organization is willing to absorb the platform-engineering overhead that comes with it.

A 2026 Alternative Worth Considering: Clone, Do Not Regenerate

Postgres 18 made an older pattern viable in ways it had not been before, and it is worth mentioning here because for CI workloads it may obviate the entire choice between A, B, and C. The relevant pieces are a new server GUC, file_copy_method, and the existing FILE_COPY strategy on CREATE DATABASE. The GUC defaults to COPY, which performs a traditional byte-by-byte copy; setting it to CLONE tells Postgres to use copy_file_range() on Linux and FreeBSD or copyfile on macOS, which lets the kernel share blocks at the filesystem layer instead of physically duplicating them. On a copy-on-write filesystem such as XFS-with-reflinks, ZFS, APFS, or btrfs, the result is a 6 GB template cloned in roughly 212 milliseconds, against about 67 seconds for the default COPY method. On ext4 or any non-CoW filesystem, the kernel cannot honor the clone request, and you fall back to the slow byte-copy regardless, which makes the filesystem choice the load-bearing decision rather than the SQL. If your bottleneck is "how do I get a populated, schema-correct database in front of every test run as fast as possible," then cloning a template you seeded once is often a better answer than regenerating from scratch, regardless of which of the three strategies above you would have used to populate the template.

SET file_copy_method = 'clone';  -- session-level; for CI, set in postgresql.conf

CREATE DATABASE test_run_42
  TEMPLATE seedfast_template
  STRATEGY = FILE_COPY;

The catch is that the template must be idle at clone time, with no other connections, which is awkward inside the same Postgres instance that is running everything else. Most teams who lean on this pattern run a dedicated CI Postgres instance whose only job is hosting the templates, which is more infrastructure but a one-time setup.

When to Pick Which

A Note on Cycle Hygiene

A surprising number of "cycles" in production schemas turn out, on inspection, to be accidents that crept in across a few migrations rather than load-bearing design choices. Someone added created_by_user_id to an audit table that the users table already referenced, and nobody noticed the loop until a fresh seed run failed two sprints later. If the cycle in question is not actually load-bearing in your business logic, breaking it at the schema level by making one of the FK columns nullable in production is almost always a better long-term move than carrying any of the workarounds above. A seed script that does not have to resolve cycles is faster, simpler, and harder to break than any of the three strategies, and the documentation cost of explaining why the column is nullable is much smaller than the cost of explaining the seeding workaround to every new engineer who joins the team.

For the cycles that really are intentional, such as the ownership patterns, hierarchical references, audit chains, and any other places where both sides of the relationship genuinely cannot exist without each other, the right move is to pick the strategy that matches your stack and your appetite for ongoing maintenance, and then to write that choice down somewhere your future colleagues will find it. The undocumented version of "we use deferred constraints because Strategy A broke our integration tests last year" is exactly the kind of folklore that gets reinvented from scratch every eighteen months when the engineer who knew it leaves.