The Multi-Tenant Invoice Numbering Dilemma in PostgreSQL: Why Standard Tools Fail and How to Get It Right

In the architecture of a multi-tenant Software-as-a-Service (SaaS) product, certain functional requirements appear deceptively simple at first glance. Among the most deceptive is invoice generation. In a system where multiple businesses—or tenants—share the same database infrastructure, every single tenant operates as a distinct commercial entity requiring its own independent, isolated invoice series. Tenant A issues invoices numbered 1, 2, and 3, while Tenant B concurrently issues its own 1, 2, and 3, completely oblivious to the volume or pacing of transactions occurring across other accounts.

To the uninitiated backend engineer, this sounds like a trivial problem. Naturally, the instinct is to reach for the default features offered out of the box by enterprise relational databases like PostgreSQL: built-in sequence objects or the classic MAX(number) + 1 query pattern.

However, rigorous performance benchmarking and real-world compliance standards reveal a stark reality: these out-of-the-box tools are precisely the wrong solutions for multi-tenant invoice numbering. Choosing the wrong mechanism invites legal non-compliance, catastrophic data corruption through duplicate invoicing, or severe system bottlenecks under load.

Main Facts: The Core Engineering Challenge

When designing a robust database architecture for SaaS invoice generation, engineers must reconcile two competing forces: strict regulatory frameworks governing financial records and the hard physical realities of concurrent database transactions.

What the Law Demands

Tax authorities across jurisdictions enforce rigorous standards on commercial documentation. For instance, the European Union VAT Directive (Directive 2006/112/EC, Article 226, point 2) explicitly mandates “a sequential number, based on one or more series, which uniquely identifies the invoice” as a non-negotiable requirement.

Similarly, the Dutch tax authority (Belastingdienst) dictates: “Gebruik opeenvolgende nummers voor uw facturen, met 1 of meer reeksen. Elk factuurnummer mag maar 1 keer voorkomen”—meaning companies must use consecutive numbers in one or more series, and each number must appear only once.

From a legal and accounting standpoint, two criteria are paramount:

1. Uniqueness: No two invoices within the same series can ever share the same number.
2. Sequential Order: Numbers must follow an ascending sequence.

Crucially, neither legal text uses the word "gapless." Whether an occasional numeric gap occurs due to a cancelled transaction or a technical rollback is an accounting question, not a database design flaw. What the database must guarantee is absolute uniqueness and strict ordering within a series. Furthermore, the provision for “one or more series” establishes that issuing numbers per tenant—or per tenant per year—is entirely compliant.

The PostgreSQL Failure Modes

When put to the test under realistic concurrent loads, standard PostgreSQL patterns display critical flaws:

• PostgreSQL Sequences: Designed explicitly not to be gapless. As documented by the PostgreSQL project, values obtained via nextval are never reclaimed if the calling transaction subsequently aborts. If 10% of transactions roll back, sequences leave massive numerical gaps. Furthermore, a single global sequence forces all tenants into one shared pool, destroying tenant isolation.
• MAX(number) + 1: Under default transaction isolation levels, concurrent transactions reading the same maximum number simultaneously will calculate identical next values, resulting in massive duplication errors.
• SERIALIZABLE Isolation: While it prevents duplicates and gaps theoretically, high contention leads to severe transaction abort rates and performance degradation, shifting the burden of endless retries entirely to the application layer.

Chronology: Evaluating the Five Approaches

To understand how these patterns behave under stress, a comprehensive set of benchmarks was executed on PostgreSQL 17.10. Each test simulated aggressive production pressure: eight concurrent sessions hammering the database for fifteen seconds using pgbench.

Phase 1: The Baseline Failure of Global Sequences

Initial testing evaluated a standard identity column (backed by a sequence) configured with a 10% simulated transaction rollback rate. While throughput reached an impressive 12,131 invoices per second, the sequence skipped 17,973 numbers out of 181,937 total issues—almost precisely matching the 10% rollback rate. More fundamentally, this approach failed the multi-tenant requirement by forcing all tenants onto a single global numeric sequence.

Phase 2: The Disaster of MAX() + 1

Next, developers evaluated the common anti-pattern of querying SELECT max(number) + 1 FROM invoices. Under default read-committed isolation, eight concurrent sessions targeting a single tenant generated 161,479 rows, but managed to produce only 20,502 distinct numbers. A staggering 140,977 numbers were issued twice.

Attempting to fix this with SERIALIZABLE isolation and up to 20 automatic retries eliminated duplicates and gaps, but cratered throughput down to 1,475 invoices per second, while 26.6% of transactions permanently failed after exhausting their retry limits.

Phase 3: Discovering the Counter Row Pattern

To resolve both concurrency conflicts and multi-tenant isolation, engineers tested a dedicated counter row per tenant, updated using UPDATE ... RETURNING at the absolute end of the transaction.

When confined to a single tenant under load, throughput sat at 2,143 invoices per second with zero duplicates and zero skipped numbers—even when 10% of transactions rolled back. When that exact same load was distributed horizontally across 1,000 distinct tenants, throughput surged to 10,787 invoices per second, maintaining absolute uniqueness and zero skipped numbers across all 1,000 independent series.

Supporting Data: Benchmark Results

The empirical data gathered from PostgreSQL 17.10 definitively illustrates the performance and reliability trade-offs of each architectural pattern:

The Impact of Transaction Latency

A crucial discovery during the benchmarking process involved the placement of heavy computational work (such as generating PDF invoices, calling external microservices, or running validation queries) within the transaction block.

When a transaction locks the tenant’s counter row before performing 10 milliseconds of external work, every subsequent session for that tenant stalls. Throughput dropped to a paltry 94 invoices per second under eight concurrent sessions. However, by deferring the counter update to the very final statement of the transaction—after all heavy work is completed—the lock duration is minimized. Throughput jumped to 746 invoices per second for a single tenant, preserving system capacity while maintaining strict serial integrity.

Official Guidelines and Compliance Frameworks

Navigating financial software engineering requires strict adherence to statutory definitions. Regulatory bodies emphasize authenticity, integrity, and chronological continuity rather than low-level database engine trivia.

• European Union (Directive 2006/112/EC, Article 226): Establishes that invoice series must be uniquely identifiable. The allowance for "one or more series" validates modern SaaS architectures that shard sequencing per tenant or per accounting period.
• National Tax Agencies (e.g., Dutch Belastingdienst): Explicitly require consecutive numbering within series without permitting duplicate issuance. Accountants and auditors evaluate compliance based on the final ledger state—meaning that internal technical sequence gaps caused by aborted application workflows are acceptable, whereas duplicate numbers or overlapping sequences constitute a severe audit violation.
• PostgreSQL Documentation: Explicitly warns developers against treating native sequence objects as gapless auditing tools, noting that rolled-back transactions permanently consume sequence values.

Implications and the Recommended Pattern

The architectural consensus drawn from these insights points directly to a specialized pattern: the Tenant Counter Row Pattern.

The Implementation Pattern

To implement this safely in PostgreSQL, developers should maintain an explicit counter table tied directly to tenant management:

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

When finalizing an invoice, the next number is securely fetched and written in a single atomic statement executed late in the transaction lifecycle:

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 experiences a rollback, the counter update rolls back synchronously with it, preserving numerical integrity without creating permanent orphaned gaps. Furthermore, integrating this table with PostgreSQL’s Row-Level Security (RLS) ensures that counter data benefits from the exact same multi-tenant isolation boundaries as core application data.

Auditing and Verification

To ensure ongoing database hygiene, administrators can run routine validation queries to detect anomalies. The following queries successfully identify internal gaps or duplicate number assignments:

-- Query 1: Detecting gaps inside each tenant's invoice 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;

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

Conclusion

Building a multi-tenant SaaS invoicing engine requires looking past default database conveniences. While native sequences and naive MAX() + 1 queries appear attractive for their simplicity, they inevitably lead to either systemic data corruption or regulatory non-compliance. By adopting a dedicated counter row pattern paired with disciplined transaction management, engineering teams can achieve legally compliant, high-performance, and entirely secure invoice numbering at scale.