Known limitations on Identity Columns

[gokhangulbiz]

This is a list of known limitations related to identity column support on distributed tables, which should be tracked for future releases:

• [ ] Metadata resync causes a loss of state for the sequence used by identity columns. https://github.com/citusdata/citus/pull/6738#pullrequestreview-1363223440
• [ ] Identity columns can only be used with the bigint data type on distributed tables. https://github.com/citusdata/citus/pull/6738#issue-1602985110
• [ ] alter_distributed_table and undistribute_table UDFs currently do not support identity columns. https://github.com/citusdata/citus/pull/6738#issue-1602985110
• [ ] Adding an identity constraint to an existing column using an ALTER TABLE statement is not supported.
• [ ] ALTER TABLE statements that involve changing the identity column are not supported.
• [ ] Adding a new identity column to a table with existing data is not supported.

[scottgarland33]

limitation example I am running into with my application

• identity columns are the preferred way of using an identifier in postgresql now, and have been for a few major releases. I found citus that solved a scaling issue but allowed me to continue using relational db with postgresql
• anyone trying out citus, moving an existing application, may have already successfully distributed tables, and if their application was created in the last few years has a good chance of having identity columns
• currently in a production state, realizing shard_count needs to be adjusted, due to increased load and sizes of the distributed data. Adjusting that is not possible due to the identity column. Changing an identity column to something else is not supported (alter_distributed_table) to even take off the identity manually. Undistribute table is not possible. I imagine this will be a problem for any quickly growing application where shard_count needs to be adjusted for existing tables... we are completely stuck and only way to fix is to spend more on hardware or rebuild db from scratch on new resources, with shard count set as desired for existing data.