How do you generate and store UUIDs in PostgreSQL?
Use the uuid type, which stores 16 bytes, and generate values with the built-in gen_random_uuid() (available without an extension since PostgreSQL 13). Store them as uuid, not as 36-character text.
Explain it simply
A UUID is like a very long random booking reference. Two servers can each create one without talking to each other, and the chance of a clash is negligible. That is why they are popular for IDs created in many places at once, such as mobile apps or several services.
The uuid type holds the value in 16 bytes. The same value as text takes 36 characters, so text is bigger and slower to compare.
Example
CREATE TABLE tickets (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
passenger text NOT NULL
);
INSERT INTO tickets (passenger) VALUES ('Karan') RETURNING id;
-- e.g. 3f8e2a1c-9b47-4d0e-8c55-6a1d2f7b9e10On PostgreSQL 12 and older you would install the pgcrypto or uuid-ossp extension for a generator.
Fully random UUIDs insert at random places in the primary key index, which can be less cache-friendly than ever-increasing numbers. Time-ordered UUID variants reduce that effect.
What to say in the interview
"I use the uuid type with gen_random_uuid() as the default. It is 16 bytes, not 36 characters of text. The trade-off against a bigint identity is bigger indexes and random insert order, so I choose UUIDs when IDs must be created outside the database or must not be guessable."
Common mistakes
- Storing UUIDs in a text or varchar column.