What is the difference between SERIAL and IDENTITY columns, and when would you use a UUID primary key?
SERIAL is an older PostgreSQL shorthand that creates a sequence and sets it as the column default. IDENTITY (GENERATED ALWAYS or BY DEFAULT AS IDENTITY) is the SQL-standard replacement and is preferred for new tables. Use a UUID when IDs must be generated outside the database, merged across systems, or must not be guessable.
Explain it simply
A roll number counter in a college office gives 1, 2, 3 to each new student. SERIAL and IDENTITY are both that counter, just set up in two ways. SERIAL is the old method: the office hangs a separate counter on the wall and writes a note "take the next number from that counter". IDENTITY is the modern method: the counter is built into the register itself, follows the SQL standard, and can refuse handwritten numbers.
A UUID is different. It is like a long random code, such as 3f2a9c1e-..., that anyone can generate on their own without asking the office, with almost no chance of two people getting the same one.
Example
-- Old style
CREATE TABLE students_old (
id serial PRIMARY KEY,
name text NOT NULL
);
-- Preferred today
CREATE TABLE students (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
INSERT INTO students (name) VALUES ('Priya'); -- id = 1
INSERT INTO students (id, name) VALUES (99, 'Ravi');
-- ERROR: cannot insert a non-DEFAULT value into column "id"
-- (GENERATED ALWAYS protects the counter; OVERRIDING SYSTEM VALUE bypasses it)
-- UUID primary key; gen_random_uuid() is built in since PostgreSQL 13
CREATE TABLE payments (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
amount numeric(12, 2) NOT NULL
);How it works
serial is not a real type. PostgreSQL expands it into an integer column, a separate sequence object, and DEFAULT nextval('students_old_id_seq'). Because the sequence is a separate object, you have to manage its permissions separately, and copying the table definition can leave it pointing at the old sequence.
GENERATED ... AS IDENTITY (PostgreSQL 10 and later) still uses a sequence internally, but it is tied to the column:
GENERATED ALWAYSrejects manual values unless you writeOVERRIDING SYSTEM VALUE.GENERATED BY DEFAULTuses the counter only when you do not supply a value, which helps when importing old data.
Both give gaps. If a transaction takes a number and then rolls back, that number is not reused. Never rely on IDs being continuous.
| bigint IDENTITY | UUID | |
|---|---|---|
| Size | 8 bytes | 16 bytes |
| Generated by | The database | The database or the application |
| Guessable | Yes, /orders/1001 hints at /orders/1002 | No (random versions) |
| Index friendliness | New values go to the end of the index | Random v4 values land all over the index |
Real-world use
- Most internal tables:
bigint GENERATED ALWAYS AS IDENTITY. Small, fast, simple. - Public IDs in URLs or APIs, where you do not want competitors to count your orders: UUID.
- Mobile apps that create records offline and sync later, or data merged from several databases: UUID, because each side can generate IDs without clashing.
Common mistakes
- Using
serial(a 32-bitinteger) for a table that could pass about 2.1 billion rows. Usebigintorbigserialfrom the start. - Assuming IDs have no gaps, for example to count rows or to number invoices legally. Use a separate numbering method for that.
- Storing UUIDs as
text. Use theuuidtype, which is 16 bytes instead of 36 characters.
What to say in the interview
"SERIAL is a PostgreSQL shorthand that creates an integer column, a separate sequence and a default that calls nextval. IDENTITY is the SQL-standard way, available since version 10. The sequence belongs to the column, and GENERATED ALWAYS stops people from inserting their own values by mistake. For new tables I use bigint GENERATED ALWAYS AS IDENTITY. I choose UUIDs when IDs need to be created outside the database, merged across systems, or exposed publicly without being guessable, accepting that they are larger and random UUIDs scatter inserts across the index."
Follow-up questions
- Why do sequences have gaps?
- What is the difference between UUID v4 and UUID v7?
- How do you reset a sequence after a bulk import?
Go deeper
Read these once the basics above feel easy.
Random UUID v4 values spread inserts across the whole B-tree, so on large tables more index pages are touched and cached. UUID v7 puts a timestamp in the leading bits, so new values are roughly in time order and behave much more like an increasing bigint in an index. PostgreSQL 18 adds a built-in uuidv7() function; on older versions you can generate v7 values in the application. A common middle path is a bigint identity as the internal primary key plus a separate unique UUID column for public use.