PostgreSQL Sequences

A PostgreSQL sequence is a special database object that generates a series of unique integers. Sequences are the underlying engine powering SERIAL columns and GENERATED AS IDENTITY columns, but they can be created independently to generate numbers following custom patterns — for example, starting at 1000 and incrementing by 10, or cycling through a fixed range.

Syntax

CREATE SEQUENCE [IF NOT EXISTS] sequence_name
  [AS { SMALLINT | INT | BIGINT }]
  [INCREMENT [BY] increment]
  [MINVALUE minvalue | NO MINVALUE]
  [MAXVALUE maxvalue | NO MAXVALUE]
  [START [WITH] start]
  [CACHE cache]
  [[ NO ] CYCLE]
  [OWNED BY { table_name.column_name | NONE }];

Key options:

• INCREMENT — step size per call; positive for ascending, negative for descending. Default: 1.
• START — first value from nextval(). Default: 1 for ascending.
• CYCLE — wraps around at the limit. Default NO CYCLE raises an error at the limit.
• CACHE — pre-allocates values in memory per session for faster access.
• OWNED BY — ties the sequence lifetime to a column; auto-dropped when the column is dropped.

Practical Example

Create an ascending sequence for event ticket numbers, starting at 1000 and incrementing by 1:

CREATE SEQUENCE ticket_number_seq
  START 1000
  INCREMENT 1
  NO CYCLE;

Use it in a table:

CREATE TABLE event_tickets (
  ticket_number BIGINT       NOT NULL DEFAULT nextval('ticket_number_seq'),
  event_id      INT          NOT NULL,
  holder_name   VARCHAR(200) NOT NULL,
  issued_at     TIMESTAMPTZ  NOT NULL DEFAULT now(),
  PRIMARY KEY (ticket_number)
);

INSERT INTO event_tickets (event_id, holder_name)
VALUES (42, 'Alice Tan')
RETURNING ticket_number;

 ticket_number
---------------
          1000

Create a cycling descending sequence for countdown IDs:

CREATE SEQUENCE countdown_seq
  INCREMENT -1
  MINVALUE 1
  MAXVALUE 5
  START 5
  CYCLE;

SELECT nextval('countdown_seq'); -- 5
SELECT nextval('countdown_seq'); -- 4
SELECT nextval('countdown_seq'); -- 3

List all sequences in the current database:

SELECT relname AS sequence_name
FROM pg_class
WHERE relkind = 'S'
ORDER BY relname;

Reset a sequence after a bulk data import:

SELECT setval('ticket_number_seq', MAX(ticket_number))
FROM event_tickets;

Drop a sequence:

DROP SEQUENCE IF EXISTS ticket_number_seq CASCADE;

Testing with Vela

Sequence gaps and exhaustion issues are notoriously difficult to reproduce in development. Vela database branching lets you clone production with its real sequence state, run bulk imports or load tests, and verify setval() resets work correctly before touching the live database.

Production Tips

• Sequence advances are not transaction-safe: a rolled-back transaction permanently skips the consumed value. Never build logic that assumes gap-free IDs.
• Use CACHE greater than 1 to reduce sequence lock contention in high-concurrency workloads, at the cost of larger gaps between sessions.
• Use OWNED BY table.column to bind the sequence so it is automatically dropped when the column or table is removed.
• After bulk data imports with manual IDs, always resync the sequence with setval() to prevent future duplicate-key errors.
• For standard auto-increment IDs, prefer SERIAL or GENERATED AS IDENTITY over manually created sequences — they wire up the OWNED BY relationship and column default automatically.
• Call currval() only after nextval() has been called in the same session — it raises an error otherwise.