GitLab Sharding Working Group, 29 Apr 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Postgres: Partitioning + FDW / foreign partitions

Description

Quick demo of using postgres declarative partitioning in combination with foreign data wrappers to push partitions to a foreign server.

We're tackling one table of the GitLab schema here, creating 8 partitions and distributing them on 2 shards. Shard in this example is a local database, which can also live on a remote cluster.

Part 1: https://www.youtube.com/watch?v=MiZFtM84x44
Part 2: https://www.youtube.com/watch?v=nt4Khi9Gr3o&feature=youtu.be
Part 3: https://youtu.be/ztQtNmSYmEo

https://gitlab.com/gitlab-org/database-team/team-tasks/-/issues/53

A

Okay, so on the github database group we're thinking about how to do. Database, sharding and we've been thinking about Postgres partitioning a lot and I've been toying with the idea that we had about putting those partitions on the foreign service so using a foreign data Ripper to basically distribute the data across multiple charts and I'm, going to record a quick demo of how that could look like pretty hacked together. Perhaps that's in start so in this example I'm again using the issues table here and we're petitioning by a hash of the project.

A

Id reason is simply that this is already available, so we're gonna have to add more columns and load the data for that we're going to create eight partitions we distributed on to shards and I. Shard is a local database here. So that's a physically separate database, mostly in cluster, and the idea is that, obviously you can. You can use that later and run that on a different classroom as well um I'm in my development environment here I have a blank database. So that's the standard, github schema and I have a pending migration here.

A

So while I run that, in the background, we can perhaps take a look at this, so in this migration we basically create those two shard databases. We drop the issues table, so that's the existing one just get rid of that, and then we run this sequel script and that basically recreates the issues table and makes that a partition table partition by fatigue, Rd create a sequence and all that and note that there is, we don't create any indexes primary keys, Franky's. Those all go to the foreign tables that we create later is the interesting part.

A

Basically, we create a separate schema for putting the tables or petitions later we create the extension for the for another rapper. We create two shards, those are servers, foreign service and they basically point to the same my local police instance and they use a local database each those are the ones we created earlier, a bit of a user mapping and then we create the foreign tables, also, basically the partitions of the issues table. So this is pretty standard partitioning, except that we do it on the foreign manor, and we point this to those charts.

A

So basically cutting this in half and distributing those those partitions equally on the shards all right, so that worked fine. um Then, let's take a look at how to create those charts. I put that into simple script, basically taking a template for a foreign table and then just creating a sequel scripts for each of the charts, and you can look at those as well. So this is yeah creating the first foreign table or you know the first partition same schema, creating the indexes not adding the foreign keys.

A

We can talk about it later and then repeating the same thing for for the next partition. Basically, so let's run that as well doing that on shard one.

A

And then we do the same thing on shards too.

A

Okay and then, while I run some seeding so that we get some actual data, I run that in the background and that we can perhaps look at the the stuff that we just created just getting the seating right here all right. So this is the original schema. This is the issues table are pretty much the same, except it doesn't have any Nexus constraints. It has those eight partitions. Take a look at those. It is pretty standard so pointing pointing to those parishioners that we created taking a look at those.

A

We can see that those are foreign tables so partitions and there's no foreign tables. And now, if we take a look, we have those two local databases, char 1, HR 2, can connect to those and then again see there is a hard schema.

A

Easier here and those are just plain tables to only created earlier looking at those, this is again the same schema there. We have those indexes, but no foreign keys, reason why we don't have foreign keys here is that you can obviously only relate to something or reference, something that is on the same chart. In this case. We, for example, we'd want to reference the users table from the cheese table, but truth is we don't have the users tables on the sanctuary, so we can't do that.

A

So that is a sort of limitation other you can't have cross charts, crush our foreign keys or constraints stuff like that. Okay, then, let's go back to the main database and the seedings are still running, but it already created some issues, I suppose, let's take a look at this, just selecting everything so there's a bunch of issues in the table.

A

Let's purposely a plan for the fourth career, and we can see that just looks like partitioning, except that those are foreign scans, so we're scanning each of those partitions and and each of them lives somewhere else. We scan that foreign server.

A

Perhaps we can take a look at how this works for when you have the partitioning here available. So in our case it's project ID.

A

We can perhaps take quick look which projects we have so just taking a random example.

A

So now we add the project, ID filter, let's say project 1 2, and then we look at this and now we don't scan or we only scan the project. The partition for this particular project, or you know very- were project the hash of the project. Id or modulo. 8 is 2 and those all those projects are in there and we only scan the Frankie, so we basically skip over all the other partitions. That's pretty much the same with with local partitioning here again, just that we're scanning on the foreign server.

A

That's pretty much it I. Think next step would be to figure out how we would do schema migrations because that's a real pain. We already know that and with Geo, so I think that's the reason why GU moved away with from foreign data representing the schema in the basically the main database and then also you have the same schema in the in the shard database.

A

I'm getting that right and actually look at this yeah there is this schema you have to maintain, and you also have to maintain a scheme out the foreign table in the main database and that might that's for sure, more complex to manage all right. That's pretty much it! Let's see what the next steps.