Invoice numbers per tenant in PostgreSQL, without gaps

In a multi-tenant SaaS product that sends invoices, every tenant is a business with its own invoice series. Tenant A’s invoices go 1, 2, 3, and so do tenant B’s, regardless of how many invoices the other tenants issued in between. That sounds like a small requirement, and it is the one place where the tools PostgreSQL offers first — a sequence, or max() + 1 — are exactly the wrong ones.

We measured five approaches under the same load: eight concurrent sessions for fifteen seconds with pgbench, on PostgreSQL 17.10, counting afterwards how many numbers were issued twice and how many were skipped.

How do you number invoices per tenant in PostgreSQL?

With a counter row per tenant, incremented with UPDATE … RETURNING in the same transaction that writes the invoice, and as late as possible in that transaction. In our measurement that gave zero duplicate and zero missing numbers across 1,000 tenants, also when one in ten transactions rolled back. A sequence skipped 17,973 numbers under the same rollbacks, and max(number) + 1 issued 140,977 numbers twice.

What does the law ask for?

The EU VAT Directive lists “a sequential number, based on one or more series, which uniquely identifies the invoice” among the required details of an invoice (Directive 2006/112/EC, Article 226, point 2). The Dutch tax authority puts it as: “Gebruik opeenvolgende nummers voor uw facturen, met 1 of meer reeksen. Elk factuurnummer mag maar 1 keer voorkomen” — use consecutive numbers, in one or more series, and each number may appear only once.

So two things: unique, and sequential within a series. Neither text uses the word “gapless”, and whether an occasional gap is acceptable is a question for an accountant, not for a database. What the database decides is whether you can promise it. And “one or more series” means a series per year, or per tenant per year, is allowed — the counter below takes a series column without any change.

Five approaches, measured

Eight sessions, fifteen seconds, all creating invoices for the same tenant unless stated otherwise.

Why is a sequence not enough?

Because it is built not to be. The PostgreSQL documentation says it outright: “the value obtained by nextval is not reclaimed for re-use if the calling transaction later aborts”, and therefore “PostgreSQL sequence objects cannot be used to obtain ‘gapless’ sequences”. With one in ten transactions rolled back, 17,973 of 181,937 numbers were never used — almost exactly the ten per cent that rolled back.

And that is the smaller problem. One sequence is one series for all tenants together: tenant A gets 1, 7, 12 and tenant B gets 2, 3, 8. That is not a series per business at all. A sequence per tenant solves that and brings back the problem of objects per tenant — a thousand tenants, a thousand sequences to create, migrate and back up, which is the road that schema per tenant runs into as well.

Why does max() + 1 give duplicate numbers?

Because under the default isolation level two transactions that start at the same moment see the same highest number, both add one, and both write it. At eight sessions on one tenant that happened for 87% of all invoices: 161,479 rows, only 20,502 distinct numbers.

A unique constraint on (tenant_id, number) turns those duplicates into errors, which is better — nothing wrong gets stored — but then it is your user who sees an invoice fail to save. The constraint belongs there anyway; it just does not solve the numbering.

Is SERIALIZABLE the answer?

It is correct: no duplicates, no gaps. But at eight sessions on one tenant, 76% of transactions had to be retried and 26.6% still failed after twenty attempts, at 1,475 invoices per second. PostgreSQL does what the isolation level promises — it aborts one of two conflicting transactions — and the retries become your application’s job.

What does gapless numbering cost?

Every invoice of one tenant waits for the previous one to commit, because they all update the same counter row. That is the whole mechanism, and you can see it in the numbers: one session did 1,778 invoices per second, eight sessions did 2,143. Adding concurrency for one tenant hardly helps. Spread over 1,000 tenants, the same eight sessions did 10,787 per second, because they rarely wait for the same row.

What that cost really depends on is how long you hold the row. We added 10 ms of other work to the transaction — the kind of thing an invoice save does: a PDF, a call to another service, a few more queries.

Taking the number first holds the counter row for the whole transaction, so every other session for that tenant waits the full 10 ms. With one session instead of eight it was 93.5 per second: eight sessions bought nothing. Taking the number last holds the row for the final statement only. Same work, same guarantee, eight times the throughput.

The pattern we use

A table with one row per tenant (and per series, if you number per year), created together with the tenant:

create table invoice_counters (
  tenant_id   uuid primary key references tenants(id),
  last_number int not null default 0
);

Taking the next number and writing the invoice in one statement, at the end of the transaction:

with n as (
  update invoice_counters
     set last_number = last_number + 1
   where tenant_id = $1
  returning tenant_id, last_number
)
insert into invoices (tenant_id, number, …)
select tenant_id, last_number, … from n;
commit;

If the transaction rolls back, the counter rolls back with it — that is why the 10% rollback test left no gaps. And in practice there is a simpler way to keep the lock short: a draft invoice has no number yet. Number it when it is finalised, in a short transaction of its own, and all the slow work happens while there is nothing to wait for.

With row-level security, invoice_counters gets the same policy as every other tenant table; a counter is tenant data too.

How do you check that your numbers have no gaps?

Two queries. The first lists every gap inside each tenant’s series; the second every number that occurs more than once. We tested both by deliberately removing numbers 3 and 4 from one tenant, number 10 from another, and inserting a second number 5 at a third: they returned exactly those three findings.

-- gaps inside each tenant's series
select tenant_id, number + 1 as gap_starts_at, next_number - 1 as gap_ends_at
from (
  select tenant_id, number,
         lead(number) over (partition by tenant_id order by number) as next_number
  from invoices
) s
where next_number > number + 1
order by tenant_id, number;

-- numbers issued more than once
select tenant_id, number, count(*)
from invoices
group by tenant_id, number
having count(*) > 1;

The first query does not see a gap before the first invoice; if a series should start at 1, also check min(number) per tenant. If you number per year, add the series column to the partition by and the group by.

What we did not measure

Crashes, replication, and more than eight sessions. A crash is covered by the same argument as a rollback — the counter update and the invoice commit together or not at all — but we did not pull the plug to prove it. All figures come from one machine with default settings, so read them as proportions. The proportions are clear enough: max() + 1 is wrong under any load, a sequence is fast and has gaps by design, and a counter row is correct and costs exactly as much as you make the rest of the transaction wait.

Sources: Directive 2006/112/EC, Article 226, on EUR-Lex (consolidated version of 14 April 2025); the Belastingdienst page factuureisen; the PostgreSQL 17 documentation on sequence functions. All read on 28 September 2026. The measurements are our own, on PostgreSQL 17.10, on 28 September 2026.