UUID in SQL

What is a UUID in SQL

Storing a UUID as text is the most common expensive mistake. PostgreSQL has a native uuid type of exactly 16 bytes; MySQL 8 offers BINARY(16) with UUID_TO_BIN(); SQL Server has uniqueidentifier. All three are smaller and faster to compare than a 36-character string, and all three validate on write.

Choosing the version matters more in a database than anywhere else, because of the index. Random v4 values scatter across a B-tree, so every insert touches a different page; a time-ordered v7 keeps new rows together and inserts stay cheap. PostgreSQL 18 ships uuidv7() for exactly that reason; on older versions or other engines you generate v7 in the application and insert it as a parameter.

How to

1. Pick the function for your engine — uuidv7(), gen_random_uuid(), UUID() or NEWID().
2. Make it the column default so inserts never have to supply a value.
3. Keep the storage type native (16 bytes) rather than text, and use v7 when the column is indexed.

Use cases

• Primary keys that must be generated without asking the database, for example in a distributed service.
• Idempotent imports, where a deterministic v5 derived from the source row prevents duplicates on re-run.
• Public identifiers that should not reveal how many rows the table holds, unlike an auto-increment column.

Compare UUID in SQL with other formats

Code examples

PostgreSQL

-- Random v4 (pgcrypto, or built in since PostgreSQL 13)
SELECT gen_random_uuid();

-- Time-ordered v7 (PostgreSQL 18+)
SELECT uuidv7();

-- Column default + metadata queries
CREATE TABLE events (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  created_at timestamptz NOT NULL DEFAULT now()
);
SELECT uuid_extract_version(id), uuid_extract_timestamp(id) FROM events;

MySQL

SELECT UUID();                        -- v1, time based
SELECT UUID_TO_BIN(UUID(), 1);         -- v1 bytes, time part first: index friendly

CREATE TABLE events (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT BIN_TO_UUID(id, 1) FROM events; -- back to text

SQL Server

SELECT NEWID();                -- v4, random
SELECT NEWSEQUENTIALID();      -- ordered, but only allowed as a column default

CREATE TABLE events (
  id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
  created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT pk_events PRIMARY KEY CLUSTERED (id)
);

Deterministic keys

-- PostgreSQL: derive a stable id from a natural key
SELECT md5('tenant:42')::uuid;

-- Safer: keep the key stable with a namespace UUID
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com/tenant/42');

Frequently asked questions