UUID vs SERIAL in PostgreSQL: Pros, Cons & When to Use Each

PostgreSQL UUID vs SERIAL: Choosing the Right Primary Key

One of the first decisions you make when designing a table is: “What’s my primary key?” SERIAL (auto-incrementing integers) is the traditional choice—simple, small, fast. UUID (globally unique identifiers) is the modern choice—bigger, more complex, but brings distributed system superpowers.

I’ve made this choice dozens of times, and there’s no universal answer. SERIAL is perfect for traditional monolithic applications. UUID shines in microservices, distributed systems, and APIs where you can’t rely on a central sequence generator. The wrong choice causes problems down the line—you can’t easily change primary key strategy after launch.

This guide walks you through both approaches, shows you the real trade-offs, and helps you choose based on your specific situation.

What You’ll Learn

This guide covers:

SERIAL: Sequential auto-incrementing integers
UUID: Globally unique 128-bit identifiers
Storage size and performance impact
Security implications of sequential IDs
When to use each
Distributed systems considerations
Hybrid approaches

SERIAL: The Traditional Approach

Creating SERIAL Primary Keys

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data (user_id auto-increments)
INSERT INTO users (username, email) VALUES ('james_wilson', '[email protected]');
INSERT INTO users (username, email) VALUES ('mary_johnson', '[email protected]');
INSERT INTO users (username, email) VALUES ('david_miller', '[email protected]');

SELECT * FROM users;

Result:

user_id | username      | email              | created_at
-------+---------------+--------------------+---------------------
     1 | james_wilson   | [email protected]  | 2026-02-21 10:00:00
     2 | mary_johnson   | [email protected]   | 2026-02-21 10:00:01
     3 | david_miller   | [email protected]  | 2026-02-21 10:00:02

SERIAL is a convenience wrapper around:

INTEGER column
SEQUENCE object
DEFAULT clause using nextval()

BIGSERIAL for Large Tables

CREATE TABLE transactions (
    transaction_id BIGSERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    amount DECIMAL(10, 2),
    transaction_date TIMESTAMP DEFAULT NOW()
);

-- BIGSERIAL supports 9,223,372,036,854,775,807 values (63 bits + sign)
-- Use BIGSERIAL for tables expecting > 2 billion rows

SERIAL Characteristics

Storage Size:   4 bytes (INTEGER) or 8 bytes (BIGSERIAL)
Range:          1 to 2,147,483,647 (32-bit) or ~9 quintillion (64-bit)
Generation:     Database-controlled sequence
Uniqueness:     Guaranteed within table (primary key constraint)
Reusable:       Can gap if transactions rollback
Security:       Reveals table size and growth rate to anyone
Performance:    Very fast (sequence lookup)
Distributed:    Problematic (one database generates all IDs)

UUID: Globally Unique Identifiers

Creating UUID Primary Keys

CREATE TABLE products (
    product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data (UUID auto-generated)
INSERT INTO products (product_name) VALUES ('Laptop');
INSERT INTO products (product_name) VALUES ('Monitor');
INSERT INTO products (product_name) VALUES ('Keyboard');

SELECT product_id, product_name FROM products;

Result:

product_id                           | product_name
-------------------------------------+---------------
550e8400-e29b-41d4-a716-446655440000| Laptop
6ba7b810-9dad-11d1-80b4-00c04fd430c8| Monitor
f47ac10b-58cc-4372-a567-0e02b2c3d479| Keyboard

UUID Generation Methods

-- Method 1: gen_random_uuid() (recommended, v4 - truly random)
SELECT gen_random_uuid();

-- Method 2: uuid_generate_v1() (time-based, requires uuid-ossp extension)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();

-- Method 3: uuid_generate_v4() (random, from uuid-ossp)
SELECT uuid_generate_v4();

-- Method 4: Explicit UUID
INSERT INTO products (product_id, product_name)
VALUES ('550e8400-e29b-41d4-a716-446655440000'::UUID, 'Custom Product');

UUID Characteristics

Storage Size:   16 bytes (fixed)
Range:          2^128 possible values
Generation:     Client or database-generated
Uniqueness:     Globally unique across systems
Reusable:       Never (astronomically unlikely collision)
Security:       Unguessable, doesn't reveal data
Performance:    Slower index lookups than sequential
Distributed:    Excellent (generate on any node)
Collisions:     Virtually impossible

SERIAL vs UUID: Comparison

Performance Impact

-- Create test table with SERIAL
CREATE TABLE serial_test (
    id SERIAL PRIMARY KEY,
    data VARCHAR(255)
);

-- Create test table with UUID
CREATE TABLE uuid_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data VARCHAR(255)
);

-- SERIAL index (sequential)
-- - Insert performance: Very fast (append to sequence)
-- - Index size: Small (4 bytes per row)
-- - Lookup speed: Very fast (predictable access)
-- - Range queries: Very efficient (sequential)

-- UUID index (random)
-- - Insert performance: Slower (random insertion into index)
-- - Index size: Larger (16 bytes per row)
-- - Lookup speed: Slower (random scattered data)
-- - Range queries: Less efficient (no ordering)

Storage Comparison

SELECT
    pg_size_pretty(pg_total_relation_size('serial_test')) as serial_size,
    pg_size_pretty(pg_total_relation_size('uuid_test')) as uuid_size;

With 1 million rows:

SERIAL: ~50 MB
UUID: ~100 MB (about 2x larger)

The UUID index causes more page splits and worse cache locality.

Security Implications

-- SERIAL: Anyone can guess the next ID
SELECT * FROM users WHERE user_id = 1;       -- Guessable
SELECT * FROM users WHERE user_id = 2;       -- Predictable sequence

-- UUID: Unguessable
SELECT * FROM orders WHERE order_id = '550e8400-e29b-41d4-a716-446655440000';
-- Can't guess valid order_id without knowing it

UUIDs prevent:

Enumeration attacks (guessing IDs to access others’ data)
Inferring growth rate or volume
Sequential ID manipulation

When to Use SERIAL

Good for SERIAL:

Single Database Only

-- SERIAL works great here
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

High-Performance Applications

-- SERIAL is fastest for read-heavy workloads
CREATE TABLE logs (
    log_id BIGSERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP
);

Sequential IDs Important

-- When order matters (invoice numbers, ticket IDs)
CREATE TABLE invoices (
    invoice_number SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    amount DECIMAL(10, 2)
);

INSERT INTO invoices (customer_id, amount) VALUES (1, 1000.00);
-- Automatically gets invoice_number = 1, 2, 3...

Internal Tables (not exposed to users)

-- Fine for internal ETL, caching, staging
CREATE TABLE staging_data (
    id SERIAL PRIMARY KEY,
    raw_json TEXT
);

When to Use UUID

Good for UUID:

Distributed Systems

-- Multiple databases generate IDs independently
CREATE TABLE distributed_orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(user_id),
    amount DECIMAL(10, 2)
);

-- Two databases can safely generate UUIDs without coordination

User-Facing IDs

-- Hard to guess your data
CREATE TABLE user_accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255)
);

-- Users can't enumerate all accounts by trying sequential IDs

Microservices Architecture

-- Each service generates its own IDs
CREATE TABLE api_clients (
    client_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    api_key TEXT UNIQUE,
    created_at TIMESTAMP
);

Multi-Tenant Systems

-- Each tenant's data is isolated
CREATE TABLE tenant_projects (
    project_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES tenants(tenant_id),
    project_name VARCHAR(255)
);

Real-Time Collaboration

-- Generate IDs before saving to database
CREATE TABLE document_changes (
    change_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(document_id),
    change_type VARCHAR(50),
    timestamp TIMESTAMP DEFAULT NOW()
);

Hybrid Approaches

UUID Primary Key + SERIAL Display ID

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_number SERIAL UNIQUE,  -- User-facing sequential ID
    customer_id INTEGER,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Users see: Order #1, Order #2, Order #3...
-- Database uses: UUIDs for safety and distribution
SELECT order_number, order_id, customer_id, amount FROM orders LIMIT 3;

Result:

order_number | order_id                         | customer_id | amount
-------------+----------------------------------+-------------+--------
            1| 550e8400-e29b-41d4-a716-44665540| 1           | 500.00
            2| 6ba7b810-9dad-11d1-80b4-00c04fd4| 2           | 750.00
            3| f47ac10b-58cc-4372-a567-0e02b2c3| 3           | 250.00

Best of both worlds:

Users see sequential, readable numbers
Database uses secure, distributed UUIDs

Composite Key with UUID and SERIAL

CREATE TABLE document_revisions (
    document_id UUID REFERENCES documents(document_id),
    revision_number SERIAL,
    content TEXT,
    revised_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (document_id, revision_number)
);

-- Each document has revisions 1, 2, 3... (SERIAL resets per document)

Real-World Patterns

SaaS Application

-- Multi-tenant SaaS needs UUID for security
CREATE TABLE accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_name VARCHAR(255),
    stripe_customer_id VARCHAR(255) UNIQUE
);

CREATE TABLE projects (
    project_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID REFERENCES accounts(account_id),
    project_name VARCHAR(255)
);

CREATE TABLE tasks (
    task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID REFERENCES projects(project_id),
    title VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Users can't guess other accounts' IDs
SELECT * FROM projects WHERE project_id = '550e8400-e29b-41d4-a716-446655440000';

High-Volume Logging

-- Logging needs speed, not security
CREATE TABLE application_logs (
    log_id BIGSERIAL PRIMARY KEY,
    level VARCHAR(10),  -- ERROR, WARN, INFO, DEBUG
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Millions of logs per day, SERIAL is fastest
INSERT INTO application_logs (level, message) VALUES ('INFO', 'User login');

Migration Path

-- Start with SERIAL for simplicity
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

-- Later, add UUID column for future migration
ALTER TABLE users ADD COLUMN uuid UUID UNIQUE DEFAULT gen_random_uuid();

-- Eventually migrate foreign keys to UUID
-- Then drop SERIAL column

Common Errors & Solutions

Error: “uuid-ossp not installed”

Problem:

SELECT uuid_generate_v1();  -- ERROR: function uuid_generate_v1 does not exist

Solution:

Create the extension:

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v1();  -- Now works

Or use built-in gen_random_uuid():

SELECT gen_random_uuid();  -- No extension needed (PostgreSQL 13+)

Error: “duplicate key value violates unique constraint”

Problem:

INSERT INTO products (product_id, product_name)
VALUES ('550e8400-e29b-41d4-a716-446655440000'::UUID, 'Product A');

INSERT INTO products (product_id, product_name)
VALUES ('550e8400-e29b-41d4-a716-446655440000'::UUID, 'Product B');  -- ERROR: Duplicate

Solution:

UUIDs must be unique. Generate new ones:

INSERT INTO products (product_name) VALUES ('Product B');
-- Uses DEFAULT gen_random_uuid() instead

Performance Degradation with UUID

Problem:

-- Large table with UUID primary key
CREATE INDEX idx_uuid_lookup ON large_table(uuid_column);

-- Queries slower than expected
SELECT * FROM large_table WHERE uuid_column = '550e8400-e29b-41d4-a716-446655440000';

Solution:

This is expected behavior. UUID has worse cache locality than SERIAL. If performance is critical:

Use SERIAL if possible
Add SERIAL surrogate key
Optimize queries differently (partitioning, caching)

Performance Tips

1. Choose Early

Changing from SERIAL to UUID later requires:

Adding UUID column
Migrating all foreign keys
Updating all indexes
Dropping old column

Better to decide upfront.

2. Use gen_random_uuid() Not uuid_generate_v4()

-- Better: Built-in, no extension needed
DEFAULT gen_random_uuid()

-- Older: Requires uuid-ossp extension
DEFAULT uuid_generate_v4()

3. Index UUID Columns

CREATE INDEX idx_user_id ON orders(user_id);

-- Essential for performance with UUID

4. Consider Partial Indexes for UUID Filtering

CREATE INDEX idx_active_users ON users(user_id)
WHERE is_active = TRUE;

-- Smaller index for common queries

FAQ

Q: Can I convert from SERIAL to UUID? A: Yes, but complex. Add UUID column, migrate data, update foreign keys, then drop SERIAL. Plan ahead.

Q: Is UUID slower than SERIAL? A: Yes, noticeably for very large tables. UUID is ~10-20% slower for random lookups. For distributed systems, the security benefit outweighs the cost.

Q: What’s the collision risk with UUID? A: Astronomically low. You’d need to generate 2^60 UUIDs to have a 50% collision chance. Practically impossible.

Q: Should I expose SERIAL IDs in APIs? A: Not recommended. Use UUID or obscure the ID (like invoice numbers). SERIAL exposes your data volume.

Q: Can I reuse deleted SERIAL IDs? A: No, sequences never go backward. Gaps are normal and expected.

Q: Is UUID or SERIAL better for PostgreSQL replication? A: UUID is better. SERIAL requires coordination between replicas to avoid conflicts.

Continue mastering PostgreSQL design:

PostgreSQL Data Types Reference - All data types explained
JSON Functions & Operators - Store semi-structured data
Date Functions & Formatting - Temporal data handling

Choosing the right primary key is foundational database design. Make the decision that fits your architecture - SERIAL for simplicity, UUID for distribution and security.

PostgreSQL UUID vs SERIAL: Choosing the Right Primary Key Strategy

PostgreSQL UUID vs SERIAL: Choosing the Right Primary Key

What You’ll Learn

SERIAL: The Traditional Approach

Creating SERIAL Primary Keys

BIGSERIAL for Large Tables

SERIAL Characteristics

UUID: Globally Unique Identifiers

Creating UUID Primary Keys

UUID Generation Methods

UUID Characteristics

SERIAL vs UUID: Comparison

Performance Impact

Storage Comparison

Security Implications

When to Use SERIAL

Good for SERIAL:

When to Use UUID

Good for UUID:

Hybrid Approaches

UUID Primary Key + SERIAL Display ID

Composite Key with UUID and SERIAL

Real-World Patterns

SaaS Application

High-Volume Logging

Migration Path

Common Errors & Solutions

Error: “uuid-ossp not installed”

Error: “duplicate key value violates unique constraint”

Performance Degradation with UUID

Performance Tips

FAQ

Related Topics