Documentation — Concepts
SynSQL cuts every table into partitions. You decide how with the hash key.
The hash key is one or more columns of the table. When you insert a row, SynSQL computes a hash from the values of these columns, and the result decides the partition. Two rows with the same hash key values always go to the same partition. The hash key is mandatory: a table without a hash key cannot be deployed.
alter table visitors set hash key id;
alter table multi_omics set hash key hkSeg1, hkSeg2;Choose key columns whose values are spread evenly. A key with few distinct values (a country code, for example) gives unbalanced partitions.
Once the table is deployed, the hash key cannot change.
You choose the number of partitions when you deploy the table:
deploy table visitors on 4 partitions;Without a number, SynSQL uses as many partitions as the cluster can hold. The Partitions line of describe shows the result: gaia_source has 600.
Each partition is stored in segments, on the data brokers. A narrow table has one segment per partition and per replicate. A wide table has one segment per partition, per chunk and per replicate: single_cell has 42 partitions and 14 chunks, so 588 segments for one replicate.
A wide table is also cut vertically, into chunks of about 1500 columns. This is invisible when you write your statements, but it is the reason for the restrictions listed in Wide or narrow tables.
A table can be stored in one, two or three copies, set before deployment with alter table T set replicates N;. The default is one copy. With two or three copies, SynSQL places each copy in a different rack of the datacenter.
Two narrow tables with the same hash key and the same number of partitions put matching rows in the same partition. A join between them can then run partition by partition, without moving data. deploy table B close to A; creates B on the same data brokers as A for this purpose. SynSQL does not verify that your join and your keys are compatible: this is your design.