Sequences

sequences Sequences#

Sequences (PostgreSQL)#

1️⃣ What is a Sequence?#

A sequence is a database object that generates unique numbers, usually for IDs.

Think of it as:

a safe, concurrent counter managed by PostgreSQL itself.

PostgreSQL guarantees that:

  • Two sessions never get the same value

  • Values are generated without locking tables

  • Performance stays high even under load

2️⃣ Why Sequences exist#

Without sequences, generating IDs safely would require:

  • Locks

  • Transactions

  • Race-condition handling

Sequences solve this problem at the engine level.

3️⃣ The classic use case: primary keys#

CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    name TEXT
);
Behind the scenes, PostgreSQL creates:

  • a sequence

  • links it to users.id

You usually don’t see it, but it exists.

4️⃣ Manual sequence creation#

CREATE SEQUENCE order_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Get the next value:

SELECT nextval('order_id_seq');

5️⃣ Attaching a sequence to a column#

ALTER TABLE orders
ALTER COLUMN id SET DEFAULT nextval('order_id_seq');

Now:

INSERT INTO orders DEFAULT VALUES;

→ ID is auto-generated.

6️⃣ nextval, currval, setval (important!)

nextval()

SELECT nextval('order_id_seq');

✔ Advances sequence

✔ Returns a new value

currval()

SELECT currval('order_id_seq');

✔ Returns last value used in this session

❌ Errors if nextval() was never called

setval()

SELECT setval('order_id_seq', 1000);

✔ Manually resets the sequence

Very useful after bulk imports.

7️⃣ Sequences are NOT transactional

This is critical:

BEGIN;
SELECT nextval('order_id_seq');
ROLLBACK;

The number is lost forever

This is by design to ensure performance and concurrency.

8️⃣ Sequences vs SERIAL vs IDENTITY#

Old style (SERIAL)

id SERIAL
Problems:

  • Hidden sequence

  • Harder to control

  • Not SQL-standard

Modern style (IDENTITY)

id BIGINT GENERATED ALWAYS AS IDENTITY
Advantages:

  • SQL standard

  • Explicit ownership

  • Safer schema migrations

Recommendation:

Use IDENTITY, not SERIAL.

9️⃣ Sequence ownership#

Sequences should be owned by a column:

ALTER SEQUENCE order_id_seq
OWNED BY orders.id;
Why this matters:

  • Dropping table drops sequence

  • Cleaner schema

  • Fewer orphan objects

🔟 Permissions & security#

You can restrict sequence usage:

REVOKE ALL ON SEQUENCE order_id_seq FROM PUBLIC;
GRANT USAGE, SELECT ON SEQUENCE order_id_seq TO app_role;
In production:

  • Applications usually don’t call sequences directly

  • They rely on DEFAULT values

1️⃣1️⃣ Performance tuning (advanced)#

CACHE

CACHE 100

  • Improves performance

  • May skip numbers on crash

  • Usually safe

NO CYCLE (default)

NO CYCLE

  • Prevents reuse of IDs

  • Almost always what you want

1️⃣2️⃣ When NOT to use sequences#

Avoid sequences if:

  • IDs must be gapless (accounting numbers)

  • Ordering must reflect commit order

  • IDs must be meaningful

Use:

  • Explicit locking

  • Custom logic

  • Separate numbering tables

1️⃣3️⃣ Sequences vs UUIDs#

Feature

Sequence

UUID

Readability

✅

❌

Index size

Small

Large

Ordering

Natural

Random

Distributed-safe

❌

✅

Performance

Fast

Slower
Many systems use:

  • Sequence for internal PK

  • UUID for public/external IDs

Summary

A sequence is a high-performance, concurrency-safe number generator used primarily for primary keys in PostgreSQL.

Contents