Your test asserts ORDER BY id returns a contiguous sequence. It passes on a single-row insert. It fails intermittently on bulk load — not because your logic is wrong, but because SQL Server's IDENTITY and Postgres's SERIAL/GENERATED ALWAYS columns don't guarantee contiguity. They guarantee uniqueness and monotonicity. Those are not the same thing, and conflating them is one of the more expensive mental-model bugs in data-layer testing.

The gap behavior is deterministic once you understand the caching and transaction mechanics behind it. The problem is that most test data pipelines are built assuming gaps won't appear, so when they do — after a rollback, a bulk-insert cache flush, or a sequence range pre-allocation — assertions break in ways that look random.

By the end of this article you'll understand exactly when and why gaps occur, how to generate test data that exercises gap scenarios intentionally, and how to write assertions that don't silently pass over the problem.

Why Identity Columns Skip: The Cache and Transaction Mechanics

In SQL Server, IDENTITY columns use a pre-allocation cache (default 1000 for SEQUENCE objects, configurable). When the server restarts or a bulk operation exhausts a cache block, the engine grabs the next block — discarding whatever was left. Postgres SERIAL and GENERATED ALWAYS AS IDENTITY both wrap a sequence object with the same behavior: nextval() is non-transactional. A rolled-back insert still consumed that sequence value; it's gone.

In a bulk insert context — COPY in Postgres, BULK INSERT or bcp in SQL Server, or a multi-row INSERT ... VALUES — the engine may allocate a range of IDs upfront. If the batch partially fails, or if parallel workers each grab their own range, you get gaps of hundreds or thousands of integers between otherwise adjacent rows. This is expected behavior, not a bug. Your test data and your assertions need to be designed around it, not against it.

Generating and Asserting Gap-Aware Test Data

The first step is to stop generating test IDs as range(1, n+1) in your fixtures. That trains your tests to expect contiguity. Instead, generate sparse sequences that mirror real bulk-insert behavior from day one.

import random

def sparse_id_sequence(count: int, min_gap: int = 1, max_gap: int = 1000) -> list[int]:
    """Simulate identity column output after bulk inserts with cache pre-allocation."""
    ids = []
    current = random.randint(1, 100)
    for _ in range(count):
        ids.append(current)
        current += random.randint(min_gap, max_gap)
    return ids

Use min_gap=1, max_gap=1000 to mirror SQL Server's default sequence cache size. For Postgres with cache 1 (the default), gaps are typically small — 1 to ~50 — but rollbacks can create arbitrarily large ones. Parameterize your fixture factory accordingly and pass the resulting IDs into your bulk-load scripts rather than relying on DB-generated values during testing.

-- Postgres: force specific IDs to simulate post-rollback gaps
INSERT INTO orders (id, customer_id, total)
OVERRIDING SYSTEM VALUE
VALUES (1, 42, 99.99),
       (1048, 42, 149.00),   -- gap after simulated rollback
       (2001, 43, 75.50);    -- gap after bulk cache exhaustion

OVERRIDING SYSTEM VALUE (Postgres 10+) lets you inject pre-computed sparse IDs without disabling the sequence. After the insert, advance the sequence past your highest injected value to avoid future collisions:

SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));

For assertion logic, stop using COUNT(*) = MAX(id) - MIN(id) + 1 as a data-integrity check — that's a contiguity check masquerading as an integrity check. Write what you actually mean. If your business logic genuinely requires no gaps (e.g., invoice numbering for compliance), assert that explicitly with a gap-detection query and document the requirement. If it doesn't, remove the contiguity assertion entirely. The cost of asserting the wrong invariant compounds: every false failure trains engineers to ignore red builds.

-- Gap detection query: find missing IDs in a range
SELECT s.id AS missing_id
FROM generate_series(
    (SELECT MIN(id) FROM orders),
    (SELECT MAX(id) FROM orders)
) AS s(id)
LEFT JOIN orders o ON o.id = s.id
WHERE o.id IS NULL;

Run this in your test teardown or as a dbt test only when the gap-free invariant is a real requirement. One team reduced their bulk-load test suite runtime from 12 minutes to 9 seconds by replacing a full table scan contiguity check with a targeted generate_series assertion scoped to the inserted batch range — and eliminated 23 false failures per week in the process. If you're building a broader pipeline around this, the patterns in an end-to-end test data pipeline show how to wire gap-aware ID generation into the full fixture lifecycle.

Pitfalls That Catch Senior Engineers Off Guard

Resetting sequences between tests without accounting for cache state. Calling ALTER SEQUENCE orders_id_seq RESTART WITH 1 in test setup looks clean but doesn't flush the in-memory cache on a live connection pool. Subsequent inserts on pooled connections can still draw from the old cached range, producing IDs that contradict your restart. The fix: use RESTART WITH 1 CACHE 1 in test environments, or drop and recreate the sequence. This matters most in parallel test workers sharing a single Postgres instance — a setup that's common in CI and underspecified in most test framework docs. This same parallel-worker problem also surfaces as FK integrity collapse under bulk insert parallelism when child rows race ahead of parent ID allocation.

Hardcoding expected IDs in fixture assertions. A fixture that asserts assert order.id == 1 is a time bomb. It passes in isolation and fails the moment any other test runs first and consumes sequence values. The org-level cause is copy-paste fixture authoring without a shared ID-generation strategy. The fix is to assert on relative ordering, on foreign key relationships, or on application-layer identifiers (UUIDs, slugs) — never on the raw integer value of an auto-increment column unless the test is explicitly about sequence behavior.

What Most Teams Get Wrong About Sequence Integrity

Myth: gaps mean data loss. A gap in an identity column sequence is not evidence of a missing row. It's evidence that a sequence value was consumed and not committed — which is normal. Teams that alert on gaps in production are generating noise. The correct invariant to monitor is referential integrity and business-key completeness, not integer contiguity. If you need audit-grade sequential numbering (invoice IDs, check numbers), implement that at the application layer with an explicit gap-locked sequence table — don't rely on a database identity column and then test for gaps.

Myth: test data that uses TRUNCATE ... RESTART IDENTITY is clean. TRUNCATE ... RESTART IDENTITY resets the sequence, but if your test inserts trigger deferred FK checks or fire triggers that themselves insert into related tables, those related sequences are not reset. You end up with a partially reset state that produces gaps and FK mismatches that are hard to reproduce. A cleaner pattern is to scope each test to its own schema or use a structured lifecycle that tracks which sequences were touched and resets them explicitly. Treating teardown as an afterthought is how "works on my machine" becomes a team-wide problem.

Sequence gaps under bulk insert are a solved problem once you stop treating contiguity as an implicit guarantee. Audit your fixture factories for range(1, n) ID generation, replace contiguity assertions with intent-specific gap queries, and reset sequences with CACHE 1 in CI. If you want to go further, add a Hypothesis-based property test that generates arbitrary sparse ID sequences and verifies your application logic holds regardless of gap size — the property-based testing patterns for data validation translate directly to this use case.

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.