Don’t Use UUIDs/GUIDs in Databases. Use this Instead

Hello, everybody. I'm Nick, and in this video, I will talk about a type of ID called ULID which aims to fix performance issues with using UUIDs as primary keys for databases.

Don't forget to comment, like and subscribe :)

Social Media:

#csharp #dotnet

[fusedqyou]

I think I decided not to comment about ULID due to the sheer difference, but it's nice to see it being mentioned. I personally do use GUIDs for ids, but I use ULIDs for public ids that are shared publicly. ULIDs are very easy to use as public ids since they are short and can also easily be used in a URL.

[johnsonlu]

Besides the base32 encoding, it is the same as UUID v7. both are 128 bits, ULID just do not have the 4bits version and 2 bits variant part.

[reikooters]

One thing to keep in mind is that postgresql and mssql don't order guids the same way. Sql Server groups the bytes together in a certain way when ordering. To my understanding ulid is good for inserting into a postgres database but not right for sql server. I'm using the RT.Comb package for generating sequential guids for use with sql server.

[dddaamystuffhaha]

Performance is not really the most important concern when it comes to ID generation. This video's focus on perf was kind of pointless.

[keyser]

Do the resulting Guids from Ulids still appear ordered based on time? I'd assume so?

[kabal]

That’s amazing. I’ve been using Ulid for ages, but able to use it in existing systems is awesome

[olimilo]

Why would you sort for an unique identifier? If we would have to sort for time would'nt we just add a datetime variable?

[chadgrant]

uuid's in RDMS is fine, use them to avoid enumeration and easy number based lookups/attacks. Don't use them for relationships. Easy. No need to re-invent the wheel

[magnusbergh]

If you are creating your v7 Guid (or Ulid for that matter) in distributed computing like multiple instances of a service running on multiple machines or for that matter if you generate it on a local client, then you can't be sure they are sequential because time might differ between machines. Also there is a sequential UUID in MS Sql server if you generate the ID on db server side. Buit one point of using a Guid is so you can generate the row "client side". If you want a server assigned id then I personally think it might be better to use a sequence.

[Andrei]

This can be useful for Azure Table Storage as well since data is stored ordered by partition key then by row key.

[adrian-px]

The main purpose of uuid came in order to prevent enumeration, in turn make public data sets more secure. SQL still is the most performant when using PKs ( as far as i know anyway ) so the way i deal with this is at Component level you can use whatever identifier you want, DB level you join and index via PK and presentation layer only had access to uuid. To me its just straightforward instead of adding a bunch of different hashes

[stoino]

Can I replace existing Guid v4 with v7 (or ulid.toguid) on an existing dataset?

[Robert-G]

man, that’s really neat.
thx, didn’t have that on my radar. heck, was using hilo even most of the time to not get fragmented and/or massive i’d values just to be able ensure uniqueness.
will see how that one fits in with new stuff. still creates large keys, but the simplicity might be well worth it

[androidsavior]

So ulid will be stored as varchar string in the database ?

[bbqchickenrobot]

How is the database performance w/ sorting, etc, etc ?

[RobinJohannesson]

It would be nice to have some sort of conversion between getting the timestamp from UUID v7. For instance, seeing when the ID was created.

[BlTemplar]

Hi Nick! Thanks for the video, never heard about ULID.
You recommend to not use UUIDs but if I didn't miss anything you didn't cover how fast ULID works in the database.
Lets take Postgres for example. I assume we store ULIDs in a text column.
Sorting 5 millions rows by text ULID column takes twice as long as sorting 5 million GUIDs (typical use cases: create materialized view optimised for reading in the sorted order, select distinct, etc). Text ULIDs also consume more space.
It looks like they are considerably worse for the database in both storage and operations than UUIDv7, but more optimised when generating them.
So I would say it depends on the usecase.

[VanDameDev]

So glad ULIDs are getting popular. They are really better in every way than GUIDs/UUIDs.

[yordantodorov]

Great presentation! 🎉 I have learnt a lot from it and I really sorry that I didn't know about ulid 10 years ago.

[RamiNassarPlus]

Is it going be a best practise if I replaced all my GUIDs in SQL database with ULIDs of a type char(26)/varchar(26) knowing that most of those IDs exposed by APIs for CRUD operations? what I understood from the video the performance should be even better and more secure, I'm I correct?