UUID Storage in Databases — BINARY(16) vs VARCHAR(36) vs Native Type

UUID storage format has a real performance impact at scale. A 100-million-row table using VARCHAR(36) for a UUID primary key wastes about 2GB compared to BINARY(16) — before counting indexes. The right choice depends on your database engine, your application's query patterns, and how much you care about index fragmentation.

Storage Size: Native vs VARCHAR vs BINARY

Format	Bytes	Database Support	Human-Readable
Native UUID / UNIQUEIDENTIFIER	16	PostgreSQL, SQL Server, MariaDB 10.7+	Yes (on display)
BINARY(16)	16	MySQL, MariaDB	No (hex display only)
CHAR(36)	36	All	Yes
VARCHAR(36)	37 (+ length byte)	All	Yes

At 100 million rows with a UUID primary key:

• BINARY(16): ~1.5 GB for the column data
• VARCHAR(36): ~3.5 GB for the column data — 2 GB wasted

The difference compounds for every secondary index that includes the UUID column.

PostgreSQL — Use the Native UUID Type

-- PostgreSQL has a native UUID type — use it:
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Or generate in application and pass in:
INSERT INTO orders (id, status) VALUES ($1, $2);

-- gen_random_uuid() requires pgcrypto in older PostgreSQL versions:
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- PostgreSQL 13+ has gen_random_uuid() built-in, no extension needed.

-- UUID v7 (PostgreSQL 17+):
SELECT uuidv7();

PostgreSQL stores UUID as 16 bytes internally, displays and accepts the standard hyphenated string format. The native type validates format on insert — invalid UUID strings are rejected at the database level.

Sell Custom Apparel — We Handle Printing & Free Shipping

MySQL — BINARY(16) Is Better Than VARCHAR(36)

MySQL has no native UUID column type (before MariaDB 10.7). The two common options:

-- Option A: VARCHAR(36) — readable but large:
CREATE TABLE orders (
    id VARCHAR(36) PRIMARY KEY,
    status VARCHAR(50)
);
INSERT INTO orders VALUES (UUID(), 'pending');

-- Option B: BINARY(16) — compact, faster indexes:
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    status VARCHAR(50)
);

-- Insert with UUID_TO_BIN() — strips dashes, optionally swaps bytes:
INSERT INTO orders VALUES (UUID_TO_BIN(UUID(), 1), 'pending');

-- Query:
SELECT BIN_TO_UUID(id, 1) as id, status FROM orders;

-- The second argument (1) swaps time bytes for better sequential ordering.
-- Use 0 for standard byte order, 1 for time-swap optimization.

MySQL's UUID_TO_BIN(uuid, 1) with the swap flag reorders the time bytes of UUID v1 so they insert sequentially. For UUID v4 (random), this flag has no benefit — use v7 if you need sequential inserts.

SQL Server — UNIQUEIDENTIFIER and Index Fragmentation

-- SQL Server native UUID type:
CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    Status NVARCHAR(50)
);

-- The problem: NEWID() generates random GUIDs — random inserts = high fragmentation.
-- Solution: NEWSEQUENTIALID() generates sequential GUIDs for the server session:
CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Status NVARCHAR(50)
);

-- NEWSEQUENTIALID() generates UUIDs that increase over time on this server.
-- Limitation: values reset on server restart — only sequential within a session.

-- Alternative: generate UUID in application code and use INSERT with explicit value.
-- For the best sequential behavior in SQL Server, use sequential GUIDs from a library.

UNIQUEIDENTIFIER stores as 16 bytes. NEWID() is the equivalent of UUID v4 — random. For primary keys that must be indexed efficiently, use NEWSEQUENTIALID() or an application-level sequential UUID library.

UUID v7 Solves the Fragmentation Problem

The fundamental issue with UUID v4 as a primary key is random index insertion — every new row inserts at an essentially random position in the B-tree, causing page splits and index fragmentation.

UUID v7 fixes this by embedding a millisecond timestamp prefix. UUIDs generated in sequence are lexicographically ordered — they always insert at or near the end of the B-tree index, just like an auto-increment integer:

-- PostgreSQL 17+:
ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

-- With gorm and uuid v7 library in Go:
import "github.com/gofrs/uuid/v5"
id, _ := uuid.NewV7()

-- JavaScript:
import { v7 as uuidv7 } from 'uuid'; // npm package
const id = uuidv7();

If you are starting a new project with UUID primary keys and performance at scale matters, choose UUID v7 over v4 for database keys. Use v4 for everything else (API identifiers, log correlation, external-facing IDs).

Frequently Asked Questions

Should I store UUID as BINARY(16) or VARCHAR(36) in MySQL?

BINARY(16) for production systems at scale — it is half the size of VARCHAR(36), uses less memory, and has faster index operations. VARCHAR(36) is acceptable for small tables or when human-readable rows in the database matter more than performance.

Does PostgreSQL store UUID as 16 bytes or 36 characters?

PostgreSQL stores the native UUID type as 16 bytes internally. The 36-character hyphenated representation is only used for display and input — not for storage. Index operations use the 16-byte representation.

Why does UUID cause index fragmentation in MySQL?

UUID v4 is random — each new UUID inserts at a random position in the B-tree primary key index, causing page splits. Auto-increment inserts at the end of the index, which is far more efficient. UUID v7 or ULID solve this by being time-ordered.

What is UUID_TO_BIN() in MySQL?

A MySQL function (8.0+) that converts a UUID string to a 16-byte binary value for BINARY(16) storage. The optional second argument (1) swaps the time bytes of UUID v1 for better index ordering. BIN_TO_UUID() reverses the conversion for display.

Can I mix VARCHAR and BINARY UUID storage in the same application?

Technically yes, but it causes bugs. Comparison between VARCHAR("550e8400...") and BINARY(16) values will always fail because the types differ. Pick one storage format and use it consistently across all tables that reference the same UUID.