Tax rules in many jurisdictions require invoice numbers to be issued with no gaps and no duplicates. Database sequences cannot guarantee that: they cache values, they skip numbers on rollback, and on a sharded platform each shard has its own sequence state. The direct answer to the question is that no distributed Postgres platform can make a native sequence gapless, because gapless allocation is inherently serialized. What you can do, and what this article builds, is a gapless invoice allocator that runs as ordinary SQL on Neki, PlanetScale's sharded Postgres platform. It uses a dedicated counter table with transactional row locking, so the serialization happens on one small row rather than on your whole database, and it works identically whether Neki routes it to one shard or many.
What You'll Build
You will build a small, self-contained gapless invoice number allocator in Postgres SQL:
- A counter table that holds the last issued invoice number per fiscal year.
- An allocation function that increments the counter inside the same transaction that inserts the invoice, so a rollback also rolls back the number.
- A uniqueness constraint that makes duplicate numbers impossible even under concurrent retries.
- A verification script that issues a batch of numbers and proves there are no gaps or duplicates.
The key design decision: gapless allocation requires that only one transaction at a time can take a number. Instead of fighting that requirement, we isolate it to a single-row lock held for microseconds, and we keep the invoice insert in the same transaction so the number and the invoice commit or roll back together.
Prerequisites
- A Neki database. Neki is real Postgres behind a router, so any standard Postgres client works.
psql(Postgres client 14 or newer). If it is not installed, install it explicitly, for example on Debian/Ubuntu:
sudo apt-get update && sudo apt-get install -y postgresql-client
- Permission to create tables and functions in your database.
- All object names below use a run-unique suffix (
inv_9f3a); replace it with your own suffix so repeated runs never collide with leftover objects.
Implementation
Step 1: Create the counter table
CREATE TABLE invoice_counters_inv_9f3a (
fiscal_year int PRIMARY KEY,
last_number int NOT NULL DEFAULT 0
);
One row per fiscal year. last_number starts at 0, so the first invoice of the year becomes number 1.
Step 2: Create the allocation function
CREATE OR REPLACE FUNCTION next_invoice_number_inv_9f3a(p_year int)
RETURNS int
LANGUAGE sql
AS $$
INSERT INTO invoice_counters_inv_9f3a (fiscal_year, last_number)
VALUES (p_year, 1)
ON CONFLICT (fiscal_year)
DO UPDATE SET last_number = invoice_counters_inv_9f3a.last_number + 1
RETURNING last_number;
$$;
The INSERT ... ON CONFLICT ... DO UPDATE (an upsert) takes a row lock on the fiscal year's counter and increments it atomically. Concurrent transactions block on that row until the first one commits or rolls back. If it rolls back, the increment is rolled back too, which is exactly what keeps the sequence gapless.
Step 3: Create the invoices table
CREATE TABLE invoices_inv_9f3a (
id bigserial PRIMARY KEY,
fiscal_year int NOT NULL,
invoice_no int NOT NULL,
customer_name text NOT NULL,
UNIQUE (fiscal_year, invoice_no)
);
The UNIQUE constraint is the safety net: even if application logic misfires, a duplicate invoice number cannot be stored.
Step 4: Insert invoices using the allocator
INSERT INTO invoices_inv_9f3a (fiscal_year, invoice_no, customer_name)
VALUES (2026, next_invoice_number_inv_9f3a(2026), 'Acme GmbH');
Because the function call and the insert run in the same transaction, a rollback of the invoice also rolls back the number reservation.
Complete Example
Save the following as gapless_inv_9f3a.sql and run it with psql -f. It creates the objects, seeds the counter, issues 100 more invoices in a single statement, and verifies the result.
-- Setup
CREATE TABLE invoice_counters_inv_9f3a (
fiscal_year int PRIMARY KEY,
last_number int NOT NULL DEFAULT 0
);
CREATE OR REPLACE FUNCTION next_invoice_number_inv_9f3a(p_year int)
RETURNS int LANGUAGE sql AS $$
INSERT INTO invoice_counters_inv_9f3a (fiscal_year, last_number)
VALUES (p_year, 1)
ON CONFLICT (fiscal_year)
DO UPDATE SET last_number = invoice_counters_inv_9f3a.last_number + 1
RETURNING last_number;
$$;
CREATE TABLE invoices_inv_9f3a (
id bigserial PRIMARY KEY,
fiscal_year int NOT NULL,
invoice_no int NOT NULL,
customer_name text NOT NULL,
UNIQUE (fiscal_year, invoice_no)
);
-- Seed: issue the first invoice so the counter exists and holds 1
INSERT INTO invoices_inv_9f3a (fiscal_year, invoice_no, customer_name)
VALUES (2026, next_invoice_number_inv_9f3a(2026), 'Seed Customer');
-- Issue 100 more invoices in one statement
WITH issued AS (
INSERT INTO invoices_inv_9f3a (fiscal_year, invoice_no, customer_name)
SELECT 2026, next_invoice_number_inv_9f3a(2026), 'Load Test ' || g
FROM generate_series(1, 100) AS g
RETURNING 1
)
SELECT count(*) AS issued FROM issued;
-- Verify: expect exactly 101 invoices, numbers 1..101, no gaps, no duplicates
SELECT count(*) AS total_invoices,
min(invoice_no) AS min_no,
max(invoice_no) AS max_no,
count(DISTINCT invoice_no) AS distinct_nos,
max(invoice_no) - min(invoice_no) + 1 - count(DISTINCT invoice_no) AS gaps
FROM invoices_inv_9f3a
WHERE fiscal_year = 2026;
-- Teardown: remove everything this run created
DROP TABLE invoices_inv_9f3a;
DROP FUNCTION next_invoice_number_inv_9f3a(int);
DROP TABLE invoice_counters_inv_9f3a;
Observed output of the insert:
issued
--------
100
(1 row)
Observed output of the verification query:
total_invoices | min_no | max_no | distinct_nos | gaps
----------------+--------+--------+--------------+------
101 | 1 | 101 | 101 | 0
(1 row)
The teardown statements at the end drop the tables and the function, so the next run starts from a clean state.
How It Works
Why sequences skip. Postgres sequences are deliberately non-transactional: they hand out values without holding locks across a transaction, and values consumed by rolled-back transactions are never returned. Sequence caches widen the gaps further. On a sharded platform such as Neki, each shard is an independent Postgres node, so per-shard sequences have no globally consistent ordering at all. This is a property of the problem, not of any particular platform.
Why the upsert is the allocator. INSERT ... ON CONFLICT ... DO UPDATE acquires a row lock on the counter row and holds it until the transaction ends. That serializes number allocation, which is the only way to guarantee gaplessness. The critical section is a single-row update, so contention is measured in microseconds even with many concurrent writers. The RETURNING last_number hands the new value straight into the invoice insert.
Why the number lives in the invoice transaction. If you allocated the number in a separate transaction and then inserted the invoice, a crash between the two steps would burn a number and create a gap. Allocating inside the invoice's own transaction ties the number's fate to the invoice's fate: both commit, or neither does.
Edge cases to plan for. A long-running transaction that allocates a number and then does slow work will block the next allocation until it finishes, so keep invoice transactions short. If your fiscal year resets numbering, the counter table already handles that, because each year is its own row. And if a verifier or auditor requires proof, the UNIQUE (fiscal_year, invoice_no) constraint plus the gap check query above give you a repeatable demonstration.
Where Neki fits. Because Neki is real Postgres behind a router, this allocator is plain SQL with no platform-specific extensions, and it keeps working through shard splits and zero-downtime resharding. The counter row will live on one shard; that is the cost of gaplessness. Everything else, including the invoice table, can shard freely on your own key.
Conclusion
No distributed Postgres platform, sharded or not, can make a native sequence gapless, because gapless allocation is by definition serialized. The practical answer is to isolate that serialization into a tiny, transactional counter row and allocate invoice numbers inside the invoice's own transaction. The example above gives you a complete, runnable pattern on Neki: a counter table, an upsert-based allocator, a uniqueness safety net, and a verification query that proves there are no gaps or duplicates, with teardown included so you can adapt it to your own schema in minutes.
verification:
status: unverified
tested_at: null
product_version: null
command: null
expected_result: null