P.4. Data Modeling #

What is a sharded (distributed) table in Postgres Pro Shardman?

Sharded tables are tables with partitions hosted on different shards. For more information, refer to Section 54.1.1.

What is a colocated table in Postgres Pro Shardman?

A colocated table is a partitioned table which data distribution is aligned with the parent table. Rows in both tables with the same sharding key are physically located on the same nodes. This allows processing heavy JOIN queries using a sharding key locally to eliminate network overhead.

What are the advantages and disadvantages of using global tables in Postgres Pro Shardman?

Advantages: A full copy of the table is stored on each shard. This guarantees zero network overhead and minimal latencies for SELECT and JOIN operations with such tables. This is ideal for references.

Disadvantages: Any data change (INSERT, UPDATE, DELETE) must be synchronized to all nodes of a cluster. Thus, frequently updated global tables significantly increase the network load and can lock the system.

How do global (replicated) tables operate under heavy write workload?

Reading data from global (replicated) tables on any node occurs locally without requesting data from other nodes over the network. However, changing data in such a table on any node results in updating the data on the other nodes of the cluster. This operation requires a distributed two-phase commit, data synchronization, and network overhead. That is why global tables are an ideal solution for small and rarely updated reference tables.

Does exchanging data between shards lead to performance degradation?

Performance can degrade because of intensive data exchange between shards. However, using sharding keys for cross-shard requests actually increases performance compared to regular databases.

How do I determine a key for sharding in Postgres Pro Shardman?

Try to determine a natural key (an existing business attribute) using dimensional modelling: create a matrix with business processes as rows and database entities as columns. Your objective is to find a data clump to meet the following requirements:

  • already exists in the architecture

  • distributes the data automatically and evenly

  • minimizes cross-shard operations and network overhead

Alternative options:

  • Composite sharding key. In this case, connections between processes and entities are created deliberately. The entire composite sharding key must be a part of the primary or foreign key. However, composite sharding keys do not support some of the optimizations, such as array_membership_inferences.

  • Computed sharding key. Use it only in extreme cases because it complicates the system. It requires manual control over data distribution (hash-based distribution is not available in this case). It also consumes compute resources during write operations, which is critical for real-time OLTP.

Why should I include a sharding key in my queries?

If a query does not include a sharding key, Postgres Pro Shardman cannot determine what data is stored on what shard. Consequently, it must scan all partitions on all remote nodes of the cluster (multiple index scans or sequential scan of sequential scans) and then filter and aggregate the data on the coordinator. This operation is very slow. This is acceptable if the number of such operations remains minimal.

How does using a sharding key affect table joins?

Table joins demonstrate optimal performance with colocated tables when the tables use columns of the same type for sharding keys and have the same number of partitions. In this case, partition-wise joins and partial aggregation occur on the nodes locally, and the coordinator just aggregates final results. If tables are joined using a column that is not a sharding key, the data will have to be transferred over the network. For OLTP workloads, it is recommended to avoid distributed transactions and unnecessary network transfers.

How much does performance degrade if you do not use a sharding key to perform a JOIN on tables?

Performance degradation depends on the JOIN type. Maximum performance is achieved by using colocation (colocate_with) when tables are sharded by the same key. In this case, the partition-wise join and partial aggregation occur locally right on the nodes, while the coordinator only aggregates the final results. If you perform a JOIN on columns that are not the sharding key, the data will have to be transferred over the network. For OLTP workloads with distributed transactions, you should generally avoid unnecessary network transfers.

How do I determine the required number of table partitions? Can I change the number of partitions later?

You can specify the number of partitions only when you create a table. If you want to change the number of partitions later, you will have to recreate the table.

The number of partitions must at least match the planned number of physical shards in a cluster. It is recommended to use 2n for the number of partitions. Start with at least two partitions per shard.

Avoid creating too many partitions, because this slows down query planning.

How can I determine the partition for a given record so that I can connect to the required shard?

Use the shardman.get_partition_for_value function.

What function does Postgres Pro Shardman use for hashing?

Postgres Pro Shardman uses the hashint4extended and hashtextextended SQL functions for calculating hashes.

How does Postgres Pro Shardman generate unique IDs?

Instead of classic sequences that would have become a bottleneck in a distributed system, Postgres Pro Shardman uses a mechanism to assign ranges (batches). Each shard requests a range of values and assigns them locally. This mechanism guarantees uniqueness in the entire cluster, but the generated IDs lose strict monotonicity.

What is the difference between isolation and consistency in Postgres Pro Shardman?

The main difference between isolation and consistency in a distributed system is in scale: CSNs become a mechanism of global coordination, and consistency becomes distributed at a cluster level.

A global CSN is a coordination tool. Global consistency is a target state.

The transaction visibility check compares snapshot_csn of a transaction with CSN of the committed transactions. The CSN value is extracted from CSNLog by using the transaction ID (xid).

FAQ