Thread: New function to show index being vacuumed

New function to show index being vacuumed

From
"Imseih (AWS), Sami"
Date:

Hi,

 

[1] is a ready-for-committer enhancement to pg_stat_progress_vacuum which exposes

the total number of indexes to vacuum and how many indexes have been vacuumed in

the current vacuum cycle.

 

To even further improve visibility into index vacuuming, it would be beneficial to have a

function called pg_stat_get_vacuum_index(pid) that takes in a pid and returns the

indexrelid of the index being processed.

 

Currently the only way to get the index being vacuumed by a process

Is through os tools such as pstack.

 

I had a patch for this as part of [1], but it was decided to handle this in a separate

discussion.

 

Comments/feedback will be appreciated before sending out a v1 of the patch.

 

 

Regards,

 

Sami Imseih

Amazon Web Services (AWS)

 

1. https://www.postgresql.org/message-id/flat/5478DFCD-2333-401A-B2F0-0D186AB09228@amazon.com

 

Re: New function to show index being vacuumed

From
Matthias van de Meent
Date:
On Thu, 22 Jun 2023 at 16:45, Imseih (AWS), Sami <simseih@amazon.com> wrote:
>
> Hi,
>
> [1] is a ready-for-committer enhancement to pg_stat_progress_vacuum which exposes
> the total number of indexes to vacuum and how many indexes have been vacuumed in
> the current vacuum cycle.
>
> To even further improve visibility into index vacuuming, it would be beneficial to have a
> function called pg_stat_get_vacuum_index(pid) that takes in a pid and returns the
> indexrelid of the index being processed.

I'm sorry for not having read (and not reading) the other thread yet,
but what was the reason we couldn't store that oid in a column in the
pg_s_p_vacuum-view?

Could you summarize the other solutions that were considered for this issue?

Kind regards,

Matthias van de Meent
Neon, Inc.



Re: New function to show index being vacuumed

From
"Imseih (AWS), Sami"
Date:
> I'm sorry for not having read (and not reading) the other thread yet,
> but what was the reason we couldn't store that oid in a column in the
> pg_s_p_vacuum-view?


> Could you summarize the other solutions that were considered for this issue?

Thanks for your feedback!

The reason we cannot stick the oid in pg_s_p_vacuum is because it will
not work for parallel vacuum as only the leader process has an entry
in pg_s_p_vacuum.

With a function the leader or worker pid can be passed in to the function
and will return the indexrelid being processed.

Regards,

Sami