public inbox for [email protected]  

help / color / mirror / Atom feed

From: Muhammad Usman Khan <[email protected]>
To: Aditya Singh <[email protected]>
Cc: [email protected]
Subject: Re: Request for Insights on ID Column Migration Approach
Date: Fri, 27 Sep 2024 08:56:49 +0500
Message-ID: <CAPnRvGueByRv1jKEVnvQpDQLEA6X2nC-5TAWnVVeX4sVmx+J5w@mail.gmail.com> (raw)
In-Reply-To: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
References: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>

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 <[email protected]> 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.
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Request for Insights on ID Column Migration Approach
  In-Reply-To: <CAPnRvGueByRv1jKEVnvQpDQLEA6X2nC-5TAWnVVeX4sVmx+J5w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox