Thread: Request for Insights on ID Column Migration Approach
I am just contacting you to talk about a current issue with our database. We have run out of a positive sequence in one of our tables and are now operating with negative sequences. To address this, we plan to migrate from the int4 ID column to an int8 ID column.
The plan involves renaming the int8 column to the id column and setting it as the primary key. However, this process will require downtime, which may be substantial in a production environment. Fortunately, we have noted that other tables do not use the id column as a foreign key, which may help mitigate some concerns.
Our Approach:
Create a Unique Index: We will first create a unique index on the new ID column before renaming it and altering it to be non-nullable. This step will necessitate scanning the entire table to verify uniqueness.
Add Primary Key: After ensuring the uniqueness, we will add the ID column as the primary key. By doing this, we hope to bypass the additional scanning for uniqueness and nullability, as the column will already be set as not nullable and will have the uniqueness constraint from the unique index.
We want to confirm if this approach will work as expected. If we should be aware of any potential pitfalls or considerations, could you please provide insights or point us toward relevant documentation?
Thank you so much for your help, and I look forward to your guidance.
Best regards,
Aditya Narayan Singh
Loyalty Juggernaut Inc.
This message and any attachments are intended only for the use of the intended recipient(s), are confidential, and may be privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or other use of this message and any attachments is strictly prohibited. If received in error, please notify the sender immediately and permanently delete it.
Your approach to migrating the ID column from int4 to int8 with minimal downtime is generally sound but in my option, consider the following also:
- Consider using PostgreSQL's CONCURRENTLY option when creating the unique index to avoid locking the entire table
- Make sure to first alter the new column to be non-nullable if it’s not already
I am just contacting you to talk about a current issue with our database. We have run out of a positive sequence in one of our tables and are now operating with negative sequences. To address this, we plan to migrate from the
int4ID column to anint8ID column.The plan involves renaming the
int8column to theidcolumn and setting it as the primary key. However, this process will require downtime, which may be substantial in a production environment. Fortunately, we have noted that other tables do not use theidcolumn as a foreign key, which may help mitigate some concerns.Our Approach:
Create a Unique Index: We will first create a unique index on the new ID column before renaming it and altering it to be non-nullable. This step will necessitate scanning the entire table to verify uniqueness.
Add Primary Key: After ensuring the uniqueness, we will add the ID column as the primary key. By doing this, we hope to bypass the additional scanning for uniqueness and nullability, as the column will already be set as not nullable and will have the uniqueness constraint from the unique index.
We want to confirm if this approach will work as expected. If we should be aware of any potential pitfalls or considerations, could you please provide insights or point us toward relevant documentation?
Thank you so much for your help, and I look forward to your guidance.
Best regards,
Aditya Narayan Singh
Loyalty Juggernaut Inc.Confidentiality Warning:
This message and any attachments are intended only for the use of the intended recipient(s), are confidential, and may be privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or other use of this message and any attachments is strictly prohibited. If received in error, please notify the sender immediately and permanently delete it.
"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> As you can see, adding the primary key takes just as much time as
> creating the unique index. So it doesn't look like PostgreSQL is able to
> take advantage of the existing index (which makes sense since it still
> has to create a new index).
No, but you can attach an existing unique index as a primary key:
regression=# create table t1 (f1 int not null);
CREATE TABLE
regression=# create unique index t1_f1 on t1(f1);
CREATE INDEX
regression=# alter table t1 add primary key using index t1_f1;
ALTER TABLE
If you build the unique index concurrently, this needn't involve
much downtime.
regards, tom lane