Choosing a Primary Key: A Tour of the IDs Real Systems Actually Use

The last article in this series was all about picking the right UUID version. This one pulls back a bit further, because UUIDs are just one item on a much longer menu, and depending on what you're building, they might not even be the right pick. Auto-increment integers, short UUIDs, Snowflake-style IDs, ULIDs, KSUIDs, NanoIDs, CUID2s: every one of these exists because somebody hit a real wall with the others and built something to get past it.

I've used some of these at some point in production, usually because whatever I'd picked earlier stopped scaling the way I expected it to. So think of this less as a spec sheet and more as "here's what each one is actually good for," with real generated values throughout so you're not taking my word for any of it.

Auto-Increment Integers: the one everyone starts with

Before UUIDs became the reflexive default, this was the default, and honestly it still should be for a lot of single-database systems.

CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, customer TEXT);
INSERT INTO orders (customer) VALUES ('Alice'), ('Bob'), ('Carol');
SELECT id, customer FROM orders;

(1, 'Alice')
(2, 'Bob')
(3, 'Carol')

The database just hands out the next number in line. Simple, fast, and small, a 4-byte int or 8-byte bigint next to a UUID's 16 bytes. It's also about the friendliest shape you can give a B-tree index, since every new row gets tacked onto the end instead of landing somewhere random.

The trade-offs here are well worn at this point. A single sequence means a single source of truth, and that turns into a coordination bottleneck the moment you've got more than one writer, more than one region, or more than one shard. The values are also sequential and easy to guess, which leaks more than people realize: an order ID of 48213 sitting on a public confirmation page quietly tells anyone paying attention roughly how many orders you've processed. That's usually the moment teams go looking for something else.

Even so, if you're running a single-instance service backed by one Postgres or MySQL database, a bigint auto-increment key is often exactly the right call. Don't reach for something fancier just because it sounds more impressive in a design doc.

UUIDs: the usual next step once you outgrow a single writer

I went deep on this in the last article, so I won't rehash the whole thing here. Short version: UUIDs let any service, anywhere, generate a globally unique ID without asking anyone else first, at the cost of size and, for the random versions, index locality. If you haven't read that piece yet, v4 is a fine general-purpose default, and v7, with its leading timestamp, is what I'd reach for now for anything that's both a primary key and getting written to constantly.

>>> import uuid
>>> uuid.uuid4()
UUID('0b7c3408-56d2-44bc-9188-8eda3b7fef7a')

36 characters as a string, 16 bytes once packed. Worth keeping that number in your head, because a good chunk of a UUID's footprint is just the text encoding, not the actual entropy, which brings us to the next one.

Short UUIDs: same entropy, a much friendlier string

A "short UUID" isn't really a different scheme. It's a UUID wearing a nicer outfit. Standard UUID formatting is hex with hyphens sprinkled in, which is a pretty wasteful way to write out 128 bits as text. Re-encode those same bits in base57 or base62 and you get something noticeably shorter and URL-safe, with zero loss of uniqueness along the way.

>>> import shortuuid, uuid
>>> u = uuid.uuid4()
>>> u
UUID('0b7c3408-56d2-44bc-9188-8eda3b7fef7a')
>>> shortuuid.encode(u)
'44VCR8KaYvNQoqr5RadzQr'

Same 128 bits, same collision resistance, just under 22 characters instead of 36, no hyphens, and safe to drop straight into a URL without escaping anything. It's purely cosmetic though. It doesn't touch UUIDv4's index-locality problem, since the underlying bits are still random, it just makes the ID nicer to look at and quicker to type. It's a good fit for things like public share links or display codes where the raw hyphenated format feels clunky, but it's not a substitute for choosing a time-ordered version if what you're actually fighting is index performance.

Twitter Snowflake IDs: coordinated, time-sortable, and fast

Twitter open-sourced this design back around 2010, when plain auto-increment stopped working for them at scale. They needed IDs minted across many database shards while still staying roughly ordered so tweets could sort sensibly. The fix: pack a timestamp, a machine identifier, and a per-millisecond sequence number into one 64-bit integer.

The original layout runs 1 unused sign bit, 41 bits for a millisecond timestamp measured from a custom epoch, 10 bits for a machine or worker ID, and 12 bits for a sequence number that ticks up if more than one ID gets requested in the same millisecond.

TWITTER_EPOCH = 1288834974657  # Nov 4, 2010, in unix ms

def snowflake(epoch, timestamp_ms, machine_id, sequence):
    ts_bits = (timestamp_ms - epoch) & 0x1FFFFFFFFFF   # 41 bits
    machine_bits = machine_id & 0x3FF                   # 10 bits
    seq_bits = sequence & 0xFFF                          # 12 bits
    return (ts_bits << 22) | (machine_bits << 12) | seq_bits

Running it against the current time:

>>> now_ms = 1789369239951
>>> snowflake(TWITTER_EPOCH, now_ms, machine_id=17, sequence=0)
2099392871059755008
>>> snowflake(TWITTER_EPOCH, now_ms, machine_id=17, sequence=1)
2099392871059755009

Two calls in the same millisecond, from the same machine, only differ in those last few sequence bits. That's exactly why these sort and index so well: underneath the timestamp dressing, they're basically an incrementing integer. Decode one and you get the original timestamp and machine ID straight back out:

>>> decode(2099392871059755008, TWITTER_EPOCH)
(1789369239951, 17, 0)

Since it's a 64-bit integer rather than 128, it's half the size of a UUID and slots naturally into a bigint column. The catch is you need to give each generator a unique machine ID somehow, which means a bit of coordination at startup, whether that's a config value, a Zookeeper-style registry, or a database-assigned slot, unlike UUIDs, where any process can generate one blind, no setup required. Instagram's ID scheme and Discord's Snowflake variant both run on this same basic shape with small tweaks to the epoch and bit widths, and Sonyflake is a well-known Go implementation that trades a bit of the layout differently to buy longer uptime before rollover. This is the design worth reaching for when you need compact, sortable, database-friendly IDs generated across a fleet of machines without leaning on a shared auto-increment sequence.

ULID: UUID-compatible, sortable, and actually pleasant to type

ULID stands for Universally Unique Lexicographically Sortable Identifier, which is a mouthful for a pretty simple idea. Keep everything people already like about UUIDs (128 bits, no coordination needed, drop-in compatible storage), and fix the two things people always complain about: no natural sort order, and a string format that's easy to mistype.

>>> from ulid import ULID
>>> u = ULID()
>>> str(u)
'01M2FBFA0ZNB390G8EKDPNBJE6'
>>> u.datetime
datetime.datetime(2026, 9, 14, 7, 0, 31, 391000, tzinfo=datetime.timezone.utc)

The first 10 characters, 01M2FBFA0Z, encode a 48-bit millisecond timestamp in Crockford's base32, which is case-insensitive and skips the characters people mix up (no I, L, O, or U). The rest is random. Sort a column of ULIDs as plain strings and you get chronological order for free, which is the same benefit UUIDv7 gives you now, except ULID predates v7 and was purpose-built to slot into anything already storing UUIDs as strings or 128-bit values.

It's a strong pick if you want sortable IDs today, want a format people can actually read over the phone or paste into a support ticket without squinting, and either can't wait for native UUIDv7 support across your stack or just don't need it.

KSUID: sortable, with a much bigger safety margin

Segment built KSUID (K-Sortable Unique ID) chasing a similar goal to ULID, chronological order plus global uniqueness, but they made a different trade. Instead of a millisecond timestamp, KSUID uses second-resolution time with a custom epoch (2014), paired with a much larger 128-bit random payload instead of ULID's 80.

>>> from ksuid import Ksuid
>>> k = Ksuid()
>>> str(k)
'3JJBL5u9nnfzFaOXFyXdRbK2C8K'
>>> k.datetime
datetime.datetime(2026, 9, 14, 7, 0, 31, tzinfo=datetime.timezone.utc)

27 characters, base62 encoded. That extra random payload buys an even smaller chance of collision than ULID, but it costs sub-second ordering: two KSUIDs generated in the same second sort arbitrarily relative to each other. If you care about ordering down to the millisecond, ULID or Snowflake fits better. If you mostly want rough chronological grouping and would rather max out the random component for long-term collision safety, KSUID is a solid, if less common, choice.

NanoID: small, quick, and only about uniqueness

NanoID skips the timestamp idea entirely and just focuses on being a tiny, URL-safe, purely random generator, basically a modern stand-in for UUIDv4 when you don't need the full 128 bits of entropy and don't want the hyphenated format cluttering things up.

>>> from nanoid import generate
>>> generate()
'0HZJDcgbmCLDaC4ogOTIv'
>>> generate()
'VHqtXE4caCZQfzTafsGI1'

By default that's 21 characters pulled from a 64-character alphabet, and both the length and alphabet are tunable if you want something different. There's no structure to decode, nothing but randomness, and that's the whole appeal: it's small, quick to generate, and safe to drop directly into a URL. It shows up a lot in frontend and Node ecosystems for things like short-lived request IDs, React keys, or session identifiers, anywhere you want something shorter and friendlier than a UUID but genuinely don't care about sort order.

CUID2: built for IDs generated out in the wild

CUID2 (Collision-resistant Unique IDentifier) is solving a slightly different problem than the rest. It's meant for IDs that might get generated client-side, in a browser or mobile app, before the record ever touches a server, while staying safe from prediction and collision even across a huge number of independent, untrusted clients.

>>> from cuid2 import cuid_wrapper
>>> cuid_generator = cuid_wrapper()
>>> cuid_generator()
'dd1cgl34kpd8gay3k1ij4y23'
>>> cuid_generator()
'nofrp8yettv06z6luwukryb9'

Under the hood it mixes a timestamp, a counter, a session-specific fingerprint, and randomness, then hashes it all together so the output doesn't give away which part came from where. That's different from Snowflake or ULID, where the timestamp is sitting right there in plain sight if you know how to read it. It's a deliberate security choice: CUID2 assumes IDs might be minted by clients you don't fully trust, and it's built to avoid leaking generation order or machine identity as a side effect. It's popular in JavaScript-heavy stacks for exactly that reason. Prisma ships it as a default ID generator, for what it's worth.

The Comparison

Scheme	Size	Sortable	Coordination Needed	Leaks Info	Best For
Auto-increment	4-8 bytes	Yes	Yes (single sequence)	Row count/volume	Single-writer relational systems
UUIDv4	16 bytes	No	No	No	General-purpose, distributed generation
UUIDv7	16 bytes	Yes	No	Rough creation time	High-write primary keys, distributed generation
Short UUID	~22 chars	Depends on source UUID	No	Same as source UUID	Public-facing links, shorter display IDs
Snowflake	8 bytes (64-bit int)	Yes	Yes (machine ID)	Timestamp, machine ID	High-throughput distributed systems, compact sortable IDs
ULID	26 chars / 16 bytes	Yes (ms)	No	Creation time	Sortable, UUID-compatible, human-readable IDs
KSUID	27 chars / 20 bytes	Yes (sec)	No	Creation time (to the second)	Long-term collision safety with rough ordering
NanoID	~21 chars (tunable)	No	No	No	Short, fast, URL-safe random IDs
CUID2	~24 chars (tunable)	No	No	No	Client-generated IDs, collision resistance without leaking origin

Where I'd land

For a single-database service, I still start with a bigint auto-increment primary key, and I only move off it once I actually hit a multi-writer or multi-region need, not preemptively because it seemed like the "grown-up" choice. Once distributed generation is genuinely necessary, it's UUIDv7 for anything relational and write-heavy, ULID if I want that same sortability with a friendlier string and need it before v7 support has landed everywhere I need it, and Snowflake-style IDs if I'm already running a fleet of stateful services where assigning machine IDs isn't a big lift and I want the smallest sortable identifier I can get. NanoID and CUID2 both earn their spot for anything generated outside the database entirely, request IDs, client-side identifiers, short-lived tokens, where sort order doesn't matter but size, safety, or keeping the origin private does.

None of these is universally "the best one." Each was built to solve a specific constraint somebody actually ran into, and the fastest way to pick wrong is grabbing whatever's trendiest instead of asking what your system needs: coordination-free generation, sort order, compactness, or not giving away where an ID came from. Most of the time you only need two or three of those at once, and once you know which ones, the list narrows fast.