I don't think CREATE INDEX CONCURRENTLY will be plays catch up game with INSERT/UPDATE/DELETE, instead for all new changes it can start inserting to index and for old tuples it can build index in background using LSN. This is just assumption though.
that implementation would not work... especially with postgres... taking snapshot of db -> you cant just retain this many snapshots... what if there are multiple indexes being created by separate threads? see where im going with it? there are other things to keep an eye out for, like the mvcc nature of postgres. like how updates and deletes actually create dead tuples. there are a bunch of things we need to consider because postgres is quite complex piece of software. i do completely agree with your approach on how to think though! i always do it myself, dont get how a software is doing something? how would you do it? the software is definitely doing it much better than your 5 min pseudocode but it does give one courage to actually explore the software, and gives you appreciation for the tool!
i have a question, and thanks a lot for the video. what happens if one row in an index -- covering index -- or a table is bigger than a page in PostgreSQL? ( which is usually 8KB) , and is it fine to create a denormelized table that exceeds 8KB to avoid joins and be able to create a covering index on the whole view ?
This sounds like what happens during a live VM migration with memory. As I recall part of the process at the very end is that once everything is more or less caught up, it pauses the vCPU, and finish and copies any remaining pages it needs so that everything will remain in sync. Just to note it has been a while since I read up on that so I am probably grossly oversimplifying and missing out on some crucial details. If anyone knows more please feel free to correct me.
I was thinking in the same lines. My instinct for creating big index online where concurrently option is not available goes like this. I would have a copy of the database, where i create the index, then apply the WAL entries from the online db in here which should be fast (will pause transaction in the online db for milliseconds), do multiple passes if too many WAL entries. Then will make the new db online. Do you think this will work? I mean is the assumption that the diff WAL apply should be really fast for small no of remaining WAL entries?
Their is this saying in my country/language: did you fall off the stairs ? 🙂 Because you got short hair now (implying that you might have had surgery, etc.) Having said that: I wonder if they have a process/thread which just keeps track of the changes which apply to the index and keep a list of those (so you don't need to check all the logs)/
![Shenseea - Dating Szn (Options) [Official Music Video]](http://i.ytimg.com/vi/eUePmZFIXUg/mqdefault.jpg)
When I listen to his videos while closing my eyes, it feels like Dictator movie's Alladin is teaching computer science. 😂❤
Lmao
Silly
I don't think CREATE INDEX CONCURRENTLY will be plays catch up game with INSERT/UPDATE/DELETE, instead for all new changes it can start inserting to index and for old tuples it can build index in background using LSN. This is just assumption though.
Potentially we can use hybrid strategy. Lock when LSN theta is under certain thresh-hold.
this was very insightful, thank you for this
that implementation would not work... especially with postgres...
taking snapshot of db -> you cant just retain this many snapshots... what if there are multiple indexes being created by separate threads? see where im going with it?
there are other things to keep an eye out for, like the mvcc nature of postgres. like how updates and deletes actually create dead tuples. there are a bunch of things we need to consider because postgres is quite complex piece of software.
i do completely agree with your approach on how to think though! i always do it myself, dont get how a software is doing something? how would you do it? the software is definitely doing it much better than your 5 min pseudocode but it does give one courage to actually explore the software, and gives you appreciation for the tool!
Please make a video on parallel writes and things that affect that like row lock or issue because of index which causes deadlocks
I think locking the table for catching the rows that have been inserted while building the index for the first time won't be an issue.
Best books for back end engineering mid level having bit knowledge on code
i have a question, and thanks a lot for the video.
what happens if one row in an index -- covering index -- or a table is bigger than a page in PostgreSQL? ( which is usually 8KB) , and is it fine to create a denormelized table that exceeds 8KB to avoid joins and be able to create a covering index on the whole view ?
This sounds like what happens during a live VM migration with memory. As I recall part of the process at the very end is that once everything is more or less caught up, it pauses the vCPU, and finish and copies any remaining pages it needs so that everything will remain in sync.
Just to note it has been a while since I read up on that so I am probably grossly oversimplifying and missing out on some crucial details. If anyone knows more please feel free to correct me.
I thought that create index was a writing process too, because it doesn't read from the database or do i not understand it well 🤔?
Hey HN... can you do a video on Vitess and the proc/cons
I was thinking in the same lines. My instinct for creating big index online where concurrently option is not available goes like this. I would have a copy of the database, where i create the index, then apply the WAL entries from the online db in here which should be fast (will pause transaction in the online db for milliseconds), do multiple passes if too many WAL entries. Then will make the new db online.
Do you think this will work? I mean is the assumption that the diff WAL apply should be really fast for small no of remaining WAL entries?
Their is this saying in my country/language: did you fall off the stairs ? 🙂
Because you got short hair now (implying that you might have had surgery, etc.)
Having said that: I wonder if they have a process/thread which just keeps track of the changes which apply to the index and keep a list of those (so you don't need to check all the logs)/
Mussahi book contd. from collection of books in older videos
Great video!
I thought you had a new guest but in fact it's just you with a new haircut hahahaha
Nice video though, bro 👍
What’s up with the haircut brooo 😂