Right, so you need a UUID. Universally Unique Identifier. A 128-bit number so astronomically unique that if you generated one every second for the next 100 years, the probability of you creating a duplicate is, for all practical purposes, zero. It’s the go-to for any distributed system where you can’t rely on a central authority to hand out sequential IDs. And sometimes, the most sensible place to generate this thing is right inside the database, right before you insert a row. That’s where the uuid-ossp extension saunters in.

Now, a quick history lesson because it’s mildly interesting: the ossp in uuid-ossp stands for “Open Source Software Postgres”, a naming relic from a time when this was a separate library. These days, the core UUID generation functions are actually built right into PostgreSQL itself. You can do SELECT gen_random_uuid(); on any moderately modern version (9.4+) without any extension at all. So why are we even talking about uuid-ossp? Two reasons: history (a ton of existing code uses it) and the fact that it offers a few more variants of UUIDs, some of which you might actually need.

The Two Main Flavors of UUID

You’ll primarily deal with two versions: v1 and v4. Understanding the difference is key to not making a silly choice.

Version 4 (random): This is the one you get from the built-in gen_random_uuid(). It’s 122 bits of glorious, cryptographically strong randomness. This is your default, your workhorse. Use it unless you have a specific reason not to. It’s anonymous and doesn’t leak any information about when or where it was created.

Version 1 (time-based): This is uuid-ossp’s main party trick. A v1 UUID is generated from a timestamp, plus the MAC address of the machine generating it (a “node identifier”). This has pros and cons. The pro is that they’re time-ordered; generating them in sequence will yield values that mostly sort chronologically. This can be friendlier to your database’s index structure than completely random data. The massive, glaring con is that it exposes the MAC address of your database server. This is often a deal-breaker for security and privacy reasons. If you use these, you should know exactly why.

Enabling the Extension and Basic Generation

First, you gotta enable it. It’s not a huge, complex extension, so this is painless.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Now, let’s generate some IDs. The main function you’ll use is uuid_generate_v1() or uuid_generate_v4().

-- Generate a v4 (random) UUID
SELECT uuid_generate_v4();
-- Result: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

-- Generate a v1 (time-based) UUID
SELECT uuid_generate_v1();
-- Result: a0eebc99-9c0b-11ef-bb6d-6bb9bd380a11 (note the '1' in the version field)

See the difference in the fourth group of digits? 4ef8 vs 11ef. That’s where the version number is encoded. The 1 in the third group of the v1 UUID (1ef) signifies the variant (which is how you know it’s a standard RFC 4122 UUID).

Using UUIDs as Primary Keys

This is the most common use case. You want a table where the ID is a UUID, not a serial integer.

CREATE TABLE orders (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id int NOT NULL,
    amount numeric(10,2) NOT NULL,
    created_at timestamptz DEFAULT NOW()
);

-- Insert a row, and the ID is generated automatically
INSERT INTO orders (user_id, amount)
VALUES (123, 99.95);

-- See the generated UUID
SELECT * FROM orders;

The magic is in the DEFAULT clause. The database takes care of it automatically on insert. Beautiful.

The gen_random_uuid() Shortcut

As I mentioned, if you’re on PostgreSQL 9.4 or later and you just need a random UUID (v4), you can skip the extension altogether. The function is built-in.

-- This works without ever enabling uuid-ossp
CREATE TABLE events (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    description text
);

This is arguably the cleaner, more modern approach for new projects. It’s one less extension to manage.

Best Practices and Pitfalls

  1. Storage Cost: A UUID is 128 bits, or 16 bytes. That’s double the size of a bigint (8 bytes) and four times the size of an integer (4 bytes). This has a real impact on your primary key indexes. Larger indexes mean more memory usage and potentially slower performance. Don’t use a UUID just because it looks cool. Use it because you have a real need for a globally unique identifier outside the control of your single database.

  2. Index Fragmentation: This is the big one with random UUIDs (v4). Because they’re random, every new insert is going to land at a random spot in the index tree. This destroys locality, causes page splits, and can lead to index bloat. If you have high-volume inserts, this can become a performance issue. If you need the randomness but also need performance, consider using a sequential prefix (like a time-based one) or look into UUIDv7, which is designed for this (though not natively supported in PostgreSQL yet).

  3. The MAC Address Problem: Just to hammer it home: Be incredibly careful with uuid_generate_v1(). Leaking your database server’s MAC address is a security no-no in most environments. There are functions in uuid-ossp to generate v1 UUIDs with a fake node ID (uuid_generate_v1mc), but at that point, you’re losing the ordering guarantee, so you might as well just use a v4.

So, the tl;dr: For 99% of new use cases, use gen_random_uuid() and forget the uuid-ossp extension exists. It’s simpler and more secure. Keep uuid-ossp in your back pocket for that one weird legacy project or if you suddenly find yourself needing to care about the minute differences between UUID variants.