UUID Primary Keys in PostgreSQL, MySQL, and MongoDB: Performance Differences and Implementation Patterns

UUID primary keys have different performance characteristics in every major database — and knowing which matters before you build

The choice to use UUIDs as primary keys has architectural implications that vary significantly between PostgreSQL, MySQL/MariaDB, and MongoDB. A decision that's trivial in PostgreSQL (where gen_random_uuid() is built-in and UUID indexing is well-optimised) requires more careful planning in MySQL, where UUID format and storage type meaningfully affect performance.

PostgreSQL: the most UUID-friendly relational database

PostgreSQL has native UUID data type support — stored internally as 16 bytes (not the 36-character string representation). This is efficient: a UUID primary key column uses the same storage as two BIGINT columns.

Generating UUIDs:

-- pgcrypto extension (available in most PostgreSQL installations)
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();
-- → f47ac10b-58cc-4372-a567-0e02b2c3d479

-- PostgreSQL 13+ built-in (no extension needed)
SELECT gen_random_uuid();

-- UUID v7 (time-ordered) — PostgreSQL 17+
SELECT uuidv7();
-- → 01934a8c-b400-7xxx-xxxx-xxxxxxxxxxxx (time-ordered prefix)

Table with UUID primary key:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_amount NUMERIC(10,2) NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

The index fragmentation issue in PostgreSQL: UUID v4 with random values fragments the B-tree index (same as discussed in the UUID v4 vs v7 article). PostgreSQL's fill_factor setting for indexes can mitigate fragmentation — setting fill_factor = 70 leaves 30% of each page free, reducing splits:

CREATE INDEX idx_orders_id ON orders(id) WITH (fillfactor = 70);

However, with UUID v7 or pg_ulid, the problem is eliminated at source — time-ordered IDs insert sequentially.

MySQL / MariaDB: UUID requires more care

MySQL's UUID handling is more complex. By default, MySQL stores UUIDs as CHAR(36) — the 36-character hyphenated string representation. This is 36 bytes instead of 16 bytes, and string comparison is slower than integer comparison.

Better: use BINARY(16) with conversion functions:

-- MySQL 8.0+ has built-in UUID_TO_BIN / BIN_TO_UUID
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
    -- UUID_TO_BIN(uuid, 1) = swap time-high bytes to front (makes UUID v1 time-ordered)
    user_id BINARY(16) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    total_amount DECIMAL(10,2) NOT NULL
);

-- Insert
INSERT INTO orders (user_id, total_amount) 
VALUES (UUID_TO_BIN('f47ac10b-58cc-4372-a567-0e02b2c3d479'), 299.99);

-- Query
SELECT BIN_TO_UUID(id), total_amount 
FROM orders 
WHERE id = UUID_TO_BIN('f47ac10b-58cc-4372-a567-0e02b2c3d479');

The 1 parameter in UUID_TO_BIN(uuid, 1) swaps the time-high field of a UUID v1 to the front, making it temporally ordered — essentially doing what UUID v7 does automatically. For UUID v4 (random), this has no effect.

InnoDB and clustered indexes: MySQL InnoDB uses the primary key as the clustered index (the table's physical row order). Random UUID v4 primary keys cause extremely high page fragmentation — far worse than in PostgreSQL. This is why MySQL documentation historically recommended AUTO_INCREMENT over UUIDs and why time-ordered UUIDs (v7, or v1 with byte-swap) are particularly important in MySQL.

Using UUIDs as foreign keys

Foreign keys referencing UUID primary keys require the same data type:

-- PostgreSQL
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL
);

-- The foreign key column should be indexed separately
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Always index foreign key columns. In MySQL, foreign key columns are automatically indexed. In PostgreSQL, they are not — a missing index on a UUID foreign key causes full table scans on cascade operations and JOIN queries.

MongoDB: ObjectId vs UUID

MongoDB's native identifier format is ObjectId — a 12-byte value:

• 4 bytes: Unix timestamp (seconds)
• 5 bytes: random value (per process)
• 3 bytes: incrementing counter (per process, per second)

// MongoDB ObjectId structure
const { ObjectId } = require('mongodb');
const id = new ObjectId();

id.getTimestamp()  // Date object from the embedded timestamp
id.toString()      // "6737f2a4b8c1234567890abc" — 24-char hex string
id.toHexString()   // same

ObjectId advantages:

• Naturally time-ordered (timestamp in the 4 most-significant bytes)
• Compact (12 bytes vs UUID's 16 bytes)
• Contains creation timestamp — no separate created_at field needed for ordering
• Sortable by _id = sorted by creation time

UUID in MongoDB: MongoDB supports UUID storage as BinData subtype 4 (the BSON binary UUID type):

// Using UUID in MongoDB (Node.js driver v5+)
const { UUID } = require('bson');

const doc = {
    _id: new UUID(), // 16-byte UUID stored as BinData
    name: "Example"
};

await collection.insertOne(doc);
await collection.findOne({ _id: new UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479') });

When to use UUID vs ObjectId in MongoDB:

• Cross-system compatibility: UUID if the ID must be shared with relational databases or external systems
• MongoDB-native: ObjectId for its compactness and built-in timestamp
• Existing schema: follow whatever convention the codebase already uses

ORM patterns for UUID primary keys

Sequelize (Node.js, supports PostgreSQL/MySQL):

const Order = sequelize.define('Order', {
    id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true
    },
    totalAmount: {
        type: DataTypes.DECIMAL(10, 2),
        allowNull: false
    }
});

SQLAlchemy (Python):

import uuid
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class Order(Base):
    __tablename__ = 'orders'
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    # SQLAlchemy uses Python uuid.uuid4() by default
    # For PostgreSQL: server_default=text("gen_random_uuid()") generates on the DB side

How to use the UUID Generator on sadiqbd.com

1. Generate UUID v4 — for immediate use as a primary key, session token, or unique identifier
2. Bulk generation — generate multiple UUIDs at once for seeding test data
3. Verify format — validate that an existing string is a valid UUID format
4. Copy in different formats — with or without hyphens, uppercase/lowercase, as URN

Frequently Asked Questions

Should UUID primary keys be generated client-side or server-side? Either is valid. Server-side generation (using gen_random_uuid() in the database) ensures the ID is generated at the point of insertion and never results in a mismatch. Client-side generation (in application code) allows the ID to be known before the insert — useful for referencing the record before it's committed (e.g., creating related records in the same transaction). Both are common; server-side is simpler.

Is there a performance difference between UUID and BIGINT primary keys? Yes — BIGINT is smaller (8 bytes vs 16 bytes), fits more rows per index page, and is faster to compare. For UUID v4 in MySQL InnoDB, the random insert pattern adds substantial overhead. At low-to-moderate scale (millions of rows), the difference is negligible. At hundreds of millions of rows with high write rates, the choice matters.

Is the UUID Generator free? Yes — completely free, no sign-up required.

Try the UUID Generator free at sadiqbd.com — generate any number of UUIDs instantly in v1, v4, or v7 format.