MIME-Version: 1.0
From: Aditya Singh <aditya.singh@lji.io>
Date: Fri, 27 Sep 2024 07:26:45 +0530
Message-ID: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
Subject: Request for Insights on ID Column Migration Approach
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007af83b0623102b95"
Archived-At: <https://www.postgresql.org/message-id/CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw%40mail.gmail.com>
Precedence: bulk

--0000000000007af83b0623102b95
Content-Type: text/plain; charset="UTF-8"

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.

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

<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 tab=
les 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=
>int8</code>=C2=A0ID column.</p><p>The plan involves renaming the=C2=A0<cod=
e>int8</code>=C2=A0column to the=C2=A0<code>id</code>=C2=A0column and setti=
ng it as the primary key. However, this process will require downtime, whic=
h may be substantial in a production environment. Fortunately, we have note=
d that other tables do not use the=C2=A0<code>id</code>=C2=A0column as a fo=
reign 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=
>: 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.</p></li><li style=3D"margin-left:15=
px"><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=
 already be set as not nullable and will have the uniqueness constraint fro=
m the unique index.</p></li></ol><p>We want to confirm if this approach wil=
l work as expected. If we should be aware of any potential pitfalls or cons=
iderations, could you please provide insights or point us toward relevant d=
ocumentation?</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 Ju=
ggernaut 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>
--0000000000007af83b0623102b95--