Routing based on multiple keys

[winterec]

Describe the bug
I'm tinkering with a fully horizontally distributed application using Snowflake IDs described by Instagram: https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c

In my application there is no interaction between accounts, so when an account it first created it is assigned to a logical shard, and all future dependent objects for that account will also be located in that shard.

I've been able to make some very small patches to SPQR to extract the shard from the snowflake ID, and to add my snowflake hash function to the parser grammar. Overall it's working very well and it's able to intelligently route SELECTs to a single shard when given an ID, but also route to all shards when no shard is provided.

Multiple Keys

I'm adding multiple distribution keys on the same table, but in my use case they will always all refer to the same shard. These generally seem to resolve to a MultiMatchState in the code and error out. When I've patched out the errors those queries are working in my use case.

There are some notes in the code that the multi-shard routing is a TODO, but I would like to suggest that perhaps a first version of this could be to route to the single shard when the multiple calls to the hash function all resolve to the same single shard.

I can give this a go myself if that would be useful?

Insert statements

I'm having issues with queries of the INSERT INTO table (a, b, c) VALUES ($1, $2, $3); type, where both b and c are distribution keys. It seems that this is not supported. But when I skip past that error for debugging I'm seeing an additional issue where an empty input seems to be provided to the hash function.

Syntax errors in psql built-in queries.

Lastly, some psql commands like \dt seem to be failing. The work when run directly against the database but throw a syntax error when run through spqr-router.

example:

postgres=# \dt
ERROR:  client processing error: error processing query 'SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;': syntax error on pos 363, tx status IDLE

Additional context
Currently working off latest master, 214dc2d
Postgresql 16.3 both server and client

[Denchick]

Hi, @winterec,

I'm extremely sorry for the delayed response.

I've been able to make some very small patches to SPQR to extract the shard from the snowflake ID, and to add my snowflake hash function to the parser grammar. Overall it's working very well and it's able to intelligently route SELECTs to a single shard when given an ID, but also route to all shards when no shard is provided.

This is infinitely cool that you not only run SPQR, but also patch it! It's a really fantastic result!

I'm adding multiple distribution keys on the same table, but in my use case they will always all refer to the same shard.

There are some notes in the code that the multi-shard routing is a TODO, but I would like to suggest that perhaps a first version of this could be to route to the single shard when the multiple calls to the hash function all resolve to the same single shard.

Yes, I'm fully agree with you. What do you think, @reshke?

I can give this a go myself if that would be useful?

I think it would be very helpful and we would appreciate it.

I'm having issues with queries of the INSERT INTO table (a, b, c) VALUES ($1, $2, $3); type, where both b and c are distribution keys.

We have a problem with the multiple key separation feature. This feature is currently under development and one of our clients would like to see it implemented. However, we do not know when this will happen, because we do not have a specific deadline. But I'm very interested in your case, can you share the details why do you need this feature?

Lastly, some psql commands like \dt seem to be failing.
We do not currently support these commands. I will add this to our plans.
By the way, what result are you expecting to get here? Do you want a list of all possible tables on each shard (distinct) or just a list of the tables that the SPQR Router knows about?

Since it's been a long time since your message, I don't expect that the discussion is still relevant to you. But it would be cool if you send us your code, and we'll at least look at it.