public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Re: Request for Insights on ID Column Migration Approach
Date: Sat, 28 Sep 2024 00:27:42 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
References: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>

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.
|_|_) |                    |
| |   | [email protected]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"


Attachments:

  [application/pgp-signature] signature.asc (833B, 2-signature.asc)
  download

view thread (2+ messages)

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

* 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