[PROPOSAL] VACUUM Progress Checker.

Hello Hackers,

Following is a proposal for feature to calculate VACUUM progress.

Use Case : Measuring progress of long running VACUUMs to help DBAs make
informed decision
whether to continue running VACUUM or abort it.

Design:

A shared preload library to store progress information from different
backends running VACUUM, calculate remaining time for each and display
progress in the
in the form a view.

VACUUM needs to be instrumented with a hook to collect progress
information (pages vacuumed/scanned) periodically.

The patch attached implements a new hook to store vacuumed_pages and
scanned_pages count at the end of each page scanned by VACUUM.

This information is stored in a shared memory structure.

In addition to measuring progress this function using hook also calculates
remaining time for VACUUM.

The frequency of collecting progress information can be reduced by
appending delays in between hook function calls.

Also, a GUC parameter

log_vacuum_min_duration can be used.

This will cause VACUUM progress to be calculated only if VACUUM runs more
than specified milliseconds.

A value of zero calculates VACUUM progress for each page processed. -1
disables logging.

Progress calculation :

percent_complete = scanned_pages * 100 / total_pages_to_be_scanned;

remaining_time = elapsed_time * (total_pages_to_be_scanned - scanned_pages)
/ scanned_pages;

Shared memory struct:

typedef struct PgStat_VacuumStats

{

Oid databaseoid;

Oid tableoid;

Int32 vacuumed_pages;

Int32 total_pages;

Int32 scanned_pages;

double elapsed_time;

double remaining_time;

} PgStat_VacuumStats[max_connections];

Reporting :

A view named 'pg_maintenance_progress' can be created using the values in
the struct above.

pg_stat_maintenance can be called from any other backend and will display
progress of

each running VACUUM.

Other uses of hook in VACUUM:

Cost of VACUUM in terms of pages hit , missed and dirtied same as
autovacuum can be collected using this hook.

Autovacuum does it at the end of VACUUM for each table. It can be done
while VACUUM on a table is in progress.
This can be helpful to track manual VACUUMs also not just autovacuum.

Read/Write(I/O) rates can be computed on the lines of autovacuum.
Read rate patterns can be used to help tuning future vacuum on the
table(like shared buffers tuning)
Other resource usages can also be collected using progress checker hook.

Attached patch is POC patch of progress calculation for a single backend.

Also attached is a brief snapshot of the output log.