3 ms·
I think the key line is here: > Another way to run the query on Citus is to always join on the distribution key, such that the filter can be derived from the j
by gshulegaard 9y ago
I think the key line is here:
> Another way to run the query on Citus is to always join on the distribution key, such that the filter can be derived from the join, which gives an equivalent query plan...
This article is a bit opaque and could be explained a bit more IMO. It requires a bit of background intuition about what is going on in it's current form.
But from my perspective, this Schema appears to be using USER_ID as a distribution key. As in, USER_ID specifies nodes/shards where corresponding data exists.
I don't use Citus, but I work on a project that rolls our own sharding for PostgreSQL in a similar way. We use ACCOUNT_ID to distribute data to a specific shard(s). Therefore all of our tables in a shard must have an ACCOUNT_ID column in order for our shard lookup/routing to work. In my data model the control flow works somewhat like:
1. Pass ACCOUNT_ID as part of Query.
2. Routing middleware looks up shards corresponding to that ACCOUNT_ID.
3. Connections are made to appropriate shard(s) and query is run. Other shards are ignored since they should not have any data for that ACCOUNT_ID.
- ngrilly 9y ago> Pass ACCOUNT_ID as part of Query How do you pass the ACCOUNT_ID? Do you parse the SQL query to know where the ACCOUNT_ID is used? Or do you pass it out of band?
- gshulegaard 9y agoOne SQL Query, middleware is responsible for grabbing the ACCOUNT_ID and connecting to the appropriate shards. This is why ACCOUNT_ID is needed as part of all the models on the shards because we don't want to alter the SQL query after middleware connects, we just want to run the unaltered query.
- ngrilly 9y agoYes. I meant where does the middleware grab the ACCOUNT_ID from?
- gshulegaard 9y agoThe SQL query.
- ngrilly 9y agoDoes your middleware parse the SQL query in order to extract the ACCOUNT_ID?
- gshulegaard 9y agoShort answer is No. We use an ORM/abstraction layer so we can actually get the ACCOUNT_ID as a property of our in memory object before the query is formatted. In this way we can inject the proper connection for the formatted query to be run on before/during handoff to the ORM. Longer answer is you could do this same injection any number of ways...it is just convenient for our existing stack to do it this way. In some places we actually do it differently in order to facilitate direct queries (without the ORM layer).
- ngrilly 9y agoVery clear. Thanks!