Use column alias to identify sharding key from where clause

[JanKaul]

Is your feature request related to a problem? Please describe.
Using the custom SQL to specify the sharding key breaks the compatibility with some systems. It would be great if the sharding key could be specified with standard SQL.

Describe the solution you'd like
The sharding key could be specified for a select query by creating a column alias with a temporary name of pgcat_sharding_key and checking if there is a where clause that includes WHERE pgcat_sharding_key = X.

For example:

SELECT id as pgcat_sharding_key FROM data WHERE pgcat_sharding_key = 1;

Describe alternatives you've considered
Issue #168 proposes to use comments to specify the sharding key.

Additional context
None

[levkk]

Hi there,

Thank you for filing an issue. Could you give me a couple examples when the custom SQL syntax breaks compatibility with existing systems?

Thanks!

[JanKaul]

Hi,

First of all, thanks for all your contributions, this is an awesome project.

I would like to use pgcat with postgrest. It currently doesn't support the custom SQL from pgcat.

And I would expect that ORM systems also don't support the custom SQL syntax.

[levkk]

Ah yes, this is expected.

For this reason, we've introduced a couple more ways to handle sharding:

• using comments and parsing them with regex, e.g. /* sharding_key: 5 */ SELECT * FROM table_x WHERE id = 5; See regex_* settings in pgcat.toml.
• automatically by parsing the SQL with sqlparser crate, that can be configured with automatic_sharding_key setting in pgcat.toml

What I usually see people do is extend their ORMs to allow for working with pgcat and sharding. Of course that's not possible or desirable for everyone so automatic sharding is the north star for us. If you could test it on your end and tell us how it's working for you, that would be great. We also welcome PRs if you find any areas of improvement.

Your proposal could also work, although I'm not entirely sure why you can't issue a SET SHARD TO query instead of the query you've proposed. Is that something inherent to postgrest that limits you to only writing queries against tables?

Let me know if any of those work for you.

[JanKaul]

Thank you for your suggestions. I will have look at them. Especially the automatic sharding sounds interesting. Somehow I missed that feature before.

I will report my findings.

Postgrest maps the CRUD operations on a table to the http methods: POST, GET, PATCH, DELETE on a specific route. That's why the capabilities apart from the table operations is somewhat limited.

[JanKaul]

The automatic sharding looks great and it almost solves my use case. To entirely solve my problem I would need to define automatic sharding keys for multiple tables, but as I understood this is not supported.

I have one table with the actual sharding key and then dependent tables referencing the sharding key as a foreign key. I would need to also shard the dependent tables according to the foreign key.

[levkk]

We can add support for multiple sharding keys. I was already thinking about that, because foreign keys is a very common use case.

[JanKaul]

That would be cool. If you point me to where I could get started, I could try to implement it.

I guess the following steps are required:

1. Define how sharding keys should be specified in pgcat.toml. Possible solutions could be a list of entries like ["data.id","dependent.data_id"] or a map like { data = id, dependent = data_id }
2. Adopt serde to serialize the entries into a hashmap that maps tables to sharding keys.
3. Adopt sqlparser to check for all sharding keys in Hashmap

I guess I know what I should do to solve the first two steps. But I'm not really sure how to do the last one.

[levkk]

For 3, take a look at query_router.rs, specifically: https://github.com/levkk/pgcat/blob/master/src/query_router.rs#L568

Convert this to a for each sharding key loop and match all of them. We dedup on shard values later and only allow to query one shard per query.

[JanKaul]

Thanks! I will look into it. I will hopefully find some time in the next couple of days.

[JanKaul]

I finally had time to work on the sharding keys. I created a PR that should enable multiple automatic sharding keys. If I'm not completely mistaken it was surprisingly simple to implement.