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 …

Tag: postgres vs mysql, [vid_tags]
Xem thêm:


32 thoughts on “Indexing in PostgreSQL vs MySQL

  1. Jack Wong says:

    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]

  2. Flavius Aspra says:

    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?

  3. Pulkit Kedia says:

    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 ?

  4. Ashutosh Mishra says:

    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 😊

  5. Flankymanga says:

    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.

  6. Abhishek Agarwal says:

    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 🙂

  7. Luis Martinez says:

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

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *