Laravel UUID with Foreign Keys: Speed Benchmark of Two Approaches
Vložit
- čas přidán 15. 01. 2023
- There are two common ways to use UUIDs: as a primary key, and as a non-primary field. With foreign keys involved, what would work faster, and by how much?
- [Video] New in Laravel 9.30: UUIDs as Primary Keys with a Trait • New in Laravel 9.30: U...
- [Premium Tutorial] UUID in Laravel: All You Need To Know laraveldaily.com/post/uuid-in...
- [Video] New in Laravel 9.32: Benchmark dd() to Measure Execution Time • New in Laravel 9.32: B...
- - - - -
Support the channel by checking out my products:
- My Laravel courses: laraveldaily.com/courses?mtm_...
- Laravel QuickAdminPanel: quickadminpanel.com
- Livewire Kit Components: livewirekit.com
- - - - -
Other places to follow:
- My weekly Laravel newsletter: us11.campaign-archive.com/hom...
- My personal Twitter: / povilaskorop - Jak na to + styl
Update: made a quick benchmark comparing UUIDs and relatively new ULIDs in Laravel.
Performance is identical, haven't noticed any performance bump in using ULIDs in the same project as in the video.
So not shooting the follow-up video, nothing to "report".
Thank you to test that for us I was to lazy to test it! In my case I use UUID or slugs for the URLs and then IDs for the internal queries. Now looking forward for the ULIDs that I didn't know about it, great commenters!
If I recall correctly, The laravel schema simply transforms uuid() into varchar(36) for mysql. Even with a pk index, it should be slower to query than an integer (or big integer) type. PostgreSQL has a proper UUID type.
MariaDB 10.xx has uuid data type, but you need to custom column type for migrations. (May do it in boot function if I correct)
The UUID relationship slightly higher cost due to the way Laravel use ORM, the query parsing time is little higher than the normal integer query
excellent video, personally when I want to do optimization I go directly to the execution plans, I think that at the moment I have not seen a library where, in addition to the queries, they give you the cost in I know that it is not to delve much into the database engine but it is very important to know what things affect our applications.
Thank you for this information
Awesome video! Thanks
ULID (with Laravel support since >9.30) is considered better than UUID in certain situations because it has a lexicographically sortable representation, meaning it can be sorted as a string, whereas UUIDs cannot. Also ULIDs are shorter than UUIDs
Laravel has a “sortable UUID”
Method which I’ve used in the past but it’s non-standard. ULIDs seem to be very popular at the moment and I think they would be great for a follow up video, as well as different ways of storing ulids/uuids in the database.
Exactly my though... ULIDs are much better because they are generated based on current time at the moment they are generated.
Thanks for this ☺️
For me I use Primary Integer key for relations and I use UUID just for Model binding to prevent show IDs in Route URL :)
this is the best practice
how u did to add primary key id and uuid at the same time? thank you.
yeah I do same. Primary integer key for table relations (private) and UUID for model binding (public). And luckily, in laravel we can set primary or other key as route key.
@@dontama i see, i try before how is it but i can't see docs about that hehe can u share a links u follows? btw thank you for this
Why do this though when you can easily use hashIds? I don’t see any reason to go all the way of adding extra column in db to store uuid when you can easily use hashIds to hide the original id from outside world.
Best vidéo, thanks you for that
Great experiment :) One more dangers with using uuids and relations, we see in video second query where you have some list with ids (where alpha_role_user.alpha_user_id in (list of uuids)) and very easy you can broke limit for how DB query can be long with this approach
Thank you
Thanks for the interesting topic. I like these kind of videos, and love that you go the extra mile showing the use of the `Benchmark` facade.
I understand you try to start the conversation between us developers at the end of the video and like to open the debate about whether we should or should not use the uuid's over regular id's. But in this case I would like to see this answered in a (new) video as well. Maybe there is a good example of an open repo project using uuid's. I'd love to go into the trade offs (as usual when talking about software engineering) and really see all the pro's and con's on when to use what.
I think I've answered that question in this video. But I will do another followup video about some things people suggested, like ulid, other uuid types and indexes.
@@LaravelDaily Yes you did. :) It was just a suggestion for a new video. There might be more to uncover. :)
Thank you can keep up the good work.
When there are millions record the difference is huge.
We had to deal with that. Our table threads, related to groups, and having messages.
we were oblige to add auto-incrent column and change the index.
The fact is that UUID are char, not Number.
When you have a lot relationship, it take more time to build query, and more time for Mysql to run queries.
Thanks for this experiment.
I also suspected that it would hurt the performance.
There are also ULIDs, which are shorter than UUIDs and sortable because they start with timestamps. It would be interesting to see if this has an impact on efficiency.
a string is a string, no matter the content. So my best guess is that it won't make a big difference at all.
Topic for another video, thanks!
Thank you for the video. UUID v7, which is based on timestamp like ULID (which means it is also sortable), would have a similar performance as ULID but perhaps a bit worse because of the required higher storage size. Does Laravel support different versions of UUID?
When i use UUID as primary id key for user auth with sanctum i get some issues for creating token for a user. At the moment of inserting the token is truncated (i think becoz of the length of the id that takes 36 char and the token only 20).
I was searching for a unique id solution that starts from 00001 and saves as INT for better performance. I found Mysql fill with zero which stores in db like '00001'. And printf() PHP to show the result. Is it a right practice?
I use a “oid” column that is incrementing and stands for “order” id, for sorting etc. then a uuid id column to identify the object.
Hello sir, please solve my problem.
Problem is I used pagination in url:: signedRoure. Then error invalid signature
Laravel uses UUID 4 versions. If you use the UUID version where there is sorting, the indicators will be almost equal.
Just use Snowflake UUID. UUIDs with numbers mixed with letters is really slow i huge set of data. Snowflake is perfect solution for unique id and performance... not only with selecting data but also with creating new records.
An Snowflake ID comparison would be nice
“Challenging” issue I have with UUIDs is I’m migrating an old system that uses them as a primary key, but I want to use an auto increment as a primary id and use the UUID for public facing parts like your beta demo.
The issue is, how do I go about creating this with existing data and maintaining the relationships previously based on those UUIDs? It’s causing me pain! 😂
Here what i've done.
Create a migration file to:
1. disable foreign constraints.
2. rename primary keys to "public_id" or something you like.
3. add a auto increment id to each table (i've added serial on postgres)
4. update foreign key columns type to integer and update their values using the id you have added and of course matching the right data through the uuid column.
5. Bring back the foreign key constraints.
I've written a script to save all primary keys and foreign keys before and after the migration to later on, in the same script, verify the data integrity.
Note: i maintained the uuid as public id to avoid sending the incremental ids back to the client. So, queries doing equality operations will use the public one but relationship operations will use the incremental id.
Note²: if the project is not your own, ask, before doing or running it, if someone could schedule a maintenance window because depending on the size of your database, it can take hours.
Don't forget to backup.
Se puede tener los dos uuid y iden una tabla
Interesting, but appart from performance, when you need to take a look at the data to troubleshoot it, having integers forming the relationships is a lot easier to read and to work with. Try to imagine yourself reading the primary key of a row and trying to spot it in a collection of related rows ...
I use UUIDs but as BINARY(16). Fast, really fast.
Nanoid could be a better option. Neither for UUID or NanoId as primary keys or foreign keys, instead for relationships use integer for performace. Keep in mind, UUID has versions, MariaDB 10+ has a UUID type and it's v1 and store it backards, that way is easy to index. If you try to use v4 for a large table, inserts will hit your performance for sure
Tough call, I would probably sacrifice performance for better security by picking UUID as primary key.
Having a mixture is good for performance and security but just having integer based primary key, there will always be that 1 coder who will use the integer primary key in the route/blade for record management instead of uuid.
For small Apps, why not. But for Large Apps I don't want to believe a developer would use an integer based PK in route.
Still depends on the context of the app, performance over security or vice versa
This is interesting! I was looking at performance of relationships with Uuids just yesterday.
Could it be something to do with the lack of index on the relationship tables uuids?
That could be an extra factor, yes, unfortunately I forgot to measure it.
@@LaravelDaily Index should bypass most of the differnece I think (should be tested on large scale). I'm not sure about what if you eager loading a bunch of data, the SQL query string can be huge. That can be a problem I think too.
@@tamasnogradi4524 A primary key column is automatically indexed...so i dont think its the fatcor
@@toheebabiodun70 It is a factor when working with relationships
In my experience, utilizing Universally Unique Identifiers (UUIDs) has resulted in a significant performance improvement, with a 5-fold increase noted after indexing certain heavily queried columns on separate tables. While it is true that UUIDs do consume more storage, it can be argued that traditional incremental integer IDs will generally exhibit faster performance. However, with proper caching and sound infrastructure, either option can provide adequate results and the difference in microseconds is unlikely to be noticeable to the average user.
Very nice illustration that indicates that UUID indeed is slower, and should be used carefully. And I am curious to see the same experiment of your video repeated with proper indexes applied to the pivot table in both cases. To people jumping to conclusions: this experiment is statistically not sound yet. E.g. I see comments below like "drain the performance by half", but that's a conclusion that simply cannot be made based on one experiment with a very limited amount of users. I guess that the performance gap will be a function of the size of the dataset, but have no idea how steep the slope will be...
Yes, like all similar experiments, there should be probably an *asterisk with a list of disclaimers. And maybe indexes would show a bit different results, indeed.
@@LaravelDaily Proper indexing will most certainly speed things up, but more things need to be considered, when dealing with indexes, e.g.: A proper index can improve read performance (but that also depends on the actual queries against the indexed data), but will always cost some write performance - how much do I care about that? Will a regular index do or do I need a unique index (costing even more write performance, but prevents UUID collisions, which are unlikely but not impossible)? Next, how big is my dataset? An index will only perform optimally if it can fit into the database server's memory in its entirety. And not just this one, but all indexes. Otherwise, using the index means going to disk, which is always slow. Speaking of disk, non-native UUID fields will eat up a lot of disk space, and indexing the field means storing its value at least twice: Once in the actual row, once in each index that uses it. Do I care about disk space? What does increasing disk usage mean for secondary operations like backup/restore duration or replication?
None of this really matters when you only have a couple of hundred thousand records, but if you deal with bigger datasets, it's a different ballgame ;-)
Hey mate, could you do a video of how to integrate Livewire Calendar with Laravel 9? Thanks for the content it helps alot, keep it up! :)
Which exactly Livewire calendar, there are a few packages from what I remember
@@LaravelDaily The creator is Asantibanez, you made a video back then with Laravel 8, but it doesn't support L9, even though there are some ways to get it working, I tried some solutions but couldn't get it to work. Thanks for replying It means alot. ❤️
Hello there,
Thank you for your video. Can you try it with PostgreSQL and record it, please?
I don't personally use PostgreSQL, sorry
UUID has many disadvantages as a primary key. One of them is we can use the same uuid of the deleted row again, while in default int primary-key we can't use deleted row-id again. 2nd is you can save blank data in uuid, but 2nd time you can't if uuid also unique-key. Think about it, you can save blank data as primary key.
That's not a UUID issue, if you set NULL, column constraints, then you allow for blank data, and this also goes for regular Integer ID (Auto Increment).
If you don’t want a blank set NOT NULL constraint
@@aeadedoyin Column is set to NOT NULL for uuid, but we can save 1st value as empty string, and we try to save 2nd empty value then an error message trigger. Here we have an empty value as primary-key ⚠️
How were you able to use primary key and uuid in the same table.....is there a tutorial video on that?
Yes you can read about it in that premium tutorial I mentioned in the video
MySql dont have type for uuid in MSSql has uniqueidetifier type for this. and of corse is faster
Does that mean when using other database types like Postgress, SQLite, and SQL Server which are also natively supported by Laravel, or even others like MongDB, could have no difference in speed? I would love to see a benchmark for that, and maybe I will do so myself. :)
I think the real disadvantage of using uuids as primary keys comes on insert queries. This topic was also discussed on laracon summer 2022 czcams.com/video/f4QShF42c6E/video.html
Please try it for PostgreSQL.
I personally don't use PostgreSQL. Maybe you can try and measure and tell us all? I could then make your comment as Pinned comment on this video.
@@LaravelDaily Ok. In order for the test to be as reliable as possible, I need the same code that was used in this benchmark (the same composer.json and php version). You can share this project on github ?
I don't have it ready-made on GitHub, sorry, it was just a set of local experiments.
I will need to re-create it myself again in a week or two, to test the indexes and ULIDs.
I feel bad for using UUIDs, refactoring the code now is going to be hard