Date: Tue, 13 Aug 2024 22:53:51 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Column type modification in big tables
Message-ID: <20240813205351.eqzihh5t7urh7olf@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAD=mzVUX13ZM16kP4QhY+F5XiLr=ezCXftKOTKA4eUvhphgOJw@mail.gmail.com>
 <CAKna9Vb_mx+dX02XOV6mpr8RFC-5io38kM6=4xRHQj_MUvQ+aQ@mail.gmail.com>
 <CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com>
 <CAKna9VZJ4fginFJZenGQxWs9eAw9Z8g-YkdnOFcie5RvuJ=5OQ@mail.gmail.com>
 <CAKAnmmLv72uk1p8+zmWpkC+BTatrdmRe_NpRbwRsi1LAU-cJFQ@mail.gmail.com>
 <CAKna9VZGwtNx9NAZ0QjdT-WhtFETAaFzpUsvM6R90mjaAoP3vA@mail.gmail.com>
 <CAD=mzVVS6HbV25M7EA+TJYk22G=GJvQK5Gbe9eeifYSmadYtNA@mail.gmail.com>
 <CAKna9Vb0ABStAWogCsXK+jTT9ZuLESJ0_-r3Wtvd=rZpYMYwxw@mail.gmail.com>
 <CAKAnmm+_avfVEFGgADebEyH=oQrEDuvviOcMYNa+myjJrds8Eg@mail.gmail.com>
 <CAKna9VaA5N7sNRmjL7QujG92Mk-rx-7R1nSGhOVNZyXma=8LPw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="6nx56pmt4kfotw2m"
Content-Disposition: inline
In-Reply-To: <CAKna9VaA5N7sNRmjL7QujG92Mk-rx-7R1nSGhOVNZyXma=8LPw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240813205351.eqzihh5t7urh7olf%40hjp.at>
Precedence: bulk


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

On 2024-08-14 01:26:36 +0530, Lok P wrote:
> Is there any possible method(maybe by looking into the data dictionary ta=
bles/
> views etc) to see the progress of the Alter=A0statement by which we can e=
stimate
> the expected completion time of the "Alter" command? I understand
> pg_stat_activity doesn't=A0show any completion percentage of a statement,=
 but
> wondering if by any other possible way we can estimate the amount of time=
 it
> will take in prod for the completion of the=A0ALTER=A0command.

You could look at the data files. Tables in PostgreSQL are stored as a
series of 1GB files, so you watching them being created and/or read
gives you a pretty good idea about progress.

For example, here is an alter table (changing one column from int to
bigint)  on a 1.8 GB table on my laptop:

The original table: Two data files with 1 and 0.8 GB respectively:

22:26:51 1073741824 Aug 13 22:24 266648
22:26:51  853794816 Aug 13 22:26 266648.1

The operation begins: A data file for the new table appears:

22:26:55 1073741824 Aug 13 22:26 266648
22:26:55  853794816 Aug 13 22:26 266648.1
22:26:55   79298560 Aug 13 22:26 266659

=2E.. and grows:

22:26:57 1073741824 Aug 13 22:26 266648
22:26:57  853794816 Aug 13 22:26 266648.1
22:26:57  208977920 Aug 13 22:26 266659

=2E.. and grows:

22:26:59 1073741824 Aug 13 22:26 266648
22:26:59  853794816 Aug 13 22:26 266648.1
22:26:59  284024832 Aug 13 22:26 266659

and now the table has exceeded 1 GB, so there's a second file:

22:27:17 1073741824 Aug 13 22:26 266648
22:27:17 1073741824 Aug 13 22:27 266659
22:27:17  853794816 Aug 13 22:27 266648.1
22:27:17    3022848 Aug 13 22:27 266659.1

=2E.. and a third:

22:27:44 1073741824 Aug 13 22:26 266648
22:27:44 1073741824 Aug 13 22:27 266659
22:27:44 1073741824 Aug 13 22:27 266659.1
22:27:44  853794816 Aug 13 22:27 266648.1
22:27:44   36798464 Aug 13 22:27 266659.2

almost finished:

22:28:08 1073741824 Aug 13 22:26 266648
22:28:08 1073741824 Aug 13 22:27 266659
22:28:08 1073741824 Aug 13 22:27 266659.1
22:28:08  853794816 Aug 13 22:27 266648.1
22:28:08   36798464 Aug 13 22:28 266659.2

Done: The old table has been reduced to an empty file (not sure why
PostgreSQL keeps that around):

22:28:10 1073741824 Aug 13 22:27 266659
22:28:10 1073741824 Aug 13 22:27 266659.1
22:28:10   36798464 Aug 13 22:28 266659.2
22:28:10          0 Aug 13 22:28 266648

Of course you need to be postgres or root to do this. Be careful!

Watching the access times may be useful, too, but on Linux by default
the access time is only updated under some special circumstances, so
this may be misleading.

        hp


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

--6nx56pmt4kfotw2m
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAma7x9kACgkQ8g5IURL+
KF21MA/+KL6+WRsyfRa14TF7P+s2+wOzI2P4Ge6ZGWGzK0AjHxAlbxztrR4rT5Yv
czrk1T6Y95mUOLj3IVBdCPGuT4GZ90vGNFcgbO/Ou+XmH+lHOJxmQM88fmL7oJWN
UWD/ISJ1Dhkk922DR+/v9sQLy5oYqKLFpyickEiVMs7yxnO4pkbyR8faFj0H1qaV
9vf9Vo/1K/DoyMZC7DUtf82Mt8Oxt+rKWsSh7QP/mjQ3bsGoBQLVH2bRklzBZDpr
NbNatTnkYZqMc/bEGHKnssdzTWNrhP0A9e1YdY5yCM3TQQpVvvx7dgzeRXFq1Ed2
ut2wGl9UKEV1Y+oZ21twp5e+TqRfQ7xBYW8YSSJZJYyvFBJ6E8VQMWQ3M1OEeDcj
DCI6wZBfKd88yHIG8Yz1M/oPZrEDCsy0RpzFG+O6beSP9O/B5pN93p6v7mJg7O1Z
nzA7BufDutdXmFTlr5QYmC7YANR4zR53SeYbA5rBNO1o0fje/nG5bthKYzBao3Hp
ci+60Gw40tn3QBoKCDypwVKVjps0BgBg8tIClpeWFmXP7KbrWszg6ng/94kUNO+F
W0o8iwlC4FWcZLeXXmLSyuGqCRH0liOkMldkR18iBV3RGmpL4+omUtSM9rJn1D/e
jPBVhC5sbAC47dqyU/R4LtCqnuSuuNJ9HuIz68R/eYWFFCX0UN0=
=55qX
-----END PGP SIGNATURE-----

--6nx56pmt4kfotw2m--