‫What's going on, guys?
‫My name is Hussein.
‫And in this video, I want to discuss the idea of working with billion row tables in general.
‫And this is a very, very interesting point because as you design a system, it will force you to ask
‫a question.
‫Your table design, your document design, whatever flavor of a database you use.
‫How?
‫Do you anticipate this table to grow in the future after one, three, four years?
‫Do you anticipate these tables to grow so large that again and reach the billion level?
‫So in this video, I want to kind of discuss how do we work with such huge volume of data?
‫Right, because there are many ways of tackling this problem.
‫And I want to just discuss some of them.
‫I have three concepts here to discuss.
‫Obviously, guys, if I missed anything, let me know in the comment section below.
‫Before we jump into the video, guys, this video was basically inspired from my comment section specifically
‫with Vinnie.
‫He's a great database engineer and he always finds mistakes and things that I say wrong and and obviously
‫has a great feedback in general on my videos.
‫So I like this is just a shout out for him.
‫So it was spawn as a discussion on my Twitter system design video when I came up with with this arbitrary
‫design for the flow feature, which I'm going to reference the video right here.
‫Go check it out.
‫And so as a result, that design
‫generated a table which is.
‫Very, very huge.
‫Right.
‫So what I basically did is I have this following feature, right?
‫So I have this person following this person.
‫So I put the whole thing in one table and had added two roles with their ideas.
‫So this person is following this step.
‫And I said in the video, this is going to be a huge table, so what do we do?
‫And we had a discussion back and forth of what do we do with these kind of intuition, which inspired
‫me to actually make a video to discuss What do we do?
‫What do people do today?
‫If you have a large table, how about we discuss it?
‫So I have three basically concepts.
‫The first one is brute forcing your way to to process the table or work on the table.
‫So if you are trying to find a row inside this table, right, what you can do without the concept of
‫indexing, without acceptance of anything brute force your way, which is do multithreading, do multi
‫processing and chunk the table into multiple segments and search in parallel.
‫Right.
‫That's how basically.
‫Big data essentially and and Hadoop works, right?
‫So it was like map and reduce the subset of the table into smaller concepts.
‫So you can run in parallel and brute force your way and find what you're looking for and try to do the
‫process yourself.
‫Right.
‫So that's the idea.
‫Can I break this table into hundreds of pieces and search these spaces in parallel concurrently throwing
‫this problem on on 100 machine cluster that will work sometimes.
‫That's why I want to discuss the second point, which is can I.
‫Avoid processing the entire table.
‫Can I avoid processing the entire table and instead process a subset of this table only?
‫How do I do that?
‫The best best approach is use indexing, right?
‫Because that's what we do.
‫If we index a column on a table, then you essentially create a structure on the desk that will basically
‫it's ab3 or less M three that will help you reduce the subset on what you're searching.
‫So instead of searching the entire table for what you want, you search only a small subset, which
‫is the index, and that even it's own, it's a scan to find what you want.
‫And then you by finding that you, you kind of narrow what you were looking for.
‫It's like a binder.
‫And then in the secretary's office, right where you have, okay, there is the book and there is the
‫letter.
‫A And any contract that starts with A is is this anyone that starts with B?
‫Is this and that started with C, is this.
‫So you see it in color coded, right?
‫So if you if your contract is, I don't know, company is called zebra.
‫So you only immediately go to the Z color and then you start searching.
‫So you minimize what you're searching for.
‫However, that's indexing.
‫So let's search with a smaller set, right?
‫So instead of having the whole table, let's reduce the set, right?
‫So instead of working with a billion rows, maybe we're working with a few millions in this case.
‫Right.
‫Can I even go and reduce that set even more?
‫That's where database people do tricks like partitioning.
‫So partitioning is on desk by this huge table is now.
‫Broken into.
‫And I'm talking about here essentially horizontal partitioning, not vertical partitioning.
‫So horizontal partitioning means like slice the table in half, almost like in the middle.
‫And then you say, okay, rows from this to this is is here on this location on disk.
‫Right?
‫And then rows from this range to this range.
‫And then this location is different than indexing.
‫So the whole thing is still index, but we're literally partitioning the table into multiple parts.
‫So now.
‫How do I know which partition to search for?
‫You need another concept that tells you which partition to hit, and if you're lucky, you might search
‫one partition only or a couple.
‫And this is called the partition key.
‫So you always partition on a key.
‫Very similar to indexing, except the indexing work and the whole table.
‫Partitioning works also on the whole table.
‫But.
‫But it will partition will break down the table into smaller, smaller pieces.
‫And now you can you will have different indexes per partition.
‫Usually the database takes care of all that stuff for you, so it's almost transparent.
‫Working with indexes was working with what?
‫Partition is transparent from you as a client who queries this table.
‫So it's incredibly fast, right?
‫So if you know where to search for, you can hit the right partition and only hoping that you the partition
‫that you're searching for is in that and indexing also make that even smaller set.
‫So that's pretty cool, right?
‫And that's still where we have one database, still we have one machine and we broken this into multiple
‫partitions and now I can search exactly what I want to.
‫Now you can distribute that even further across multiple hosts by doing sharding, right?
‫So, so similarly to the concept of partition, you can still have partitioning and also add the idea
‫of sharding on top of that, which adds a little bit of complexity to your system.
‫But now you put the first 100,000 customers in one database and you put the second 100,001 database,
‫and they don't talk to each other.
‫And here's now the problem of transactions, right?
‫Because they are two different databases.
‫You just reduce the size of the table, obviously.
‫But now you complicated the client because the client is now should be aware of the Shard.
‫It's like, okay, I am searching for customer number 500.
‫Which shard should I head?
‫Oh, you had shard one because that's where it is, right?
‫And now going down deep into that shard, there are partitions of that table and going down into each
‫partition, there are indexes, right.
‫Or index.
‫And now you just you basically narrowed the billions into maybe a few thousand or few hundred thousand
‫rows, which is pretty good.
‫So that's the idea of what we do, right?
‫Shard partition and then index and then find a row exactly what we're looking for.
‫So that's the idea of of limiting what do we want to work with?
‫And the final thing is, and as I started thinking about it, it's like, okay, maybe we can avoid
‫all this together.
‫Why do you have a billion row table to begin with?
‫So that's on the database design, which was me in that case, right?
‫It's like, okay, maybe it's not a good idea to have a table so large.
‫Can we solve this problem so that I don't need to have a billion row table?
‫And in case of the of the Twitter following example, we might actually be able to I still didn't complete
‫the thought yet, but if you have like a profile table and say, okay, this is my ID, this is my name,
‫this is my picture, we can add a field called.
‫Followers count.
‫It's an integer.
‫We'll come to that.
‫Now there is another field.
‫Now most relational database support JSON.
‫We can put a JSON there.
‫Or a list field and add your followers in your profile.
‫So now we don't have a relational table that tells you, Oh, this guy is following this guy, this
‫guy is following this gal.
‫Now we have one profile and if you want to get your followers, then you go to your profile and fetch
‫that row and and pull that information.
‫And that's you have all the profile.
‫And every time someone follows you or some, you follow someone.
‫Now.
‫The hit is on the right level if I want to write.
‫Hey, someone just followed me.
‫I need to update those two columns.
‫I need to update the count to that.
‫I didn't have this problem in the first design, but the first design wouldn't scale as better as this,
‫in my opinion.
‫Right.
‫You can.
‫Now we start worrying about the right throughput, but I don't want to go through that stuff.
‫We can do message queues where we can.
‫Okay, let's write it asynchronously.
‫Update that.
‫Yeah.
‫There will be a little bit delay.
‫Who cares?
‫It's a follower count anyway.
‫We're going to pick the queue and then slowly just update these things.
‫So we have many ways to solve a problem.
‫So instead of to summarise the whole video, instead of working with the whole billion table row, try
‫first concurrently, process it, processing it.
‫Maybe I'll flip that a little bit.
‫Maybe try to avoid having a billion row.
‫That's the first thing.
‫I kind of said it last, right?
‫The second one, if you can't avoid it, then can you index it of your obviously what field to index,
‫then can you partition it right on the same table, on the same disk?
‫Right.
‫Can you partition your table so that they are smaller sizes?
‫And if if you can't partition and you can index, can you even if do you really need to short it so
‫that if it's even smaller and smaller or small pieces on multiple hosts.
‫Right.
‫Because if that host dies, then that's a problem.
‫Right.
‫So you even partition it on on horizontally, essentially on multiple databases.
‫Right.
‫Shards that will create complexity, which I try to avoid.
‫I talked about that a little bit.
‫And then finally, if you can't do any of that stuff, just do do a MapReduce to just run parallel processing
‫and try to process run your work so that you process the billion row concurrently with a massive army
‫of machines, if possible, if your database transactional.
‫Then that's kind of pointless because the moment you start the army searching or working with your huge
‫table partitioned, right spliced, then it will go out of there the moment you start because people
‫start editing, right?
‫People changing all the time.
‫All right, guys, I'm going to see you in the next one.
‫You guys stay awesome.
‫Goodbye.