UUIDs are 128-bit identifiers — but most developers store them as 36-character strings, which is far from optimal in terms of size and performance.
In reality, a UUID is just 16 bytes. So why store it as a long, slow string?
Let’s explore how to store UUIDs in binary format to reduce disk usage, improve indexing, and speed up your database — with examples in MySQL, PostgreSQL, and MongoDB.
🧬 Why Binary UUIDs?
| Format | Size (bytes) | Storage Type |
|---|---|---|
| UUID text | 36 | CHAR(36) |
| UUID hex | 32 | CHAR(32) |
| Binary UUID | 16 | BINARY(16) |
| Native UUID | 16 | uuid (Postgres) |
Benefits:
- 50% smaller than string UUIDs
- Faster joins and lookups
- More cache-efficient indexes
- Fewer pages read = faster queries
🛠️ MySQL: Using BINARY(16)
🔧 Schema
CREATE TABLE users (
id BINARY(16) PRIMARY KEY,
name VARCHAR(255)
);🔁 Converting UUID to Binary
INSERT INTO users (id, name)
VALUES (UUID_TO_BIN(UUID()), 'Alice');The UUID_TO_BIN() function converts a UUID string to 16-byte binary.
🔁 Converting Binary to UUID
SELECT BIN_TO_UUID(id) AS uuid, name FROM users;🔁 Recommended Index Format
Use UUID_TO_BIN(UUID(), 1) to reorder UUID bytes for index optimization (for InnoDB clustered indexes).
🐘 PostgreSQL: Use the Native `uuid` Type
Postgres has a built-in uuid column type that stores values as compact binary.
🔧 Schema
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT
);📦 Performance Tip
- No need to use
BINARY(16)—uuidis efficient and binary under the hood - Supported by GIN/GiST indexes if needed
- Compatible with
pgcryptoandgen_random_uuid()
🍃 MongoDB: Use `BinData` Type
MongoDB stores UUIDs as BinData(4, ...) — a 16-byte binary field.
🔧 Insert Example
In MongoDB shell or driver:
{
_id: UUID("550e8400-e29b-41d4-a716-446655440000"),
name: "Alice"
}Under the hood, this becomes:
"_id": BinData(4, "1Q6EAOKbQdSnFkRmVUQAAA==")Use the UUID helper:
UUID("...") // safe from string parsing errors🔎 Query
db.users.find({ _id: UUID("550e8400-e29b-41d4-a716-446655440000") })📈 Benchmarks (Estimated)
| Format | Query Speed | Disk Space | Index Size | Notes |
|---|---|---|---|---|
CHAR(36) | ❌ Slow | ❌ Large | ❌ Large | Inefficient all around |
BINARY(16) | ✅ Fast | ✅ Small | ✅ Compact | Recommended in MySQL |
uuid (PG) | ✅ Native | ✅ Compact | ✅ Indexable | Built-in Postgres type |
BinData | ✅ Optimized | ✅ Compact | ✅ Fast | Native in MongoDB |
🧪 Working With Binary UUIDs in Code
Python
import uuid
# Get binary representation
binary_uuid = uuid.uuid4().bytes
# Convert back
restored = uuid.UUID(bytes=binary_uuid)JavaScript (Node.js)
const { v4: uuidv4, parse, stringify } = require('uuid');
const u = uuidv4();
const binary = Buffer.from(parse(u)); // 16-byte binary
const restored = stringify(binary);🔐 Tips & Best Practices
- Use binary UUIDs in storage, but convert to string at API boundaries
- Add human-readable indexes only if needed for debugging
- For MySQL, use
UUID_TO_BIN(uuid, 1)for better index locality - Don’t truncate UUIDs — use base64 if you want short string IDs
Final Thoughts
Binary UUIDs are easy to implement and deliver real-world gains in performance and efficiency.
Whether you're scaling up in MySQL, tuning PostgreSQL, or designing a MongoDB schema — storing UUIDs as compact 16-byte values will save space, boost speed, and simplify indexing.
📦 UUIDs don’t need to be bulky to be powerful.