Date: Sat, 28 Sep 2024 00:27:42 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Request for Insights on ID Column Migration Approach
Message-ID: <20240927222742.xmun3i57hjwiakuz@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="3l344kndpj2ku24b"
Content-Disposition: inline
In-Reply-To: <CAE_cSysRBuzmGoRGknPX6Y5sws7qgh0iitYLfQj3g3iiSygzDw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240927222742.xmun3i57hjwiakuz%40hjp.at>
Precedence: bulk


--3l344kndpj2ku24b
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-27 07:26:45 +0530, Aditya Singh wrote:
> we plan to migrate from the=A0int4=A0ID column to an=A0int8=A0ID column.
>=20
> The plan involves renaming the=A0int8=A0column to the=A0id=A0column and s=
etting 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=A0id=A0column as a foreign key, which may help miti=
gate some
> concerns.
>=20
> Our Approach:
>=20
>  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 st=
ep
>     will necessitate scanning the entire table to verify uniqueness.
>=20
>  2. Add Primary Key: After ensuring the uniqueness, we will add the ID co=
lumn
>     as the primary key. By doing this, we hope to bypass the additional
>     scanning for uniqueness and nullability, as the column will already b=
e set
>     as not nullable and will have the uniqueness constraint from the uniq=
ue
>     index.

This doesn't seem to be the case:

psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type "help" for help.

hjp=3D> create table t(i int);
CREATE TABLE
hjp=3D> insert into t select generate_series(1, 10000000);
INSERT 0 10000000
Time: 11011.637 ms (00:11.012)

hjp=3D> alter table t alter column i set not null;
ALTER TABLE
Time: 539.737 ms
hjp=3D> create unique index on t(i);
CREATE INDEX
Time: 5051.584 ms (00:05.052)
hjp=3D> 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

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--3l344kndpj2ku24b
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmb3MVcACgkQ8g5IURL+
KF3NsRAAm6Zh9XFUvzHUuOJio2mo0vN2B6v6dfllX2rOjZYhgoxbf/Hh2XA9M6kz
UOwN2JPdK7cLgim5lRoeN45BGH5uF7eRuCLNenVSxh5o743//SABkViZQBQtCZmP
WhMRrMyZ5l8PtzyF9ZCEEdy6g3wLUAww3s+/O0EtPdq/WeNSo+MXc4G3p1I9IPX1
7qN3UpTholIB8dsxZg3aqfnqZUjTiaXe+QDgQaFLWfmgZ6gwOp0Jq4ALPs5PQQUg
u3PvtHurSau1fQY0WTRdo4VUrPFYAfXdjQlNKB6eaDjHInAGhU1hGPQFgXP2eDt7
g3JyvnF7cB9wcdWDenw6AUzqVUGvuSZP2eSMM+L4MSw7h+AbPuEcC43b6L75FU+K
VgnLtsYxO6ZWXN82qwBROJFVGa4Yde2OYjoYab86DTDHqjvPW3qfskzr/+ge6r94
uv141juAudqD0qPBtfaSWDEOg/3WUwWmdTP+mLwnt/3Ho0eZcmlXUlbhDRSpJsM3
K2H7/NGhRSK/DPkC4aOH8izy2jqjuvXcImaIb+CT9mbdkUXHbRd9lcMQxQbUBHzy
Sdayx4C77tSLJ6HfVfDZYASNgYjWMssNpJ6oxl4LM+M3DfWTXdrsaGGt3iJ/f5Kq
V7qLH+D80xzS3fhU1xO6rykOiI3aeiHD7ADeVW3JLReonP1OEK4=
=os5Y
-----END PGP SIGNATURE-----

--3l344kndpj2ku24b--