GUID/UUID Database Performance & Indexing Guide

Overview: Why database performance matters

The primary performance concern with GUIDs / UUIDs in databases is their impact on index behavior, particularly B-tree indexes used by most relational databases. Random identifiers can cause poor insert performance due to index fragmentation, while sequential identifiers maintain better locality.

B-tree indexes and GUID / UUID insertion patterns

Most relational databases (PostgreSQL, MySQL, SQL Server, Oracle, SQLite and many others) use B-tree indexes for primary keys and secondary indexes. B-tree structures maintain sorted data in leaf pages, with internal nodes pointing to child pages.

How B-tree indexes work

A B-tree index organizes data in a hierarchical tree structure where:

• Leaf pages contain the actual index entries (key + pointer to row)
• Internal nodes contain keys and pointers to child pages
• Pages have fixed size (typically 8-16 KB depending on database)
• Sorted order is maintained for efficient range queries and lookups

Sequential vs random insertion

Index fragmentation explained

Index fragmentation occurs when the logical order of index pages does not match their physical order on disk. This happens with random UUIDs because insertions split pages throughout the index tree.

Types of fragmentation

• Logical fragmentation (internal): Pages are not fully packed, wasting space within each page
• Physical fragmentation (external): Logically consecutive pages are not physically adjacent on disk
• Page splits: When a page is full, it splits into two half-full pages, reducing density

Performance impact

• Range scans: Slower because consecutive pages may require random disk I/O
• Buffer pool efficiency: More pages needed in memory for same data volume
• Insert performance: Additional I/O to read, modify, and write split pages
• Index size: Fragmented indexes consume more storage space (30-50% larger)

Mitigation strategies

• Use GUID / UUID v7: Time-ordered UUIDs reduce fragmentation significantly
• Rebuild indexes: Periodic index rebuilds to defragment (requires maintenance window)
• Fillfactor tuning: Leave space in pages to accommodate future inserts (database-specific)
• Partition tables: Split large tables to limit index size and fragmentation scope

Storage considerations

GUIDs / UUIDs are 128 bits (16 bytes), compared to typical integer primary keys like BIGINT (8 bytes) or INT (4 bytes). This affects storage, memory and I/O.

Storage overhead

Memory and cache impact

• Buffer pool: Larger keys mean fewer rows fit per page, reducing cache efficiency
• Index nodes: Internal B-tree nodes hold fewer keys, increasing tree height and lookup cost
• Foreign keys: Every foreign key reference also stores 16 bytes (multiplied across relationships)
• Network transfer: Larger identifiers increase query result payload size

Database-specific considerations

PostgreSQL

PostgreSQL has native UUID type and excellent UUID support.

• Native type: Use UUID type (16 bytes storage)
• Extensions: uuid-ossp for v1/v4, pgcrypto for v4
• UUID v7: Use custom function or application-generated values
• Index type: B-tree by default, consider BRIN for time-ordered UUIDs on large tables

-- Using UUID v4 (random)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Using UUID v7 (time-ordered, application-generated)
CREATE TABLE events (
    id UUID PRIMARY KEY, -- Generated by application
    event_type VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Index performance check
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

MySQL / MariaDB

MySQL 8.0+ and MariaDB 10.7+ have native UUID support. Earlier versions require BINARY(16).

• MySQL 8.0+: UUID type and UUID_TO_BIN() / BIN_TO_UUID() functions
• Older versions: Use BINARY(16) for storage
• InnoDB: Clustered index on primary key makes sequential inserts critical
• UUID v1 reordering: MySQL's UUID_TO_BIN(uuid, 1) reorders v1 for time-sorting

-- MySQL 8.0+ with native UUID
CREATE TABLE products (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
);

-- Insert with UUID conversion
INSERT INTO products (id, name)
VALUES (UUID_TO_BIN(UUID()), 'Product Name');

-- Query with conversion back
SELECT BIN_TO_UUID(id) as id, name
FROM products
WHERE id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

-- Check fragmentation
SELECT TABLE_NAME, DATA_FREE, DATA_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database';

Microsoft SQL Server

SQL Server has UNIQUEIDENTIFIER type and NEWSEQUENTIALID() for sequential GUIDs.

• Native type: UNIQUEIDENTIFIER (16 bytes)
• Sequential GUIDs: NEWSEQUENTIALID() generates time-based sequential values
• Byte ordering: SQL Server reorders bytes differently than RFC 4122 standard
• Clustered indexes: Sequential GUIDs essential for clustered primary keys

-- Using NEWSEQUENTIALID() for sequential GUIDs
CREATE TABLE Orders (
    OrderId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    CustomerName NVARCHAR(200) NOT NULL,
    OrderDate DATETIME2 DEFAULT GETDATE()
);

-- Index fragmentation check
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

SQLite

SQLite has no native UUID type but can store as BLOB or TEXT.

• Storage: Use BLOB (16 bytes) for efficiency or TEXT for readability
• B-tree index: SQLite uses B-tree indexes; sequential UUIDs improve performance
• Generation: No built-in UUID functions, use application layer
• Page size: Default 4KB pages fill faster with UUIDs vs integers

-- Store as BLOB for efficiency
CREATE TABLE sessions (
    id BLOB PRIMARY KEY,  -- 16 bytes
    user_id INTEGER NOT NULL,
    expires_at INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert (application generates UUID and converts to bytes)
-- Example with Python: uuid.uuid4().bytes
INSERT INTO sessions (id, user_id, expires_at)
VALUES (X'550e8400e29b41d4a716446655440000', 1, 1735689600);

-- Query
SELECT hex(id) as id, user_id FROM sessions;

MongoDB

MongoDB uses ObjectId by default but supports UUID storage as BinData.

• ObjectId: 12-byte time-ordered identifier (similar concept to UUID v7)
• UUID support: Store as BinData subtype 4 (UUID)
• Indexes: B-tree style indexes benefit from time-ordered identifiers
• Sharding: Random UUIDs distribute well across shards (ObjectId also works)

// Using ObjectId (default, recommended)
db.users.insertOne({
    _id: ObjectId(),  // 12 bytes, time-ordered
    username: "john_doe",
    createdAt: new Date()
});

// Using UUID (if required for interoperability)
db.users.insertOne({
    _id: UUID("550e8400-e29b-41d4-a716-446655440000"),
    username: "jane_doe",
    createdAt: new Date()
});

// Index stats
db.users.stats().indexSizes;

Practical recommendations

Choosing the right GUID / UUID version

Database optimization checklist

• Use native UUID types: UUID (PostgreSQL), BINARY(16) (MySQL), UNIQUEIDENTIFIER (SQL Server)
• Never store as VARCHAR: String storage wastes 2-3x space and degrades performance
• Prefer UUID v7 for primary keys: Minimizes fragmentation in B-tree indexes
• Monitor fragmentation: Check index health regularly and rebuild when needed
• Consider partitioning: For very large tables (100M+ rows), partition by time range
• Tune fillfactor: Leave space in index pages for future inserts (e.g., 90% fillfactor)
• Benchmark your workload: Read-heavy vs write-heavy patterns impact UUID choice

When NOT to use UUIDs

• Small tables: Integer IDs are simpler and more efficient for small datasets
• Extreme performance requirements: Auto-increment integers provide best performance
• Storage-constrained systems: UUIDs consume 2-4x more space than integers
• Simple single-database apps: No need for distributed ID generation

Performance benchmarks

Real-world performance varies by database, hardware, and workload. These approximate benchmarks illustrate typical differences:

Insert performance comparison

Approximate benchmarks on PostgreSQL 15 with 16GB RAM, SSD storage. Actual performance varies by hardware and configuration.

Query performance impact

• Point lookups: GUID / UUID indexes ~10-20% slower than integer indexes (larger keys)
• Range scans: Sequential GUIDs / UUIDs comparable to integers; random GUIDs / UUIDs 2-3x slower
• Join performance: GUID / UUID joins slightly slower due to larger key comparison
• Index-only scans: GUIDs / UUIDs reduce number of rows cached per page

Conclusion

GUIDs / UUIDs offer significant advantages for distributed systems and avoiding ID coordination, but come with performance trade-offs in databases. The choice between random (GUID / UUID v4) and time-ordered (GUID / UUID v7) identifiers has substantial impact on index behavior and insert performance.

For most database use cases, GUID / UUID v7 provides the best balance: standard-compliant, time-ordered for index efficiency, and no coordination overhead. Random GUID / UUID v4 remains appropriate for privacy-sensitive contexts or public API identifiers where time leakage is unacceptable.

Always use native GUID / UUID storage types, monitor index fragmentation and benchmark with representative workloads to optimize for your specific requirements.