Primary key and partitioning fundamentally incompatible?

CrateDB
1

A primary key has high cardinality, so cannot be a partition key.
If a primary key is defined, you cannot use any other column as the partition key.
You cannot use a generated column (eg (ID % 1000000)) as the partition key.

So am I correct that with Crate one MUST effectively give up primary key usage?

I can understand why this would be so, but seems like quite a problem. Are there any well known mitigations to doing so?

Thank you.

2

Hi,
The partition key needs to be part of the PK, so the actual PK would be defined as what you would normally have as PK plus the generated column that you want to use as partition key:

CREATE TABLE test (
	ID BIGINT
	,another_field TEXT
	,mypartkey GENERATED ALWAYS AS (ID % 1000000)
	,PRIMARY KEY (
		ID
		,mypartkey
		)
	) PARTITIONED BY (mypartkey);
1 Like
3

Sorry one more comment on this, because each partition will have its own shards, you want to keep the number of partitions under control so modulo 1000000 is probably too high.

4

Ooops, I meant / , not %

5

Much appreciated, thanks.