When should you CHOOSE a UUID key vs AVOID it

-- National Bank of India: distributed payment services
-- Each service mints its own UUID, no central sequence needed
CREATE TABLE payment_requests (
    request_id UUID PRIMARY KEY,  -- minted by any service
    account_id VARCHAR(20) NOT NULL,
    amount     DECIMAL(15,2) NOT NULL,
    status     VARCHAR(10) NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

// com.bank.service.PaymentRequestService
package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.UUID;

public class PaymentRequestService {

    /**
     * Each service instance mints a UUID locally.
     * No central sequence, no coordination, no bottleneck.
     */
    public UUID createRequest(
            Connection conn, String accountId, BigDecimal amount)
            throws SQLException {

        UUID requestId = UUID.randomUUID(); // UUIDv4
        // Better: use UUIDv7 for time ordering

        String sql = """
            INSERT INTO payment_requests
                (request_id, account_id, amount)
            VALUES (?, ?, ?)
            """;
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setObject(1, requestId);
            ps.setString(2, accountId);
            ps.setBigDecimal(3, amount);
            ps.executeUpdate();
        }
        return requestId;
    }
}

-- Non-guessable public URL
-- Bad: /api/orders/1001 (attacker tries 1002, 1003...)
-- Good: /api/orders/550e8400-e29b-41d4-a716-446655440000

-- 1. Heavy-write relational table: random UUIDs fragment the B-tree
CREATE TABLE audit_log (
    log_id BIGSERIAL PRIMARY KEY,  -- auto-increment: dense, fast
    -- NOT: log_id UUID DEFAULT gen_random_uuid() -- fragmented
    event_type VARCHAR(50),
    event_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tight storage: 16 bytes vs 8 bytes per row
-- With 200M rows:
--   BIGINT PK: 200M x 8 bytes = 1.6 GB
--   UUID PK:   200M x 16 bytes = 3.2 GB (double)
--   CHAR(36):  200M x 36 bytes = 7.2 GB (quadruple)
-- Plus: InnoDB embeds PK in every secondary index

-- 3. Chronological sorting needed
SELECT * FROM transactions ORDER BY txn_id;
-- Auto-increment: sorted by creation time (free)
-- UUIDv4: sorted by random bytes (meaningless)

-- 4. Simple single-node app: no need for distributed generation
-- A database sequence is simpler, smaller, and sufficient

-- UUIDv7: coordination-free + time-ordered + insert locality
CREATE TABLE payments (
    payment_id UUID PRIMARY KEY,  -- UUIDv7 generated in app
    account_id VARCHAR(20) NOT NULL,
    amount     DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- UUIDv7 values are approximately monotonically increasing
-- Inserts land near the rightmost page, like auto-increment
-- But every service can mint them independently

-- National Bank of India: separate internal and public identifiers
CREATE TABLE orders (
    order_id    BIGSERIAL PRIMARY KEY,      -- internal: compact, fast
    public_ref  UUID NOT NULL DEFAULT gen_random_uuid(),  -- public: unguessable
    account_id  VARCHAR(20) NOT NULL,
    amount      DECIMAL(15,2) NOT NULL
);

CREATE UNIQUE INDEX ux_orders_public_ref ON orders(public_ref);

-- Internal queries use the fast BIGINT PK
SELECT * FROM orders WHERE order_id = 12345;

-- Public API uses the unguessable UUID
SELECT * FROM orders WHERE public_ref = '550e8400-e29b-...';

1. Do multiple actors need to mint IDs without coordination?
   YES -> UUID (prefer UUIDv7)
   NO  -> auto-increment / sequence

2. Must the ID be non-guessable in a public URL?
   YES -> UUID for the public identifier
   NO  -> auto-increment is fine internally

3. Is the table write-heavy with many secondary indexes?
   YES + needs UUID -> UUIDv7 (not UUIDv4)
   YES + no UUID needed -> auto-increment

-- Mistake 1: UUID everywhere regardless of need
-- A simple lookup table does not need a UUID
CREATE TABLE branches (
    branch_id UUID PRIMARY KEY  -- overkill for 50 branches
);
-- Better:
CREATE TABLE branches (
    branch_code VARCHAR(10) PRIMARY KEY  -- simple, meaningful
);

-- Mistake 2: Storing UUID as CHAR(36) instead of native UUID
CREATE TABLE payments (
    id CHAR(36) PRIMARY KEY  -- 36 bytes, wastes space
);
-- Better:
CREATE TABLE payments (
    id UUID PRIMARY KEY  -- 16 bytes, native type
);

-- Mistake 3: Using UUID as an auth token
-- A UUID is an identifier, not a secret
-- Use cryptographically secure tokens for authentication