MIME-Version: 1.0
References: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
In-Reply-To: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
From: Muhammad Usman Khan <usman.k@bitnine.net>
Date: Fri, 27 Sep 2024 08:56:49 +0500
Message-ID: <CAPnRvGueByRv1jKEVnvQpDQLEA6X2nC-5TAWnVVeX4sVmx+J5w@mail.gmail.com>
Subject: Re: Request for Insights on ID Column Migration Approach
To: Aditya Singh <aditya.singh@lji.io>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fd0438062311d816"
Archived-At: <https://www.postgresql.org/message-id/CAPnRvGueByRv1jKEVnvQpDQLEA6X2nC-5TAWnVVeX4sVmx%2BJ5w%40mail.gmail.com>
Precedence: bulk

--000000000000fd0438062311d816
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=
=99s not
   already


On Fri, 27 Sept 2024 at 06:57, Aditya Singh <aditya.singh@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 fr=
om
> 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 note=
d
> 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. T=
his
>    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 additi=
onal
>    scanning for uniqueness and nullability, as the column will already be=
 set
>    as not nullable and will have the uniqueness constraint from the uniqu=
e
>    index.
>
> We want to confirm if this approach will work as expected. If we should b=
e
> aware of any potential pitfalls or considerations, could you please provi=
de
> 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 ar=
e
> not the intended recipient, you are hereby notified that any disclosure,
> copying, distribution, or other use of this message and any attachments i=
s
> strictly prohibited. If received in error, please notify the sender
> immediately and permanently delete it.
>

--000000000000fd0438062311d816
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div dir=3D"ltr" class=3D"gmail_signature" data-smart=
mail=3D"gmail_signature"><div dir=3D"ltr"><div>Hi,</div><div><br>Your appro=
ach to migrating the ID column from int4 to int8 with minimal downtime is g=
enerally sound but in my option, consider the following also:<br><br><ul><l=
i>Consider using PostgreSQL&#39;s CONCURRENTLY option when creating the uni=
que index to avoid locking the entire table</li><li>Make sure to first alte=
r the new column to be non-nullable if it=E2=80=99s not already</li></ul></=
div></div></div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr"=
 class=3D"gmail_attr">On Fri, 27 Sept 2024 at 06:57, Aditya Singh &lt;<a hr=
ef=3D"mailto:aditya.singh@lji.io" target=3D"_blank">aditya.singh@lji.io</a>=
&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div=
 dir=3D"ltr"><p>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=C2=A0<code>int4</code>=C2=A0ID column to an=C2=A0<code>int=
8</code>=C2=A0ID column.</p><p>The plan involves renaming the=C2=A0<code>in=
t8</code>=C2=A0column to the=C2=A0<code>id</code>=C2=A0column and setting i=
t as the primary key. However, this process will require downtime, which ma=
y be substantial in a production environment. Fortunately, we have noted th=
at other tables do not use the=C2=A0<code>id</code>=C2=A0column as a foreig=
n key, which may help mitigate some concerns.</p><h3>Our Approach:</h3><ol>=
<li style=3D"margin-left:15px"><p><strong>Create a Unique Index</strong>: W=
e 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.</p></li><li style=3D"margin-left:15px">=
<p><strong>Add Primary Key</strong>: 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 alr=
eady be set as not nullable and will have the uniqueness constraint from th=
e unique index.</p></li></ol><p>We want to confirm if this approach will wo=
rk as expected. If we should be aware of any potential pitfalls or consider=
ations, could you please provide insights or point us toward relevant docum=
entation?</p><p>Thank you so much for your help, and I look forward to your=
 guidance.</p><p>Best regards,</p><p>Aditya Narayan Singh<br>Loyalty Jugger=
naut Inc.</p></div>

<br>
<div><hr></div><font face=3D"Arial"><b><font size=3D"2" color=3D"#808080">C=
onfidentiality Warning:</font></b><br><font color=3D"#808080" size=3D"2">Th=
is message and any attachments are intended only for the use of the intende=
d 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.</font></font><br></blockquote></div>

--000000000000fd0438062311d816--