UUID

-- 1) Postgres has a built-in UUID type — 16 bytes
CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       TEXT UNIQUE NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- gen_random_uuid() is in core Postgres since 13 (was in pgcrypto before).

-- Insert
INSERT INTO users (email) VALUES ('mara@example.com') RETURNING id;
-- id: 1c5b03d7-…

-- 2) UUID literals + casting
SELECT '1c5b03d7-2c97-4e0e-8c91-…'::uuid;
SELECT uuid '1c5b03d7-2c97-4e0e-8c91-…';

SELECT * FROM users WHERE id = '1c5b03d7-2c97-4e0e-8c91-…';
-- Postgres compares as binary, not text — fast.

-- 3) UUID versions cheat sheet
--   v1   timestamp + node MAC — leaks MAC; sortable but exposes machine identity
--   v3   namespace + MD5 hash — deterministic from inputs
--   v4   random — most common; not sortable (random insert pattern hurts index locality)
--   v5   namespace + SHA-1 — deterministic
--   v6   reordered v1 — sortable, retains identifying info
--   v7   unix-ms timestamp + random — sortable AND privacy-preserving (RECOMMENDED for new tables)
--   v8   custom

-- Why v7 matters for databases: rows insert at the END of the B-tree index,
-- which preserves cache locality and avoids the random-insert pessimisation of v4.

-- 4) Generate v7 in Postgres (until built-in support lands)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION uuid_v7() RETURNS uuid AS $$
DECLARE
    ts_ms bigint := (extract(epoch from clock_timestamp()) * 1000)::bigint;
    rand_bytes bytea := gen_random_bytes(10);
    result bytea;
BEGIN
    result := set_byte(decode(lpad(to_hex(ts_ms), 12, '0') || '7' || '0' || encode(rand_bytes, 'hex'), 'hex'), 8,
                                         (get_byte(rand_bytes, 0) & 63) | 128);
    RETURN encode(result, 'hex')::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE TABLE events (
    id          UUID PRIMARY KEY DEFAULT uuid_v7(),
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    payload     JSONB
);

-- 5) Generate v7 on the client side (preferred for application speed)
-- Node
import { v7 as uuidv7 } from 'uuid';
const id = uuidv7();             // '018f-…' (sortable by creation time)

-- Python
from uuid_extensions import uuid7
id = uuid7()

-- 6) Indexing — UUIDs are 16 bytes, half the size of a 36-char text representation
CREATE INDEX idx_users_email ON users(email);
-- B-tree on UUID is fine; if your IDs are v4 (random), expect more page splits and bloat
-- compared to v7 / serial bigints.

-- 7) Compare to bigint / serial
-- bigint serial:
--   8 bytes
--   monotonic — fastest inserts and joins
--   leaks scale (your customer can count records)
-- uuid v4:
--   16 bytes
--   random — slower inserts at scale, more index bloat
--   no information leak
-- uuid v7:
--   16 bytes
--   monotonic-ish — close to serial perf for inserts
--   no information leak, sortable by creation

-- For most new tables: uuid v7 is a great default. Internal admin tables that don't leave the network can use bigserial.

-- 8) Foreign keys
CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT uuid_v7(),
    customer_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total_cents BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 9) URL-safe shorter representations — Base58 / ULID
-- Sometimes you want a SHORTER human-readable form (URLs). Two options:
--   a) Store UUID, render as Base58 in the API layer (no extra column)
--   b) Use ULIDs (also 128-bit, lexicographically sortable) and store as UUID column
--      ULID and UUID are byte-compatible — the same 16 bytes, different encoding

-- 10) Generate from an external source
INSERT INTO users (id, email) VALUES ('018f-…', 'sam@example.com');
-- Often safer than DB-side generation for distributed apps — clients can pre-compute IDs
-- and use them in outbox/idempotency keys before the row is written.

-- 11) Comparing performance
-- For 100M-row tables with random v4 PKs:
--   • Index bloat noticeably worse than monotonic IDs
--   • Inserts slower under sustained load (more random I/O at the index leaves)
--   • Sequential range scans aren't useful (no meaning to order)
-- For v7 with the same volume:
--   • Bloat comparable to bigserial
--   • Index pages mostly hot at the right edge
--   • Range scans by id ≈ range scan by created_at (useful for paging)

-- 12) JSONB / API payloads
SELECT id::text AS id, jsonb_build_object('id', id, 'email', email)
FROM users WHERE id = '1c5b03d7-…';
-- ::text gives the canonical 36-char form; JSON returns it as a string automatically.

-- 13) Migrating an existing serial PK to UUID
-- 1. ALTER TABLE … ADD COLUMN id_new UUID DEFAULT gen_random_uuid();
-- 2. UPDATE every foreign key to use id_new
-- 3. ALTER TABLE … DROP CONSTRAINT pkey; …  ADD PRIMARY KEY (id_new);
-- 4. Drop the old id column
-- This is invasive — plan a maintenance window or do it in shadow tables.

-- 14) Common bugs
--   • Using v4 for high-throughput insert paths and seeing index bloat — switch to v7
--   • Storing UUIDs as TEXT/varchar(36) instead of UUID type — 2-3x the size, slower comparisons
--   • Forgetting to cast a string parameter — `WHERE id = $1` fails if $1 binds as text in some drivers
--   • Exposing v1 UUIDs publicly — they encode the machine MAC + timestamp
--   • Using uuid as a join key without an index — sequential scans on 16-byte comparisons hurt
--   • Mixing UUID generation styles (v4 in app code, v7 in DB defaults) — confused ordering, debug headaches

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
ALTER TABLE users ADD COLUMN public_id uuid DEFAULT gen_random_uuid();