Generating unique ids at scale

Key points

• A single auto-increment column needs one coordinator. Perfect on one node, a bottleneck the moment you shard.
• Random UUIDv4 is unique anywhere but unordered, so every insert lands on a random page and the buffer cache thrashes.
• UUIDv7 puts a millisecond timestamp in the high bits: still globally unique, but sorted, so inserts stay at one edge.
• A Snowflake id packs timestamp, node id and a per-millisecond counter into 64 bits - small, sortable, generated with no network call.
• Do not expose sequential ids publicly. They leak your volume and invite enumeration; hand out an opaque public code instead.
• Clock skew is the trap. If the wall clock jumps backwards, refuse to issue ids until it catches up rather than repeat one.

Example

import java.util.HashSet;
import java.util.Set;

public class Main {

    // Snowflake: 41 bits of milliseconds | 10 bits of node | 12 bits of sequence.
    static final class Snowflake {
        private static final long EPOCH = 1700000000000L;      // any fixed start
        private final long node;
        private long lastMillis = -1L;
        private long seq = 0L;

        Snowflake(long node) { this.node = node; }

        synchronized long next() {
            long now = System.currentTimeMillis();
            if (now < lastMillis) {
                throw new IllegalStateException("clock went backwards - refuse, never repeat");
            }
            if (now == lastMillis) {
                seq = (seq + 1) & 0xFFF;                       // 4096 ids per millisecond
                while (seq == 0 && now <= lastMillis) {         // exhausted: wait for the tick
                    now = System.currentTimeMillis();
                }
            } else {
                seq = 0;
            }
            lastMillis = now;
            return ((now - EPOCH) << 22) | (node << 12) | seq;
        }
    }

    public static void main(String[] args) {
        Snowflake a = new Snowflake(1);
        Snowflake b = new Snowflake(2);

        long[] ids = new long[6];
        for (int i = 0; i < ids.length; i++) ids[i] = (i % 2 == 0 ? a : b).next();

        Set<Long> seen = new HashSet<>();
        boolean millisNeverGoBack = true;
        for (int i = 0; i < ids.length; i++) {
            seen.add(ids[i]);
            if (i > 0 && (ids[i] >>> 22) < (ids[i - 1] >>> 22)) millisNeverGoBack = false;
            System.out.println("id " + ids[i] + "   node=" + ((ids[i] >>> 12) & 0x3FF));
        }

        System.out.println("all unique          : " + (seen.size() == ids.length));
        System.out.println("milliseconds sorted : " + millisNeverGoBack);
        System.out.println("coordinator needed  : none - each node numbers its own");
    }
}

Time-prefixed ids buy you global uniqueness and index locality at the same time.