Shard keys

An important decision to make when porting your schema is setting up the appropriate shard keys, which determine how rows are partitioned (see Sharding for more details).

You can choose how to shard a table by adding a SHARD KEY. This functions like a normal table index, and can contain any number of columns. For example, the following table is hash partitioned based on the values of a and b - all rows with the same values for both a and b will be on the same partition:

CREATE TABLE t1(a INT, b INT, c INT, SHARD KEY(a, b));

If your table has a PRIMARY key, SingleStoreDB Cloud will by default choose that key as the shard key. For example, both of the following table definitions will shard the table by a:

CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a));

CREATE TABLE t1(a INT, b INT, SHARD KEY(a), PRIMARY KEY(a));

If your table does not have a primary key, then SingleStoreDB Cloud will by default shard rows uniformly across the partitions. You can also choose to shard your table this way by entering a blank shard key. For example, both of the following will shard the table this way:

CREATE TABLE t1(a INT, b INT);

CREATE TABLE t1(a INT, b INT, SHARD KEY());

If you aren’t sure yet what your workload will look like, these defaults will allow you to continue developing your application and do not impose any functional restrictions. Once you understand how your workload performs in SingleStoreDB Cloud better, you can recreate your table with a different chosen shard key.