Storage & Implementation

⚠️ The Golden Rule of Storage

NEVER store UUIDs as VARCHAR(36) strings in a large database.
Storing 36 bytes per row kills performance. It bloats the index, consumes 2.5x more RAM than necessary, and slows down joins. Always store UUIDs as 128-bit integers or 16-byte binary data.

PostgreSQL (Best Support)

Postgres has a native uuid data type. It stores the ID as a 128-bit integer automatically, ensuring maximum efficiency.

sql

-- 1. Enable extension (if needed for generation)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 2. Create table with native type
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT
);

SQL

MySQL / MariaDB

MySQL lacks a native uuid type. Use BINARY(16) and helper functions to convert. Do not use VARCHAR.

sql

-- Create table with BINARY(16)
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(255)
);

-- Insert using UUID_TO_BIN (MySQL 8.0+)
INSERT INTO users (id, name) VALUES (UUID_TO_BIN(UUID()), 'Alice');

-- Select human-readable string
SELECT BIN_TO_UUID(id), name FROM users;

URL Optimization (Base62)

UUIDs in URLs (e.g., /user/123e4567-e89b-12d3-a456-426614174000) are long and ugly. Convert the 128-bit integer to Base62 (0-9, a-z, A-Z) to compress it to 22 characters.

123e4567-e89b-12d3-a456-426614174000

→

2qY91rW3d4s5G6j8h9k0L