I'm currently designing a table and want to partition it by account_name. For now I'm thinking of going with a small number of partitions (e.g. 8) but since I expect a lot of data there is a chance I will need to re-partition it and make more partitions.
What is the best way to do this? If I understand correctly I can't just attach new partitions since I need to change modulus for previously used ones.
Should I copy and re-insert all the data or there is an easier way?
Repartitioning would mean to completely rewrite the table, as in
INSERT INTO new_tab SELECT * FROM old_tab;
which will cause extensive down time. One way around this is to use logical replication with new_tab on the standby side (possible from v13 on).
But my recommendation is not to do that. Choose a reasonable number of partitions and stick with that.