UUIDs are Bad for Performance in MySQL - Is Postgres better? Let us Discuss

MySQL is clustered by default on the primary key which means inserts have to be ordered, let us discuss why UUID (random in nature) has bad performance in MySQL and whether postgres wins here. We will also explain why Sequential Writes are Faster than Random in MYSQL and

🎙️Listen to the Backend Engineering Podcast

🏭 Backend Engineering Videos

💾 Database Engineering Videos

🏰 Load Balancing and Proxies Videos

🏛️ Software Archtiecture Videos

📩 Messaging Systems

Become a Member

Support me on PayPal

Join our Thriving Backend Community on Discord

Stay Awesome,
Hussein

[hnasr]

Learn the fundamentals of database systems to understand and build performant backend apps

Check out my udemy Introduction to Database Engineering

[yvestrudeau]

Thanks for discussing my article :) Pg will be faster at inserting rows in the table itself which is organized by rowid. It has to maintain a btree for the uuid PK values so it will eventually struggle like InnoDB, maybe a bit later in term of number of rows.

[rogersteele]

This channel has been a wealth of knowledge for understanding backend processes

[duyquang]

very well explained. this article show that using UUID will break important property of page cache, which is spatial locality

[VladimirSitnikov]

Nice review. However, at 17:57 you mention "in PostgreSQL there will be no problem because the table is not clustered". I am afraid you miss the fact that b-tree index pages have fixed (ordered) structure, so inserting random values (ints or uuids) would cause the same issue with random reads of **index** pages

[mosespeter]

Using the MySQL function UUID_TO_BIN(uuid, 1) would give you almost same performance as integer. I've been using it for a while now and it's nice

[kamesharumugam]

Thanks @hussein and Yves for this excellent information :) loved this info, reading more about clustered indexing now.

[BaishaliGhosh]

Your knowledge of RDBMS is amazing. Can you share links to other channels who explain NoSQL databases like MongoDB in a similar manner?

[hritiksingh]

Can you do a short video on uuid vs sequence . Such as auto incriment in MySQL and bigserial in postgres like which one to use where.. and where not to use

[vitiok]

Guys! Be kind to your Database. She doesn't deserve such a disrespect!

[Dimonion]

Fantastic explanation! Will use your videos as a guide to my colleagues :)

[teengrammer]

We rrright the rrrow, this was both funny and eductional. Kudos!!

[chairlovawitabat]

So the lesson here is that MySQL is best used if your database stores data in a roughly sequential manner, and PostresSQL best for random insertions (UUID insertions)?

[autohmae]

If I understand it correctly modern GUID in Windows are the same as the UUID RFC, but the RFC has a reserved part for older GUID Microsoft use to have (Microsoft didn't invent it, but the RFC came later and for compatibility it was added).
No, the biggest project where we communicate IDs with the outside world is also running MySQL (it wasn't my choice, I didn't start the project. PostgreSQL would also have been my choice), it's similar to Youtube video IDs, base70 or something of a pseudo random number and it's not the Primary Key of the table.

[henrikschmidt]

But if you add all uuids to a unique index that is stored in an unsorted list in postgres, doen´t it require the database to read the whole unique index list to compare every uuid in the list to know that it is not already in there? And in that case sorting it once by making a btree sounds like a lot less work to me.

[IBITZEE]

Hussein... you're the man!!! great info...

I'd like very much to ear from you about the page-mechanics in the databases (mysql, pg, mssql, acess/mdb, etc.)

Also the way rows are stored... how the data types are 'squeeze' in the record/row...
also how BLOBs are stored... ?along with the record... in a related-hidden-table... etc.

[rachydamine]

Very well explained, thank you Hussein

[hassaneljebyly]

well.. that's refreshing, thank you very much.

[luisgarciaalanis]

Ulids are sequential random guid, they should always be inserted at the end of the table, an alternative to guids

[milad]

Hi Hussein
But it's in clustered index situation