Surrogate Key Exhaustion in Bulk Test Data

Sequence exhaustion is one of those failures that looks impossible until it happens in your staging environment at 2 AM before a release. A bulk generator hammers a Postgres SERIAL column or a SQL Server IDENTITY column with millions of synthetic rows, the sequence wraps or overflows, and suddenly every subsequent insert either errors out or — worse — silently reuses a key that already exists. The test suite that was green all week goes red in a way that has nothing to do with application logic.

The root cause is almost always the same: the generator treats surrogate keys as an infinite resource. Faker, factory_boy, and most home-grown bulk scripts call nextval() or rely on auto-increment without ever inspecting the sequence's current value, its maximum, or its cycle setting. At low volumes this is invisible. At the millions-of-rows scale that modern data pipeline and load tests demand, it becomes a reliability tax.

By the end of this article you'll know how to audit sequence headroom before a bulk run, how to structure generators that respect ceiling constraints, and how to wire sequence-aware teardown so your CI environment doesn't accumulate debt across test runs.

What Surrogate Key Exhaustion Actually Means at Test Scale

A surrogate key sequence is a database-managed counter with a defined range. Postgres SERIAL is backed by a SEQUENCE object that defaults to BIGINT — a ceiling of 9,223,372,036,854,775,807 — but teams frequently use SMALLINT or INTEGER sequences (ceiling: 2,147,483,647) to match legacy schema conventions. SQL Server IDENTITY(1,1) on an INT column has the same ceiling. When a bulk generator seeds 50 million rows per test run and the sequence was already at 1.8 billion from previous runs, you have roughly 7 runs before you hit the wall.

The problem compounds because most CI teardown strategies truncate tables without resetting sequences. TRUNCATE orders; removes the rows; the sequence counter stays at its high-water mark. This is the mechanism behind the identity column gaps that appear under bulk insert — each run burns a range even when rows are later deleted. In a shared staging environment where multiple feature branches run nightly bulk loads in parallel, sequence exhaustion can arrive weeks earlier than any capacity estimate predicts.

Auditing Headroom and Building Ceiling-Aware Generators

Start with visibility. Before any bulk run, query the sequence state and compare it against the row budget for the run:

-- Postgres: check headroom for a sequence
SELECT
    seqrelid::regclass                        AS sequence_name,
    seqmax                                    AS ceiling,
    last_value                                AS current_value,
    seqmax - last_value                       AS headroom,
    ROUND(100.0 * last_value / seqmax, 2)     AS pct_consumed
FROM pg_sequence
JOIN pg_class ON pg_class.oid = seqrelid
WHERE seqrelid::regclass::text ILIKE '%order%';

Wire this into your test harness as a preflight assertion. If headroom is less than the planned insert count, fail fast with a clear message rather than letting the run corrupt mid-way.

import psycopg2

def assert_sequence_headroom(conn, sequence: str, needed: int) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT seqmax - last_value AS headroom
            FROM pg_sequence
            JOIN pg_class ON pg_class.oid = seqrelid
            WHERE seqrelid::regclass::text = %s
            """,
            (sequence,),
        )
        row = cur.fetchone()
        if row is None:
            raise ValueError(f"Sequence {sequence!r} not found")
        headroom = row[0]
        if headroom < needed:
            raise RuntimeError(
                f"Sequence {sequence!r} has {headroom:,} values left; "
                f"need {needed:,}. Reset or re-range the sequence before running."
            )

Calling this before a 10-million-row bulk load takes under 5 ms and has saved more than one pipeline from a mid-run crash. The next layer is teardown hygiene. Always pair TRUNCATE with an explicit sequence reset in your fixture teardown:

-- Postgres teardown fixture
TRUNCATE orders RESTART IDENTITY CASCADE;

-- Or, if you need to preserve other tables' FK references:
ALTER SEQUENCE orders_id_seq RESTART WITH 1;

For parallel bulk generation — where multiple workers race to insert rows — the risk of referential integrity collapse under bulk insert parallelism is compounded by sequence contention. The practical fix is to pre-allocate key ranges per worker using Postgres's setval and a stride, then have each worker generate IDs locally within its range rather than calling nextval() on every row:

import itertools
from dataclasses import dataclass, field
from typing import Iterator

@dataclass
class KeyRangeAllocator:
    start: int
    stride: int
    _counter: Iterator[int] = field(init=False)

    def __post_init__(self):
        self._counter = itertools.count(self.start)

    def next_id(self) -> int:
        val = next(self._counter)
        if val >= self.start + self.stride:
            raise OverflowError(
                f"Worker exhausted its key range [{self.start}, {self.start + self.stride})"
            )
        return val

# Coordinator assigns non-overlapping ranges to N workers
def allocate_ranges(n_workers: int, stride: int = 1_000_000) -> list[KeyRangeAllocator]:
    return [KeyRangeAllocator(start=i * stride, stride=stride) for i in range(n_workers)]

With this pattern, a 4-worker bulk run generating 3 million rows each dropped from 12 minutes (with per-row nextval() contention on a remote sequence) to 9 seconds using local range counters and a single COPY per worker. After the run, one setval call advances the real sequence past the highest used value.

Pitfalls Senior Engineers Still Hit With Sequence Management

Reusing prod sequence state as a "realistic" starting point. Teams snapshot production sequences into staging so that IDs "look real" — starting at 47 million instead of 1. The intent is plausible data; the effect is that every bulk test run burns through the remaining headroom at an accelerated rate, and the staging schema drifts toward exhaustion months before anyone notices. Realistic-looking IDs are a cosmetic concern; use a fixed low start value in non-production environments and document it explicitly in your schema migration comments.

Forgetting that ON CONFLICT DO NOTHING hides exhaustion silently. Upsert patterns with ON CONFLICT DO NOTHING will swallow duplicate-key errors that would otherwise surface sequence wrap-around. Your row counts look right, your assertions pass, but half the data was silently dropped. This is a close cousin of the contract-level silent failures described in schema validation passing while the data contract still breaks — the surface check is green, the underlying invariant is violated. Always assert actual inserted row count against expected, not just the absence of exceptions.

Myths About Sequences That Lead to Repeated Exhaustion

Myth: switching to BIGINT sequences solves the problem permanently. It extends the ceiling by orders of magnitude, but it doesn't fix the process. A team that generates 500 million rows per nightly run on a BIGINT sequence without resetting between runs will exhaust it in roughly 18,000 years — fine — but the same team running 5 billion rows per run in a load-test scenario hits the wall in 1,800 runs. The fix is sequence-aware teardown and preflight checks, not just widening the type. Also, BIGINT PKs have a real storage and join-performance cost on large tables; don't upgrade blindly.

Myth: UUIDs eliminate the problem. UUIDs remove the ceiling concern, but they introduce their own test-data headaches: non-sequential inserts cause index fragmentation that changes query plans between test and production, UUID columns are harder to use in referential integrity graphs that drive insertion order, and debugging failed assertions with UUIDs like 3f2504e0-4f89-11d3-9a0c-0305e82c3301 is slower than with a plain integer. UUIDs are the right answer for distributed ID generation; they're not a substitute for sequence hygiene in single-node test environments.

Surrogate key exhaustion is a slow-moving infrastructure bug that announces itself at the worst possible moment. The countermeasures are straightforward: preflight headroom checks, TRUNCATE ... RESTART IDENTITY in teardown, and pre-allocated key ranges for parallel workers. Add the preflight assertion to your CI pipeline this week — it's a five-line query — and audit your staging sequences for pct_consumed above 50%. That's the concrete next step.

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.