citusdata / citusdata/citus

Shard routing support for DDL commands

Open
#5,957 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

For regular CRUD operations on rows of tables, when passed a value for the distribution column as part of the command, Citus is able to route that command to the specific shard that holds data for that distribution value. The same is not true for DDL statements. An example where this would be a benefit is in partial index creation by distribution value.

With an example schema for table `my_table` :
```
CREATE TABLE my_table
(
shard_key text not null;
id text not null;
col1 text;
col2 text;
....
constraint table_pk primary key (shard_key, id)
);
SELECT create_distributed_table('my_table', 'shard_key');
```

For large scale clusters with many nodes/shards in a multi-tenant scenario, splitting up indexes by shard_key values to scale our number of indexes in line with our number of customers, we'd love to be able to do something like:
```
CREATE INDEX my_idx on my_table (id)
WHERE shard_key = ''
AND col1 = 'foo'
AND col2 = 'bar'
...
```
This functionally works, but Citus doesn't route the command to the specific shard, and instead broadcasts it to be created on all shards (the other shards being empty), causing index multiplication in relevant pg_* tables and affects cluster performance, at times rendering it unusable if Postgres background stats/tasks are running. This prevents our partial index requirements to scale in sync with our customer scale out.

A workaround for this seems to be to manually find the shard + worker that a given distribution value exists on, and then run the index create command directly on that worker, directly referencing that shard's table. However, it would be much easier to manage if Citus was able to handle this for us.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.