Generating Unique Identifiers

When computers first started connecting to networks everywhere, a simple but super important problem popped up: how do you make sure two different devices, sitting on opposite sides of the planet, don't generate the same identifier?

Picture this: two servers, one in Tokyo, one in Berlin, both independently creating IDs for objects. If they accidentally make the same ID — your data could get mixed up, overwritten, or the whole system could crash.

So back in the 1990s, during the wild growth of distributed systems and network protocols, deep inside Microsoft and the Open Software Foundation (OSF), the idea of the GUID was born — Globally Unique Identifier, or globally unique identifier.

GUID (and in the ISO standard — UUID, Universally Unique Identifier) is a 128-bit number that looks something like this:

550e8400-e29b-41d4-a716-446655440000

It's like a digital fingerprint: it's so long and random that the chance of a collision (two IDs matching) is basically zero.

It's created using:

• time,
• random numbers,
• the device's MAC address (in older versions),
• and even cryptographic hash functions.

Why was this a big deal? Well, GUID let you create unique identifiers without a central server, no coordination, no locks or delays. This was a real lifesaver for:

• distributed databases,
• network protocols,
• document management systems,
• and of course, modern APIs.

UUID in PostgreSQL

GUID/UUID is used everywhere — from PostgreSQL to cloud services. They're the invisible builders of the modern internet: without them, connecting the world would be way harder.

Basically, it's just a really long random number that's 16 bytes, written in hexadecimal. Just a really big integer.

This data type is perfect if you need to guarantee uniqueness across your whole system, without relying on a centralized ID generator. It's especially useful in distributed systems or when data is generated on different servers.

Why not just use INTEGER?

You might think, why bother with UUID when you could just use an auto-incrementing number as an ID? Let's break it down:

Global uniqueness: If your database runs on multiple servers, it's tough to guarantee uniqueness with INTEGER. With UUID, this problem goes away.

Security and data hiding: UUID is way harder to guess than a sequential number. This lowers the risk of leaking info through predictable IDs (like /users/1, /users/2).

Distributed systems: If data is generated in different parts of the system, auto-increment INTEGER won't work without complicated syncing.

Another plus for UUID — it's a standard, supported in tons of programming languages and data storage systems.

Advantages of using UUID

Main pros:

• Uniqueness: you get guaranteed unique IDs, even if data is created on different servers or systems.
• Flexibility: you can use it for primary keys, foreign keys, and other stuff.
• Scalability: super handy when working with distributed databases.

But UUID does have some downsides:

• Size: UUID takes up more memory (16 bytes) than INTEGER (4 bytes).
• Readability: they're a lot harder to read and remember.

Generating UUID in PostgreSQL

Built-in function gen_random_uuid()

Starting with PostgreSQL version 13, there's a built-in gen_random_uuid() function to generate random UUIDs. This function returns a unique identifier you can use as a value for a UUID column.

Example:

SELECT gen_random_uuid();

Result:

d17fc23b-22e5-4fcb-bf86-1b4c766d77b7

Footnote: in PostgreSQL versions before 13, gen_random_uuid() required the pgcrypto extension (CREATE EXTENSION pgcrypto). With our course pin = PostgreSQL 17, you don’t need that anymore — the function is built in.

Using UUID as a foreign key

UUID is super handy for building relationships between tables. Say you have a users table:

Creating the orders table

Let's make an orders table, where user_id will be a foreign key referencing id in the users table.

The user_id field is linked to the id field in the users table, letting you create relationships between users and their orders.

Selecting data with JOIN

Let's see how the data is connected in the users and orders tables:

SELECT 
    u.id AS user_id, 
    u.name, 
    o.order_id, 
    o.order_date 
FROM users u
JOIN orders o ON u.id = o.user_id;

Result:

Main use cases for UUID

User and order IDs: in distributed systems, where data can come from different sources.

Tags for APIs: UUID is often used in REST APIs to identify entities.

Global data sync: for example, when data is collected from different servers.

Common mistakes and gotchas

Trying to generate UUID by hand: it's better to use built-in functions like gen_random_uuid() to avoid mistakes.

Overusing it: don't use UUID where a simple auto-increment INTEGER is enough. For example, in local tables that will never need to scale.

Size: UUID takes up more space, which can affect query performance, especially when indexing.