Indexing in PostgreSQL vs MySQL

In this video, I explain how both Postgres and MySQL store their indexes and their effect on reads vs writes. Let us discuss

0:00 Intro
1:00 Tables
2:00 Indexes
3:20 Indexing in Postgres
5:00 Indexing in MySQL
6:35 What Happens on Update on Postgres
7:20 What Happens on Update on MySQL
9:00 Reads on Postgres
9:40 Reads on MySQL

🎙️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

Stay Awesome,
Hussein

[abhishek-agarwal]

Hussein, thanks to you I've got multiple job offers. I cannot stress this enough how much you've helped me. Software engineering is all about a certain way to think, to see problems from a certain perspective and I learnt it from you. Once again, thanks a lot :)

[vanhieu]

Hi Hussein, I believe some of the information you provided may be a bit unclear. First, when a row is deleted in MySQL, all the indexes for that table will be updated.

Second, when discussing the differences between MySQL and PostgreSQL indexing, it’s important to consider how these two databases handle data writes:

• PostgreSQL: When a row is updated, a new version of that row is created because rows in PostgreSQL are immutable. As a result, all indexes on the table must reference the new row. Additionally, if any transactions are still holding the old version of the row, the indexes will need to maintain entries for both the old and new rows.
• MySQL: When a row is updated, it update directly the row in place not create new version so only the index related to the column with the updated data is modified.

[fahadalobaid]

Hi Hussein, I found your channel yesterday and it is just gold. Thanks a lot for the amazing content.

[TigasFMS]

Suggestion: Show a PP presentation for these kinds of videos. Thanks for your content. :)

[oscarbarajas]

I like that you summarize the information in your videos and your style seems like mine. Thanks man... keep pushing this channel!

[GauravBoraJodhpur]

8:18 - Heath Ledger (Joker) explaining database indexing.
I studied indices in college but now I can feel them in my mind! Many thanks!!

[virendrabhati]

It was in my bucket list to watch Uber from postgresql to MySQL and clear it in single line.

[debashisdeb]

I guess this is coming from the Ubers move from PG to MySQL :) Your videos are gold ! making a habbit to watch them everyday !

[ashutoshmishra]

Hussein, two doubts.
1). In innodb if we delete a row from table, primary index has to be updated ( actually one entry would be deleted), then all the other indexes pointing to primary index should also be updated otherwise they'll be pointing to a "no longer existing" memory location. If it'll happen then there is no difference in innodb and postgres in case of deletion.
2). When update operation is performed on a column which is a secondary index in innodb, then it is not necessary that primary index will be updated (because it might not have that column as part of primary index), only secondary index(s) will be updated which has that column. But again it will be same as postgres because there also all the indexes which has that column, will be modified.

If both of the above mentioned points are valid then innodb indexes are same as postgres indexes in case of update and delete, and for read operation innodb indexes would be little bit slow because of extra hop as you have mentioned.

Please correct me if I'm not getting it correctly.
Thanks for this video Hussein 😊

[danishhasan]

Hey Hussein, you're genius, it's a pure gold. Thanks a lot :)

[prionkor]

WOW! I don't understand why this video has so little views and likes, I have just subscribed to your channel. Very nice explanation, even without any diagrams and charts you did great!! Love from Bangladesh!

[jackedelic]

Hi Hussein, 7:22, you said that in mysql, if we delete a row, only the primary key needs to be updated, other secondary indexes are not aware of this removal. Does this mean there is dangling/redundant information in the secondary indexes (column values no longer exist after deleting the row but still exist in their corresponding indexes)? How does the db engine handle this? Is there any documentation/source I can read? Appreciate your help :) [for the context i have completed your db course, but struggling to understand this indexing part]

[viralr]

OMG, you explain things ao well. Just one thing, please exaplain with any simple table and drawing.

[springer-qbdv]

You misunderstood innodb layout. Innodb tables are IOT (index organized table). MEANING that all table data is stored in primary key!!!

[patelmalavdev]

Hussain, Indepth Intro to FaunaDB plz, reason It's an Document based DB with ACID compliment on all the Clusters and some graph based too

[BoomChockolaca]

Great job explaining the difference!
Thank you for your videos

[Flankymanga]

7:24 so basically InnoDB is good when you have multiple indexes in a table that is being frequently changed - deletes are being performed frequenty. So that in order not to update all the indexes only the primary key is being updated...

Edit: Also it means you should keep the number of indexes to a minim - only columns that you know will be searched frequently.

[emjaytripleo]

Is this the reason why deletes are considered an expensive operation on a large table ? From the looks of it we should be wary of deletes in a postgres table which has more than 2 indexes and a has the potential to have grow to millions of records, and so basically updates on tables are the same in both postrges and InnoDb? I have to say you have me hooked on backend Engineering

[luckystrike]

Hi Hussein, your videos are really helpful, keep it up! So, one question, Is it safe to say postgres does not have PK, since they're all the same. And also, using UUID as PK in postgres is the same as using AUTOINCREMENT INT, while in MySQL could cause Page thrashing ?

[justinmobile]

Not sure about mysql but with postgresql it automatically creates an index for PK's, FK's, unique and exclude constraints. So you don't want to inadvertently create duplicate index's in these use cases.