What is UUID in PostgreSQL: Explanation and Usage

In PostgreSQL, UUID stands for Universally Unique Identifier, a 128-bit value used to uniquely identify rows or objects across systems. It ensures uniqueness without relying on sequential numbers, making it ideal for distributed databases and applications.

How It Works

A UUID in PostgreSQL is like a very long, unique serial number that is almost impossible to duplicate. Imagine giving every item in a huge warehouse a special code that no other item anywhere else in the world has. This code is 128 bits long, which means it can create billions of unique IDs without repeating.

PostgreSQL stores UUIDs as a special data type, which is more efficient than storing them as plain text. When you generate a UUID, it uses algorithms that combine time, hardware information, and random numbers to make sure each ID is unique. This is helpful when many systems or users create data independently but still need unique identifiers.

Example

This example shows how to create a table with a UUID column and insert a new row with a generated UUID.

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('Alice');

SELECT * FROM users;

Output

id                                   | name
------------------------------------|-------
550e8400-e29b-41d4-a716-446655440000 | Alice

When to Use

Use UUID when you need unique identifiers that must be globally unique across different systems or databases. For example, in distributed applications where multiple servers create records independently, UUIDs prevent ID conflicts.

They are also useful when you want to avoid exposing sequential IDs for security reasons or when merging data from multiple sources. However, UUIDs are larger than integers, so consider performance and storage needs.

Key Points

• UUID is a 128-bit unique identifier stored efficiently in PostgreSQL.
• It helps avoid ID conflicts in distributed or multi-source systems.
• PostgreSQL supports UUID generation via extensions like uuid-ossp.
• UUIDs are larger than integers, so use them when global uniqueness is more important than storage size.

Key Takeaways

UUID in PostgreSQL provides globally unique identifiers for database rows.

Use the uuid-ossp extension to generate UUIDs with functions like uuid_generate_v4().

UUIDs are ideal for distributed systems where unique IDs must not collide.

They are larger than integers, so consider storage and performance trade-offs.

UUIDs help improve security by avoiding predictable sequential IDs.