Can I change the distribution method to an existing Citus table?

Question

During the migration from MySQL to the Citus cluster, I used the range distribution method. The migration is complete, but now I would like to change the distribution method to hash .

Is there a way to change the distribution method from range to hash for an existing table with data already in it?

I came up with the following procedure, but not sure if this is valid:

• Update the minvalue and maxvalue columns of the minvalue table for all mutable covers
• Update the pg_dist_partition table fragment storage type column from r to h
• COMMIT;

Answer 1

That's a good question. Citus does not currently provide a direct way to change the type of shared data.

In the breakdown by ranges, records are placed in fragments in accordance with their value in the section column and min / max values. If the record x is in the shard y, then this means y.minvalue <= x.partition_column <= y.maxvalue .

In the hash, the partition column is hashed, and the entries are routed according to this hashed value. Therefore, the min / max values ​​that you see in pg_dist_shard are the boundary values ​​for the result of the hash function. In this case, y.minvalue <= hash(x.partition_column) <= y.maxvalue .

Therefore, your changes will result in incorrect distribution. To switch from a range partition to a hash partition, data must be redistributed. For this, I suggest reloading the data into an empty hash-partitioned table.

For more information, you can refer to Working with Distributed Tables and Hash Distribution in the Citus Documentation Section.