Feedback-ID: ia2694551:Fastmail
Date: Wed, 4 Dec 2024 18:01:47 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Kirill Reshke <reshkekirill@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Add pg_stat_progress_rewrite view.
Message-ID: <202412041701.xp7ha4k5du6u@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CALdSSPgzP6RerthQ12wrxDLVooJuuA+8h0WKQuJ3NeWoxe1Prw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202412041701.xp7ha4k5du6u%40alvherre.pgsql>
Precedence: bulk

Hello,

On 2024-Dec-04, Kirill Reshke wrote:

> Recently, one administrator asked me how to monitor the progress of
> his ALTER TABLE... ALTER COLUMN TYPE bigint.
> 
> That leads me to believe that consumers might benefit from having an
> API to track the table change process.

I agree that tracking progress of a table rewrite is useful, so +1 for
the feature in general.

> PFA draft patch adding number of tuples (tuples_processed) that was
> read from the altered table.

I'm not sure that this is very usable though, because the user would
have to know how many tuples the table originally had before they can
make any sense of this number.  Maybe in addition to that number, we can
show the total number of pages of the old relation as well as the number
of pages scanned so far.  That way, the user knows what fraction of the
old table has already been scanned.  In addition, but I'm not sure sure
aobut this one, it may also be useful to show the number of pages
written in the new relation.  (It lets you see how better packed the new
copy is.)

Do we also need to show metrics about the associated toast table?
There's no concept of "how many pages we've scanned" for the toast
table, but we could say how many pages the old one has vs. how many
we've written in the new one so far.  Though, again, that may not say a
lot.

On the other hand, IIRC we also create indexes after the heap rewrite
itself is complete, so we should show the progress of that part of the
process as well.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"La persona que no quería pecar / estaba obligada a sentarse
 en duras y empinadas sillas    / desprovistas, por cierto
 de blandos atenuantes"                          (Patricio Vogel)