Sharding

Every distributed table has exactly one shard key. A shard key can contain any number of columns. When you run CREATE TABLE (this command creates a columnstore table which is the default table type for SingleStore Helios) or CREATE ROWSTORE TABLE (this command creates a rowstore table) to create a table, you can specify a shard key for the table. If you choose not to specify a shard key:

• If the table has a primary key, then the shard key will be whatever the primary key is.

• If the table has neither a shard key nor a primary key, then the engine will perform keyless sharding for that table, which means the engine randomly distributes rows of the table across database partitions.

A table’s shard key determines in which partition a given row in the table is stored. When you run anINSERT query, the node computes a hash function of the values in the column or columns that make up the shard key, which produces the partition number where the row should be stored. The node then directs the INSERT operation to the appropriate node machine and partition.

DML queries that fully match the shard key can be routed directly to a single partition on a single node server. Group-by queries where the set of keys are guaranteed to not overlap between partitions can be executed in parallel on the node, with the results streamed back without any additional processing on the node.

Any two rows with the same shard key value are guaranteed to be on the same partition.

SingleStore requires that the set of columns in the PRIMARY or UNIQUE index needs to be a superset of the shard key columns, meaning that the primary / unique key contains all columns in the shard key. Rows that might have a primary key conflict thus map to the same partition so uniqueness can be enforced locally.

Choosing an appropriate shard key is important for minimizing data skew.

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

CREATE TABLE clicks (
    click_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    page_id INT,
    ts TIMESTAMP
);

CREATE TABLE clicks (
    click_id BIGINT AUTO_INCREMENT,
    user_id INT,
    page_id INT,
    ts TIMESTAMP,
    SHARD KEY (user_id),
    PRIMARY KEY (click_id, user_id)
);