Database Testing Sequence Gap Handling: Prove IDs Are Unique Without Demanding Continuity

Database testing sequence gap handling starts with one decisive rule: an auto-generated numeric identifier should normally be tested for uniqueness and forward progress, not for a perfectly continuous series. PostgreSQL sequences, SQL standard identity columns, and comparable database generators allocate values independently from the transaction that inserts a row. A rollback can consume 42 even though no committed row ever receives 42. Concurrency, sequence caches, conflict handling, and failover can create larger holes without corrupting a single record.

The useful QA question is therefore not, "Why is ID 42 missing?" It is, "Can the generator ever issue a duplicate, move behind existing data, violate ownership expectations, or prevent a valid insert?" This guide turns that question into concrete SQL and TypeScript tests. It also shows when a gap really is a product defect, such as a missing legally controlled invoice number, and when the identifier design itself needs to change.

Sequence behavior can interact with concurrent visibility, so validate those guarantees separately with database testing transaction isolation levels. Once database invariants are solid, exercise the HTTP creation path with the SuperTest Node API testing complete guide. The layers should share fixtures, but each layer should report a failure in its own vocabulary.

Define the sequence contract before counting anything

Teams often inherit an implicit contract from a dashboard screenshot: IDs look consecutive, so someone assumes they must remain consecutive. That observation is not a specification. Write the intended guarantees down before producing test data.

For an internal primary key, a strong and realistic contract usually says:

• Every committed row has a non-null identifier.
• No two rows can have the same identifier.
• Normal inserts obtain identifiers without application coordination.
• The generator remains ahead of the values it is expected to generate.
• Rollback, retry, and conflict paths may consume unused values.
• Clients do not infer row count, chronology, or authorization from the number.

For a regulated business number, the contract can be different. An invoice reference may require auditable allocation, an explicit void record for every abandoned number, and a series scoped by legal entity and fiscal year. That is not merely a stricter sequence. It is a business workflow with a ledger.

Identifier role	Continuity required?	Primary test oracle	Suitable mechanism
Internal row primary key	Usually no	uniqueness, non-null, generator health	identity column or sequence
Public opaque resource ID	No	uniqueness, unguessability if required	UUID or another opaque ID
Display order	Yes within a defined view	deterministic ordering rules	query ordering, not a primary key
Legal invoice number	Depends on jurisdiction and policy	allocation ledger, void reason, series scope	transactional business-number service
Event stream position	Often monotonic per stream	ordering and concurrency semantics	stream-specific append mechanism
Human ticket reference	Usually no continuity promise	uniqueness and support lookup	sequence plus formatted prefix

This classification prevents a common category error: applying an accounting requirement to a storage surrogate, or treating a storage surrogate as proof of accounting completeness.

Build a fixture that exposes real PostgreSQL behavior

Use an isolated schema or disposable database. The following PostgreSQL fixture creates an identity-backed table and makes the generated sequence discoverable through documented catalog functions. It does not hard-code the sequence name.

DROP TABLE IF EXISTS qa_orders;

CREATE TABLE qa_orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  request_key text NOT NULL UNIQUE,
  amount_cents integer NOT NULL CHECK (amount_cents >= 0),
  created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

SELECT pg_get_serial_sequence('qa_orders', 'id') AS sequence_name;

GENERATED BY DEFAULT allows a controlled explicit-ID fixture later. Production teams that never permit supplied identifiers can choose GENERATED ALWAYS and use OVERRIDING SYSTEM VALUE only in an authorized migration. The test goal is not to prescribe one mode, but to assert the mode your schema declares.

Start with the rollback case. It demonstrates why a query such as count(*) = max(id) is not a legitimate health check.

INSERT INTO qa_orders (request_key, amount_cents)
VALUES ('committed-a', 1200)
RETURNING id;

BEGIN;
INSERT INTO qa_orders (request_key, amount_cents)
VALUES ('rolled-back', 900)
RETURNING id;
ROLLBACK;

INSERT INTO qa_orders (request_key, amount_cents)
VALUES ('committed-b', 1500)
RETURNING id;

SELECT id, request_key
FROM qa_orders
ORDER BY id;

The second committed row can be separated from the first by a missing value. That is expected because nextval is not rolled back. The exact numbers should not be asserted when the database may contain prior fixture activity. Assert relationships instead: both committed keys exist, the rolled-back key does not exist, IDs are different, and a later allocation succeeds.

Conflict handling can consume a value too. PostgreSQL evaluates expressions needed for an attempted row before ON CONFLICT decides what to do. This example deliberately collides on request_key:

INSERT INTO qa_orders (request_key, amount_cents)
VALUES ('idempotent-1', 1000)
RETURNING id;

INSERT INTO qa_orders (request_key, amount_cents)
VALUES ('idempotent-1', 1000)
ON CONFLICT (request_key) DO NOTHING
RETURNING id;

INSERT INTO qa_orders (request_key, amount_cents)
VALUES ('idempotent-2', 1000)
RETURNING id;

The middle statement returns no row, but it may still advance the identity sequence. A gap after a correctly deduplicated API retry is evidence that the idempotency constraint worked, not evidence that the table lost an order.

Inspect generator state without brittle assumptions

PostgreSQL exposes sequence metadata through system catalogs and helper functions. Prefer those interfaces over parsing default expressions or guessing names. A reusable health query can compare the sequence's last recorded value with the table maximum, while also detecting empty-table cases.

WITH sequence_ref AS (
  SELECT pg_get_serial_sequence('qa_orders', 'id') AS qualified_name
), sequence_state AS (
  SELECT last_value
  FROM pg_sequences
  WHERE schemaname = split_part((SELECT qualified_name FROM sequence_ref), '.', 1)
    AND sequencename = split_part((SELECT qualified_name FROM sequence_ref), '.', 2)
), table_state AS (
  SELECT max(id) AS max_id, count(*) AS row_count
  FROM qa_orders
)
SELECT
  table_state.row_count,
  table_state.max_id,
  sequence_state.last_value
FROM table_state
CROSS JOIN sequence_state;

That query assumes the returned name has a simple schema and object component. If your naming policy permits quoted identifiers containing dots, resolve the relation through catalogs using its OID rather than splitting text. Most application schemas prohibit such names, which makes this compact form practical for CI.

Interpret state carefully. A sequence can be ahead of max(id) because of rollbacks or cache allocation. That is healthy. A sequence at or behind max(id) is not automatically broken either, because the next value and increment matter, but it deserves a controlled allocation test. Negative increments and cycling sequences require a different ordering rule. For ordinary ascending, non-cycling identity keys, the safest proof is a rolled-back probe in an isolated transaction or a real insert cleaned up through fixture teardown.

Observation	Likely meaning	QA response
last_value is greater than max(id)	rollback, conflict, cache, or deleted high row	accept if inserts stay unique
last_value is lower than an explicitly imported high ID	sequence not synchronized after import	reproduce with controlled insert and repair migration
large jump after restart or failover	unused cached values or topology behavior	verify documented configuration and uniqueness
duplicate-key error on generated ID	generator behind data or wrong default	block release and diagnose ownership/state
IDs decrease	negative increment, reset, another writer, or assumption error	compare actual sequence configuration to contract
gaps correlate with failed requests	attempts allocate before transaction outcome	expected unless business numbering says otherwise

Do not turn the size of a gap into a universal pass/fail threshold. Cache sizes and failure volume are operational choices. A 1,000-value hole could be harmless; a single duplicate is not.

Automate invariants with Node and PostgreSQL

The following test uses the documented pg client and Node's built-in test runner. It creates its own schema objects, observes a rollback gap without depending on exact values, and verifies the next committed allocation remains unique.

import assert from 'node:assert/strict';
import test from 'node:test';
import { Client } from 'pg';

test('rollback may leave a gap but cannot reuse a committed id', async () => {
  const client = new Client({ connectionString: process.env.TEST_DATABASE_URL });
  await client.connect();

  try {
    await client.query('DROP TABLE IF EXISTS qa_sequence_orders');
    await client.query(`
      CREATE TABLE qa_sequence_orders (
        id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        request_key text NOT NULL UNIQUE
      )
    `);

    const first = await client.query<{ id: string }>(
      'INSERT INTO qa_sequence_orders (request_key) VALUES ($1) RETURNING id',
      ['first']
    );

    await client.query('BEGIN');
    const abandoned = await client.query<{ id: string }>(
      'INSERT INTO qa_sequence_orders (request_key) VALUES ($1) RETURNING id',
      ['abandoned']
    );
    await client.query('ROLLBACK');

    const second = await client.query<{ id: string }>(
      'INSERT INTO qa_sequence_orders (request_key) VALUES ($1) RETURNING id',
      ['second']
    );

    assert.notEqual(first.rows[0].id, second.rows[0].id);
    assert.notEqual(abandoned.rows[0].id, second.rows[0].id);

    const rows = await client.query<{ request_key: string }>(
      'SELECT request_key FROM qa_sequence_orders ORDER BY id'
    );
    assert.deepEqual(rows.rows.map((row) => row.request_key), ['first', 'second']);
  } finally {
    await client.query('DROP TABLE IF EXISTS qa_sequence_orders');
    await client.end();
  }
});

This is intentionally not assert.equal(secondId, firstId + 1). The abandoned allocation sits between them. Converting the bigint result to a JavaScript number is also avoided because sufficiently large database integers exceed JavaScript's safe integer range. Comparing returned strings is adequate for inequality; use native BigInt when arithmetic is required.

Reproduce the dangerous reset-and-import failure

The most consequential sequence defect commonly appears after a data import, clone, manual repair, or table restoration. An operator supplies explicit high IDs, but the associated generator retains a low state. Everything looks fine until normal inserts catch up and collide.

Create a minimal reproduction:

DROP TABLE IF EXISTS imported_customers;

CREATE TABLE imported_customers (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

INSERT INTO imported_customers (id, email)
VALUES
  (1, 'one@example.test'),
  (2, 'two@example.test'),
  (3, 'three@example.test');

INSERT INTO imported_customers (email)
VALUES ('generated@example.test')
RETURNING id;

On a fresh table, the generated insert attempts to use 1 and fails with a primary-key conflict. This is a real release blocker. The gap is not the problem. Generator state lagging behind imported data is the problem.

Use setval only in a reviewed repair or migration, and understand its third argument. The following synchronizes an ascending sequence so the next nextval returns one greater than the current table maximum. It also handles an empty table by setting the sequence start value as not yet called.

DO $$
DECLARE
  sequence_name text := pg_get_serial_sequence('imported_customers', 'id');
  highest_id bigint;
BEGIN
  LOCK TABLE imported_customers IN ACCESS EXCLUSIVE MODE;
  SELECT max(id) INTO highest_id FROM imported_customers;

  IF highest_id IS NULL THEN
    PERFORM setval(sequence_name::regclass, 1, false);
  ELSE
    PERFORM setval(sequence_name::regclass, highest_id, true);
  END IF;
END
$$;

INSERT INTO imported_customers (email)
VALUES ('generated@example.test')
RETURNING id;

The lock matters when writers are active. Without coordination, another session can allocate or insert between max(id) and setval, producing an incorrect repair. A production repair should be adapted to the actual start, increment, permissions, and maintenance plan. The official PostgreSQL sequence documentation is at https://www.postgresql.org/docs/current/functions-sequence.html.

Test concurrent allocators for uniqueness, not arrival order

Concurrent sessions can obtain sequence values in an order that differs from commit order. Session A can allocate 101, pause, and commit after session B allocates and commits 102. Sorting by ID then appears to place A before B, even though B became visible first. A primary key is not a commit timestamp.

This runnable Node test launches distinct connections and proves only the property the sequence promises: generated values are unique. It does not pretend that promise includes commit ordering.

import assert from 'node:assert/strict';
import test from 'node:test';
import { Client } from 'pg';

test('parallel inserts receive unique generated ids', async () => {
  const admin = new Client({ connectionString: process.env.TEST_DATABASE_URL });
  await admin.connect();
  await admin.query('DROP TABLE IF EXISTS qa_parallel_ids');
  await admin.query(`
    CREATE TABLE qa_parallel_ids (
      id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      worker integer NOT NULL
    )
  `);

  const insertFromWorker = async (worker: number): Promise<string> => {
    const client = new Client({ connectionString: process.env.TEST_DATABASE_URL });
    await client.connect();
    try {
      const result = await client.query<{ id: string }>(
        'INSERT INTO qa_parallel_ids (worker) VALUES ($1) RETURNING id',
        [worker]
      );
      return result.rows[0].id;
    } finally {
      await client.end();
    }
  };

  try {
    const ids = await Promise.all(
      Array.from({ length: 20 }, (_, index) => insertFromWorker(index))
    );
    assert.equal(new Set(ids).size, ids.length);

    const count = await admin.query<{ total: string }>(
      'SELECT count(*) AS total FROM qa_parallel_ids'
    );
    assert.equal(count.rows[0].total, '20');
  } finally {
    await admin.query('DROP TABLE IF EXISTS qa_parallel_ids');
    await admin.end();
  }
});

Twenty workers is an illustrative CI load, not a capacity claim. Use a larger, controlled performance scenario if generator contention is the risk. Keep the correctness test small enough that failures are diagnosable.

Separate gaps caused by deletion from gaps caused by allocation

A missing value does not reveal its cause. ID 77 might have been allocated in a transaction that rolled back, assigned to a row later deleted, skipped after a cache loss, or never issued because the sequence increment is greater than one. You cannot reconstruct history from the remaining primary keys alone.

If the business must distinguish deletion from failed allocation, record domain events or an audit ledger. Do not query adjacent IDs and infer that a delete occurred. For example, a controlled invoice allocation table can preserve every disposition:

CREATE TABLE invoice_number_ledger (
  legal_entity text NOT NULL,
  fiscal_year integer NOT NULL,
  number integer NOT NULL,
  status text NOT NULL CHECK (status IN ('reserved', 'issued', 'void')),
  reason text,
  order_id bigint,
  allocated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  PRIMARY KEY (legal_entity, fiscal_year, number),
  CHECK (status <> 'void' OR reason IS NOT NULL),
  CHECK (status <> 'issued' OR order_id IS NOT NULL)
);

This schema does not by itself allocate numbers safely. The application still needs a transaction, locking strategy, permissions, and explicit state transitions. It does make the real requirement testable: every number in the controlled series has a status, and void numbers carry a reason. That is far stronger than demanding no gaps from a general-purpose sequence.

Failure symptom	Diagnostic query or experiment	Probable root cause
generated insert hits primary-key duplicate	compare generator state, maximum ID, increment, and identity mode	explicit import or incorrect reset
apparent missing IDs after failed API requests	correlate request outcomes with attempted inserts	expected non-transactional allocation
IDs jump after database restart	inspect configured cache and topology event	unused cached allocation
child table uses another sequence	inspect column identity/default and dependency catalogs	clone or migration lost ownership
order by ID disagrees with event time	compare commit/event timestamps	concurrency, not sequence corruption
wraparound or exhausted sequence	inspect data type maximum, increment, cycle setting	capacity planning failure

Diagnose a realistic CI failure without "fixing" valid gaps

Imagine an end-to-end test creates an order, forces payment authorization to fail, retries with a corrected card, and expects the final order ID to equal the previous fixture's ID plus one. The test begins failing only after the service starts writing the order before authorization, inside a transaction that rolls back on decline.

The tempting diagnosis is "the new flow leaks sequence numbers." Technically it does consume one, but that is documented sequence behavior and not a row leak. Querying the table shows no declined order. The unique request key prevents duplicate accepted orders. A subsequent insert succeeds. The broken component is the test oracle.

Rewrite the assertions around observable requirements:

1. The declined request returns the specified error and commits no order row.
2. The accepted retry commits exactly one row for the idempotency key.
3. The committed row has a unique non-null ID.
4. Audit or payment-attempt records match the product contract.
5. No claim is made about adjacency to unrelated rows.

Now consider a second failure: after restoring a production-shaped snapshot into staging, the first generated insert raises a duplicate-key error. Here, replacing adjacency assertions would hide a real problem. The explicit snapshot data contains ID 85000 while the restored sequence is at 120. Diagnosis should compare catalog state, confirm the sequence associated with the column, and correct the restoration procedure. Similar symptoms around "weird IDs" demand different responses because one violates uniqueness and the other violates only an invented continuity rule.

What people get wrong about sequence safety

The deepest mistake is treating max(id) + 1 as a safer, gap-free replacement. Under concurrency, two transactions can read the same maximum and propose the same next value. Adding a broad table lock serializes writers, damages throughput, and still does not solve rollback continuity in a useful business sense. If the allocated number cannot disappear, you need a durable allocation record, not arithmetic over current rows.

Other recurring mistakes include:

• Using row count as the expected next ID. Deletes, rollbacks, and non-one start values invalidate it.
• Resetting a shared sequence in parallel tests. Another worker can allocate between reset and assertion.
• Assuming a high ID proves many committed records exist. Failed attempts can advance the generator.
• Exposing sequential IDs and relying on obscurity for authorization. Every resource request still needs an access check.
• Ordering business events only by generated ID. Concurrent transactions make allocation order differ from commit order.
• Calling every gap "data loss" before checking whether a row ever committed.
• Using explicit IDs in seed data without synchronizing the generator or isolating the fixture.

There is also a subtler testing mistake: performing a health-check allocation with nextval on a production sequence and then alerting because the check itself created a gap. Prefer metadata inspection where possible. If an allocation probe is necessary, accept that it consumes a value and document the observation. Never attempt to put the value back while other writers are active.

Design a migration gate for identity and sequence changes

Schema migrations can rename tables, replace identity columns, alter types, clone tables, or move ownership. Add a focused gate whenever a migration touches generation behavior.

SELECT
  column_name,
  data_type,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment,
  identity_cycle
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'qa_orders'
  AND column_name = 'id';

SELECT pg_get_serial_sequence('public.qa_orders', 'id') AS sequence_name;

Assert the fields your contract actually fixes. Avoid snapshotting every catalog column because database upgrades can add metadata without changing semantics. A robust migration check usually performs four actions: inspect declared identity mode, discover the associated generator, insert a row without specifying the ID, and confirm that an explicit ID is accepted or rejected according to policy.

Run destructive reset scenarios only against an isolated database. Ready-made QA skills can be installed from qaskills.sh with the qaskills CLI if your agent needs a repeatable database-test scaffold, but review generated migrations and privileges before using them in a shared environment.

Turn the contract into a release checklist

Use this compact review sequence for every table with generated numeric identifiers:

• Classify the identifier as storage, public reference, ordering token, or regulated business number.
• Confirm primary-key or unique enforcement exists in the database, not only application code.
• Test one committed insert, one rolled-back insert, and another committed insert without adjacency assertions.
• Test conflict or retry behavior on the application's idempotency key.
• After imports, verify generated inserts cannot collide with explicit data.
• Exercise concurrent inserts using separate database sessions.
• Inspect identity mode, increment, cycle setting, ownership, and data type capacity.
• Verify API consumers treat identifiers as opaque values.
• Keep parallel test workers away from shared resets.
• Use a ledger when every abandoned business number needs a recorded disposition.

The result is a test suite that tolerates harmless holes while being uncompromising about corruption. That is the practical purpose of database testing sequence gap handling.

Frequently Asked Questions

Should a failed transaction return its allocated ID to the sequence?

No. In PostgreSQL, sequence allocation is not rolled back with the surrounding transaction. Returning a value would be unsafe because another session may already have obtained a later value, and coordinating reuse would undermine the concurrency benefit. Tests should verify that the failed transaction committed no row and that later generated IDs remain unique. If a domain requires every number to be accounted for, allocate through a durable business ledger that records issued and void states rather than trying to recycle primary-key values.

How can I tell whether a sequence gap means data was deleted?

You cannot determine that from the remaining IDs alone. A hole can result from rollback, conflict handling, cached values abandoned during restart, deletion, an increment greater than one, or a manual allocation. Use audit events, change-data capture, soft-delete records, or a dedicated allocation ledger if the cause matters. A query that searches for missing integers only proves that values are absent from the current table. It does not prove those rows previously existed, committed, or were deleted.

When should CI fail on sequence state?

CI should fail when a generated insert collides, the configured identity mode differs from the schema contract, the generator is detached or inaccessible, concurrent inserts produce duplicates, or an import leaves normal allocation unable to progress. CI should not fail merely because max(id) - count(*) is positive. Test behavior after migrations and restores using an isolated database, because catalog inspection alone may miss a generator that looks plausible but issues a conflicting next value.

Are UUIDs a complete solution to database testing sequence gap handling?

UUIDs remove the expectation of numeric continuity and avoid a centralized numeric generator for many designs, but they do not eliminate identifier testing. You still need uniqueness enforcement, correct defaults, serialization checks, API validation, migration coverage, and authorization that treats IDs as untrusted input. They also do not solve regulated sequential numbering or business ordering. Choose UUIDs for distribution or opacity requirements, not simply to silence a brittle test that should have asserted invariants instead of adjacency.