UUID Data Type in PostgreSQL | PostgreSQL Tutorial

The PostgreSQL UUID data type stores a 128-bit Universally Unique Identifier (UUID) as defined by RFC 4122. PostgreSQL holds the value in 16 bytes of binary storage and displays it in the standard hyphenated format:

40e6215d-b5c6-4896-987c-f30f3678f608

Unlike SERIAL which generates IDs within a single database sequence, a UUID is globally unique — making it ideal for distributed systems, multi-database architectures, and public-facing resource identifiers.

Generating UUID Values

PostgreSQL 13+ includes gen_random_uuid() as a built-in function:

SELECT gen_random_uuid();
-- d6eb621f-6dd0-4cdc-93f5-07f51b249b51

For PostgreSQL versions older than 13, install the uuid-ossp extension:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
-- 351c1afe-21b2-486c-951b-66bc9e852530

Practical Example

Create a products catalog with a UUID primary key:

CREATE TABLE products (
  id          UUID         DEFAULT gen_random_uuid() PRIMARY KEY,
  sku         VARCHAR(50)  NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL,
  unit_price  NUMERIC(10,2) NOT NULL,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  product_id  UUID        NOT NULL REFERENCES products(id),
  quantity    INTEGER     NOT NULL CHECK (quantity > 0),
  placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, unit_price)
VALUES
  ('SKU-001', 'Titanium Bolt Set',   19.99),
  ('SKU-002', 'Carbon Fiber Panel', 149.50)
RETURNING id, sku;

                  id                   |   sku
--------------------------------------+---------
 ca61da8c-938a-48a6-8eb6-55aa08cd1b08 | SKU-001
 fe2af584-8576-4d0e-b10d-6ec970732f8e | SKU-002

UUID vs. SERIAL: Choosing a Primary Key Strategy

Property	UUID	SERIAL / BIGSERIAL
Global uniqueness	Yes — across databases and services	No — unique only within one sequence
Storage	16 bytes	4–8 bytes
Sequential inserts	No (random order causes B-tree fragmentation)	Yes (monotonically increasing)
ID enumeration risk	Low (unpredictable)	High (sequential IDs are guessable)
Distributed systems	Excellent	Requires coordination

Indexing UUID Primary Keys

PostgreSQL creates a B-tree index on the primary key by default. For write-heavy tables, random UUID insertion order can cause B-tree page splits and index bloat. Monitor with:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('products_pkey');

If fragmentation becomes a problem, consider time-ordered UUIDs (UUIDv7) or run VACUUM regularly.

Testing with Vela

Migrating an existing integer primary key to UUID is one of the most impactful — and risky — schema changes you can make. Vela’s database branching lets you create a production-data clone, run the full multi-step UUID migration (add UUID columns, backfill values, update foreign keys, drop integer columns), and validate that all application queries, foreign key constraints, and ORM models work correctly before touching the live database.

Production Tips

Always use DEFAULT gen_random_uuid() on UUID primary key columns so the database — not the application — is responsible for ID generation.
For high-throughput write tables, consider time-ordered UUID variants (UUIDv7 or ULID) to reduce B-tree index fragmentation from random insertion order.
UUID primary keys prevent sequential ID enumeration in public APIs — an attacker cannot guess adjacent resource IDs.
Index UUID columns with a standard B-tree index; for large tables with only equality lookups, a hash index may be faster and smaller.
Log the UUID returned by RETURNING id on every insert rather than relying on lastval() — this is safer under concurrent inserts.

Frequently Asked Questions

What is a UUID in PostgreSQL?

UUID (Universally Unique Identifier) is a 128-bit value defined by RFC 4122. PostgreSQL stores it in 16 bytes (binary) and displays it as a 32-digit hexadecimal string in five hyphen-separated groups, e.g. 40e6215d-b5c6-4896-987c-f30f3678f608. Unlike SERIAL, a UUID is globally unique — the same value will never be generated by two separate systems.

Does using UUID primary keys lock the table on insert?

No. Generating a UUID with gen_random_uuid() is a pure CPU operation with no shared state or locking, unlike sequences which require a brief lock. UUID generation scales well under high concurrency.

What happens to dependent foreign keys when I change a SERIAL primary key to UUID?

Every table with a foreign key referencing the SERIAL column must also have that column type changed to UUID. This is a multi-step migration: add the UUID column to the parent table, add UUID columns to all child tables, backfill values, update foreign key constraints, then drop the old integer columns.

Should I use gen_random_uuid() or the uuid-ossp extension?

On PostgreSQL 13 and later, use gen_random_uuid() — it is a built-in function requiring no extension. For PostgreSQL versions before 13, install the uuid-ossp extension with CREATE EXTENSION IF NOT EXISTS 'uuid-ossp' and use uuid_generate_v4(). Both generate version 4 random UUIDs.

What is the safest way to use UUID primary keys in production?

Define the primary key as UUID DEFAULT gen_random_uuid(). For write-heavy tables, consider time-ordered UUID variants (UUIDv7 or ULID) to reduce B-tree index fragmentation from random insertion order. Monitor index bloat with pgstattuple on large tables.