Indexing in PostgreSQL vs MySQL

[@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 :)

[@hnasr]

Best of luck! most of the work is done by you by having the willingness to learn and implementing it :) All the best in your career

[@abhishek-agarwal]

@@hnasr Thanks a lot🖤

[@shreysom2060]

Congratulations bro

[@fahadalobaid4517]

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

[@hnasr]

Thanks Fahad! so glad! Welcome to the channel and enjoy the content dear, all the best

[@hnasr]

Check out my Introduction to Database Engineering Udemy course database.husseinnasser.com

[@oscarbarajas3610]

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

[@hnasr]

Appreciate it Don! Will do

[@TigasFMS]

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

[@hnasr]

[@esra_erimez]

@@hnasr If you are interested in adding presentations to your videos, then I suggest looking into Manim by Grant Sanderson, the host of the KZbin channel 3brown1blue.

[@koripellamohanakrishna2686]

@@hnasr brother having great content, if ppt was added it would be excellent.

[@debashisdeb472]

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

[@hnasr]

Glad you like them! and yes my videos are mostly related, I make on video , which trigger interest in another area and I make more videos on the topic

[@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!

[@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!!

[@jackedelic9188]

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]

[@chvenkatarajesh]

This is exactly what I was thinking. Appreciate if anyone has any answers

[@jackedelic9188]

9 months in and I'm (maybe other too) still looking for the answer.

[@anchalsharma0843]

​​@@jackedelic9188 Great question! I did a little bit of research on it, and I found out that to make sure the indexes (primary and secondary)are consistent MySQL would update all the secondary indexes( as well as primary of course) when you delete a row or update its primary key.

[@virendrabhati6685]

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

[@justinmobile8112]

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.

[@viralr5]

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

[@ashutoshmishra2328]

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 😊

[@hnasr]

You are correct! Deleting a row in innodb must update all indexes, updating a row only updates indexes on fields you touched

[@ashutoshmishra2328]

@@hnasr then we actually don't have any benifit in terms of time taken for deletion and updation, if we choose innodb over postgres. Only Reads makes the difference.

[@springer-qb4dv]

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

[@hnasr]

Thanks for correcting my understanding !! Appreciate it. Innodb always uses a clustered index by default and all data is organized in that way.

[@balakrishnan3725]

@@hnasr So, if Innodb uses clustered index/IOT then there wont be any extra hop which you mentioned. Hence, theoritically the time taken by read operation in Innodb and postgres would be almost same, correct? Also, any update on secondary index columns need changes in primary index and secondary index data structures. So, I am getting a question on how Uber gets benefited when they migrate from postgres to Innodb? Note: I had gone thru your talk on Uber's problem video as well.

[@danishhasan4688]

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

[@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

[@VCR47527]

I'm confused, if you delete a row, why do you call that changing the primary key? Wouldn't the primary key also be deleted, necessitating changes in the other indices?

[@gokukakarot6323]

Hi, hussain, is there something like this for any nosql db? Like indexing comparison with a sql database. We all know how CAP theorem and ACID compliance comes into play. But we dont get to compare at low level.

[@slahomar1497]

so let's say we have an index on column "name" in MySQL, when I search by name, 1 - I go to the name index, 2- go the primary key index, 3- go to the table and get the data ? so there are 3 steps till we get the required data ? as far as I understood from you on the other lectures is that, the table itself in MySQL in this case IS the index !! so is it 2 steps in this case or 3 ? I'm confused about this point

[@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.

[@vinny142]

"so basically InnoDB is good when " It's more rudementary than that. InnoDB is the only (as far as I know) storage engine that supports transactions, MVCC, and that is a thing that you *NEED* in order to make reliable database at all. So even if MyISAM is faster, it's also unreliable and therefor not an option, so I'd refer to say "InnoDB is your only choice in MySQL. regardess of it's issues." "good when you have multiple indexes in a table that is being frequently changed" I think it's more accurate to say that InnoDB does not have to update indexes if the column that is changed in the record is not in the index. I put like that to point out that it doesn't benefit you when you are updating indexed columns and columns that you update often are usually also the ones you want to search for, or that you want to add t a index to enable index-only lookups. Rememer: most databases spend 99.9% of their time doing SELECTs, not mutations, so in most applications you want SELECT to be fast. " Also it means you should keep the number of indexes to a minim - only columns that you know will be searched frequently." You need to use as many indexes as you need to keep performance within your requirements. If that reduces the update speed to much then you have to either scale up or change the way you do things. You should never put a column in an index if it does not need to be there, that's just common sense. :-) But sometimes you may have to create more indexes than you'd like simply because it makes your selects 100x faster, at the cost of making the inserts slightly slower. And sometimes you'll want to add a column that does not *need* to be in the index, but enables index-only scans. Basically: databases are voodoo. Damn cool voodoo, but still voodoo.

[@Flankymanga]

@@vinny142 "I think it's more accurate to say that InnoDB does not have to update indexes if the column that is changed in the record is not in the index." Hmm i had a bit trouble understanding this statment... As if there is an update to the row what trully happens is that new updated row is inserted and the old one is just flagged (unless vacuumed). If those were regular indexes all of them would have to be updated to incorporate the new row. But since in innoDB they are just "pointers" to the primary key - yes you could say that your way... but then even if in InnoDB the column updated was one of the indexes still only that index of the column updated will have to be updated as well to point to the primary key, and the primary key index ofcourse will have to be updated as well, but all the other indexes are safe? "You need to use as many indexes as you need to keep performance within your requirements. " - Did you mean "...as you can have..." i would not say that this is the intention otherwise indexes would be created implicitly on every column of a table created? Nope that to me would not make sense? Not each data column stored is meant to be searchable. If you have such a use case then you probably need some database system more like elasticsearch? Classic RDBMS are not meant to be used this way... "If that reduces the update speed to much then you have to either scale up or change the way you do things" - you probably meant to scale horizontally. Vertical scaling to my knowledge is about upgrading and tuning of hw. But you can only do so much... Horizontal scaling is more popular as you can add more nodes to the cluster that can be and often is a cheaper solution rather than have top tier hardware that is costly, companies opt for more of very cheap hardware to meet their demand? But ofcourse i would gladly hear someone correcting me.

[@vinny142]

@@Flankymanga " As if there is an update to the row what trully happens is that new updated row is inserted and the old one is just flagged (unless vacuumed)." Ok, this is getting very confusing very quickly :-) let me rephrase and see if you still have a question. PostgreSQL: - Every insert/update/delete creates a new version of a record so every index that points to that record must be updated to point to the new version of the record. - If the new version of the record changes a value that is indexed then the contents of the index must also be changed. InnoDB: - Every insert/update/delete creates a new version of a record so every PK index that points to that record must be updated. (other indexes are not touched) - If the new version of the record changes a value that is indexed then the contents of the index must also be changed. "You need to use as many indexes as you need to keep performance within your requirements. " - Did you mean "...as you can have..." No,I meant what I said: you should use as many indexes as you *need*, not "as you can create". This was a response to the notion that it's better to have fewer indexes. Having fewer indexes makes writing faster but indexes are required to create read-performance and to guanratee consistency so you do not really have a choice to just drop a few indexess just so writes will be faster. "If that reduces the update speed to much then you have to either scale up or change the way you do things" - you probably meant to scale horizontally. To scale up: increase in scale, get bigger, grow, more more more! Wether it is horizontal or vertical or diagonal, doesn't matter. If your performance is not good enough with all the required indexes then scaling is the only real solution. "Horizontal scaling is more popular as you can add more nodes to the cluster that can be and often is a cheaper solution rather than have top tier hardware that is costly, companies opt for more of very cheap hardware to meet their demand? But ofcourse i would gladly hear someone correcting me." It depends entirely on the company's financial situation and production requirements. Top tier hardware is more expensive, but it's also guaranteed to not crash or explode as often as cheap hardware. Also: if you start working with nodes for performance, you need to create a failover ability that can deal with a disaster; if you need two nodes to keep performance acceptable then you must have at least one spare server to replace a node if it fail (because you can nolonger run on a single node). If a node does fail and you replace it with the spare, you nolonger have a spare so if a second node dies, you are in deep sh*t. (and if all nodes where built at the same date they are likely to fail around the same age too...) Anyway, this reply is getting too long and it's not even my channel :-)

[@Flankymanga]

@@vinny142 Well basically i misunderstood what you wrote. But regarding the scaling facebook / google and other companies nowadays are also choosing the path of cheap hardware and they are including the mitigation strategies of node failures in ther everyday operations. i.e: regular hardware replacements, hds's, RAMs, entire nodes - it's called predictive failure mitigation. But thats a whole differrent topic.

[@vinny142]

@@Flankymanga "But regarding the scaling facebook / google and other companies nowadays are also choosing the path of cheap hardware" Sure, that has always been a preferred path if you need to scale a lot and do it quickly. Back in the MySpace-days they would expand their servercluster with dozens of servers per day to keep up with demand, but there comes a time that you are spending more money on maintaining the cluster than you would on just buying a few huge servers. Today we have blade servers that have thousands of cores that can run nodes in a virtual environment, we have kubernetis, we have EMC units with petabytes of storage. Expensive, sure, but also more reliable and that may make them cheaper on the long run. And ofcourse there is geological redundancy etc... very interesting but not even remotely relevant for 99% of companies today :-) Anyway, it's always interesting to see what the giant companies do and why they do it, just remember that what's best for a *huge* company is not necessarily good for a smaller company. Huge companies have probems that smaller companies simly do not have so the solutions that the huge companies come up with are designed so solve problems that smaller companies don't have. Interesting subject though!

[@luckystrike10557]

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 ?

[@alooooooola]

i think the purpose of using uuid is your id is unique across your services if there is a microservices app so it doesn't cause confusion

[@slahomar1497]

When mysql creates the index in the primary key, will it be IOC by default? And in this case the table itself is the index OR an actual index is created ?

[@ericndirangu5758]

Would be interesting to know how are indexes managed in other non transactional mysql engines such as myisam

[@user-sy4yz3kw4y]

Not a DB guy but innoDB primary key is the cluster index. So only one hop. Isnt it?

[@shahman1]

Hey Hussein! How would you go about deleting/updating rows to improve performances in the presence of multiple indexes? What I mean is, is there any way to overcome the problem of multiple index updates in case of many row deletions/updates?

[@0xc0ffee_]

I would suppose you're talking about PSQL, in that case nope, change engine

[@ekaterinagalkina7303]

Hi, Hussein, 5:50. I've also read about clustered index in InnoDB - that it is the same as a primary key index, and it contains table data (not points to it). Now I'me confused: is that true, and what's the difference between clustered index and the table itself.

[@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

[@hnasr]

Deletes and inserts have the most write-amplification in postgres ( need to update indexes) but an update only touches indexes when the fields updated are indexed) There is a heap only tuple optimization but wouldn’t worry about it now. Again we are talking about gaining an extra millisecond or two per insert. Which in normal operations doesn’t matter but in bulk it adds up..

[@BoomChockolaca]

Great job explaining the difference! Thank you for your videos

[@Flankymanga]

Good day to you Hussein -sensei :D

[@hnasr]

Good day to you to 🌞❤️

[@changlt9258]

Hi Hussein, thanks for making great content! Just a little question, why doesn’t MySQL need to update every index trees after deleting a row? Wouldn’t be a problem If a leaf node of secondary index tree points to a primary key that does not exist anymore? If that could happen, why don't Postgres do that too? Just leave the leaf nodes point to an illegal address and it will know the row is deleted

[@mohamedelidrissi810]

It has been answered in another comment. If a row is deleted, InnoDB will update all indexes because they can't point to a non-existent primary key.

[@_danish19]

A week ago I found your channel and I have already spent hours watching you gaining some real knowledge..Thank You For the efforts. I Wanna ask you a question, How does Whatsapp Sync contacts with the database like if someone have hundreds of contacts but only those are seen who uses Whatsapp and this all happens in seconds. Also when a contacts changes the display picture how does it updates in all the saved contacts phone locally how does it notify for a single contact update. Because If anyone updates We don't want all the users to again go to the backend and sync all the contacts but just one. Hoping for your reply

[@michaeldeng1981]

you are a good teacher

[@mohammed22hussain]

ابو علي، اطلق شخص يشرح باك اند وداتا بيس 🫡

[@nooruddinraotiwala490]

hey man awesome videos

[@FlaviusAspra]

How about non-indexed but variable-length columns like TEXT? If the text values are changed, don't all offsets in the table after the updated row have to be updated in the index, basically +delta bytes? Or such columns use some other tricks, e.g. Fixed-width pointers to the actual string? But then, where are the strings kept? I could imagine a lot of tricks, both with advantages and disadvantages, but basically the question is: how is data organized in a table for both innodb and postgres?

[@hnasr]

Fantastic question! EDIT: So in Postgres any update to any field whether it has an index or not will generate a new ctid for the row which will have to update all indexes in that table to point to the new ctid. I know in Postgres using variable length text field will store long-strings in secondary tables so the row-offsets do not change (8kb page and tuble can't span multiple pages so max tuble size must be 8kb) when those columns are updated. I am not sure of MySQL though.

[@FlaviusAspra]

@@hnasr is "TOAST" here related? Also, can I give it a hint about how much data I expect for such a column, so that that it reserves that space in the table?

[@hnasr]

Correct this is where TOAST plays a major role

[@FlaviusAspra]

@@hnasr tuble? tuple? table?

[@utsabbanerjee9672]

@Hussein I am confused. If I update the PK in InnoDB, why every other index has to be updated? I understand that the BTree structure of the PK index will change but doesn't it change only a few on-disk pointers? It probably doesn't change the actual location of the BTree node on disk. If so, then the BTree structure for other indexes need not change and the pointers to the PK index also need NO change. Please correct me, if I am wrong.

[@hnasr]

I guess I have to clarify what do I mean by updating the primary key. Secondary indexes in a row point to primates key of that row. When you change the value primary key for that row (from 7 to 9) for example with an update SQL, all those indexes have to now point to 9 instead of 7. Hope that clarifies i

[@utsabbanerjee9672]

@@hnasr I think I got the answer, my assumption was wrong. Thanks

[@akshayaghera8123]

do you have some content for , how to scale backend server with some live example ? it would be very helpful.

[@hnasr]

I did a couple of videos check out my software architecture playlist and system design playlists kzbin.info/aero/PLQnljOFTspQXNP6mQchJVP3S-3oKGEuw9 kzbin.info/aero/PLQnljOFTspQXSevtRqvMNycWfHM7cXc3d

[@SachithNalakaMuhandiram]

Just one question.. How many books you completed in behind shelves?

[@hnasr]

I like to reread books instead of stacking on new books. here are the books I read www.goodreads.com/review/list/37611091-hussein-nasser

[@pulkitkedia8679]

Hussein , if there is an update/deletion on an indexed column , then only the Btree (for that indexed column )would be rebalanced right ? and this would be true for both postgres as well as mysql . updation/deletion on a non indexed column wouldn't rebalance the Btree right ? , so why do they use different (index) pointer mechanism ?

[@hnasr]

Remember a non-index column doesn’t have an index therefore no btree to balance 😊

[@pulkitkedia8679]

@@hnasr Yes , so my point was updation/deletion isnt faster in mysql(compared to psql) on indexed column as btree is getting rebalanced same as Psql , only reads will matter .

[@elmeroranchero]

Very educational, I think the volume was too low, I'm not sure if it's a compression issue but anyway thanks for the information.

[@hnasr]

Sorry about that :( I suck at Audio trying to get better.. its either too low or too loud

[@elmeroranchero]

@@hnasr you're fine brother, it's the knowledge what's important. I use Postgres in one of my projects and knowing this really helped

[@saggitt]

If you delete a row in MySQL, the indexes are not updated? Are they updated later during some kind of cleanup? They still contain pointers to a primary key value that does not exist.

[@hnasr]

If you delete a row, only the primary index will need to be updated since is it the only index that points to the heap. And since mySQL uses InnoDB by default that index is a clustered index so delete is fast since the row and the index are organized to be in the same location. If you also have secondary indexes on other columns, those indexes will have to be updated to remove the entries that points to the primary key that we just deleted.

[@saggitt]

@@hnasr So MySQL will have an advantage over Postgres in terms of number of required operations in case when we update the columns that are not indexed, right? In case of Postgres a new row would be created and all the indexes would have to be updated to point to that new row. In case of MySQL only the row itself would have to be updated, the indexes are pointing to the primary key. If you are updating an indexed column, MySQL might still have the same advantage as only the indexes that contain that column would have to be updated. Thanks, I think I got it =)

[@kailashkarki770]

You changed the PlayStation box position.

[@user-he7zv2on2n]

Thanks, but there are some lack of illustration (

[@AndresFCamacho]

Great video.

[@mrluismartinezzz]

When using Postgres, many times I have encounter the error of trying to insert a new row with a duplicated primary ID. (Which this id is generated by Postgres) The table would always get out of sync after deleting some rows. So to fix, I would have to manually count how many rows and afterwards insert +2 or something. Could this be because of the “row has to know about every row mechanism” & somehow would glitch & get out of sync? I don’t know....

[@hnasr]

Were you using serial as a data type to auto generate the primary key? I have not seen this problem and I use postgres daily .. Interested to know more about your workflow because your workaround is not a good idea (slow+error prune with multiple transactions)

[@vinny142]

"Could this be because of the “row has to know about every row mechanism” & somehow would glitch & get out of sync? I don’t know...." No. The only way that PostgreSQl generates it's PK values is by a sequence and that sequence will never *ever* give you a duplicate value unless *you* reset the sequence.

[@mrluismartinezzz]

@@hnasr I think it would happened because when we wiped the database we didn't truncate the PK generations (i.e. force keys to start back at 1) or reverse ... we truncated and so it will start at 1 and then eventually hit a conflict if we didn't wipe all the database.

[@mrluismartinezzz]

@@vinny142 ^

[@vinny142]

@@mrluismartinezzz It's probably the second; you did reset the sequences and the database was not wiped. If you did wipe the database and reset the sequences everything would be fine. If you did wipe but not reset the sequences all the new PK's would just be higher and everything would still be fine. It's only if you reset the sequences and *not* wipe that you get this problem. So mental not: don't reset the sequences. Ever. Well, only if you have an extremely good reason which is.... yeah I don't really know a good reason except in composite keys. The only proper way to fix that is to update the sequences to MAX()+1 for each PK column, assuming that there are no composite PK's, and this *requires* that you put an exclusive lock on the entire table, otherwise you can get issues where a transaction has already fetched a new valuie from the sequence but your transaction canbnos see it yet and will reset the sequence back to the wrong value. I believe there is a query for this in the postgresql wiki somewhere.

[@DucNguyen-ez7oe]

your voice is great

[@ahmedwafik4784]

thanks for this great content

[@tanujdave814]

Awesome content 👍

[@hnasr]

Thank you 🙌

[@whenlifegivesyouLSD]

عظمة ♥️

[@abdullahyahya2471]

Nice video, .. Just the voice is a bit hard to hear..

[@professortrog7742]

Test, test, test. Any decision must be based on valid and realistic information.

[@mohammedhewedy8231]

Good video ... however lots and lots of annoying ads

[@GauravJain108]

You rock!

[@ranjithsivanandham1927]

Sir I have a doubt Why no one is creating a operating system after Microsoft and apple Even if they do it why it is not being a huge hit

[@hnasr]

There are no value of creating a new OS that Unix/Linux free os doesn’t give you. People have been contributing to the linux kernel to improve it (thats what Google did with containers cgroups)

[@vinny142]

Why nobody does it: it's a process that takes many many years and is extremely exensive. Once you do have an OS, no manufacturer of any device will have a driver ready so you can pretty much do nothing at all with the OS. Why it's not a hit: the only way to make money from selling an OS is by selling it to companies and companies will simply not migrate to a different OS. Companies use hardware that wasselected for the OS, they use programs on that OS, they have in-houseknowledge of that OS, the new OS would have to be extremely, absurdly, unbelievably better to make a migration worth the time and money.

[@Flankymanga]

there is even a linux that emulates Windows 10 GUI www.linuxfx.org - there are lots of operating systems based on linux kernel and they provide all the functionality and services that user needs. Currently there is no need to reinvent the wheel.

[@ranjithsivanandham1927]

@@vinny142 but Linux was built just in a year right

[@ranjithsivanandham1927]

@@hnasr if we make our own operating system with more number of features than windows and Microsoft can it be a huge hit sir

[@ranjithsivanandham1927]

Plz anyone answer the below question

[@gunjanrajtiwari1435]

I suggest keeping a conclusion or summary at the end of the video for those who dont want to see the whole video. Great content btw.

[@717deorajgope4]

PlayStation 😂😂

[@fantasticchupacabra7205]

speak a bit faster, even at 2x it's too slow for me...