Storing UUIDs as Binary (16 Bytes)

A UUID is a 128-bit value, which naturally fits in 16 bytes. However, many applications store UUIDs as their 36-character string representation, wasting more than half the storage. Converting to binary storage can significantly improve database performance and reduce costs.

Storage comparison per UUID:

Format	Size	Overhead vs binary
Binary (16 bytes)	16 bytes	Baseline
Hex without hyphens	32 bytes	+100%
Standard string	36 bytes	+125%
With overhead (VARCHAR)	37-40 bytes	+137-150%

Impact at scale (100 million rows, 3 UUID columns):

Format	Total UUID storage	Index size
BINARY(16)	4.47 GB	~3.5 GB
CHAR(36)	10.05 GB	~8.0 GB
Savings	5.58 GB	~4.5 GB

MySQL binary storage:

-- Table definition
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    customer_id BINARY(16) NOT NULL,
    INDEX idx_customer (customer_id)
);

-- MySQL 8.0+ UUID conversion functions
INSERT INTO orders (id, customer_id)
VALUES (UUID_TO_BIN(UUID(), true), UUID_TO_BIN(@customer_uuid));

-- The 'true' parameter swaps time fields for UUID v1 ordering
-- For UUID v7, use: UUID_TO_BIN(UUID(), false) or just UUID_TO_BIN(UUID())

SELECT BIN_TO_UUID(id, true) as id FROM orders;

Application-level conversion (JavaScript):

// UUID string to 16-byte Buffer
function uuidToBytes(uuid) {
  const hex = uuid.replace(/-/g, '');
  return Buffer.from(hex, 'hex');
}

// 16-byte Buffer to UUID string
function bytesToUuid(bytes) {
  const hex = bytes.toString('hex');
  return [
    hex.slice(0, 8), hex.slice(8, 12), hex.slice(12, 16),
    hex.slice(16, 20), hex.slice(20)
  ].join('-');
}

Application-level conversion (Python):

import uuid

id = uuid.uuid4()
raw = id.bytes        # 16 bytes
restored = uuid.UUID(bytes=raw)
assert id == restored

Why binary storage improves performance beyond just size:

1. Smaller indexes: B-tree nodes hold more keys per page, meaning fewer disk reads per lookup. A 16-byte key allows roughly twice as many entries per page as a 36-byte key.
2. CPU comparison speed: Comparing 16 bytes is faster than comparing 36 characters, especially when the CPU can use 128-bit SIMD instructions.
3. Memory efficiency: Smaller indexes fit better in the buffer pool/page cache, reducing disk I/O.
4. Network transfer: Binary UUIDs in query results consume less bandwidth, which matters for large result sets.

Debugging concern: Binary UUIDs are not human-readable in query results. Always provide a conversion layer in your application or use database functions (BIN_TO_UUID in MySQL, automatic in PostgreSQL's uuid type) when debugging.