Hi, 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 On Fri, 27 Sept 2024 at 06:57, Aditya Singh <aditya.si...@lji.io> wrote: > 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: > > 1. > > *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. > 2. > > *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. >
