Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 90 ยท Data Types

Sequences in Depth

Master PostgreSQL sequences: CREATE SEQUENCE options, nextval, setval, CACHE, CYCLE, why gaps appear, fixing a sequence after a load and gapless alternatives.

UUIDs, Serials and Identity Columns showed that serial and GENERATED AS IDENTITY columns are built on sequences. This lesson is about the sequence itself: a small, separate database object whose only job is to hand out numbers quickly and without making sessions wait for each other.

Knowing how sequences really work answers a family of practical questions. Why are there holes in my ids after a failed insert? Why does currval raise an error? What should I do after loading rows with explicit ids? Can I get invoice numbers with no gaps? The sequence functions only exist on a PostgreSQL server, so those blocks are read-only. Two live demos follow: a Python model of sessions sharing a sequence (to make CACHE, CYCLE and gaps visible), and a SQL counter table that shows the gapless alternative.

A sequence in four ideas

nextval Hands out the next number.
Not transactional A rollback never gives a number back.
Fast and shared No locks held between sessions.
Gaps are normal Uniqueness yes, continuity no.
1 / 2

Creating a sequence

SQL
-- PostgreSQL (read-only)
CREATE SEQUENCE invoice_no_seq
  AS bigint            -- smallint, integer or bigint (default bigint)
  START WITH 1000      -- the first value handed out
  INCREMENT BY 1       -- may be negative for a descending sequence
  MINVALUE 1000
  MAXVALUE 9999999
  CACHE 1              -- how many values a session pre-allocates
  NO CYCLE;            -- raise an error at the limit (the default)

All options are optional. With no options you get a bigint sequence that starts at 1, increments by 1, runs to 9223372036854775807, caches 1 value and does not cycle.

Option Meaning and default
AS type Data type, which sets the limits: smallint up to 32,767, integer up to 2,147,483,647, bigint by default
START WITH n First value. Defaults to MINVALUE for ascending, MAXVALUE for descending sequences
INCREMENT BY n Step. Negative steps count down. Default 1
MINVALUE / MAXVALUE Bounds. NO MINVALUE and NO MAXVALUE select the defaults
CACHE n Values pre-allocated per session. Default 1 (no caching)
CYCLE / NO CYCLE Wrap around at the limit, or raise an error (default)
OWNED BY table.column Ties the sequence's life to a column, so dropping the column or table drops the sequence

The sequence functions

SQL
-- PostgreSQL (read-only)
SELECT nextval('invoice_no_seq');          -- 1000, then 1001, 1002 ...
SELECT currval('invoice_no_seq');          -- the value THIS session got last
SELECT lastval();                          -- last value from ANY sequence in this session
SELECT setval('invoice_no_seq', 5000);     -- next nextval returns 5001
SELECT setval('invoice_no_seq', 5000, false);   -- next nextval returns 5000

Details that trip people up:

  • nextval takes effect immediately and permanently. It is deliberately outside transaction control, so many transactions can draw numbers at once with no waiting. Rolling back never returns a number.
  • currval is per session. It reports what your session received from nextval. If you have not called nextval on that sequence in this session, you get an error: currval of sequence "invoice_no_seq" is not yet defined in this session. Other sessions' activity never affects it.
  • setval(seq, v) marks v as already used, so the next nextval returns v + 1. With the third argument false, the next nextval returns v itself. Use it to reposition a sequence.
  • ALTER SEQUENCE ... RESTART WITH n changes the position and also resets the stored start for later RESTART commands. TRUNCATE tbl RESTART IDENTITY restarts the sequences owned by the table.

You can inspect all sequences with a catalog view:

SQL
-- PostgreSQL (read-only)
SELECT schemaname, sequencename, data_type, start_value, increment_by,
       max_value, cycle, cache_size, last_value
FROM pg_sequences;

Using a sequence yourself

You can use a sequence anywhere you can use a function, not only as a column default:

SQL
-- PostgreSQL (read-only)
CREATE TABLE invoice (
  invoice_no bigint PRIMARY KEY DEFAULT nextval('invoice_no_seq'),
  customer   text NOT NULL
);

ALTER SEQUENCE invoice_no_seq OWNED BY invoice.invoice_no;

INSERT INTO invoice (customer) VALUES ('Ana') RETURNING invoice_no;

A table with a serial column or an identity column does exactly this behind the scenes. To find the sequence that belongs to such a column (this also works for identity columns), ask:

SQL
-- PostgreSQL (read-only)
SELECT pg_get_serial_sequence('invoice', 'invoice_no');

Permissions are separate from the table's. To insert through a default that calls nextval, a role needs USAGE on the sequence (GRANT USAGE ON SEQUENCE invoice_no_seq TO app_user;), or use GRANT ALL ON ALL SEQUENCES IN SCHEMA public. Reading last_value or calling currval needs SELECT, and setval needs UPDATE. Identity columns avoid the usual serial problem, where permissions on a table do not extend to its sequence. See Roles and Privileges.

CACHE, CYCLE and why gaps appear

A sequence is unique and increasing, but not gapless. A gap appears whenever a number is handed out and not used. The common causes:

  1. Rollbacks. A transaction draws 5, then fails. The 5 is gone.
  2. Failed inserts. A unique violation, a constraint failure or an ON CONFLICT DO NOTHING that skips the row still consumed a value.
  3. CACHE n. With CACHE 20, a session takes 20 numbers in one step and hands them out from memory. Values it never uses are lost when it disconnects, and numbers from different sessions interleave in blocks, so the order of nextval calls across sessions is not the order of the numbers.
  4. Crashes. To avoid logging every call, PostgreSQL writes sequence state to the WAL ahead of use, in batches of about 32 values. After a crash it may skip up to that many values.
  5. Manual changes. setval and ALTER SEQUENCE move the position.

Raising CACHE reduces contention for sequences hit thousands of times a second but makes gaps bigger. The default of 1 is right for most tables.

CYCLE makes a sequence wrap to its MINVALUE after reaching MAXVALUE. That is useful for repeating slot numbers, but it breaks uniqueness: a CYCLE sequence must not be your primary key unless old rows are removed before the wrap.

A model of sessions and a shared sequence

The simulation below mimics the sequence rules in Python: a shared position, per-session caches, CYCLE, limits and setval. Run it and compare what each session receives.

Python Sessions, CACHE, CYCLE and gaps

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Case 2 shows the cost of caching: values 6 to 10 went to session B in one block, and when A left, its unused 4 and 5 vanished for good. Case 3 is the rollback gap. The model is a simplification: in PostgreSQL the shared state lives in a tiny table-like relation with its own page, protected by a lightweight lock only for the moment of the update, which is why sequences scale so well.

Fixing a sequence after loading data

A very common bug: you load rows with explicit ids (an import, a restore, COPY), and the table's sequence still sits at 1. The next normal insert tries id 1, hits the unique violation, and fails. The cure is to set the sequence to the highest id:

SQL
-- PostgreSQL (read-only)
SELECT setval(
  pg_get_serial_sequence('invoice', 'invoice_no'),
  COALESCE(max(invoice_no), 1),
  max(invoice_no) IS NOT NULL            -- is_called: false on an empty table
)
FROM invoice;

With is_called = true the next value is max + 1. With an empty table the third argument is false so the next value is 1. pg_dump output already includes setval calls for the sequences it dumps, so a plain restore does the right thing. Loading data with explicit ids and COPY does not.

Gapless numbers: when sequences are the wrong tool

Some rules require gapless numbers, such as legally numbered invoices in many countries. A sequence cannot give you that, because its numbers are consumed outside of transactions. The standard solution is a counter row that you update inside the same transaction as the business data:

SQL
-- PostgreSQL (read-only)
CREATE TABLE counters (name text PRIMARY KEY, last_value bigint NOT NULL);
INSERT INTO counters VALUES ('invoice', 0);

BEGIN;
UPDATE counters SET last_value = last_value + 1
WHERE name = 'invoice'
RETURNING last_value;            -- take this number
INSERT INTO invoice (invoice_no, customer) VALUES (<the number>, 'Ana');
COMMIT;                          -- a ROLLBACK undoes the counter change too

The UPDATE locks the counter row until commit, so concurrent invoices queue up one behind the other. That is the price of gaplessness: it serializes the writers. If invoice creation is rare, that is fine; for high-volume ids, use a sequence and accept gaps. SQLite supports UPDATE ... RETURNING, so this pattern runs live. The example below uses a rolled-back invoice to show that no number is lost.

SQL A gapless counter that survives a rollback

Press Run (or Ctrl + Enter) to see the result here.

The result has invoice numbers 1 and 2 with no hole, even though three invoices were attempted. A real sequence would have produced 1 and 3.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A transaction calls nextval and then rolls back. What happens to the number?

    Sequences are not transactional. A number once handed out is never returned, which is what allows concurrent sessions to draw numbers without waiting.
  2. 2. What does SELECT setval('s', 100, false) cause?

    With is_called = false the next nextval returns the value you set. With the default true it returns the next value after it, 101.
  3. 3. You loaded 10,000 rows with explicit ids into a table whose id comes from a sequence, and now normal inserts fail with duplicate keys. What is the fix?

    The sequence still thinks it is at 1. setval(pg_get_serial_sequence(...), max(id)) puts it past the loaded ids.
  4. 4. Why is a CYCLE sequence usually a bad primary key source?

    When the sequence wraps to its minimum, old values are issued again, which violates uniqueness if those rows still exist.
  5. 5. Which design gives legally gapless invoice numbers?

    Updating a counter row in the same transaction means a rollback undoes the increment too. The cost is that concurrent writers serialize on the row lock.

Summary

  • A sequence is a separate object that hands out numbers with nextval; currval is per session, lastval is the last of any sequence, and setval repositions it.
  • CREATE SEQUENCE options set type, start, step, bounds, CACHE, CYCLE and OWNED BY. serial and identity columns are shortcuts for a sequence plus a default.
  • Sequences are not transactional: rollbacks, failed inserts, caches, crashes and setval all create gaps. They guarantee uniqueness, not continuity.
  • After loading explicit ids, reposition the sequence with setval(pg_get_serial_sequence(...), max(id)).
  • For gapless numbers use a counter row updated in the same transaction and accept that writers queue up.

For temporary and unlogged tables, which also skip durability machinery, see Temporary and Unlogged Tables, or continue with Arrays.