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:
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.
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.singh@lji.io> wrote:
Show quoted text
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.
On 2024-09-27 07:26:45 +0530, Aditya Singh wrote:
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.
This doesn't seem to be the case:
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type "help" for help.
hjp=> create table t(i int);
CREATE TABLE
hjp=> insert into t select generate_series(1, 10000000);
INSERT 0 10000000
Time: 11011.637 ms (00:11.012)
hjp=> alter table t alter column i set not null;
ALTER TABLE
Time: 539.737 ms
hjp=> create unique index on t(i);
CREATE INDEX
Time: 5051.584 ms (00:05.052)
hjp=> alter table t add primary key(i);
ALTER TABLE
Time: 5222.788 ms (00:05.223)
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).
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
"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
On 2024-09-27 18:37:35 -0400, Tom Lane wrote:
"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
Ah, yes. I thought that was possible but I checked the docs[1]https://www.postgresql.org/docs/current/sql-altertable.html and
didn't see it. I checked again before writing this mail and still didn't
see it. Only when I started copy-pasting relevant parts of th synopsis
into the mail did I notice "ADD table_constraint_using_index". I'll get
new glasses soon, I promise. ;-)
hp
[1]: https://www.postgresql.org/docs/current/sql-altertable.html
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"