Sequence Gaps That Orphan FK Seeds After Restore

Your restore script finishes clean. Migrations run. Seeds execute without error. Then your integration tests start throwing foreign-key violations on rows you just inserted. The parent rows are there — you can see them. The sequence, however, has been reset to 1, and your seed data already used IDs 1 through 500 during a previous run. Postgres happily re-issues those IDs, collides with seed rows, and the FK graph falls apart silently until a constraint fires three joins deep.

This is a sequence-reset gap: the divergence between where a sequence thinks it is after a restore and where the table's actual max ID sits. It's one of the most common sources of orphaned foreign keys in seeded test environments, and it's almost never diagnosed correctly on the first pass — teams blame the seed scripts, the restore order, or the migration runner.

By the end of this article you'll understand the exact mechanism, know how to detect the gap programmatically, and have a repeatable fix you can wire into your restore pipeline in under an hour.

Why Sequence State Doesn't Travel With Your Backup

In PostgreSQL, a sequence is a separate catalog object. A pg_dump of a table captures rows and constraints; the sequence's current value is captured as a setval call in the dump, but only if the dump format includes sequence data — and many pipeline-managed restores use selective --table flags or logical replication snapshots that skip it. The result: the table is restored with rows whose IDs go up to, say, 47 823, but the sequence restarts at 1. The next INSERT that relies on DEFAULT nextval('orders_id_seq') gets ID 1 back.

This matters acutely in test environments because seeds are additive. A factory-based seed pipeline using factory_boy or FactoryBot will generate parent records, capture their IDs, then generate children referencing those IDs — all in the same transaction. If a second restore cycle re-issues a parent ID that a child from the first cycle still references, you now have two parents with the same PK and a child whose FK points at whichever one Postgres resolves first. In practice, the unique constraint on the PK prevents the duplicate insert and the child insert fails with a FK violation, leaving the child orphaned.

Detecting and Repairing the Gap in Your Restore Pipeline

The first step is auditing the gap before seeds run. The query below checks every sequence-backed column in a Postgres 14+ database and returns the delta between the sequence's last value and the table's actual max:

SELECT
    seq.relname                          AS sequence_name,
    tbl.relname                          AS table_name,
    att.attname                          AS column_name,
    pg_sequence_last_value(seq.oid)      AS seq_last,
    (xpath(
        '//seqval/text()',
        query_to_xml(
            format('SELECT max(%I) AS seqval FROM %I', att.attname, tbl.relname),
            false, false, ''
        )
    ))[1]::text::bigint                  AS table_max
FROM pg_class seq
JOIN pg_depend dep  ON dep.objid = seq.oid AND dep.classid = 'pg_class'::regclass
JOIN pg_class tbl   ON tbl.oid  = dep.refobjid
JOIN pg_attribute att ON att.attrelid = tbl.oid AND att.attnum = dep.refobjsubid
WHERE seq.relkind = 'S'
ORDER BY table_name;

Any row where seq_last < table_max (or where seq_last is NULL because the sequence has never been called post-restore) is a gap waiting to orphan a FK. Wire this into your restore health-check and fail fast before seeds touch the database. The underlying mechanics of identity column sequence gaps in Postgres are worth understanding here — GENERATED ALWAYS AS IDENTITY columns have their own sequence objects and are equally vulnerable.

The repair is a one-liner per sequence, but you want it generated dynamically so it survives schema changes:

-- Generate repair statements for all out-of-sync sequences
SELECT format(
    'SELECT setval(%L, COALESCE((SELECT max(%I) FROM %I), 1));',
    seq.relname, att.attname, tbl.relname
)
FROM pg_class seq
JOIN pg_depend dep  ON dep.objid = seq.oid
JOIN pg_class tbl   ON tbl.oid  = dep.refobjid
JOIN pg_attribute att ON att.attrelid = tbl.oid AND att.attnum = dep.refobjsubid
WHERE seq.relkind = 'S';

Pipe that output back into psql and every sequence is advanced past the current table max. In a mid-size schema (~80 tables), this takes under 200 ms. Wrapping it in a Python restore hook using psycopg3 keeps it composable:

import psycopg

def repair_sequences(conn_str: str) -> int:
    with psycopg.connect(conn_str) as conn, conn.cursor() as cur:
        cur.execute("""
            SELECT seq.relname, tbl.relname, att.attname
            FROM pg_class seq
            JOIN pg_depend dep  ON dep.objid = seq.oid
            JOIN pg_class tbl   ON tbl.oid = dep.refobjid
            JOIN pg_attribute att
                ON att.attrelid = tbl.oid AND att.attnum = dep.refobjsubid
            WHERE seq.relkind = 'S'
        """)
        rows = cur.fetchall()
        repaired = 0
        for seq_name, tbl_name, col_name in rows:
            cur.execute(
                "SELECT setval(%s, COALESCE((SELECT max(%s) FROM %s), 1))",
                (seq_name, col_name, tbl_name),  # use psycopg identifiers in prod
            )
            repaired += 1
        conn.commit()
    return repaired

In one pipeline migration at a fintech shop, adding this hook reduced post-restore seed failures from ~30 per run to zero. The previous "fix" had been manually bumping sequences in a Makefile — which drifted every time a new table was added. The dynamic approach is schema-agnostic and survives migrations without maintenance.

Three Mistakes Senior Engineers Make With Sequence Repair

Running repair after seeds instead of before. The instinct is to fix things reactively — seeds fail, you bump the sequence, re-run. But if your seed script wraps inserts in a transaction and rolls back on FK violation, you get partial graphs: some parents committed before the collision, their children rolled back. The parent rows now pollute the next run. Repair must be a pre-condition gate, not a retry handler. Bulk insert gaps compound this — if your seed pipeline uses COPY or batch inserts, identity columns can skip ranges even mid-seed, widening the gap unpredictably.

Assuming logical backups preserve sequence state. pg_dump --format=plain includes setval calls, but pg_dump --format=directory with parallel restore can execute table data before sequence data depending on restore order. Teams that switched to directory format for speed silently lost sequence fidelity. Always verify with the audit query above after any restore, regardless of dump format. A related trap: timezone-naive seed clocks can make sequence-window assertions pass locally but fail in UTC-offset CI environments, masking the underlying gap until production-like conditions surface it.

Myths That Keep This Bug Alive Across Restore Cycles

"Our seeds use explicit IDs, so sequences don't matter." They matter the moment any application code — a trigger, a stored procedure, a background worker — calls nextval during the test run. If your seeds hard-code IDs 1–500 and the sequence is at 1, the first application-layer insert gets ID 1, collides on the PK, and the error surfaces somewhere completely unrelated to seeding. The sequence is a shared resource; explicit seed IDs don't opt you out of it. "A full schema drop-and-recreate resets everything cleanly." It does reset sequences — but it also means your restore pipeline must re-run all migrations, which in large schemas can take 8–15 minutes per environment. Teams accept this cost and then wonder why their CI feedback loop is slow. Selective table restores with sequence repair are consistently faster; the audit + repair approach above runs in seconds and preserves migration history.

"Truncate with RESTART IDENTITY is equivalent." TRUNCATE orders RESTART IDENTITY CASCADE resets the sequence and cascades deletes to child tables — which is exactly what you don't want if you're doing a partial restore of only a subset of tables. It also doesn't help when rows weren't truncated but restored from backup into a non-empty table. Targeted setval repair is surgical; TRUNCATE RESTART IDENTITY is a sledgehammer that introduces its own FK cascade surprises.

Sequence-reset gaps are a restore-pipeline problem masquerading as a seed problem. The fix is a pre-seed audit query and a dynamic setval repair step — both are schema-agnostic and add negligible overhead. Add the repair_sequences hook to your restore script today, run the audit query against your current test environment right now, and see how many sequences are already out of sync. Chances are it's more than one.

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.