Why create Index blocks writes

Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
database.husseinnasser.com
Why create Index blocks writes
In this video I explore how create index, why does it block writes and how create index concurrently work and allow writes.
0:00 Intro
1:28 How Create Index works
4:45 Create Index blocking Writes
5:00 Create Index Concurrently
Discovering Backend Bottlenecks: Unlocking Peak Performance
performance.husseinnasser.com
Fundamentals of Backend Engineering Design patterns udemy course (link redirects to udemy with coupon)
backend.husseinnasser.com
Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
network.husseinnasser.com
Follow me on Medium
/ membership
Introduction to NGINX (link redirects to udemy with coupon)
nginx.husseinnasser.com
Python on the Backend (link redirects to udemy with coupon)
python.husseinnasser.com
Become a Member on KZbin
/ @hnasr
Buy me a coffee if you liked this
www.buymeacoffee.com/hnasr
Arabic Software Engineering Channel
/ @husseinnasser
🔥 Members Only Content
• Members-only videos
🏭 Backend Engineering Videos in Order
backend.husseinnasser.com
💾 Database Engineering Videos
• Database Engineering
🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/6...
Stay Awesome,
Hussein

[@herrxerex8484]

this was very insightful, thank you for this

[@fueledbydata]

Potentially we can use hybrid strategy. Lock when LSN theta is under certain thresh-hold.

[@kartik4792]

Great video!

[@Chill_panu_dude]

Please make a video on parallel writes and things that affect that like row lock or issue because of index which causes deadlocks

[@empuraan4710]

Hey HN... can you do a video on Vitess and the proc/cons

[@arpanmukherjee4625]

When I listen to his videos while closing my eyes, it feels like Dictator movie's Alladin is teaching computer science. 😂❤

[@xxyz9819]

Lmao

[@jigsaw2253]

Silly

[@jacob_90s]

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.

[@kartik4792]

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.

[@arpanmukherjee4625]

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?

[@user-bk5xo1gj7k]

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!

[@malekalhourani5930]

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.

[@mustaphab32]

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 ?

[@nafreenuddin6833]

Best books for back end engineering mid level having bit knowledge on code

[@al-assili-sounds]

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

[@autohmae]

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)/

[@reactdeveloper2368]

Mussahi book contd. from collection of books in older videos

[@hamza201183]

I thought you had a new guest but in fact it's just you with a new haircut hahahaha Nice video though, bro 👍

[@mustafajawed918]

What’s up with the haircut brooo 😂