A sharded table consists of several partitions. Some of them are regular tables, and others are foreign tables. By default, the number of partitions is determined by the shardman.num_parts parameter, but it can be overwritten by the num_parts CREATE TABLE parameter. Most of DDL operations are restricted on partitions of a sharded table. You should modify the parent table instead.

The number of partitions in a sharded table is defined when it is created and cannot be changed afterwards. When new nodes are added to the cluster, some partitions are moved from existing nodes to the new ones to balance the load. So, to allow scaling of clusters, the initial number of partitions should be high enough, but not too high since an extremely large number of partitions significantly slows down query planning. For example, if you expect the number of nodes in your cluster to grow by 4 times at a maximum, create sharded tables with the number of partitions equal to 4 * N, where N is the number of nodes. A cluster becomes unable to scale when the number of cluster nodes reaches the number of partitions in the sharded table with the minimal number of them.

Replicated tables are available to all nodes of a cluster. A replicated table is a set of regular tables synchronized by triggers. The main use case for a replicated table is to store a relatively rarely updated set of data that is used by all cluster nodes. When a sharded table is joined to a replicated table, joins between sharded table partitions and the replicated table can be performed on nodes where individual partitions reside. The implementation of trigger-based replication requires a non-deferrable primary key on a replicated table to be defined. Currently when a replicated table is modified, an after-statement trigger fires and propagates changes to other nodes of the cluster via foreign tables. When new nodes are added to a cluster, replicated table data is transferred to the new nodes via logical replication. When some nodes are removed from a cluster, replicated tables get locked for writes for a brief time. Use the distributed_by or replicated CREATE TABLE parameters to create a replicated table. Replicated tables created with replicated are registered in the shardman.global_tables dictionary. Partitioned replicated tables are not supported.

Postgres Pro Shardman extension allows creating several kinds of managed objects. These are sharded and replicated tables, roles, sequences, and tablespaces. The list of operations allowed on managed objects is limited particularly to protect consistency of a managed schema, type or function. Note that the roles and tablspaces are managed on a higher level. For the same reason, most operations on managed objects are cluster-wide. The list of cluster-wide operations includes:

These configuration settings control execution of the distributed DDL: shardman.broadcast_ddl and shardman.sync_schema. The first one can be used for a cluster-wide broadcast of all regular DDL operations (for example, creating schemas or functions). The second one controls broadcasting of statements related to managed objects and should never be turned off without consulting the Postgres Pro Shardman support team.

Distributed tables have some specifics. Such table is available on all cluster nodes. As partitioned table, it has a number of partitions, with this number being a constant that cannot be changed without a restart. Each shard usually stores several partitions of this shard, while other partitions are stored elsewhere. Mind that these partitions are interconnected.

Postgres Pro Shardman colocated table is a partitioned table that consists of a number of partitions that are colocated with the partitions of a distributed table for the quick access. In this case, partitioning is usually made with partition, sharding, or foreign key for efficiency purposes. These are recommended solutions for larger JOIN.