Finished ✅ ... ( Self Note ) : New Learning: 1) It's great to see the query execution plan .. an insight can help a lot in optimization ( till now I just knew the query execution plan in theory ... How to access it was new to me ) 2) partial Indexing can effectively be used in tail graph ... The idea of partial Indexing is similar to Heavy Light Decomposition ( CP Background) ( it is used in graph ) so yeah .. cool to see a idea getting implemented in lite version and gaining Huge performance. Now I think ... I can try to use Heavy Light Decomposition in Vector databases 🤔 (a must try 😁 thing )
Hi Arpit, your approach to engineering is truly inspiring. Despite the intimidating jargon like Partial Indexes, distributed transactions, etc, your emphasis on first principles makes Computer Science feel like Common Sense. Keep up the great work and continue to inspire us. Thanks!
Got some really nice insights from the topic explained Arpit sir you are really amazing in explaining and breaking down hard things into simpler chunks
Hey, this course is not for beginner-friendly. The first video provided an intro but from the next video onwards we went straight into partial indexing which most people aren't aware of. If possible, could you also insert some basic videos, just to get the audience up to speed. Thanks for the amazing explanations though. You're extremely knowledgable and a great presenter.
What if we can have another boolean column which tell us whether hashtag popular or not. Default value of this column is false but Whenever hashtag count value reached to certain threshold(in this case 100) then it will update to true. What is pros and cons of this method over the partial indexing?
Hi, instead of creating a new partial index, what if they would have fired the sql query with count >= 100 filter directly? That would also sort around 169 rows only, right? Won't that be similar in performance as with partial indexes.
No. Because to power that you would have to create index on all data and that would make it bulky because of the long tail putting a stress on the database.
@@AsliEngineering Didn't the where works first, so after filtering the count>=100, there will only be 169 rows. So sorting only those rows is just enough right? Asking out of curiosity
@Aman, @Prasanth - I think Arpit's point here is to support the range queries efficiently you would have to create index on the table. Lets say you create index on all the rows then the index would be huge resulting in stress on database. Since we anyways want to show top hashtags only we can index on only rows that have high media count. This is what we call as partial index (creating index only on subset of rows that matches our filter criteria)
Thanks man I am about to work on a product and was stuck at autocomplete feature. Was looking to go with custom solution but now will use this feature of Postgresql. 😊
Hi Arpit bhaiya saw this video and even read the medium article, i just had one question , the article content is 10 years old, do you still think they will be using the same optimization techniques on the hashtags services or it could have changed by now.
very good explanation but as a beginner I dont know about what are indexing , partial indexing in database ... will figure it out through Google , thanks.
I am not sure if PartialIndex concept is in Oracle or SQL Server. But if we just create Index on Media_Count & then filter rows for media_count >= 100 & then do ordering then will it not be similar?
Yes. You can do that. But Instagram, during their initial days, went ahead with a simpler solution that can be shipped faster. This is from around 2015.
If Instagram first sort on the basis of media_count. So, sorting on the basis of media count will not take heavy resource ? If not, please explain, It would be a great help. Thank You !!
Sorting is an extremely expensive operation. Even keeping a secondary index that is ordered by this ever changing attribute will lead to multiple tree rebalances degrading the database performance.
They are old promotion videos. Yet to record the new one. Will be recording the new one today. There is no discount. I feel they are unfair to people who paid in full.
Upon every update the database will see if an index needs to be updated or not. So partial indexes (like regular indexes) will be kept updated upon every commit.
Can we not just say include only those tags where media count is greater than 100 in the original query ??? I'm not great with databases, please forgive my naive question.
I don't know if they are using the same technique or not. Most probably not but the optimization was pretty cool. It shows how partial indexes can be used to power queries over long tail distribution.
please make a video on heavy hitters with Twitter trending Example. Many of us has doubts related to scaling, choosing right ds or db. or how you can in prove those ....
Man, you are next level. Your chanel is a binge watch for engineers.
Finished ✅ ... ( Self Note ) :
New Learning:
1) It's great to see the query execution plan .. an insight can help a lot in optimization ( till now I just knew the query execution plan in theory ... How to access it was new to me )
2) partial Indexing can effectively be used in tail graph ...
The idea of partial Indexing is similar to Heavy Light Decomposition ( CP Background) ( it is used in graph ) so yeah .. cool to see a idea getting implemented in lite version and gaining Huge performance.
Now I think ... I can try to use Heavy Light Decomposition in Vector databases 🤔 (a must try 😁 thing )
Hi Arpit, your approach to engineering is truly inspiring. Despite the intimidating jargon like Partial Indexes, distributed transactions, etc, your emphasis on first principles makes Computer Science feel like Common Sense. Keep up the great work and continue to inspire us. Thanks!
5:31 "this is the beauty of Instagram, they never optimise" - truth has been spoken 😅
I admire them for this. They keep things extremely simple.
Simple systems scale.
I’m really feeling lucky to have found your channel. Keep up the good work !
Interesting pick and very well explained..love how in your teaching you start from basics and build up to the concept!!
I AM LATE TO FIND THIS CHANNEL, BUT LUCKY.....
THIS IS ASLI ENGINEERING CHANNEL.
THANKS ARPIT SIR FOR PROVIDING SUCH CONTENT.
as always concise n effective.. love it
Got some really nice insights from the topic explained Arpit sir you are really amazing in explaining and breaking down hard things into simpler chunks
Amazing way of explaining!
That's a perfect usecase for Partial Indexing! Great Video.
GEM. Thanks for such awesome and mindblowing content.
Hey, this course is not for beginner-friendly. The first video provided an intro but from the next video onwards we went straight into partial indexing which most people aren't aware of. If possible, could you also insert some basic videos, just to get the audience up to speed. Thanks for the amazing explanations though. You're extremely knowledgable and a great presenter.
Brilliant Insights 🙌
Thank you for providing good content ❤️👍
Thanks for this video.
What if we can have another boolean column which tell us whether hashtag popular or not. Default value of this column is false but Whenever hashtag count value reached to certain threshold(in this case 100) then it will update to true. What is pros and cons of this method over the partial indexing?
Simple yet so effective
Do we have something similar to partial indexing in SQL server?
Hi, instead of creating a new partial index, what if they would have fired the sql query with count >= 100 filter directly? That would also sort around 169 rows only, right? Won't that be similar in performance as with partial indexes.
No. Because to power that you would have to create index on all data and that would make it bulky because of the long tail putting a stress on the database.
@@AsliEngineering Didn't the where works first, so after filtering the count>=100, there will only be 169 rows. So sorting only those rows is just enough right? Asking out of curiosity
@Aman, @Prasanth - I think Arpit's point here is to support the range queries efficiently you would have to create index on the table. Lets say you create index on all the rows then the index would be huge resulting in stress on database.
Since we anyways want to show top hashtags only we can index on only rows that have high media count. This is what we call as partial index (creating index only on subset of rows that matches our filter criteria)
Is it same as functional indexes?
Thanks man I am about to work on a product and was stuck at autocomplete feature. Was looking to go with custom solution but now will use this feature of Postgresql. 😊
hii arpit bhaiya can you please make the video on collaborative software like git automerge how they works?
There is a podcast coming on Collaborative Editors 2 weeks from now. You can find it on my channel home page.
Bhaiya What if I want to seach a tag with media < 100 ? (Like what if its not a popular tag and is a part of long tail)
Hi Arpit bhaiya saw this video and even read the medium article, i just had one question , the article content is 10 years old, do you still think they will be using the same optimization techniques on the hashtags services or it could have changed by now.
does that even matter? what matters is the key concept we learned from it.
very good explanation but as a beginner I dont know about what are indexing , partial indexing in database ... will figure it out through Google , thanks.
Such an amazing optimisation Instagram applied , Arpit brilliant insights
Why did MySQL did not implement Partial Indexing? Also, can you compare between MySQL and PostgreSQL? What is the better database of those two
both has some advantages and disadvantages. more than performance there are a ton of other factors that decides which one is picked.
So if we create partial index with >100, if our query has >500 then also database would be smart enough to reuse above index?
Yes, because it's a subset of >100, it will also work if you use >1000 or even bigger
I am not sure if PartialIndex concept is in Oracle or SQL Server. But if we just create Index on Media_Count & then filter rows for media_count >= 100 & then do ordering then will it not be similar?
No it is not, yet.
@@AsliEngineering Appreciate such quick response, thanks 🙂
Sir, I'm curious about what would happen if the media count is less than 100. If there's no index for that, wouldn't there be a problem?
They are not serving for those hashtags. No person would be interested in looking up hashtags with count less than 100.
hey, is there a way to achieve this in Other Databases like MySQL, or MongoDB ?
Mongo does support partial index but MySQL does not.
Why not run a flink stream processor and store it in a sorted set. Then, return the top 5 trending?
Yes. You can do that. But Instagram, during their initial days, went ahead with a simpler solution that can be shipped faster. This is from around 2015.
@@AsliEngineering Yea thats what i figured. Thanks!
If Instagram first sort on the basis of media_count. So, sorting on the basis of media count will not take heavy resource ? If not, please explain, It would be a great help. Thank You !!
Sorting is an extremely expensive operation. Even keeping a secondary index that is ordered by this ever changing attribute will lead to multiple tree rebalances degrading the database performance.
Good explanation. source link?
Instagram engineering blog.
The prices in the video shown are around 15% lower than the actual price offered on the website! Is there any discount code which we can encash?
They are old promotion videos. Yet to record the new one. Will be recording the new one today.
There is no discount. I feel they are unfair to people who paid in full.
What is the cost of keeping index upto to date??
Nothing extra. It operates like a regular index just with an added filter.
How the new tags which entered the count > 100 will get indexed?
Upon every update the database will see if an index needs to be updated or not. So partial indexes (like regular indexes) will be kept updated upon every commit.
Thanks @@AsliEngineering
too good !!!
Can we not just say include only those tags where media count is greater than 100 in the original query ??? I'm not great with databases, please forgive my naive question.
Isn't the article 10 yrs old? Are they still using these techniques?
I don't know if they are using the same technique or not. Most probably not but the optimization was pretty cool. It shows how partial indexes can be used to power queries over long tail distribution.
@@AsliEngineering Yup, true that!
interesting
Most awaited video.
after watching this video 'itni kushi ..... intni kushi....'
ref: th-cam.com/video/gSlP71exbpQ/w-d-xo.html
please make a video on heavy hitters with Twitter trending Example.
Many of us has doubts related to scaling, choosing right ds or db.
or how you can in prove those ....