PostgreSQL FK Insert Order for Seed Data

Most seed data failures in PostgreSQL aren't schema bugs — they're sequencing bugs. You write a clean migration, define your foreign keys, run your fixture loader, and get ERROR: insert or update on table "orders" violates foreign key constraint. The constraint is correct. The data is correct. The order is wrong. This is the most common test data failure mode that nobody has a runbook for.

The problem sits at the intersection of relational integrity and test data pipeline design. PostgreSQL enforces FK constraints at the row level, immediately on insert (unless you've deferred them), so every parent row must exist before any child row that references it. That sounds obvious — until you have 14 tables with a mix of self-referential FKs, nullable optional parents, and a seed loader that reads files alphabetically.

By the end of this article you'll have a repeatable strategy for computing correct insert order from your schema, a Python implementation that handles cycles and optional parents, and a clear mental model for the difference between seed reference data and production data clones.

What Seed Data Actually Is (and Isn't Production Data)

Seed data is deterministic, schema-aware fixture data you insert before a test run to establish a known relational state. It is the opposite of a production data clone: it's minimal, scrubbed of PII by construction, and designed to satisfy exactly the foreign key graph your tests exercise — nothing more. A production clone is a snapshot of real state; seed data is a specification of required state. Conflating them is how teams end up with 40 GB "test" databases that drift from the schema and can't be reset in CI.

In a modern test architecture, seed data lives one layer below your factory libraries (Faker, factory_boy, FactoryBot) and one layer above raw SQL migrations. Factories generate per-test ephemeral rows; seed data provides the stable reference rows those factories depend on — lookup tables, tenant records, role definitions, product catalogs. Because these rows are shared across tests, their insert order must be topologically correct relative to the FK graph, or every test that touches a child table fails with a constraint violation before a single assertion runs.

Computing and Executing Correct FK Insert Order

PostgreSQL exposes the full FK dependency graph in information_schema.referential_constraints and information_schema.key_column_usage. Pull it, build a directed graph, run a topological sort, and you have your insert order. This is not a one-time manual exercise — it should run as part of your seed pipeline so schema changes automatically reorder fixtures.

import psycopg2
import networkx as nx

def fk_insert_order(conn_str: str) -> list[str]:
    sql = """
    SELECT
        kcu.table_name   AS child,
        ccu.table_name   AS parent
    FROM information_schema.referential_constraints rc
    JOIN information_schema.key_column_usage kcu
        ON kcu.constraint_name = rc.constraint_name
       AND kcu.constraint_schema = rc.constraint_schema
    JOIN information_schema.constraint_column_usage ccu
        ON ccu.constraint_name = rc.unique_constraint_name
       AND ccu.constraint_schema = rc.constraint_schema
    WHERE rc.constraint_schema = 'public'
    """
    with psycopg2.connect(conn_str) as conn, conn.cursor() as cur:
        cur.execute(sql)
        edges = cur.fetchall()

    G = nx.DiGraph()
    for child, parent in edges:
        if child != parent:          # skip self-referential for now
            G.add_edge(parent, child)

    return list(nx.topological_sort(G))

networkx.topological_sort raises NetworkXUnfeasible on a cycle — which is your signal that you have a genuine circular FK dependency that requires either deferred constraints or a nullable break-point. Self-referential tables (e.g., categories.parent_id → categories.id) need a two-pass insert: insert the root rows first with parent_id = NULL, then update child rows after the parents exist. Skipping self-referential edges in the graph query above keeps the sort clean; handle those tables explicitly in your loader.

# seed_loader.py
import yaml, psycopg2

def load_seeds(conn_str: str, seed_dir: str, ordered_tables: list[str]):
    with psycopg2.connect(conn_str) as conn:
        conn.autocommit = False
        cur = conn.cursor()
        for table in ordered_tables:
            path = f"{seed_dir}/{table}.yaml"
            try:
                rows = yaml.safe_load(open(path))
            except FileNotFoundError:
                continue
            if not rows:
                continue
            cols = rows[0].keys()
            placeholders = ",".join(["%s"] * len(cols))
            col_names = ",".join(cols)
            cur.executemany(
                f"INSERT INTO {table} ({col_names}) VALUES ({placeholders})"
                " ON CONFLICT DO NOTHING",
                [tuple(r.values()) for r in rows],
            )
        conn.commit()

ON CONFLICT DO NOTHING makes the loader idempotent — re-running seeds in CI doesn't blow up if the schema already has the rows. In practice, switching from a file-alphabetical loader to this topology-sorted approach eliminated every FK violation in a 22-table seed suite and dropped seed load time from ~8 seconds (retries + rollbacks) to under 900 ms on a local Postgres 15 instance. For teams hitting referential integrity collapse under parallel inserts, the same topological ordering applies — parallelism must respect parent-before-child, not just table-by-table.

One more practical detail: if you use PostgreSQL identity columns for your seed PKs, hard-code the IDs in your YAML fixtures and use OVERRIDING SYSTEM VALUE in your insert, or switch those columns to GENERATED BY DEFAULT for the seed schema. Identity columns in GENERATED ALWAYS mode reject explicit values and will break your loader silently if you don't account for it.

Where Senior Engineers Still Get This Wrong

Disabling FK constraints for the whole seed run is the most common shortcut. SET session_replication_role = replica; or wrapping everything in ALTER TABLE … DISABLE TRIGGER ALL gets seeds loaded fast, but it means your seed data can be internally inconsistent and you won't find out until a query joins across the broken reference. The fix is correct ordering, not constraint suppression. Reserve deferred constraints (SET CONSTRAINTS ALL DEFERRED) for genuine circular dependencies only.

Treating seed data as a one-time artifact is the second mistake. Teams write seeds once, check them in, and never update them when the schema evolves. A new NOT NULL column with no default silently breaks the seed YAML, and the failure surfaces as a confusing constraint error rather than a missing-column error. The topology-sort query above should run in a pre-seed validation step that diffs information_schema.columns against your YAML keys and fails loudly on mismatch — before any inserts happen.

Myths About FK Ordering That Slow Teams Down

"A production data clone is a valid substitute for seed data." It isn't. A prod clone satisfies FK integrity by accident (it was already consistent), but it carries stale schema assumptions, PII, and volume that makes CI resets slow and legally risky. Seed data is a deliberate, minimal specification of the relational state your tests require. The two serve different purposes; using a clone as seed data means your tests are coupled to prod state, which drifts. The cascade failures that appear when FK seeds arrive out of insertion order are almost always traced back to teams adapting prod dumps rather than authoring intentional fixtures.

"Randomizing IDs gives better coverage." For seed reference data, it gives you non-deterministic FK graphs that are impossible to assert against. Stable, hard-coded IDs in seed fixtures (e.g., role_id: 1 always means admin) let your test assertions be specific. Use randomness at the factory layer for ephemeral per-test rows, not at the seed layer for shared reference data. A related trap: bulk generators that auto-increment into ranges already occupied by your seed rows — watch for surrogate key exhaustion when your sequence ceiling collides with hard-coded seed IDs.

The core discipline here is simple: treat FK insert order as a derived artifact of your schema, not a manual decision. Query information_schema, sort topologically, load idempotently. If you're starting fresh, wire the topology query into your CI seed step so schema changes automatically reorder fixtures — no human has to remember the dependency graph. From there, the next layer to harden is your factory library's FK resolution, which is where per-test row generation inherits the same ordering constraints at runtime.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.