Definition
- Per-shard sequences – Each shard has a database sequence configured with non-overlapping ranges, either interleaved (increment by N, offset k) or block-allocated.
- Snowflake-style – A 64-bit integer packing a timestamp in the high bits, a worker/machine ID, and a per-millisecond sequence counter.
- UUIDv7 – A 128-bit UUID with a millisecond timestamp prefix and random suffix, giving time-ordered uniqueness with zero coordination.
- Key principle – Avoid random UUIDv4 on large tables because random inserts fragment the B-tree and thrash the cache.
National Bank of India has many branches (shards) issuing account reference numbers at the same time, and two branches must never issue the same number. One way is to give each branch a different number block so they can never clash. Another is a smart stamp combining the current time, the branch ID, and a counter, guaranteeing uniqueness without phoning head office. A third is a very long random-but-time-sorted code (UUIDv7) that is practically impossible to duplicate and still sorts by creation time.
Understanding the Concepts
What is the problem with IDs across shards?
In a single database, an auto-increment sequence guarantees unique IDs. But with multiple shards, each shard has its own sequence, so two shards can produce the same number. You need a strategy that guarantees global uniqueness without requiring every insert to contact a central server.
package com.bank.id;
public class IdProblem {
// Single database: auto-increment works
// Shard A: next_val = 1, 2, 3, 4...
// Shard B: next_val = 1, 2, 3, 4... ← COLLISION!
// Two transactions with id=1 on different shards
// When you need to reference them globally, which is which?
}
How do per-shard non-overlapping sequences work?
You configure each shard's sequence so the number spaces can never collide. The two common schemes are interleaving and range allocation.
-- Interleaving: with 4 shards, each increments by 4
-- Shard 0: 0, 4, 8, 12, 16...
-- Shard 1: 1, 5, 9, 13, 17...
-- Shard 2: 2, 6, 10, 14, 18...
-- Shard 3: 3, 7, 11, 15, 19...
-- On Shard 0:
CREATE SEQUENCE txn_seq START 0 INCREMENT 4;
-- On Shard 1:
CREATE SEQUENCE txn_seq START 1 INCREMENT 4;
-- On Shard 2:
CREATE SEQUENCE txn_seq START 2 INCREMENT 4;
-- On Shard 3:
CREATE SEQUENCE txn_seq START 3 INCREMENT 4;
package com.bank.id;
public class InterleavedSequenceConfig {
// Each shard's sequence produces non-overlapping IDs
// Shard 0: 0, 4, 8, 12, ...
// Shard 1: 1, 5, 9, 13, ...
// Shard 2: 2, 6, 10, 14, ...
// Shard 3: 3, 7, 11, 15, ...
// Pros: compact integer IDs, no coordination at insert time
// Cons: adding shards requires reconfiguring sequences
// range allocation may hot-spot the newest block
public static int getShardFromId(long id, int shardCount) {
return (int) (id % shardCount); // ID encodes which shard it came from
}
}
How does a Snowflake-style ID generator work?
A Snowflake-style ID is a 64-bit number laid out as a timestamp in the high bits, then a worker/machine ID, then a per-millisecond sequence counter.
package com.bank.id;
public class SnowflakeIdGenerator {
private static final long EPOCH = 1704067200000L; // 2024-01-01 00:00:00 UTC
private static final long WORKER_ID_BITS = 10; // 1024 workers
private static final long SEQUENCE_BITS = 12; // 4096 per ms per worker
private static final long MAX_WORKER_ID = (1L << WORKER_ID_BITS) - 1;
private static final long MAX_SEQUENCE = (1L << SEQUENCE_BITS) - 1;
private final long workerId;
private long lastTimestamp = -1;
private long sequence = 0;
public SnowflakeIdGenerator(long workerId) {
if (workerId > MAX_WORKER_ID) {
throw new IllegalArgumentException("Worker ID exceeds max: " + MAX_WORKER_ID);
}
this.workerId = workerId;
}
public synchronized long nextId() {
long timestamp = System.currentTimeMillis() - EPOCH;
if (timestamp == lastTimestamp) {
sequence = (sequence + 1) & MAX_SEQUENCE;
if (sequence == 0) {
// Wait for next millisecond
while (timestamp <= lastTimestamp) {
timestamp = System.currentTimeMillis() - EPOCH;
}
}
} else {
sequence = 0;
}
lastTimestamp = timestamp;
return (timestamp << (WORKER_ID_BITS + SEQUENCE_BITS))
| (workerId << SEQUENCE_BITS)
| sequence;
}
// Bit layout (64 bits total):
// [41 bits: timestamp] [10 bits: worker] [12 bits: sequence]
// Timestamp: ~69 years from epoch
// Worker: up to 1024 unique workers
// Sequence: up to 4096 IDs per millisecond per worker
}
Snowflake ID structure (64 bits): ┌──────────────────┬──────────┬────────────┐ │ 41 bits: │ 10 bits: │ 12 bits: │ │ timestamp (ms) │ worker │ sequence │ │ since epoch │ ID │ per ms │ └──────────────────┴──────────┴────────────┘ Properties: - Globally unique: no two workers share the same worker ID - Time-sorted: timestamp in high bits means IDs sort by creation time - No coordination: each worker generates IDs locally - Compact: 64-bit long, fits in a BIGINT column - 4096 IDs per ms per worker = ~4M IDs/second/worker
How does UUIDv7 work?
UUIDv7 is a 128-bit UUID whose most significant bits encode a Unix-millisecond timestamp, with the rest random. Unlike random UUIDv4, it is time-ordered.
package com.bank.id;
import java.util.UUID;
public class UuidV7Generator {
// UUIDv7 layout (128 bits):
// [48 bits: Unix ms timestamp] [4 bits: version=7]
// [12 bits: random] [2 bits: variant] [62 bits: random]
public static UUID generateV7() {
long timestamp = System.currentTimeMillis();
long msb = (timestamp << 16) | 0x7000L | (randomBits(12));
long lsb = 0x8000000000000000L | randomBits(62);
return new UUID(msb, lsb);
}
private static long randomBits(int bits) {
return java.util.concurrent.ThreadLocalRandom.current()
.nextLong() >>> (64 - bits);
}
// Properties:
// - Time-ordered: inserts append near end of index
// - Zero coordination: no worker ID assignment needed
// - 128-bit: larger than Snowflake's 64-bit
// - Collision-resistant: random suffix makes collisions negligible
}
How do they compare?
| Feature | Per-shard Sequence | Snowflake | UUIDv7 |
|---------------------|--------------------|----------------|---------------|
| Size | 64-bit integer | 64-bit integer | 128-bit UUID |
| Coordination | Sequence config | Worker ID | None |
| Time-sorted | No | Yes | Yes |
| Index-friendly | Yes (sequential) | Yes (append) | Yes (append) |
| Adding shards | Reconfigure seqs | Add worker ID | No change |
| Throughput/node | DB sequence speed | 4096/ms/worker | Unlimited |
| Client-side gen | No (DB sequence) | Yes | Yes |
Why is random UUIDv4 bad for primary keys?
Random UUIDv4 values scatter inserts across the entire B-tree index, causing page splits, fragmentation, and poor cache locality.
package com.bank.id;
import java.util.UUID;
public class UuidV4Problem {
// UUIDv4: completely random
// INSERT with id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479'
// INSERT with id = '3e7a1234-abcd-4567-89ef-0123456789ab'
// INSERT with id = 'a1b2c3d4-e5f6-4789-abcd-ef0123456789'
// Each insert lands in a DIFFERENT part of the B-tree
// Pages split randomly, cache is thrashed
// UUIDv7: time-ordered
// INSERT with id = '0190a1b2-c3d4-7000-...' (time 10:00:01)
// INSERT with id = '0190a1b2-c3d5-7000-...' (time 10:00:02)
// INSERT with id = '0190a1b2-c3d6-7000-...' (time 10:00:03)
// All inserts land near the END of the B-tree
// Pages grow sequentially, cache stays hot
public static void main(String[] args) {
// Random UUIDv4
UUID v4 = UUID.randomUUID();
System.out.println("v4: " + v4); // Random, not sorted
// Time-ordered UUIDv7
UUID v7 = UuidV7Generator.generateV7();
System.out.println("v7: " + v7); // Time-sorted, append-friendly
}
}
When do you choose each strategy?
The choice depends on the system's constraints and requirements.
Decision guide:
Already sharded with DB sequences?
→ Per-shard interleaved sequences (simplest, compact)
High write throughput across many nodes, need compact 64-bit ID?
→ Snowflake (fast, time-sorted, requires worker ID assignment)
Want zero coordination, IDs generated client-side or across services?
→ UUIDv7 (no worker ID needed, 128-bit is larger)
Legacy system uses random UUIDv4?
→ Migrate to UUIDv7 for better index performance
What should a senior engineer know?
A senior engineer should know that the ID strategy and shard key must be decided together, since the ID often determines which shard a row belongs to. They should also understand clock skew handling in Snowflake (refuse to generate IDs if the clock moves backward) and the storage implications of 128-bit UUIDs versus 64-bit integers.
package com.bank.id;
public class IdStrategyDecision {
// Senior considerations:
// 1. ID strategy and shard key are linked
// If shard_key = hash(account_id), the ID must include account_id
// Or: use the Snowflake worker_id to encode the shard
// 2. Clock skew in Snowflake
// If clock moves backward, refuse to generate IDs
// Wait until clock catches up, or throw exception
// 3. Storage: 64-bit BIGINT = 8 bytes
// 128-bit UUID = 16 bytes
// On a table with 1 billion rows:
// BIGINT PK + 3 FK indexes = ~32 GB
// UUID PK + 3 FK indexes = ~64 GB (2x more)
// 4. Join performance: BIGINT comparisons are faster than UUID
}
Visual Overview
┌────────────────────────────────────────────────────────────┐ │ Globally-Unique ID Strategies │ ├────────────────────────────────────────────────────────────┤ │ │ │ Per-shard Sequences (interleaved): │ │ Shard 0: [0] [4] [8] [12] ... │ │ Shard 1: [1] [5] [9] [13] ... │ │ Shard 2: [2] [6] [10] [14] ... │ │ Shard 3: [3] [7] [11] [15] ... │ │ │ │ Snowflake (64 bits): │ │ ┌──────────────┬────────┬──────────┐ │ │ │ 41 bits: │10 bits:│ 12 bits: │ │ │ │ timestamp ms │worker │ sequence │ │ │ └──────────────┴────────┴──────────┘ │ │ Time-sorted │ No coordination │ 4096/ms/worker │ │ │ │ UUIDv7 (128 bits): │ │ ┌──────────────┬───┬───────────────────────┐ │ │ │ 48 bits: │v=7│ 74 bits: random │ │ │ │ timestamp ms │ │ │ │ │ └──────────────┴───┴───────────────────────┘ │ │ Time-sorted │ Zero coordination │ No worker ID │ │ │ │ ⚠ Avoid UUIDv4: random inserts fragment B-tree │ └────────────────────────────────────────────────────────────┘
Key Takeaway
Generating globally-unique IDs across shards means letting many independent nodes mint identifiers in parallel that never collide, without funneling every insert through a central ID server. The three strategies trade off differently. Per-shard sequences with interleaved ranges give compact integer IDs and need no coordination at insert time, but adding shards requires reconfiguring sequences. Snowflake-style IDs pack a timestamp, worker ID, and per-millisecond sequence into a compact 64-bit integer, giving time-sorted uniqueness with no network call, but require worker ID assignment and clock skew handling. UUIDv7 provides zero-coordination 128-bit IDs with a timestamp prefix, at the cost of larger keys and more storage.
The critical rule is to avoid random UUIDv4 as a primary key on large tables. Random inserts scatter across the B-tree, causing page splits, fragmentation, and poor cache locality. Time-ordered IDs (Snowflake, UUIDv7) keep inserts near the end of the index, maintaining append-friendly behavior.
At the National Bank of India, sharded transactions needed unique IDs minted concurrently. Rajesh used per-shard interleaved sequences for compact integer transaction IDs, and for a high-throughput event stream across many nodes, the team used Snowflake IDs because a compact, time-sortable 64-bit ID mattered for index locality.
Remember one sentence: make IDs globally unique without a central bottleneck using per-shard non-overlapping sequences, a Snowflake-style timestamp-plus-worker-plus-counter, or time-ordered UUIDv7.
---