public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Re: Column type modification in big tables
Date: Tue, 13 Aug 2024 22:53:51 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKna9VaA5N7sNRmjL7QujG92Mk-rx-7R1nSGhOVNZyXma=8LPw@mail.gmail.com>
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>
On 2024-08-14 01:26:36 +0530, Lok P wrote:
> Is there any possible method(maybe by looking into the data dictionary tables/
> views etc) to see the progress of the Alter statement by which we can estimate
> the expected completion time of the "Alter" command? I understand
> pg_stat_activity doesn't show 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 ALTER command.
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
... 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
... 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
... 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
--
_ | 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 (22+ messages) latest in thread
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: Column type modification in big tables
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