5

During a migration from MySQL into a Citus cluster, I used the range distribution method. The migration is complete, but now I'd 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 am not sure it's valid:

  1. Update the minvalue and maxvalue columns of the pg_dist_shard table for all shards being changed
  2. Update the shard storage type column of the pg_dist_partition table from r to h
  3. COMMIT;
jasonmp85
  • 6,749
  • 2
  • 25
  • 41
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

1 Answers1

4

That is a good question. Currently, Citus does not provide a direct way to change partition type of existing data.

In range partitioning, records are placed in shards according to their partition column value and shard min/max values. If a record x resides in shard y, then it means y.minvalue <= x.partition_column <= y.maxvalue.

In hash partitioning, the partition column is hashed and records are routed according to this hashed value. Therefore, min/max values 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, doing the changes you have mentioned would end up with an incorrect distribution. In order to switch from range partition to hash partition, the data should be re-distributed. To do that, I suggest reloading the data to an empty hash-partitioned table.

For more information, you can refer to Working with Distributed Tables and Hash Distribution sections of Citus Documentation.

Ahmet Eren Başak
  • 493
  • 1
  • 4
  • 14
  • May you confirm that if I update shard tables to have same minvalue/maxvalue everything will work fine, because `hash(x.partition_column)` will hit each shard so planner will route query to each shard. – Eugen Konkov Apr 28 '16 at 10:40
  • Reloading data is not the case. It will take few days... I will be happy to see a DOC about how the citus metadata is used by planner. – Eugen Konkov Apr 28 '16 at 10:43
  • 1
    Setting min value to INT_MIN and max value to INT_MAX in each shard would be a nice bet. It would work in SELECT queries but would be slower, because shard pruning would not be done. However, modify queries (INSERT, UPDATE, DELETE) would not work since they assume one-shard hit at every query. Note that, this solution would be positive for just making Citus work but for performance comparisons or production, I would strictly suggest loading data from scratch. – Ahmet Eren Başak Apr 28 '16 at 10:52
  • 1
    [Metadata Tables Section](https://www.citusdata.com/docs/citus/5.0/reference/metadata_tables.html) in documentation contains some high-level information about metadata. If you have specific questions, you can ask or if you like going on an adventure, Citus source is pretty clear and files related to planner can be found in `src/backend/distributed/planner`. – Ahmet Eren Başak Apr 28 '16 at 11:00
  • > However, modify queries (INSERT, UPDATE, DELETE) would not work since they assume one-shard hit at every query I am confused: The insert will put new data into exact one shard as expected and do not touch others. Why it will be broken? For hash shards min/max values are overlapped in any case... – Eugen Konkov Apr 28 '16 at 12:02
  • 1
    In a hash distributed table, no two shards overlap. You can observe this by creating an empty table with some shards and looking into `pg_dist_shard` table. With this, any new record have exactly one shard that it can go to. When you give the same min/max values to all shards, then all shards be able to hold all records and any new record can go to any shard. This is why update queries wont work. – Ahmet Eren Başak Apr 29 '16 at 07:22