‫Hey, guys, some of you asked a fantastic question regarding the politicians topic that I made in my
‫introduction to database engineering course, and the question was, are you saying I have a big table
‫of customers and I'm on a partition by idea?
‫How do I dynamically horizontally partition those?
‫I'm going to create so many politicians and he show us a way to automate that.
‫I wrote you guys a script in order to do the partitions in an automatic fashion without you actually
‫manually typing that because it gets really tedious.
‫How about we jump into what we have here is a customer table and let's say I'm going to support one
‫billion customers.
‫The default integer field in the ID can support up to four billion, which is due to the bar thirty
‫two.
‫But for some reason, let's say it's a billion, a billion customers, a lot of users, obviously this
‫looks like it's a seventh of the world's population.
‫All right.
‫So what are we want to do is we want to create multiple partitions and each partition we're going to
‫have, let's say, 10 million each.
‫OK, and as a result, we we will end up with how many partitions?
‫If you do the math, it's one hundred partitions about we go to the code, show you how to do it.
‫I wrote it in JavaScript, but you can do it in any other languages.
‫You can do it all, even in school.
‫But I didn't want to introduce you with the new language that we don't have to learn.
‫So I'm going to do with the JavaScript.
‫It's a very with no jasso.
‫It's a very readable.
‫So the point and you get to write the client side code, which is like it's going to be computer back
‫in this case.
‫How about we jump into it?
‫So in this script, guys, I'm going to share with you guys.
‫I have already spun up a docker container and I showed you a monitor.
‫How to do that is very easy.
‫Is just a one line of code, literally just it out here so you guys can have it in case of kind of share
‫the script.
‫So all you have to do is just when you have a docker run dash dash named PGT, give it a name and then
‫do dash e postscripts underscore password then equal.
‫This is the password.
‫That's the password.
‫And then expose the port five, four, three, two, because we're going to need to connect to it.
‫Right.
‫And what else finally do detach and then Posterous that we'll just show you immediately.
‫So I don't really include the detach unless I make sure that my syntax is correct.
‫But never mind.
‫Never mind.
‫But we have now a container.
‫You can just spin it up and now we have it exposed to the to the local machine.
‫Now you can use this script to connect to that.
‫Right.
‫My machine name.
‫Just change your host name instead of Hosain Mac to yours and voila, you should be able to access that
‫stuff.
‫There is the script.
‫I have this note.
‫Yes, we'll have to script, populate customers and create partitions.
‫Great politician will first connect to the Postgres database.
‫Right.
‫So which already exist and the password of the progress using the credential postscripts users as we
‫created right here and then will connect to that Postgres database through the client is going to create
‫a new database.
‫And if it isn't going to drop, it's so careful not to hit this script against an existing database
‫that has a customer table.
‫This is dangerous stuff.
‫So no matter of fact, let's just commit that so you guys don't accidentally.
‫Run this against the real life database, I just added it, so if you want to run it multiple times
‫uncommon this good, if you want to do that, then the next step is to create the database customers.
‫And then once we have the database customer, we don't we no longer need the connection.
‫We can close the actually we now go and connect to the customers database that we just created and connect
‫to it.
‫And they will get to create a table.
‫And this table is going to be called customers.
‫It has to feel for simplicity, ID and name, and it's going to be partitioned by which I explain in
‫the partitioning, of course.
‫We can execute that right, but now this is not enough, we need to create the actual partitions, right?
‫So if we did the math, we are assuming that we have one billion customers and each customer, each
‫table, each participant should have 10 million.
‫So I did a little bit of a quick loop here to loop to create a hundred partitions and each is going
‫to have one million.
‫Ten million rows.
‫Right.
‫So I created two variables here.
‫I times 10 million.
‫Right.
‫So if the first line was this is going to be zero and the second this is going to be 10 million because
‫I plus one zero plus one thousand and then you can apply these lives.
‫This is very simple programming.
‫Nothing fancy really here.
‫And then the partition name is going to be called Customers underscore ID from Android two.
‫So that's the that's the customer.
‫That's the partition name.
‫And every loop you going to be looping through each of them, then creating all this stuff.
‫So now we're going to actually create the table that's called partition name just named here.
‫And then we're going to make it exactly look like the customers in terms of indexing the tables.
‫You can just play with this, really add more fields and then this code doesn't change because we're
‫going to like really inherit from the table structure.
‫And then that's the beauty, that's what we need, we need to attach that partition that we just created
‫to the customer stable and this is the range, the ranges from this and to and just pay attention to
‫the two is actually minus one.
‫So if it's like up until 10 million from zero to 10 million, that means it's from zero to ninety nine
‫million.
‫Nine.
‫And no, no, that's just something I learned the hard way.
‫And then I said it's going to do the second loop, execute the two queries because these are the first
‫quarter.
‫That's the second quarter.
‫These are just now get actually create the partition and then we're going to close that.
‫How are we actually run this where you guys can see it on this very simple stuff?
‫Once you clone your.
‫You can you can clone this reaper, you have this code, just do npm install it, which I already did
‫to install the PJI library, which will allow you to connect to postscripts and then do this zoom in
‫so you guys can see a node index, not the schools, create partitions, dot MDGs.
‫That's it.
‫Once you do that, we have an error, I think, because I'm already connected to the device.
‫Let's just go ahead and sit here and let's run it again.
‫I need to commend this out because I already have the database posterous that has got.
‫All right.
‫So now we get a drop.
‫And as you can see.
‫Look at that, it's creating all this beautiful partitions and that said, we're done and we're going
‫to before we push it, just come in this out for you.
‫And that's it now you will need to populate this thing, right, and it's very simple as you start inserting
‫instant customers that you only need to target the customer stable, that's it.
‫And that database will do its magic based on the idea, will try to insert to the right database.
‫So I don't hear a small script that creates a 10 million customers in one shot.
‫Right in a loop.
‫So again, for each one hundred times we're going to do this 100 times.
‫Let's do this again.
‫No capewell age group.
‫Customers all of a sudden, as it were, just starting those customers that said, I'm not going to
‫go through all of them and wait because that's just going to take a long time to complete.
‫But I'm just going to wait for maybe one or two amyloids while this runs.
‫Let's go ahead and show you how to connect to the actual database at.
‫This is how but let's do it again, so a Delcourt executed it PGT part, that's my my container name,
‫bicycle Nasho, they use it as Postgres and the database dashboard is customers that boom.
‫And just like that, you're in the database now you can do like a select max I.D. from customer stable
‫and tells you what is the maximum ID so we can show you the customer and all that stuff like all.
‫So it looks like we're OK.
‫We have started 10 million customers.
‫No, let's describe the stable that we just created.
‫If you few customers, you can see that we have an ID, we have the name and we also have a partition
‫range.
‫ID says I have 100 partitions, but it's not going to show you because there is a lot of partitions
‫you can use Dasch slash D plus to show all of the partitions.
‫So old Dashty plus customers.
‫Customers.
‫And as that is going to show you all the beautiful partitions and you can just start querying all of
‫them, so that's query the first one.
‫These are just normal tables at this point, right?
‫They can do select.
‫I don't know, count star from Bo.
‫And is going to to the stable normally, like just over and he goes, that's the number I talked to.
‫I told you about our guys.
‫That was a quick video showing you how to automate this stuff.
‫I'm going to share this code with you guys.
‫I'm going to see you in the next one.
‫You guys say goodbye.