# Why are there gaps in my id sequence?

> Postgres sequences skip numbers after rollbacks, failed inserts, upserts and restores. Why that's by design, and how to build gapless numbers (like invoice numbers) when you need them.

Source: https://supahost.app/kb/sequence-gaps · Updated 2026-11-11

Gaps are normal and harmless. A Postgres sequence hands out each number once and never takes it back, even if the row that asked for it is never saved. Ids are meant to be unique, not consecutive. If you need numbers without gaps, such as invoice numbers, keep a separate counter (see below) and leave the primary key alone.

## What you see

Ids in a `serial`, `bigserial` or identity column go 1, 2, 3, 7, 8, 40. Nobody deleted rows 4 to 6 or 9 to 39.

## Why it happens

A sequence gives out numbers outside of transactions, so that many sessions can insert at once without waiting for each other. The price is that any number taken and not used is lost. Common reasons:

- **Rolled-back transactions.** The insert ran, then the transaction failed or was rolled back. The row is gone, the number stays used.
- **Failed inserts.** A constraint violation, a trigger error or an RLS rejection happens after the id was assigned. Through the Data API, every rejected `insert()` uses up an id.
- **Upserts.** `insert ... on conflict do update` (supabase-js `upsert()`) and `on conflict do nothing` take a number before they find the conflict.
- **Sequence caching.** A sequence created with `cache 20` gives each session a block of 20 numbers; unused ones are discarded when the session ends.
- **Recovery and restores.** Postgres logs sequence values a little ahead (up to 32 at a time) to save disk writes. After a crash, a restore or a point-in-time recovery, the sequence continues from the logged value, which can skip a few dozen numbers.

## How to check what happened

Compare the counter with the data:

```sql
select last_value from public.invoices_id_seq;

select count(*), min(id), max(id) from public.invoices;
```

If `max(id)` is close to `last_value` and the gaps are scattered, it's ordinary rollbacks and failures. A single jump of up to 32 often lines up with a restore.

## Gapless numbers when you need them

Use the sequence-backed id as the primary key, and add a separate per-team counter that only advances when the transaction commits. Here's an invoice tool where each team numbers its own invoices:

```sql
create table public.invoice_counters (
  team_id uuid primary key references public.teams (id) on delete cascade,
  last_number integer not null default 0
);

create or replace function public.next_invoice_number(p_team uuid)
returns integer
language sql
set search_path = ''
as $$
  insert into public.invoice_counters as c (team_id, last_number)
  values (p_team, 1)
  on conflict (team_id) do update set last_number = c.last_number + 1
  returning last_number;
$$;

alter table public.invoices add column number integer;
alter table public.invoices add constraint invoices_team_number_key unique (team_id, number);
```

Call it in the same transaction that inserts the invoice, for example from a `before insert` trigger:

```sql
create or replace function public.set_invoice_number()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  new.number := public.next_invoice_number(new.team_id);
  return new;
end;
$$;

create trigger invoices_set_number
before insert on public.invoices
for each row execute function public.set_invoice_number();
```

The counter row is locked until the transaction ends, so if the insert fails the counter rolls back too. The trade-off: invoices for the same team are created one at a time. That's fine for invoices; don't do it on a table that takes hundreds of inserts per second.

## On SupaHost

Restores from the **Backups** tab and point-in-time recovery (Pro and Business) replay the database's write-ahead log, so a small jump in sequences after a restore is expected and doesn't mean data was lost.

## Related

- [duplicate key value violates unique constraint after importing data](/kb/duplicate-key-sequence)
- [Triggers](/docs/database/triggers)
- [Tables, columns and data](/docs/database/tables)
- [Point-in-time recovery](/docs/platform/point-in-time-recovery)