Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 90 ยท Data Types

UUIDs, Serials and Identity Columns

Compare serial, GENERATED AS IDENTITY and UUID primary keys in PostgreSQL, learn how sequences create gaps, and choose between bigint keys, UUID v4 and time-ordered UUID v7.

Every table needs a way to tell rows apart, and in most designs that is a generated key: a number or identifier the database hands out when a row is inserted. PostgreSQL gives you three main choices. The old serial shortcut, the modern standard GENERATED ... AS IDENTITY column, and the uuid type for globally unique identifiers.

This lesson explains how each one works, why sequences leave gaps, and how to choose. The sequence machinery and uuid functions belong to PostgreSQL and appear in read-only blocks. The live examples use SQLite's INTEGER PRIMARY KEY, which shows the same ideas of generated keys, gaps and key size.

Three ways to generate keys

serial The old shortcut. Still works, now discouraged.
IDENTITY The standard way. Use it for new tables.
uuid 128-bit ids you can create anywhere.

All three give you a unique key; they differ in portability, size and ordering.

1 / 2

Sequences: the engine underneath

A sequence is a database object that hands out increasing numbers. nextval('name') returns the next one, and it is deliberately not transactional: once a number is handed out, it is never given back, even if the transaction rolls back. This is what lets many sessions insert at the same time without waiting on each other.

SQL
-- Read-only: PostgreSQL
CREATE SEQUENCE order_no_seq START 1000;
SELECT nextval('order_no_seq');   -- 1000
SELECT nextval('order_no_seq');   -- 1001
SELECT currval('order_no_seq');   -- 1001  (the last value this session received)
SELECT setval('order_no_seq', 5000);   -- the next nextval returns 5001

Both serial and identity columns are built on top of a sequence.

serial: the legacy shortcut

SQL
-- Read-only: PostgreSQL
CREATE TABLE customers (
  id   serial PRIMARY KEY,    -- also bigserial and smallserial
  name text NOT NULL
);

serial is not a real type. It is shorthand that creates an integer column, creates a sequence named customers_id_seq, sets DEFAULT nextval('customers_id_seq') and links the sequence to the column so it is dropped with the table. It works, but it has rough edges: the default can be overridden by anyone who inserts an explicit id, granting permissions on the table does not include the sequence, and copying a table definition can share one sequence between two tables by accident.

Identity columns: the modern choice

Since PostgreSQL 10, you can use the SQL-standard form:

SQL
-- Read-only: PostgreSQL
CREATE TABLE customers (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO customers (name) VALUES ('Ada') RETURNING id;   -- 1
INSERT INTO customers (id, name) VALUES (50, 'Bob');
-- ERROR: cannot insert a non-DEFAULT value into column "id"

GENERATED ALWAYS refuses manual values unless you ask for them explicitly with OVERRIDING SYSTEM VALUE, which protects you from accidental collisions. If you need to allow manual values, say during data imports, use GENERATED BY DEFAULT AS IDENTITY.

serial GENERATED ... AS IDENTITY
Standard SQL No Yes
Sequence tied to column Loosely, via ownership Fully, managed by the column
Blocks manual values No Yes, with ALWAYS
Permissions Separate on the sequence Follow the table
Recommended for new tables No Yes

Choose bigint rather than integer unless you are certain the table will stay small. An integer tops out at about 2.1 billion values, and changing the type of a huge, busy table later is painful.

Resetting a sequence

After bulk-loading rows with explicit ids, the sequence does not know about them, and the next insert collides. Reset it past the largest id:

SQL
-- Read-only: PostgreSQL
SELECT setval(
  pg_get_serial_sequence('customers', 'id'),
  (SELECT coalesce(max(id), 1) FROM customers)
);

pg_get_serial_sequence() finds the sequence behind a serial or identity column, so you do not need to know its name.

Why gaps appear

Because nextval() ignores rollbacks, gaps are normal:

SQL
-- Read-only: PostgreSQL
BEGIN;
INSERT INTO customers (name) VALUES ('Carol');   -- gets id 2
ROLLBACK;                                        -- the row is gone, id 2 is spent
INSERT INTO customers (name) VALUES ('Dan');     -- gets id 3

Gaps also come from failed inserts (for example a unique violation, or INSERT ... ON CONFLICT DO NOTHING on an existing key), crashes, and deleted rows. If your business needs gap-free numbers, such as legal invoice numbers, do not use a sequence. Generate them in a controlled way inside the transaction, with a counter table updated under a lock, and accept the lower concurrency.

Generated keys in SQLite

SQLite has no sequences or identity syntax. A column declared INTEGER PRIMARY KEY is an alias for the internal row id and gets the next number automatically. The idea of a generated key and of gaps is the same:

SQL Generated integer keys and gaps

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

The new row gets 4, so ids 1, 3, 4 remain with a gap at 2. SQLite normally reuses the highest id only when the last row is deleted. Adding the AUTOINCREMENT keyword makes SQLite never reuse an id at all, closer to a PostgreSQL sequence:

SQL Reusing ids with and without AUTOINCREMENT

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

The plain table reuses id 3 for d, but the AUTOINCREMENT table moves on to 4. In PostgreSQL, a sequence always behaves like the second table. See Auto-Increment and Identity Columns for the portable view.

UUIDs

A UUID is a 128-bit identifier written as 32 hex digits in the form 3f2a9c1e-8b47-4d52-a1e0-9b6c7d5e4f30. PostgreSQL has a native uuid type that stores it in 16 bytes (not as 36 characters of text).

SQL
-- Read-only: PostgreSQL
CREATE TABLE sessions (
  id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id bigint NOT NULL
);

SELECT gen_random_uuid();      -- a random version 4 UUID (built in since PostgreSQL 13)
SELECT uuidv7();               -- a time-ordered version 7 UUID (built in since PostgreSQL 18)

Two versions matter in practice:

  • v4 is purely random. The values are unguessable, but they land at random places in the primary key index, which causes more page splits, a bigger working set and slower inserts on large tables.
  • v7 begins with a millisecond timestamp, followed by random bits. New values sort roughly in creation order, so inserts append near the end of the index like a bigint would, while the ids stay globally unique and hard to guess.

Before PostgreSQL 18 you can generate v7 values in your application, or in a SQL function. The older uuid-ossp extension offers uuid_generate_v4(), but gen_random_uuid() makes it unnecessary.

SQLite has no UUID type either, but a random 16-byte value is easy to produce with randomblob(16). The output differs each run, so this example shows the deterministic parts: a UUID needs 32 hex characters, but only 16 bytes of binary storage.

SQL UUID size: text versus binary

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

The dashed text form takes 36 bytes, over twice the 16 bytes of the binary form. Storing UUIDs as text in PostgreSQL would waste exactly that space in the table and in every index and foreign key that refers to it, which is why the uuid type exists.

Choosing a key

bigint identity UUID v4 UUID v7
Size 8 bytes 16 bytes 16 bytes
Insert locality Excellent Poor Very good
Guessable Yes, sequential No No (mostly random)
Generate on the client No Yes Yes
Merging data from several databases Hard Easy Easy
Readable in logs and URLs Yes No No

Use bigint GENERATED ALWAYS AS IDENTITY as the default for ordinary tables. Choose a UUID when ids must be created outside the database, merged from several sources, or not exposed as an enumerable counter in URLs. Prefer v7 over v4 for tables that will grow large. A common compromise is a bigint primary key for internal joins and a separate UUID column for public references.

Do not make business meaning part of a generated key. If you need a human-friendly order number or a public slug, add it as its own UNIQUE column and keep the generated key opaque.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a rolled-back INSERT still consume a sequence value?

    Sequences are non-transactional so that concurrent sessions never wait on each other. A number once issued is gone, so gaps are normal.
  2. 2. Which is the recommended way to declare an auto-generated primary key on a new PostgreSQL table?

    Identity columns are SQL-standard, tie the sequence to the column and refuse accidental manual values. bigint avoids hitting the integer limit later.
  3. 3. What is the main advantage of UUID v7 over v4 as a primary key?

    v7 starts with a timestamp, so new values sort in roughly increasing order. That keeps index inserts efficient, while v4 values land at random positions.
  4. 4. How should you store UUIDs in PostgreSQL?

    The uuid type stores 16 bytes, compared with 36 for the dashed text form, in the table and every index that includes it.

Summary

  • Sequences hand out numbers outside transactions, so rollbacks, failed inserts and crashes create gaps.
  • serial is a legacy shortcut; for new tables use GENERATED ALWAYS AS IDENTITY, with bigint.
  • OVERRIDING SYSTEM VALUE and setval() handle imports with explicit ids.
  • The uuid type stores 16 bytes; gen_random_uuid() makes v4 and uuidv7() (PostgreSQL 18) makes time-ordered v7.
  • Random v4 keys hurt insert locality on large tables; v7 keeps the benefits of uniqueness without that cost.
  • Choose a bigint identity by default, UUIDs when ids are generated outside the database or must not be guessable.

Next: Arrays.