Indexing in PostgreSQL vs MySQL 

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

@@hnasr Thanks a lot🖤

Congratulations bro

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

Appreciate it Don! Will do

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

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

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

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

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

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

Good day to you to 🌞❤️

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

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

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

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

Thank you 🙌

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

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

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

"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.

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

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

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

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

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

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

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.

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

I did a couple of videos check out my software architecture playlist and system design playlists ru-vid.com/group/PLQnljOFTspQXNP6mQchJVP3S-3oKGEuw9 ru-vid.com/group/PLQnljOFTspQXSevtRqvMNycWfHM7cXc3d

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

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)

"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.

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

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

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.

@@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 =)

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.

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

Correct this is where TOAST plays a major role

@@hnasr tuble? tuple? table?

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

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

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)

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.

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.

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

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