Database Auto increment vs UUID - Which is Right for You?

Discover the pros and cons to use an Auto-Increment ID vs a UUID, ULID or a Snowflake ID for your database table, and when to use one or another.

If you struggling to choose the right ID format for your database? Look no further! In this video, we dive into the world of database IDs, exploring the differences between auto-increment, UUIDs (Universally Unique Identifiers), ULIDs (Universally Unique Lexicographically Sortable Identifiers), and Twitter Snowflake IDs.

Discover the inner workings of each ID format to leverage their characteristics like simplicity and sortable nature for auto-increment IDs.
The globally unique generation and distribution for UUIDv4, the timestamp information of UUIDv7. The compactness of ULIDs and the structural advantages of Twitter Snowflake IDs.

#database #backend #sql #codingtips #programming #databases #databasedesign #backenddeveloper

[jpboy]

I do both. Each table has an ID and a UID. ID is completely isolated to the backend. It is never exposed to the client, but is used for primary and foreign keys. The data type is a 64 bit Int. The UID is a Base62 encoded GUID. The client only uses the UID. All the pros, few of the cons. Thanks for the excellent video.

[JohnWasinger]

Nice summary about the differences between Auto Increment UUID ULID and Snowflake ID. I never heard of the last one and the timestamped feature of the ULID makes an easy way to check for expired tags that should be replaced.

[skorp]

Amazing video, thanks a lot! The thorough and dwtailed discussion gave a great overview over the issue! :)

Just one small suggestion: maybe increase the font size to make the text easier to read on mobile devices. There's plenty of whitespace :)

[tesla]

when using db like mysql where Secondary index stores primary key as value. Using uuid (128 bits) will increase the size of secondary indexes.

[anonymous_anonymity]

Very helpful and well explained. Thanks a lot.

[syntheticfox_]

When choosing between server vs client side ID generation largely depends on security requirements of your application. For any external facing API, you will need to generate IDs on the server in order to keep the integrity of the data healthy. UUIDs can be generated on client or server.

[timothyconnally]

I like the idea of client side id’s. This could be timestamp + session ID, making it sortable.
Server (database) sequence id’s are typically not guaranteed to be chronologically ordered as sequences are often cached for efficiency. Oracle, for example, defaults to 20 sequences in a cache. Two sessions can and often do mix their cached sequences out of chronological order. This well known and documented in several Oracle forums.
I want to add my own concern for what I call the “identity crisis”. An autogenerated I’d has no meaning. It is *not* relational per Cobb. I see too many table designs ignore the need for a natural key to properly define what is being stored. This leads to sloppy inefficient designs. Primary keys should be exogenic data, not entropic autogenerated id’s. ‘Nuff said. 😅

[LettersAndNumbers]

Are dbms’s smart enough to use the timestamp in v7 to sort / filter quickly? Mainly interested in SQL server but also open to graph DBs.

[clementseiller]

You can generate all types UUID you want on the server side too. Your list of pros and cons is a bit disingenuous as it could lead some to believe that you can only generate auto increment on the server side. Which is completely false.

As a general recommendation, do the most you can on the server side as client side is not something you own nor control. This video present good use case to generate id on the client side but again, that is something you want to avoid.

[adrianbool]

Very nicely presented video; but I feel the conclusions section let it down. I'd never use a client supplied "random" value as a key in my database. Doing so opens the system to a whole bunch of attacks. You're expecting an ID from the client that, by pure chance, can't practically collide with an existing value but then what happens to your system when someone is intentionally injecting into your system values that already exist? The Snowflake ID was obviously defined to allow for a server generated ID without most of the downsides you list in the conclusion section for Server Generated IDs. (e.g. Relying on a single generation source.)

[dariuszlenartowicz]

Using blindly the same big type id for all datasets (tables) is a big misunderstanding. In our company we reduced our databases in half migrating to appropriate I'd types.

[BigFattyNat]

Did i miss the plot here? I send a new row to my backend db from the clientside, just the meaningful fields from the client, then the db itself creates the auto incr id as an auto generated field on its own... Where is the problem?

[sdfsdfdf]

correction: uuids can be/should be stored as 128b number, so it's not _that_ worse than other numeric data.

from my point of view:

- never, ever, ever trust client to 'generate' unique id for your system. Client side generated IDs is bear trap and your system should just take it as: "yeah, this value user wan't to send as his note but we don't work with it, and it cannot be used to uniquelly identify record, so if he asks for data base on it, we always return list."

- server generated ids are predictable, and if you care about it (like in user id maybe), you might not want to use it -- you said it.

- server generated ids from sequence in db(or other source of single truth) is only way IIUC to have predictable value-based pagination, uuid types cannot be used for predictable&reliable sorting in multi-node system, IIUC.

If I'm setting up new system:
- I dont allow user to force me specific ID to use at my side as ID
- I have ID from sequence for internal use like value-based pagination, user never sees this value
- I have uuid PK user works with.

[tomaszn]

UUIDS are hundreds, thousands time slower to query (big tables) than integers.

[LettersAndNumbers]

2:54 why does a disconnected client need order IDs? This is silly…

[awksedgreep]

If you have a tiny 3nf database that will never grow bigger than say 1G then UUID is slower, but fine. If you think your database will ever grow in size stick with auto inc.

[chrishabgood]

The problem with uuid is it’s not sequential.