Tables in Shardman can be of types: global, distributed, colocated, local, and temporary.

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 the primary key. Supported primary key types are bigint, bigserial, uuid.

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);
    

Here:

distributed_by — table field being the sharding key,

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

These parts are then distributed to shards.

num_parts = number of shards if known and fixed, or 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. However, if you make it too large, for example 1024, it will take too much time to search each of the pieces and then merge the results.

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: