UUID v4 vs. UUID v1: Primary Key Optimization & Collision Probabilities

### What is a Universally Unique Identifier (UUID)?

A **UUID** (Universally Unique Identifier) is a 128-bit identifier standardized by RFC 4122. UUIDs are formatted as 32 hexadecimal digits displayed in 5 groups separated by hyphens (`8-4-4-4-12` characters, total 36 characters including hyphens).

Example: `550e8400-e29b-41d4-a716-446655440000`

---

### UUID Version 4 vs Version 1 Comparison

| Feature | UUID Version 4 | UUID Version 1 |
| :--- | :--- | :--- |
| **Generation Basis** | Cryptographically Pseudo-Random Numbers | Host MAC Address + High-Resolution Timestamp |
| **Privacy / Anonymity** | High (Contains no hardware or time signals) | Low (Exposes system clock & network adapter MAC) |
| **B-Tree Indexing Performance** | Low (Random distribution causes page splits) | High (Sequential time component improves cache hits) |
| **Collision Rate** | 1 in $2^{122}$ ($5.3 imes 10^{36}$) | Virtually zero per physical node |

---

### Collision Mathematics of UUID v4

UUID Version 4 allocates 122 bits to pure random entropy (6 bits are reserved for variant and version markers).

The probability $P$ of a collision among $N$ generated UUIDs can be approximated using the Birthday Problem formula:

$P approx 1 - e^{-rac{N^2}{2^{123}}}$

To achieve a **1 in a billion ($10^{-9}$)** chance of a single collision, a database system would need to generate approximately **$2.6 imes 10^{13}$ (26 trillion) UUIDs**.

---

### Best Practices for SQL Database primary keys

1. **Use UUIDv7 or Sequential UUIDs (v1/v6)** when writing high-volume B-tree indexed SQL tables (PostgreSQL, MySQL InnoDB) to avoid index fragmentation.
2. **Use UUIDv4** for API tokens, session IDs, client-facing resource URLs, and microservice payload identifiers where hardware privacy and randomness are mandatory.