In addition to local table types Shardman supports distributed tables: global and sharded.

A global table in Shardman is a table that has the same schema and contents on all shards in the cluster. Global tables are created as follows:

CREATE TABLE g(id bigint PRIMARY KEY, t text) WITH(global);
    

A copy of such a table is created on each shard. Data replication of global tables is based on triggers. When data is inserted into such a table on any node of the cluster, data replication to other nodes occurs. When creating a global table, it is necessary to specify non deferrable primary key.

Global tables are suitable for directories and other relatively small and infrequently modified tables. Global tables are NOT suitable for storing large amounts of data and for intensive INSERT/UPDATE/DELETE workload, especially with highly competitive access (storefronts, queues, etc.)

Sharded tables are tables whose parts are hosted on different shards. Each shard stores its own piece of data from such a table. A sharded table can be created as follows:

CREATE TABLE ... WITH(distributed_by = 'column_name', num_parts = number_of_partitions);
    

Where:

distributed_by — table field being the sharding key,

num_parts> — (default = 20) number of partitions into which the table is initially divided.

These parts are then distributed to shards.

The number of partitions should be not less than the number of shards including the shards that can be added later. In general it may be a number with quite a few divisors like 12 or 24, so you can evenly divide the table into 2, 3, 4 or 6 shards. Large amount of partitions adds overhead on planning and execution, so it is preferable to keep it reasonable.

Colocated tables are used when a table is often joined with another sharded table (usually by foreign keys) and therefore it is better to physically place their parts on the same shards.

CREATE TABLE ... WITH(distributed_by = 'column_name', num_parts = number_of_partitions, colocate_with = 'distributed_table');
    

Here:

distributed_by = 'column_name' — the name of the sharding key as it is called in the colocated table (not the colocating table) being created,

colocate_with = 'distributed_table' — the name of the table with which you want to colocate parts of the colocated table.

A local table is a table only hosted on the shard where it was created.

Foreign keys are allowed in Shardman but with some limitations:

Foreign keys are NOT allowed: