How should we allocate human-facing numbers without expecting a gapless database sequence?

Use separate identifiers. Keep an internal primary key for joins and references, then assign the human-facing number only when the business event becomes final: invoice issuance, order confirmation, or ticket publication. Do not treat a database sequence as proof of gaplessness. PostgreSQL documents that nextval is not rolled back and cannot produce gapless sequences, and SQL Server documents the same transaction-independent behavior for sequences with additional cache-loss gaps on shutdown (PostgreSQL CREATE SEQUENCE, PostgreSQL sequence functions, SQL Server sequence numbers).

What requirement do you actually have?

Start by classifying the number:

• Internal row ID: only uniqueness matters.
• Customer reference: readable formatting matters; gaps usually do not.
• Invoice number subject to an audit policy: determine the applicable scope, gap handling, and void-record requirements before selecting an allocator.

That distinction drives the design. If the requirement is only “roughly increasing and easy to read,” a normal sequence plus formatting is fine. If the requirement is “every number must be explainable,” allocate later and keep an audit trail. If the requirement is “no gaps under any failure,” accept serialized allocation and lower throughput.

Why do normal sequences and auto-increment columns create gaps?

They are built for concurrent uniqueness, not accounting semantics. PostgreSQL explicitly states that nextval and setval are never rolled back, and aborted transactions, crashes, or INSERT ... ON CONFLICT can leave holes (PostgreSQL sequence functions). SQL Server says sequence numbers are generated outside the current transaction and can have gaps from rollbacks, shared use, or cached values lost on unexpected shutdown (SQL Server sequence numbers).

So “check the current max, then insert the next one” is not a fix unless you define one serialization point that every writer must pass through.

Which allocation design fits invoices, orders, and tickets?

For invoices, assign the fiscal number in the finalization transaction, not when creating a draft. A practical pattern is a document_counters row per scope such as tenant plus year. Finalization does this in one transaction: lock that counter row, increment it, insert the formatted invoice number into the invoice, and commit. Use a unique constraint on (tenant_id, fiscal_year, invoice_number) to match that counter scope. The same formatted number can exist in different tenant scopes, so every lookup and external reference must retain its tenant context. That gives a real ordering boundary: the row lock on the counter.

For orders or support tickets, prefer looser rules. Use a UUID or bigint primary key internally, then a customer reference allocated on confirmation with a normal sequence or counter. Gaps are acceptable if the reference is not a legal ledger number.

If auditors require every missing number to be explained, reserve numbers and record failed allocations as void entries instead of reusing them. Reuse sounds tidy, but it weakens traceability after retries, timeouts, and operator intervention.

What does a concrete invoice flow look like?

Example:

1. Create invoice draft with internal id only.
2. On “issue invoice,” send an idempotency key.
3. In one database transaction, lock the tenant_id, fiscal_year counter row.
4. Increment counter from 1841 to 1842.
5. Write invoice_number = INV-2026-001842 to that invoice together with its tenant_id and fiscal_year; enforce uniqueness on that complete scope.
6. Commit, then deliver externally.

If the process crashes before commit, the invoice remains unnumbered and can be retried safely. If it crashes after commit but before the client receives success, the idempotency key lets the caller fetch the already-issued invoice instead of minting 1843.

How should you test and operate it?

Test two tenants issuing the same sequence value, duplicate values within one tenant and year, concurrent finalization, retry after timeout, duplicate submissions, year rollover, backup restore rehearsal, and reconciliation reports for voided numbers. Monitor for duplicate-key violations and gaps without matching void records. Keep formatting logic separate from allocation so prefix changes do not rewrite history.

Q: Can we make a normal PostgreSQL sequence gapless by setting CACHE 1?
No. That may reduce cache-related skips, but PostgreSQL still documents gaps from aborts and conflicts (PostgreSQL CREATE SEQUENCE).

Q: Should we ever renumber missing invoices later?
Usually no. For auditable numbering, create a void record or leave the draft unnumbered until finalization. Retroactive reuse makes incident reconstruction harder.

Next step: write down which numbers in your system are merely readable references and which are audit artifacts, then implement exactly one serialized allocation path for the latter.

Reviewed: 2026-09-05