What is the best way to alter the partition key in a table?

General
1

I have couple of tables in a Postgres database which I exported to a YugaByte cluster. While exporting, I used the same schema of the table containing the primary key but did not explicitly mention any partition key. For example, for Table 1, we have four fields as primary key (A, B, C, D). Now, as per documentation, YugaByte automatically picks up field A as the partition key and the rest (B, C, D) as the clustering key.

However, I want to change the partition key to (A, B, C, D) for the table and redistribute the data in the cluster.

I have tried removing the primary key constraint and adding a new constraint by below statement:

ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkey;
ALTER TABLE <table_name> ADD PRIMARY KEY ((A, B, C, D));

The process was fast for small tables but for tables with large data it is taking forever. Is there any efficient way to change the partition key?

2

Hi, to change the primary key you need two ALTER TABLE: to drop and re-create it. If there is lot of data, it might take long as each re-writes the table, so better load data once the key is defined.

Note1: it is rarely a good idea to have many columns as the partition key because a hash function is applied to it and then index scan will be efficient only if you have an equality predicate for each column. Better pick a highly selective column that is always used with an equality predicate to be the hash key.

Note2: a partition key is not mandatory. Without a partition key the table starts with one tablet but will be split when the table grows. You can set yb_use_hash_splitting_by_default to off and your create table will not set a hash key.