Co-partitioned tables

[rkarthik007]

Jira Link: DB-1949
This is a feature request for implementing co-partitioned tables in order to achieve distributed transactions with high performance in certain scenarios.

This should allow creating a table A, and then creating another table B with the same partition key as A and specify that we want B to be co-partitioned with A. The two tables would share tablets, raft groups, load-balancing decisions, etc. And we should allow for updates to these tables to happen atomically if the update involves the same partition key (i.e. userid in our example above).

For example:

CREATE TABLE user (
  userid int, 
  username text,
  PRIMARY KEY (userid)
);

CREATE TABLE messages (
  userid int,
  msgid int,
  msgtext text,
  PRIMARY KEY ((userid), msgid)
) with PARTITION SCHEME OF user;

This should ensure that the user and messages tables have the same tablet splits with respect to the primary hash keys, and the tablets serving a given key range across the two tables are always colocated on the same node.

Background in other NoSQL systems:

Apache Cassandra

In Apache Cassandra, if two tables share the same replication strategy and same partition key they will co-locate their partitions. So as long as the two tables are in the same keyspace AND their partition keys match, for example:

Customer table with PRIMARY KEY (customer_id)

and an

Invoice table with PRIMARY KEY ((customer_id), invoice_id)

Then, the data for a given customer_id across both tables is guaranteed to be colocated on the same set of nodes.

HBase

In HBase, co-partition happens across column families that share the same ROW KEY.

Additionally, in HBase, since a common WAL is shared for cross-column family mutations, we also guaranteed cross-column family atomicity.

[ddorian]

Will this have ability to co-partition existing tables ?

[kmuthukk]

@ddorian - you mean "after the fact" co-partition of two tables that already have data and but are arbitrarily partitioned? This requires a lot more engineering and is a bit more complex, and wasn't on the plans for the initial cut.

The initial plans are around supporting creation of a "new" table that's co-partitioned with an existing table (which may contain data already). Under the covers the co-partitioned table will share Raft groups with it's parent table.

[ddorian]

@kmuthukk Yes. If the number of tablets & partition-key is the same, data should be split the same I guess? You just had to migrate the tablets into the same servers.

Mostly asking cause it's a big performance improvement for colocated transactions/joins/aggregations.

TLDR: it's a pretty big feature, any eta ?

[kmuthukk]

Yes - number of tablets being same would make things a bit easier. Merging previously independent RAFT groups would be a bit more trickier... especially if we try to support such an operation in an "online" fashion.

Will have to get back to you on ETA after discussion with team.

[ddorian]

@kmuthukk any type of ETA ? Like Qx 20xx. The same for #531.

[kmuthukk]

hi @ddorian

Don't have an exact timeline for this feature specifically, but by Q2'2019 quite likely. Specific use cases or customer requests might change/accelerate this.

[ndeodhar]

From @ddorian's input on #1133 : Have you thought of colocation of range-sharding ? If tablet becomes too big, split it (and split all colocated tables on the same keys even though they're small. And on tablet-distribution, move them together.

[frozenspider]

This issue is quite old. While we haven't implemented co-partitioning as proposed here (we still might want to), we did introduce a concept of colocation (via either database (#3033) or tablegroups (#11665, currently in the works)).

[Bessonov]

I am also interested in this feature. My use case is partitioning tables by tenant_id (not part of PK) with a lot of relations, especially around permission checking. I have raised a duplicate of this issue #16316 .

The implementation in Citus: Creating And Distributing Tables and Choosing Distribution Column. This is a very powerful way of associating data (especially with colocate_with and reference tables). From my point of view, this is exactly what I want to achieve. Of course, there are more fine-grained wishes like a partly (row- and column-wise) distributed reference table...

If I understand correctly, colocation of tables addresses another issue, not directly related here. And I am not sure about Tablegroups. in order to do this tables must share a "common" partition key (types & number of columns) sounds right, but I can't match it with the rest of the text.

Note that cockroachdb already had interleaved tables, but removed them again. (I am glad that we didn't use them, even though they fit well into the project).

Is there a plan for this feature request? And it would be nice to see how this feature affects the benchmarks when used.