Storing UUIDs in PostgreSQL vs MySQL InnoDB: Storage and Index Optimization
Compare UUID storage strategies between PostgreSQL's native 16-byte uuid type and MySQL's BINARY(16) vs VARCHAR(36). Save up to 55% disk space.
PostgreSQL features a native 16-byte 'uuid' data type that handles hex formatting automatically. MySQL lacks a dedicated UUID type; storing UUIDs as VARCHAR(36) consumes 36-144 bytes per row and slows joins. Best practice for MySQL is BINARY(16) with UUID_TO_BIN(..., 1) to reduce storage and preserve time-ordering.
Solve this problem now with our free UUID / GUID Generator
Try it instantly in your browser — zero tracking, 100% client-side privacy.
Specification & Architecture Reference
| Database | Recommended Column Type | Bytes Per Key | Index Footprint (10M Rows) |
|---|---|---|---|
| PostgreSQL | UUID (Native type) | 16 bytes | |
| MySQL (Optimized) | BINARY(16) with swap flag | 16 bytes | |
| MySQL (Sub-optimal) | VARCHAR(36) utf8mb4 | 144 bytes |
The Hidden Cost of Storing UUIDs as Strings
A formatted UUID string contains 36 characters: 32 hexadecimal digits plus 4 hyphens (`xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`). - Stored as a raw string (`VARCHAR(36)`), each row consumes at least **36 bytes** (or more in `utf8mb4`). - Stored as binary, the same 128-bit key consumes only **16 bytes**.
When indexing 50 million rows, this 20-byte difference compounds across every secondary index that references the primary key.
1. PostgreSQL: Native and Simple PostgreSQL includes a dedicated 16-byte `UUID` data type:
CREATE TABLE accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL
);
```2. MySQL / MariaDB: BINARY(16) Strategy MySQL does not have a native `UUID` column type. If you store UUIDs as `VARCHAR(36)`, index lookups run significantly slower.
The recommended approach in MySQL 8.0+ is to use `BINARY(16)`:
CREATE TABLE accounts (
id BINARY(16) PRIMARY KEY,
email VARCHAR(255) NOT NULL-- Insert using time-swapped binary conversion: INSERT INTO accounts (id, email) VALUES (UUID_TO_BIN('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 1), 'dev@mickytool.com');
-- Retrieve formatted back to string: SELECT BIN_TO_UUID(id, 1) AS id, email FROM accounts; ``` The second argument `1` in `UUID_TO_BIN(..., 1)` swaps the timestamp bytes to place the most rapidly changing bits last, improving B-Tree locality.
Generate and format UUIDs for your database migrations using Mickytool's **UUID / GUID Generator**.
Solve this problem now with our free UUID / GUID Generator
Execute this workflow immediately without saving data on remote servers.
Frequently Asked Questions
Why does MySQL's InnoDB suffer more from random UUIDs than PostgreSQL?
InnoDB uses clustered primary indexes where the entire row's data is stored inside the primary key B-Tree leaf pages. A page split in InnoDB moves all row data, making it much more costly than in PostgreSQL heap tables.
Does gen_random_uuid() require an extension in PostgreSQL?
In PostgreSQL 13 and newer, gen_random_uuid() is built into the core engine without requiring the pgcrypto extension.