UUID vs auto-increment vs Snowflake ID

The decision

Every order, message or short link needs an ID. Three families are common. An auto-increment ID comes from a counter inside one database: 1, 2, 3. A UUID (universally unique identifier) is a 128-bit number (16 bytes) that any server can make with no help. A Snowflake ID is a 64-bit number (8 bytes) built from the time, a machine number and a count. Many servers can make it, and it sorts by time.

Choose auto-increment when

• One database makes all the IDs, and its write rate is far below its limit.
• You want the smallest key: 8 bytes as a bigint. Use bigint, not a 32-bit integer.
• You want IDs that grow in the order they were made, and you accept gaps. A rolled-back transaction leaves a gap.
• Users never see the ID in a web address.

Choose a UUID when

• Many servers, or the client itself, must make IDs without asking a central place.
• The ID appears in a web address and must be hard to guess. Still check access.
• Prefer a time-ordered kind, UUID version 7, for big tables. It starts with a 48-bit time in milliseconds, so new values land near each other in the index. Random version 4 values do not.

Choose a Snowflake ID when

• You need IDs that are small (8 bytes), sort by time, and come from many servers.
• You can give every generator its own machine number (up to 1,024).
• You can keep clocks correct. The generator must refuse to issue IDs if its clock moves back.

The numbers that change the answer

Worked example at Indian scale. A food app. Assumptions: 1,000,000 orders a day for 5 years, and the order ID is stored in 3 places (the table, one index, one child table).

A 32-bit integer ends after 5.9 years, too close to the 5-year plan. A repeat among UUID version 4 values is very unlikely. For n = 1 billion IDs, the chance of any repeat is about n x n / (2 x 2 to the power of 122). That is about 10 to the power of 18 / 10 to the power of 37. The result is 1 in 10 to the power of 19 (our estimate). Still keep a unique constraint on the column, but you need no other check.

Why order matters: an index keeps entries sorted. A new bigger ID goes to the last page, which is in memory. A random ID goes to a random page that may need a disk read. RFC 9562 says time-ordered values improve this.

What breaks with each choice

• Auto-increment: the counter is one place that must be up. Users can guess /order/10483, and a rival can count your orders. Fix: use a separate public ID.
• UUID: 16 bytes, long as text, and version 4 scatters inserts across a big index. Version 7 shows the creation time, which can be private.
• Snowflake: a clock that jumps back can repeat an ID, and two machines with the same number clash. Time order is only close to exact, because clocks differ. Fix: assign machine numbers from a registry, and wait when the clock goes back.

What it costs

• Auto-increment is free today and costly later. Moving to many writers means changing every key and every table that points to it. Plan for weeks (rough guess).
• A UUID costs 14.6 GB of extra key space here, or 43.8 GB with copies. Every table that points to it adds more.
• Snowflake costs a small service or library to hand out machine numbers, plus clock checks.

How each level answers

SDE-1: Auto-increment is a counter. A UUID is a long number that any server can make. A Snowflake ID is made from time, machine and count.

SDE-2: I use bigint for a single database. I use a time-ordered UUID when the ID is public or made by many servers, because it is hard to guess and sorts by time.

Senior: At 1,000,000 orders a day, a 32-bit integer ends in 5.9 years, so I use bigint. A UUID costs 14.6 GB more per copy of the key. If many servers must make small, sorted IDs, I use Snowflake with 1,024 machine numbers. The risk is a backward clock, so the generator waits. The fallback is a UUID version 7.

Staff: I ask who sees the ID, how many writers exist next year, and how costly a key change is. I keep the internal key and the public ID separate, so one can change alone. If a counter must go, I add the public ID column first, switch the code to it, and change keys last. One team owns the generator and its machine numbers. I check insert speed on the biggest table, clash errors and clock alarms.

Say it in the interview

For one database I would use a bigint counter, and a time-ordered UUID for anything users see. If many servers need small sorted IDs, I would use a Snowflake-style ID. The cost is 8 more bytes per key for a UUID, and a dependence on clocks for Snowflake.

Common mistakes

• Using a 32-bit integer for a big table. It ends at 2,147,483,647 and the change is painful.
• Using random UUID version 4 as the key of a huge table. Inserts scatter across the index.
• Believing that time-based IDs give exact order. Clocks differ, so the order is close, not exact.
• Treating a hard-to-guess ID as a lock. Always check that the user may see the record.

Sources

• RFC 9562, UUIDs (May 2024): random bits in version 4, a 48-bit time in version 7, index locality: https://www.rfc-editor.org/rfc/rfc9562.html (opens in a new tab) (checked 26 Oct 2026).
• Postgres UUID functions (lists uuidv7()): https://www.postgresql.org/docs/current/functions-uuid.html (opens in a new tab) (checked 26 Oct 2026).
• Twitter Snowflake (41, 10 and 12 bits; refuses IDs while the clock is behind): https://github.com/twitter-archive/snowflake/tree/snowflake-2010 (opens in a new tab) (checked 26 Oct 2026).
• Order counts and the 3 places are assumptions made for teaching. The collision estimate is our own calculation.