uuid-ossp

What uuid-ossp does

uuid-ossp provides a set of functions that generate UUIDs according to the algorithms defined in RFC 4122. Each version serves a different purpose: version 1 incorporates a timestamp and MAC address for time-ordered uniqueness, version 4 uses cryptographic randomness, and versions 3 and 5 derive deterministic UUIDs from a namespace and name.

The extension also provides functions that return the standard UUID namespace constants (DNS, URL, OID, X.500) used as inputs for the name-based variants.

When to use uuid-ossp

Before installing uuid-ossp, do yourself the courtesy of checking whether you actually need it. The most common use case — generating random v4 UUIDs — no longer requires the extension.

• You need v1 (time-based) UUIDs — for time-ordered identifiers where the timestamp is embedded in the UUID itself
• You need v3 or v5 (name-based) UUIDs — for deterministic identifiers derived from a namespace and name, such as generating stable UUIDs from URLs or DNS names
• You are on PostgreSQL 12 or earlier — before version 13, there was no built-in v4 UUID function, so uuid-ossp (or pgcrypto) was required

If you only need random v4 UUIDs and you are on PostgreSQL 13 or later, use the built-in gen_random_uuid() instead. It produces identical output, is roughly twice as fast, and requires no extension. One does not install a chandelier to read by when the lamp on the desk will do.

Installation and setup

uuid-ossp is a contrib module that ships with PostgreSQL — no external packages to install. It is a trusted extension, meaning non-superusers with CREATE privilege on the database can install it.

-- uuid-ossp is a contrib module — no extra packages needed
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Verify it's working
SELECT uuid_generate_v4();

Note the double-quoted extension name: the hyphen in uuid-ossp requires quoting in SQL, which catches more people than I care to count. No server restart is needed — this is a simple CREATE EXTENSION.

Functions

The extension provides six generation functions and four namespace constants.

-- Random UUID (version 4) — most common
SELECT uuid_generate_v4();
-- e.g. a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

-- Time-based UUID (version 1) — includes timestamp and MAC address
SELECT uuid_generate_v1();

-- Time-based with random MAC (version 1mc) — no hardware leak
SELECT uuid_generate_v1mc();

-- Name-based UUID (version 5, SHA-1) — deterministic
SELECT uuid_generate_v5(uuid_ns_dns(), 'www.example.com');

-- Name-based UUID (version 3, MD5) — deterministic, prefer v5
SELECT uuid_generate_v3(uuid_ns_dns(), 'www.example.com');

Namespace constants

The name-based functions (v3 and v5) require a namespace UUID. The extension provides the four standard namespaces defined in RFC 4122.

-- Built-in namespace constants for v3/v5 generation
SELECT uuid_ns_dns();   -- 6ba7b810-9dad-11d1-80b4-00c04fd430c8
SELECT uuid_ns_url();   -- 6ba7b811-9dad-11d1-80b4-00c04fd430c8
SELECT uuid_ns_oid();   -- 6ba7b812-9dad-11d1-80b4-00c04fd430c8
SELECT uuid_ns_x500();  -- 6ba7b813-9dad-11d1-80b4-00c04fd430c8

-- Deterministic: same inputs always produce the same UUID
SELECT uuid_generate_v5(uuid_ns_dns(), 'www.example.com');
-- Always returns: 2ed6657d-e927-568b-95e1-2665a8aea6a2

gen_random_uuid() and PostgreSQL 18

PostgreSQL 13 added gen_random_uuid() as a built-in core function — and in doing so, made uuid-ossp unnecessary for most installations. It generates v4 random UUIDs identical to uuid_generate_v4(), is roughly twice as fast in benchmarks, and requires no extension whatsoever.

-- PostgreSQL 13+ has gen_random_uuid() built-in — no extension needed
SELECT gen_random_uuid();

-- PostgreSQL 18+ adds uuidv7() — time-ordered, better for primary keys
SELECT uuidv7();

-- These are equivalent (both produce v4 random UUIDs):
SELECT gen_random_uuid();          -- built-in, no extension
SELECT uuid_generate_v4();         -- requires uuid-ossp

PostgreSQL 18 goes further by adding a built-in uuidv7() function that generates time-ordered UUIDs. Version 7 UUIDs embed a millisecond-precision timestamp in the most significant bits, making them sort chronologically — a significant advantage for primary keys (see below).

UUIDs as primary keys

UUIDs are commonly used as primary keys to avoid exposing sequential IDs and to simplify distributed ID generation. However, the UUID version you choose has real performance implications.

-- Using uuid-ossp for a primary key (pre-PostgreSQL 13)
CREATE TABLE orders (
  id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  customer_name text NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Using built-in gen_random_uuid() (PostgreSQL 13+, no extension needed)
CREATE TABLE orders (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_name text NOT NULL,
  created_at timestamptz DEFAULT now()
);

Random UUIDs (v4) and index fragmentation. Each new v4 UUID targets a random position in the B-tree primary key index. This causes frequent page splits — benchmarks show 5,000 to 10,000+ page splits per million inserts, compared to 10-20 for sequential values. Index pages average around 69% full instead of near 100%, wasting disk space and reducing cache efficiency.

Time-ordered UUIDs solve this. Version 1 UUIDs (from uuid-ossp) and version 7 UUIDs (from PostgreSQL 18) both embed timestamps in the high-order bits, so new values append near the end of the B-tree. This restores sequential insert performance while preserving the benefits of UUID uniqueness. If you are on PostgreSQL 18+, uuidv7() is the best option. On older versions, uuid_generate_v1mc() from uuid-ossp provides similar ordering without leaking your MAC address.

Cloud availability

How Gold Lapel relates

Gold Lapel passes UUID columns through transparently — v1, v4, v7, it makes no difference to the proxy. UUID generation is the database's affair, and Gold Lapel's query analysis and index recommendations work the same regardless of key type.

Where Gold Lapel can be of service is in surfacing the consequences of your UUID choice. If a table's primary key index shows high bloat from random v4 inserts, that signal will appear in Gold Lapel's analysis. The remedy — switching UUID versions or adopting a different key strategy — is a schema change you would make yourself, but at least you would know to make it.