What a sequence page tells you

The symptom

An insert into billing.events fails with duplicate key value violates unique constraint "events_pkey", the day after a restore. Or nothing has failed yet, and you notice that events.id is integer and the latest id is past 1.5 billion.

What a sequence is

A sequence is a counter that lives outside your transaction. nextval() hands over the next number at once, before the INSERT that asked for it commits, and that number is spent whether the insert commits, fails or rolls back. That is why id columns have gaps, and why gaps are fine.

The header reads Serial · feeds events.id integer · 1.5B handed out. The sections below are Pro. On Free the header keeps Serial, the table and the engine, and the page stops after Definition with the Pro sections named over a blurred silhouette (Object pages shows the gate). The verdict at the top: About 5 months left. events_id_seq has used 71.4% of the values up to the integer limit of events.id. Widening to bigint rewrites the table, so plan it ahead., with Open the widening SQL beside it.

How much is left

The sequence is bigint and could count to 9.2 quintillion. The column it feeds is integer and stops at 2,147,483,647. The gauge is drawn to the column's limit, with a dashed extension for the sequence's own, because the column is what runs out. The marker sits at 1,533,422,880, 71.4%.

Values per day is measured from occurred_at on events over the last 30 days. At 4M values a day the remaining 614 million last about five months, and until then nothing in any log says so.

Latest values

The left column is the highest eight values of events.id, with the sequence's next value placed among them. The right column says which of two states you are in.

In step with the table: the next value, 1,533,422,881, is past the highest id in events, so the next insert is safe.

Behind the table: the table already holds the value the sequence would hand out next, so inserts fail with a duplicate key until it moves past the table. This happens after a restore or a bulk import that inserted explicit ids. Open setval in a console opens the fix:

-- Moves events_id_seq so its next value follows the highest id in events.
SELECT setval('billing.events_id_seq', (SELECT max(id) FROM billing.events), true);

This sequence has CACHE 1. With CACHE 20 each connection reserves 20 values at a time, which is cheaper on a busy sequence, and loses the unused ones when it closes, so gaps of up to 20 are normal.

Widening events.id to bigint

This section appears only at warn or critical. Its lead sentence is the cost: a bigint column never runs out, and changing the type rewrites every table below, each locked for reads and writes until its rewrite finishes, about 322 GB in total.

The table lists every column that changes: events.id itself, integer → bigint, 310 GB, then every foreign key column that points at it and is still integer. Open in console opens the ALTER TABLE … ALTER COLUMN … TYPE bigint statements in one transaction.

That ALTER holds an ACCESS EXCLUSIVE lock for the whole rewrite, and on 310 GB that is hours. The five months of runway are for the alternative the page names under the button: add a bigint column, backfill it in batches, then swap it in.

Who takes values from it

billing.events.id, serial column · integer · unique · partitioned table, then billing.invoice_events and billing.event_attachments, whose columns take values from the same sequence. A column's default calls nextval(), and inserts may also set id themselves, which is how a table gets ahead of its sequence. An identity column declared GENERATED ALWAYS closes that door.

Who can use it

A table of roles against Take next, Read and Reset, which are nextval(), currval() and setval(). Here app can do all three, app_rw can take and read, and reporting can only read. A PUBLIC row is highlighted, because then every role can take values. All properties and DDL fold out below.

MySQL, MariaDB and SQLite

• MySQL and MariaDB: an AUTO_INCREMENT column is the counter, and All properties shows Next value, auto_increment_increment and innodb_autoinc_lock_mode.
• SQLite: an INTEGER PRIMARY KEY is the rowid, and the property is Largest id in the table, or Largest id handed out (sqlite_sequence) with AUTOINCREMENT. Ids are 64-bit, so there is no runway to watch.

Caveats

• The runway is a straight line through the last 30 days. A launch or a backfill changes it. Without a timestamp column the rate comes from insert counts since the statistics reset.
• A sequence with CYCLE on a unique column gets its own warning: at the limit it starts again from 1, and those values are taken.
• The sizes here come from the 12-million-row copy of the sample.