Good day everyone. Thanks for watching another episode of "Mike's Super SharePlex Show." Today I am going to demonstrate a feature of SharePlex called horizontal partition replication. Horizontal partition replication is used to divide the rows of a table into separate processing streams.
You can use horizontal partition replication to replicate a subset of rows to the target while retaining the rest of the rows in the source, replicate different subset of rows to different targets, divide the replication of a source table into parallel post-processes for faster posting to the target table.
For today, we're going to replicate a 300 plus row insert that will horizontally partition the data to three different postgres instances based on the last name. Sort of like sharding at a basic level using the last name for our shard key. Let's go ahead and start.
Here is what our configuration file looks like on the SharePlex source machine. As you can see, it has the typical entries for the source table and schema, our target schema and table. But here's where we change things up a little bit. Instead of the usual routing map, what we have is what's called a partition schema. And I'll go into that shortly for you.
Then on the next line, we have our exclamation point. And finally our routing. As you can see, we have one for postgres1, postgres2, and postgres3. So what is a partition schema? Partition schema is data that gets put inside our SharePlex partition table, and what it does is it holds information on how to split the data up.
As you can see, we had our partition schema 1, 2, and 3 that you saw in our configuration files. And if you look, shard1 goes to postgres1, and here are our column condition. That's where the column conditions column where we tell SharePlex how we want the data to be sliced up.
If you notice, on shard1 we want the last name to be less than J. On shard2, we want the last name to be greater than S. And finally, on shard3, we want the L name to be between J and S. OK, so here we go. Let's go ahead and let's run our insert today.
We're going to be running a 300 plus row insert that's going to horizontally partition the table into three different postgres instances as I showed you. This data I have here, I generated it with Toad for Oracle. Toad for Oracle is a very useful tool for generating a lot of data in a really quick time.
So let's go ahead. Let's confirm that we have nothing in our table right now. As you can see, our row count is 0. And on our postgres instances-- postgres3 zero data, postgres2 zero data, and finally postgres1 zero data. Let's run our insert.
OK, our insert has been run. Let's just confirm we did commit. And let's check and see what we have here. Here we have 333 rows, and you can see all the data. Here we're going to be partitioning it out by the last names, as you see here.
Let's confirm that they replicated out. Postgres1, as you can see, here is our data. And it starts at A and ends at I. Let's go to our postgres3. And we pick it up at J and go down to R. And finally into our postgres2. We pick it from S and we get Z.
So that completes our demonstration. As you can see, SharePlex is very versatile, and can be used in some very creative ways. Like the pseudo-sharding that I demonstrated today. For more information, you can visit SharePlex.com. Thanks for watching, and have a great day.
04:16