Avoid the need for distribution column filters when unique mapping is known
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
From the Citus NL lunch conversations lab:
When migrating a multi-tenant applications, users need to put a lot of effort into adding distribution column joins and filters. In some cases, we may be able to avoid these by ensuring relationships are explicit within the data model.
Imagine the following data model:
```
CREATE TABLE person (person_id bigint primary key, org_id bigint, UNIQUE (person_id, org_id));
CREATE TABLE salaries (person_id bigint, org_id bigint, salary double precision, FOREIGN KEY (person_id, org_id) REFERENCES person (person_id, org_id));
CREATE TABLE salary_payments (person_id bigint, org_id bigint, payment_id bigserial, paid_on timestamptz not null, amount_paid double precision, FOREIGN KEY (person_id, org_id) REFERENCES person (person_id, org_id));
SELECT create_reference_table('person');
SELECT create_distributed_table('salaries', 'org_id');
SELECT create_distributed_table('salary_payments', 'org_id');
INSERT INTO person VALUES (1,123);
```
In this data model, a person ID uniquely belongs to a specific organisation ID across all tables. Because the person ID is unique in the `person` table, it cannot be reused across organisations. Because there are foreign keys from all distributed tables that include both the person ID and the organisation ID to the person table, the relationship between the two fields is enforced in all distributed tables.
Now when we see a join such as:
```
SELECT * FROM salaries s JOIN salary_payments p USING (s.person_id = p.person_id) WHERE amount_paid < salary;
```
We can infer a distribution column join:
```
SELECT * FROM salaries s JOIN salary_payments p USING (s.org_id = p.org_id AND s.person_id = p.person_id) WHERE amount_paid < salary;
```
The general rules for being able to do this are:
1. Both distributed tables have a foreign key to a reference table that includes both the joined column and the distribution column (also requires the reference table to have a unique constraint that includes both columns)
2. The column in the reference table that is referenced by the joined column has a unique constraint
3. The tables are co-located.
The data model above tells us that a person ID will only appear in one set of co-located shards, but it does not tell us which co-located set of shards a given person ID belongs to. However, if we implement #1615 (or use MX), then the mapping between person ID and distribution column value is stored on the coordinator.
In that case, we see a query that has a filter on a column that is known to uniquely map to a distribution column value with that table, such as:
```
SELECT * FROM salaries s WHERE person_id = 1;
```
We can look up the org_id for person_id 1 in the local copy of the `person` table and automatically rewrite the query to:
```
SELECT * FROM salaries WHERE person_id = 1 AND org_id = 123;
```
It can then be sent directly to the right shard instead of an expensive multi-shard query. We could also skip rewriting the query and just query the right shard at execution time. We can also apply both techniques when there is a join and a filter.
This approach puts rather strict requirements on the data model, but changes to the data model are often easier to make than changes to the app. A downside of the approach is that it necessitates putting the columns that are referenced by the join and filter columns in a reference table.
Contributor guide
Assessment
This issue has not been assessed yet.