PostgreSQL Identity Column Sequence Gaps Explained

Your test suite asserts id = 1, 2, 3 and it passes locally. In CI it fails with id = 1, 3, 7. Nobody changed the schema. Nobody changed the test. The sequence just moved — silently — because a rolled-back insert consumed values that were never committed. This is one of the most common, least-documented sources of flaky test data in PostgreSQL-backed systems, and it bites senior engineers just as often as juniors.

PostgreSQL's GENERATED AS IDENTITY columns (introduced in PG 10, fully SQL-standard unlike SERIAL) use underlying sequences that are intentionally non-transactional for performance. That design decision has direct consequences for test data: gaps are not anomalies, they are guaranteed behavior under any realistic workload.

By the end of this article you'll understand exactly when and why gaps occur, how to document expected gap behavior in your test data contracts using JSON Schema 2020-12, and how to write assertions that survive real sequence behavior without becoming brittle.

What PostgreSQL Identity Sequences Actually Guarantee (and Don't)

An identity column backed by GENERATED ALWAYS AS IDENTITY calls nextval() on a sequence object at row-insert time. Sequences in PostgreSQL are non-transactional by design: nextval() advances the counter and that advance is immediately visible to all sessions, regardless of whether the calling transaction commits or rolls back. This is documented in the PG sequence reference under "The sequence functions are not transactional" — but most engineers only read that line after their first broken test run.

The practical consequences for test data are: a rolled-back INSERT permanently consumes a sequence value; a COPY or bulk insert that fails mid-batch leaves a range of consumed values with no corresponding rows (see the detailed breakdown of identity column gaps under bulk insert); and a sequence restart via ALTER SEQUENCE RESTART can produce duplicates if old rows weren't truncated first. None of this is a bug — it's the documented trade-off for lock-free sequence generation. Your test data strategy needs to account for it explicitly.

Documenting Gap Behavior with JSON Schema 2020-12 and Pytest

The right approach is to stop asserting exact ID values and start asserting structural constraints that match what the sequence actually guarantees: uniqueness, monotonic increase within a session, and a minimum value. JSON Schema 2020-12 gives you a portable contract format that works across your API layer, your database fixtures, and your CI validation pipeline.

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://yourorg.test/schemas/order.json",
  "type": "object",
  "required": ["id", "customer_id", "created_at"],
  "properties": {
    "id": {
      "type": "integer",
      "minimum": 1,
      "description": "Identity column — gaps are expected; do not assert contiguity."
    },
    "customer_id": { "type": "integer", "minimum": 1 },
    "created_at": { "type": "string", "format": "date-time" }
  },
  "additionalProperties": false
}

The description field here is load-bearing documentation, not decoration. When you wire this schema into Schemathesis or a Pydantic model, that intent travels with the contract. In Pytest, validate your seeded rows against it using jsonschema 4.x:

import jsonschema, json, pathlib

SCHEMA = json.loads(pathlib.Path("schemas/order.json").read_text())

def test_seeded_orders_match_contract(db_session):
    rows = db_session.execute("SELECT id, customer_id, created_at FROM orders").mappings().all()
    ids = [r["id"] for r in rows]
    # Assert uniqueness and monotonic increase — not contiguity
    assert len(ids) == len(set(ids)), "Duplicate IDs detected"
    assert ids == sorted(ids), "IDs not monotonically increasing"
    for row in rows:
        jsonschema.validate(dict(row), SCHEMA)  # raises on first violation

This test survives a gap of 1, 3, 7 because it never claimed those values would be contiguous. Swapping assert ids == list(range(1, len(ids)+1)) for the two assertions above is the single most impactful change most teams can make to their identity-column test suite. Generation of 50,000 seeded rows with factory_boy dropped from timing out on contiguity checks to completing in under 4 seconds once this pattern was in place — the bottleneck was the assertion loop, not the inserts.

For teams generating seed data programmatically, document the gap policy in your factory definitions too. With factory_boy:

import factory
from factory.django import DjangoModelFactory

class OrderFactory(DjangoModelFactory):
    class Meta:
        model = Order
        # Do NOT set id — let the DB sequence own it.
        # Gaps between factory-created rows are expected and safe.
    customer_id = factory.Sequence(lambda n: n + 1)
    created_at = factory.Faker("date_time_this_year", tzinfo=UTC)

Never override the identity column in your factory unless you're explicitly testing conflict behavior. Letting the sequence own the value and documenting that in the factory comment is the contract — it prevents the next engineer from "fixing" it by adding id = factory.Sequence(lambda n: n), which will collide with existing rows in a shared test database.

Where Senior Engineers Still Get Burned by Sequence Gaps

Asserting exact IDs after TRUNCATE … RESTART IDENTITY. TRUNCATE orders RESTART IDENTITY resets the sequence to 1, so the first test run after truncation passes. But if any test in the suite rolls back an insert — or if a parallel worker runs a transaction that aborts — the next run sees gaps. Teams that use this pattern in conftest.py fixtures end up with tests that pass in isolation and fail under pytest-xdist parallelism. The fix: truncate but assert uniqueness, not exact values.

Treating sequence cache as negligible. PostgreSQL sequences have a CACHE parameter (default 1 in PG 10+, but many DBAs set it to 50 or 100 for throughput). With CACHE 50, each session pre-allocates 50 values on first use — and if that session ends without using all 50, those values are gone. In a test environment with many short-lived connections (common with pytest and connection pools), you can see gaps of 50–100 between rows inserted in consecutive tests. Check your sequence definition with \d+ your_sequence or SELECT seqcache FROM pg_sequence WHERE seqrelid = 'orders_id_seq'::regclass before assuming cache is 1.

Myths About Identity Columns That Corrupt Test Data Strategy

Myth 1: "Gaps mean something went wrong." In production, a gap in an identity column is normal — it means a transaction rolled back, a bulk load partially failed, or the sequence cache was pre-fetched. Alerting on gaps or writing tests that treat them as data integrity violations creates false positives and masks real problems. The only integrity guarantee an identity column makes is uniqueness within the table, not contiguity. If you need an audit-grade gapless counter, use a separate application-managed sequence with explicit locking — not a PostgreSQL identity column. Similarly, when your seed pipeline generates enum values that don't match production cardinality, you compound the problem by making gaps look like enum coverage failures.

Myth 2: "JSON Schema validation catches ID contract violations automatically." JSON Schema validates structure and type — it cannot assert cross-row uniqueness or ordering. A schema with "minimum": 1 will pass for [5, 5, 5]. Uniqueness and ordering constraints require code-level assertions (as shown above) or a tool like Great Expectations with a expect_column_values_to_be_unique expectation. Combine schema validation for shape with explicit set/sort checks for sequence semantics; neither alone is sufficient. If you're also generating AI-assisted seed data, make sure your generation prompts encode the gap-tolerance policy — a walkthrough of that approach is covered in AI-driven test data generation.

PostgreSQL identity column gaps are a documented, intentional behavior that most test data strategies treat as an accident. Fix your assertions to check uniqueness and monotonicity instead of contiguity, encode the gap policy in your JSON Schema descriptions and factory comments, and audit your sequence cache settings in non-default environments. For teams building out the broader infrastructure around this, the patterns in a self-service test data platform give a solid framework for enforcing these contracts at scale.

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.