public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Jim Nasby <[email protected]>
Cc: Bertrand Drouvot <[email protected]>
Cc: Ilia Evdokimov <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: Andrei Zubkov <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: jian he <[email protected]>
Cc: [email protected]
Cc: Sami Imseih <[email protected]>
Cc: vignesh C <[email protected]>
Subject: Re: Vacuum statistics
Date: Thu, 4 Sep 2025 18:49:20 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAPpHfduoJEuoixPTTg2tjhnXqrdobuMaQGxriqxJ9TjN1uxOuA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAMFBP2oXkhX_k9FTqtW-LdTBepVq0PDuBEGO8-LpNGbyHTBrNw@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA4eK1JOUn+EqWSfRgKfgBZOXT7Q2dw2enmSZZgOhoMOFwopPA@mail.gmail.com>
<[email protected]>
<CAPpHfdtQd29O15Cmp1qeqTCerQF0Y+BGh63qtX3RkA7k=0TZ1Q@mail.gmail.com>
<[email protected]>
Hi, all!
On 02.06.2025 19:50, Alena Rybakina wrote:
>
> On 02.06.2025 19:25, Alexander Korotkov wrote:
>> On Tue, May 13, 2025 at 12:49 PM Alena Rybakina
>> <[email protected]> wrote:
>>> On 12.05.2025 08:30, Amit Kapila wrote:
>>>> On Fri, May 9, 2025 at 5:34 PM Alena Rybakina
>>>> <[email protected]> wrote:
>>>>> I did a rebase and finished the part with storing statistics
>>>>> separately from the relation statistics - now it is possible to
>>>>> disable the collection of statistics for relationsh using gucs and
>>>>> this allows us to solve the problem with the memory consumed.
>>>>>
>>>> I think this patch is trying to collect data similar to what we do for
>>>> pg_stat_statements for SQL statements. So, can't we follow a similar
>>>> idea such that these additional statistics will be collected once some
>>>> external module like pg_stat_statements is enabled? That module should
>>>> be responsible for accumulating and resetting the data, so we won't
>>>> have this memory consumption issue.
>>> The idea is good, it will require one hook for the pgstat_report_vacuum
>>> function, the extvac_stats_start and extvac_stats_end functions can be
>>> run if the extension is loaded, so as not to add more hooks.
>> +1
>> Nice idea of a hook. Given the volume of the patch, it might be a
>> good idea to keep this as an extension.
> Okay, I'll realize it and apply the patch)
>>
>>> But I see a problem here with tracking deleted objects for which
>>> statistics are no longer needed. There are two solutions to this and I
>>> don't like both of them, to be honest.
>>> The first way is to add a background process that will go through the
>>> table with saved statistics and check whether the relation or the
>>> database are relevant now or not and if not, then
>>> delete the vacuum statistics information for it. This may be
>>> resource-intensive. The second way is to add hooks for deleting the
>>> database and relationships (functions dropdb, index_drop,
>>> heap_drop_with_catalog).
>> Can we workaround this with object_access_hook?
>
> I think this could fix the problem. For the OAT-DROP access type, we
> can call a function to reset the vacuum statistics for relations that
> are about to be dropped.
>
> At the moment, I don’t see any limitations to using this approach.
>
I’ve prepared the first working version of the extension.
I haven’t yet implemented writing the statistics to a file and reloading
them into a hash table and shared memory at instance startup, and I also
haven’t implemented a proper output for database-level statistics yet.
I structured the extension as follows: statistics are stored in a hash
table keyed by a composite key - database OID, relation OID, and object
type (index, table, or database). When VACUUM or a worker processes a
table or index, an exclusive lock is taken to update the corresponding
record; a shared lock is taken when reading the statistics. For
database-level output, I plan to compute the totals by summing table and
index statistics on demand.
To optimize that, I plan to keep entries in the hash table ordered by
database OID. When accessing the first element by the partial key
(database OID), I’ll scan forward and aggregate until the partitial
database key changes.
Right now this requires adding the extension to
`shared_preload_libraries`. I haven’t found a way to avoid that because
of shared-memory setup, and I’m not sure it’s even possible.
I’m also unsure whether it’s better to store the statistics in the
cumulative statistics system (as done here) or entirely inside the
extension. Note that the code added to the core to support the extension
executes regardless of whether the extension is enabled.
Attachments:
[text/x-patch] 0001-Core-patch-for-vacuum-statistics.patch (37.3K, 2-0001-Core-patch-for-vacuum-statistics.patch)
download | inline diff:
From 098e381ca88e2600cb8e8528e3f54f588d4c43a8 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Thu, 4 Sep 2025 18:16:52 +0300
Subject: [PATCH] Core patch for vacuum statistics.
---
src/backend/access/heap/vacuumlazy.c | 309 ++++++++++++++++++-
src/backend/access/heap/visibilitymap.c | 10 +
src/backend/commands/vacuum.c | 4 +
src/backend/commands/vacuumparallel.c | 12 +
src/backend/utils/activity/pgstat_relation.c | 36 ++-
src/backend/utils/adt/pgstatfuncs.c | 6 +
src/backend/utils/error/elog.c | 13 +
src/include/catalog/pg_proc.dat | 8 +
src/include/commands/vacuum.h | 26 ++
src/include/pgstat.h | 119 ++++++-
src/include/utils/elog.h | 1 +
src/test/regress/expected/rules.out | 12 +-
12 files changed, 545 insertions(+), 11 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 932701d8420..330f9f90a6b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -289,6 +289,8 @@ typedef struct LVRelState
/* Error reporting state */
char *dbname;
char *relnamespace;
+ Oid reloid;
+ Oid indoid;
char *relname;
char *indname; /* Current index name */
BlockNumber blkno; /* used only for heap operations */
@@ -407,6 +409,10 @@ typedef struct LVRelState
* been permanently disabled.
*/
BlockNumber eager_scan_remaining_fails;
+
+ int32 wraparound_failsafe_count; /* number of emergency vacuums to prevent anti-wraparound shutdown */
+
+ PgStat_VacuumRelationCounts extVacReportIdx;
} LVRelState;
@@ -474,6 +480,209 @@ static void update_vacuum_error_info(LVRelState *vacrel,
static void restore_vacuum_error_info(LVRelState *vacrel,
const LVSavedErrInfo *saved_vacrel);
+/* ----------
+ * extvac_stats_start() -
+ *
+ * Save cut-off values of extended vacuum counters before start of a relation
+ * processing.
+ * ----------
+ */
+static void
+extvac_stats_start(Relation rel, LVExtStatCounters *counters)
+{
+ TimestampTz starttime;
+
+ memset(counters, 0, sizeof(LVExtStatCounters));
+
+ starttime = GetCurrentTimestamp();
+
+ counters->starttime = starttime;
+ counters->walusage = pgWalUsage;
+ counters->bufusage = pgBufferUsage;
+ counters->VacuumDelayTime = VacuumDelayTime;
+ counters->blocks_fetched = 0;
+ counters->blocks_hit = 0;
+
+ if (!rel->pgstat_info || !pgstat_track_counts)
+ /*
+ * if something goes wrong or user doesn't want to track a database
+ * activity - just suppress it.
+ */
+ return;
+
+ counters->blocks_fetched = rel->pgstat_info->counts.blocks_fetched;
+ counters->blocks_hit = rel->pgstat_info->counts.blocks_hit;
+}
+
+/* ----------
+ * extvac_stats_end() -
+ *
+ * Called to finish an extended vacuum statistic gathering and form a report.
+ * ----------
+ */
+static void
+extvac_stats_end(Relation rel, LVExtStatCounters *counters,
+ PgStat_VacuumRelationCounts *report)
+{
+ WalUsage walusage;
+ BufferUsage bufusage;
+ TimestampTz endtime;
+ long secs;
+ int usecs;
+
+ /* Calculate diffs of global stat parameters on WAL and buffer usage. */
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &counters->walusage);
+
+ memset(&bufusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufusage, &pgBufferUsage, &counters->bufusage);
+
+ endtime = GetCurrentTimestamp();
+ TimestampDifference(counters->starttime, endtime, &secs, &usecs);
+
+ /*
+ * Fill additional statistics on a vacuum processing operation.
+ */
+ report->common.total_blks_read += bufusage.local_blks_read + bufusage.shared_blks_read;
+ report->common.total_blks_hit += bufusage.local_blks_hit + bufusage.shared_blks_hit;
+ report->common.total_blks_dirtied += bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
+ report->common.total_blks_written += bufusage.shared_blks_written;
+
+ report->common.wal_records += walusage.wal_records;
+ report->common.wal_fpi += walusage.wal_fpi;
+ report->common.wal_bytes += walusage.wal_bytes;
+
+ report->common.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_read_time);
+ report->common.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_read_time);
+ report->common.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.local_blk_write_time);
+ report->common.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_write_time);
+ report->common.delay_time += VacuumDelayTime - counters->VacuumDelayTime;
+
+ report->common.total_time += secs * 1000. + usecs / 1000.;
+
+ if (!rel->pgstat_info || !pgstat_track_counts)
+ /*
+ * if something goes wrong or an user doesn't want to track a database
+ * activity - just suppress it.
+ */
+ return;
+
+ report->common.blks_fetched +=
+ rel->pgstat_info->counts.blocks_fetched - counters->blocks_fetched;
+ report->common.blks_hit +=
+ rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
+}
+
+void
+extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters)
+{
+ /* Set initial values for common heap and index statistics*/
+ extvac_stats_start(rel, &counters->common);
+ counters->pages_deleted = counters->tuples_removed = 0;
+
+ if (stats != NULL)
+ {
+ /*
+ * XXX: Why do we need this code here? If it is needed, I feel lack of
+ * comments, describing the reason.
+ */
+ counters->tuples_removed = stats->tuples_removed;
+ counters->pages_deleted = stats->pages_deleted;
+ }
+}
+
+void
+extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters, PgStat_VacuumRelationCounts *report)
+{
+ memset(report, 0, sizeof(PgStat_VacuumRelationCounts));
+
+ extvac_stats_end(rel, &counters->common, report);
+ report->type = PGSTAT_EXTVAC_INDEX;
+
+ if (stats != NULL)
+ {
+ /*
+ * if something goes wrong or an user doesn't want to track a database
+ * activity - just suppress it.
+ */
+
+ /* Fill index-specific extended stats fields */
+ report->index.tuples_deleted =
+ stats->tuples_removed - counters->tuples_removed;
+ report->index.pages_deleted =
+ stats->pages_deleted - counters->pages_deleted;
+ }
+}
+
+/* Accumulate vacuum statistics for heap.
+ *
+ * Because of complexity of vacuum processing: it switch procesing between
+ * the heap relation to index relations and visa versa, we need to store
+ * gathered statistics information for heap relations several times before
+ * the vacuum starts processing the indexes again.
+ *
+ * It is necessary to gather correct statistics information for heap and indexes
+ * otherwice the index statistics information would be added to his parent heap
+ * statistics information and it would be difficult to analyze it later.
+ *
+ * We can't subtract union vacuum statistics information for index from the heap relations
+ * because of total and delay time time statistics collecting during parallel vacuum
+ * procudure.
+*/
+static void
+accumulate_heap_vacuum_statistics(LVRelState *vacrel, PgStat_VacuumRelationCounts *extVacStats)
+{
+ /* Fill heap-specific extended stats fields */
+ extVacStats->type = PGSTAT_EXTVAC_TABLE;
+ extVacStats->table.pages_scanned = vacrel->scanned_pages;
+ extVacStats->table.pages_removed = vacrel->removed_pages;
+ extVacStats->table.vm_new_frozen_pages = vacrel->vm_new_frozen_pages;
+ extVacStats->table.vm_new_visible_pages = vacrel->vm_new_visible_pages;
+ extVacStats->table.vm_new_visible_frozen_pages = vacrel->vm_new_visible_frozen_pages;
+ extVacStats->table.tuples_deleted = vacrel->tuples_deleted;
+ extVacStats->table.tuples_frozen = vacrel->tuples_frozen;
+ extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
+ extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
+ extVacStats->table.missed_dead_tuples = vacrel->missed_dead_tuples;
+ extVacStats->table.missed_dead_pages = vacrel->missed_dead_pages;
+ extVacStats->table.index_vacuum_count = vacrel->num_index_scans;
+ extVacStats->common.wraparound_failsafe_count = vacrel->wraparound_failsafe_count;
+
+ extVacStats->common.blk_read_time -= vacrel->extVacReportIdx.common.blk_read_time;
+ extVacStats->common.blk_write_time -= vacrel->extVacReportIdx.common.blk_write_time;
+ extVacStats->common.total_blks_dirtied -= vacrel->extVacReportIdx.common.total_blks_dirtied;
+ extVacStats->common.total_blks_hit -= vacrel->extVacReportIdx.common.total_blks_hit;
+ extVacStats->common.total_blks_read -= vacrel->extVacReportIdx.common.total_blks_read;
+ extVacStats->common.total_blks_written -= vacrel->extVacReportIdx.common.total_blks_written;
+ extVacStats->common.wal_bytes -= vacrel->extVacReportIdx.common.wal_bytes;
+ extVacStats->common.wal_fpi -= vacrel->extVacReportIdx.common.wal_fpi;
+ extVacStats->common.wal_records -= vacrel->extVacReportIdx.common.wal_records;
+
+ extVacStats->common.total_time -= vacrel->extVacReportIdx.common.total_time;
+ extVacStats->common.delay_time -= vacrel->extVacReportIdx.common.delay_time;
+
+}
+
+static void
+accumulate_idxs_vacuum_statistics(LVRelState *vacrel, PgStat_VacuumRelationCounts *extVacIdxStats)
+{
+
+ /* Fill heap-specific extended stats fields */
+ vacrel->extVacReportIdx.common.blk_read_time += extVacIdxStats->common.blk_read_time;
+ vacrel->extVacReportIdx.common.blk_write_time += extVacIdxStats->common.blk_write_time;
+ vacrel->extVacReportIdx.common.total_blks_dirtied += extVacIdxStats->common.total_blks_dirtied;
+ vacrel->extVacReportIdx.common.total_blks_hit += extVacIdxStats->common.total_blks_hit;
+ vacrel->extVacReportIdx.common.total_blks_read += extVacIdxStats->common.total_blks_read;
+ vacrel->extVacReportIdx.common.total_blks_written += extVacIdxStats->common.total_blks_written;
+ vacrel->extVacReportIdx.common.wal_bytes += extVacIdxStats->common.wal_bytes;
+ vacrel->extVacReportIdx.common.wal_fpi += extVacIdxStats->common.wal_fpi;
+ vacrel->extVacReportIdx.common.wal_records += extVacIdxStats->common.wal_records;
+ vacrel->extVacReportIdx.common.delay_time += extVacIdxStats->common.delay_time;
+
+ vacrel->extVacReportIdx.common.total_time += extVacIdxStats->common.total_time;
+}
/*
@@ -632,6 +841,13 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
char **indnames = NULL;
+ LVExtStatCounters extVacCounters;
+ PgStat_VacuumRelationCounts ExtVacReport;
+ PgStat_VacuumRelationCounts allzero;
+
+ /* Initialize vacuum statistics */
+ memset(&allzero, 0, sizeof(PgStat_VacuumRelationCounts));
+ ExtVacReport = allzero;
verbose = (params.options & VACOPT_VERBOSE) != 0;
instrument = (verbose || (AmAutoVacuumWorkerProcess() &&
@@ -652,6 +868,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
RelationGetRelid(rel));
+ extvac_stats_start(rel, &extVacCounters);
/*
* Setup error traceback support for ereport() first. The idea is to set
* up an error context callback to display additional information on any
@@ -668,6 +885,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
vacrel->dbname = get_database_name(MyDatabaseId);
vacrel->relnamespace = get_namespace_name(RelationGetNamespace(rel));
vacrel->relname = pstrdup(RelationGetRelationName(rel));
+ vacrel->reloid = RelationGetRelid(rel);
vacrel->indname = NULL;
vacrel->phase = VACUUM_ERRCB_PHASE_UNKNOWN;
vacrel->verbose = verbose;
@@ -676,6 +894,8 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
errcallback.previous = error_context_stack;
error_context_stack = &errcallback;
+ memset(&vacrel->extVacReportIdx, 0, sizeof(PgStat_VacuumRelationCounts));
+
/* Set up high level stuff about rel and its indexes */
vacrel->rel = rel;
vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
@@ -776,6 +996,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
vacrel->aggressive = vacuum_get_cutoffs(rel, params, &vacrel->cutoffs);
vacrel->rel_pages = orig_rel_pages = RelationGetNumberOfBlocks(rel);
vacrel->vistest = GlobalVisTestFor(rel);
+ vacrel->wraparound_failsafe_count = 0;
/* Initialize state used to track oldest extant XID/MXID */
vacrel->NewRelfrozenXid = vacrel->cutoffs.OldestXmin;
@@ -924,6 +1145,9 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
vacrel->NewRelfrozenXid, vacrel->NewRelminMxid,
&frozenxid_updated, &minmulti_updated, false);
+ /* Make generic extended vacuum stats report */
+ extvac_stats_end(rel, &extVacCounters, &ExtVacReport);
+
/*
* Report results to the cumulative stats system, too.
*
@@ -934,12 +1158,20 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
* soon in cases where the failsafe prevented significant amounts of heap
* vacuuming.
*/
+ /* Make generic extended vacuum stats report and
+ * fill heap-specific extended stats fields.
+ */
+ extvac_stats_end(vacrel->rel, &extVacCounters, &ExtVacReport);
+ accumulate_heap_vacuum_statistics(vacrel, &ExtVacReport);
+
pgstat_report_vacuum(RelationGetRelid(rel),
- rel->rd_rel->relisshared,
- Max(vacrel->new_live_tuples, 0),
- vacrel->recently_dead_tuples +
- vacrel->missed_dead_tuples,
- starttime);
+ rel->rd_rel->relisshared,
+ Max(vacrel->new_live_tuples, 0),
+ vacrel->recently_dead_tuples +
+ vacrel->missed_dead_tuples,
+ starttime,
+ &ExtVacReport);
+
pgstat_progress_end_command();
if (instrument)
@@ -2631,10 +2863,20 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
}
else
{
+ LVExtStatCounters counters;
+ PgStat_VacuumRelationCounts PgStat_VacuumRelationCounts;
+
+ memset(&PgStat_VacuumRelationCounts, 0, sizeof(PgStat_VacuumRelationCounts));
+
+ extvac_stats_start(vacrel->rel, &counters);
+
/* Outsource everything to parallel variant */
parallel_vacuum_bulkdel_all_indexes(vacrel->pvs, old_live_tuples,
vacrel->num_index_scans);
+ extvac_stats_end(vacrel->rel, &counters, &PgStat_VacuumRelationCounts);
+ accumulate_idxs_vacuum_statistics(vacrel, &PgStat_VacuumRelationCounts);
+
/*
* Do a postcheck to consider applying wraparound failsafe now. Note
* that parallel VACUUM only gets the precheck and this postcheck.
@@ -2961,6 +3203,7 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
int64 progress_val[2] = {0, 0};
VacuumFailsafeActive = true;
+ vacrel->wraparound_failsafe_count ++;
/*
* Abandon use of a buffer access strategy to allow use of all of
@@ -3043,10 +3286,20 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
}
else
{
+ LVExtStatCounters counters;
+ PgStat_VacuumRelationCounts PgStat_VacuumRelationCounts;
+
+ memset(&PgStat_VacuumRelationCounts, 0, sizeof(PgStat_VacuumRelationCounts));
+
+ extvac_stats_start(vacrel->rel, &counters);
+
/* Outsource everything to parallel variant */
parallel_vacuum_cleanup_all_indexes(vacrel->pvs, reltuples,
vacrel->num_index_scans,
estimated_count);
+
+ extvac_stats_end(vacrel->rel, &counters, &PgStat_VacuumRelationCounts);
+ accumulate_idxs_vacuum_statistics(vacrel, &PgStat_VacuumRelationCounts);
}
/* Reset the progress counters */
@@ -3072,6 +3325,11 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
{
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
+ LVExtStatCountersIdx extVacCounters;
+ PgStat_VacuumRelationCounts PgStat_VacuumRelationCounts;
+
+ /* Set initial statistics values to gather vacuum statistics for the index */
+ extvac_stats_start_idx(indrel, istat, &extVacCounters);
ivinfo.index = indrel;
ivinfo.heaprel = vacrel->rel;
@@ -3090,6 +3348,7 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
*/
Assert(vacrel->indname == NULL);
vacrel->indname = pstrdup(RelationGetRelationName(indrel));
+ vacrel->indoid = RelationGetRelid(indrel);
update_vacuum_error_info(vacrel, &saved_err_info,
VACUUM_ERRCB_PHASE_VACUUM_INDEX,
InvalidBlockNumber, InvalidOffsetNumber);
@@ -3098,6 +3357,16 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
istat = vac_bulkdel_one_index(&ivinfo, istat, vacrel->dead_items,
vacrel->dead_items_info);
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &PgStat_VacuumRelationCounts);
+
+ if (!ParallelVacuumIsActive(vacrel))
+ accumulate_idxs_vacuum_statistics(vacrel, &PgStat_VacuumRelationCounts);
+
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, 0, &PgStat_VacuumRelationCounts);
+
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
pfree(vacrel->indname);
@@ -3122,6 +3391,11 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
{
IndexVacuumInfo ivinfo;
LVSavedErrInfo saved_err_info;
+ LVExtStatCountersIdx extVacCounters;
+ PgStat_VacuumRelationCounts PgStat_VacuumRelationCounts;
+
+ /* Set initial statistics values to gather vacuum statistics for the index */
+ extvac_stats_start_idx(indrel, istat, &extVacCounters);
ivinfo.index = indrel;
ivinfo.heaprel = vacrel->rel;
@@ -3141,12 +3415,22 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
*/
Assert(vacrel->indname == NULL);
vacrel->indname = pstrdup(RelationGetRelationName(indrel));
+ vacrel->indoid = RelationGetRelid(indrel);
update_vacuum_error_info(vacrel, &saved_err_info,
VACUUM_ERRCB_PHASE_INDEX_CLEANUP,
InvalidBlockNumber, InvalidOffsetNumber);
istat = vac_cleanup_one_index(&ivinfo, istat);
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &PgStat_VacuumRelationCounts);
+ if (!ParallelVacuumIsActive(vacrel))
+ accumulate_idxs_vacuum_statistics(vacrel, &PgStat_VacuumRelationCounts);
+
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, 0, &PgStat_VacuumRelationCounts);
+
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
pfree(vacrel->indname);
@@ -3759,6 +4043,9 @@ vacuum_error_callback(void *arg)
switch (errinfo->phase)
{
case VACUUM_ERRCB_PHASE_SCAN_HEAP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, errinfo->rel->rd_rel->relisshared, PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -3774,6 +4061,9 @@ vacuum_error_callback(void *arg)
break;
case VACUUM_ERRCB_PHASE_VACUUM_HEAP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, errinfo->rel->rd_rel->relisshared, PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -3789,16 +4079,25 @@ vacuum_error_callback(void *arg)
break;
case VACUUM_ERRCB_PHASE_VACUUM_INDEX:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, errinfo->rel->rd_rel->relisshared, PGSTAT_EXTVAC_INDEX);
+
errcontext("while vacuuming index \"%s\" of relation \"%s.%s\"",
errinfo->indname, errinfo->relnamespace, errinfo->relname);
break;
case VACUUM_ERRCB_PHASE_INDEX_CLEANUP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, errinfo->rel->rd_rel->relisshared, PGSTAT_EXTVAC_INDEX);
+
errcontext("while cleaning up index \"%s\" of relation \"%s.%s\"",
errinfo->indname, errinfo->relnamespace, errinfo->relname);
break;
case VACUUM_ERRCB_PHASE_TRUNCATE:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid, errinfo->rel->rd_rel->relisshared, PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
errcontext("while truncating relation \"%s.%s\" to %u blocks",
errinfo->relnamespace, errinfo->relname, errinfo->blkno);
diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c
index 953ad4a4843..a21e77cd551 100644
--- a/src/backend/access/heap/visibilitymap.c
+++ b/src/backend/access/heap/visibilitymap.c
@@ -91,6 +91,7 @@
#include "access/xloginsert.h"
#include "access/xlogutils.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "port/pg_bitutils.h"
#include "storage/bufmgr.h"
#include "storage/smgr.h"
@@ -160,6 +161,15 @@ visibilitymap_clear(Relation rel, BlockNumber heapBlk, Buffer vmbuf, uint8 flags
if (map[mapByte] & mask)
{
+ /*
+ * As part of vacuum stats, track how often all-visible or all-frozen
+ * bits are cleared.
+ */
+ if (map[mapByte] >> mapOffset & flags & VISIBILITYMAP_ALL_VISIBLE)
+ pgstat_count_vm_rev_all_visible(rel);
+ if (map[mapByte] >> mapOffset & flags & VISIBILITYMAP_ALL_FROZEN)
+ pgstat_count_vm_rev_all_frozen(rel);
+
map[mapByte] &= ~mask;
MarkBufferDirty(vmbuf);
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 733ef40ae7c..d8776ff1901 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -116,6 +116,9 @@ pg_atomic_uint32 *VacuumSharedCostBalance = NULL;
pg_atomic_uint32 *VacuumActiveNWorkers = NULL;
int VacuumCostBalanceLocal = 0;
+/* Cumulative storage to report total vacuum delay time. */
+double VacuumDelayTime = 0; /* msec. */
+
/* non-export function prototypes */
static List *expand_vacuum_rel(VacuumRelation *vrel,
MemoryContext vac_context, int options);
@@ -2533,6 +2536,7 @@ vacuum_delay_point(bool is_analyze)
exit(1);
VacuumCostBalance = 0;
+ VacuumDelayTime += msec;
/*
* Balance and update limit values for autovacuum workers. We must do
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 0feea1d30ec..b5461ec661b 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -868,6 +868,8 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
IndexBulkDeleteResult *istat = NULL;
IndexBulkDeleteResult *istat_res;
IndexVacuumInfo ivinfo;
+ LVExtStatCountersIdx extVacCounters;
+ PgStat_VacuumRelationCounts extVacReport;
/*
* Update the pointer to the corresponding bulk-deletion result if someone
@@ -876,6 +878,9 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
if (indstats->istat_updated)
istat = &(indstats->istat);
+ /* Set initial statistics values to gather vacuum statistics for the index */
+ extvac_stats_start_idx(indrel, &(indstats->istat), &extVacCounters);
+
ivinfo.index = indrel;
ivinfo.heaprel = pvs->heaprel;
ivinfo.analyze_only = false;
@@ -904,6 +909,12 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
RelationGetRelationName(indrel));
}
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat_res, &extVacCounters, &extVacReport);
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+ indrel->rd_rel->relisshared,
+ 0, 0, 0, &extVacReport);
+
/*
* Copy the index bulk-deletion result returned from ambulkdelete and
* amvacuumcleanup to the DSM segment if it's the first cycle because they
@@ -1054,6 +1065,7 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
/* Set cost-based vacuum delay */
VacuumUpdateCosts();
VacuumCostBalance = 0;
+ VacuumDelayTime = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = &(shared->cost_balance);
VacuumActiveNWorkers = &(shared->active_nworkers);
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 69df741cbf6..33a4009f746 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -203,13 +203,39 @@ pgstat_drop_relation(Relation rel)
}
}
+/* ---------
+ * pgstat_report_vacuum_error() -
+ *
+ * Tell the collector about an (auto)vacuum interruption.
+ * ---------
+ */
+void
+pgstat_report_vacuum_error(Oid tableoid, bool shared, ExtVacReportType m_type)
+{
+ PgStat_VacuumRelationCounts params;
+
+ if (!pgstat_track_counts)
+ return;
+
+ if (set_report_vacuum_hook)
+ {
+ memset(¶ms, 0, sizeof(PgStat_VacuumRelationCounts));
+
+ params.common.interrupts_count++;
+
+ (*set_report_vacuum_hook) (tableoid, shared, ¶ms);
+ }
+}
+
+set_report_vacuum_hook_type set_report_vacuum_hook = NULL;
+
/*
* Report that the table was just vacuumed and flush IO statistics.
*/
void
pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime)
+ TimestampTz starttime, PgStat_VacuumRelationCounts *params)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
@@ -235,6 +261,11 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->live_tuples = livetuples;
tabentry->dead_tuples = deadtuples;
+ if (set_report_vacuum_hook)
+ {
+ (*set_report_vacuum_hook) (tableoid, shared, params);
+ }
+
/*
* It is quite possible that a non-aggressive VACUUM ended up skipping
* various pages, however, we'll zero the insert counter here regardless.
@@ -881,6 +912,9 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
tabentry->blocks_fetched += lstats->counts.blocks_fetched;
tabentry->blocks_hit += lstats->counts.blocks_hit;
+ tabentry->rev_all_frozen_pages += lstats->counts.rev_all_frozen_pages;
+ tabentry->rev_all_visible_pages += lstats->counts.rev_all_visible_pages;
+
/* Clamp live_tuples in case of negative delta_live_tuples */
tabentry->live_tuples = Max(tabentry->live_tuples, 0);
/* Likewise for dead_tuples */
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c756c2bebaa..9482bf80721 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -106,6 +106,12 @@ PG_STAT_GET_RELENTRY_INT64(tuples_updated)
/* pg_stat_get_vacuum_count */
PG_STAT_GET_RELENTRY_INT64(vacuum_count)
+/* pg_stat_get_rev_frozen_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_frozen_pages)
+
+/* pg_stat_get_rev_all_visible_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_visible_pages)
+
#define PG_STAT_GET_RELENTRY_FLOAT8(stat) \
Datum \
CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS) \
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index b7b9692f8c8..f0ecf86e514 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -1627,6 +1627,19 @@ getinternalerrposition(void)
return edata->internalpos;
}
+/*
+ * Return elevel of errors
+ */
+int
+geterrelevel(void)
+{
+ ErrorData *edata = &errordata[errordata_stack_depth];
+
+ /* we don't bother incrementing recursion_depth */
+ CHECK_STACK_DEPTH();
+
+ return edata->elevel;
+}
/*
* Functions to allow construction of error message strings separately from
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..e0c7cf29b3a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,12 @@
proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
prosrc => 'pg_get_aios' },
+ { oid => '8002', descr => 'statistics: number of times the all-visible pages in the visibility map was removed for pages of table',
+ proname => 'pg_stat_get_rev_all_visible_pages', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_rev_all_visible_pages' },
+ { oid => '8003', descr => 'statistics: number of times the all-frozen pages in the visibility map was removed for pages of table',
+ proname => 'pg_stat_get_rev_all_frozen_pages', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_rev_all_frozen_pages' },
]
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 14eeccbd718..bc9df1433c2 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -25,6 +25,7 @@
#include "storage/buf.h"
#include "storage/lock.h"
#include "utils/relcache.h"
+#include "pgstat.h"
/*
* Flags for amparallelvacuumoptions to control the participation of bulkdelete
@@ -295,6 +296,26 @@ typedef struct VacDeadItemsInfo
int64 num_items; /* current # of entries */
} VacDeadItemsInfo;
+/*
+ * Counters and usage data for extended stats tracking.
+ */
+typedef struct LVExtStatCounters
+{
+ TimestampTz starttime;
+ WalUsage walusage;
+ BufferUsage bufusage;
+ double VacuumDelayTime;
+ PgStat_Counter blocks_fetched;
+ PgStat_Counter blocks_hit;
+} LVExtStatCounters;
+
+typedef struct LVExtStatCountersIdx
+{
+ LVExtStatCounters common;
+ int64 pages_deleted;
+ int64 tuples_removed;
+} LVExtStatCountersIdx;
+
/* GUC parameters */
extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for PostGIS */
extern PGDLLIMPORT int vacuum_freeze_min_age;
@@ -327,6 +348,7 @@ extern PGDLLIMPORT double vacuum_max_eager_freeze_failure_rate;
extern PGDLLIMPORT pg_atomic_uint32 *VacuumSharedCostBalance;
extern PGDLLIMPORT pg_atomic_uint32 *VacuumActiveNWorkers;
extern PGDLLIMPORT int VacuumCostBalanceLocal;
+extern PGDLLIMPORT double VacuumDelayTime;
extern PGDLLIMPORT bool VacuumFailsafeActive;
extern PGDLLIMPORT double vacuum_cost_delay;
@@ -407,4 +429,8 @@ extern double anl_random_fract(void);
extern double anl_init_selection_state(int n);
extern double anl_get_next_S(double t, int n, double *stateptr);
+extern void extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters);
+extern void extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
+ LVExtStatCountersIdx *counters, PgStat_VacuumRelationCounts *report);
#endif /* VACUUM_H */
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f402b17295c..ed6b3dc1d6f 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -100,6 +100,98 @@ typedef struct PgStat_FunctionCallUsage
instr_time start;
} PgStat_FunctionCallUsage;
+
+/* Type of ExtVacReport */
+typedef enum ExtVacReportType
+{
+ PGSTAT_EXTVAC_INVALID = 0,
+ PGSTAT_EXTVAC_TABLE = 1,
+ PGSTAT_EXTVAC_INDEX = 2,
+ PGSTAT_EXTVAC_DB = 3,
+} ExtVacReportType;
+
+typedef struct PgStat_CommonCounts
+{
+ /* blocks */
+ int64 total_blks_read;
+ int64 total_blks_hit;
+ int64 total_blks_dirtied;
+ int64 total_blks_written;
+
+ /* heap blocks */
+ int64 blks_fetched;
+ int64 blks_hit;
+
+ /* WAL */
+ int64 wal_records;
+ int64 wal_fpi;
+ uint64 wal_bytes;
+
+ /* Time */
+ double blk_read_time;
+ double blk_write_time;
+ double delay_time;
+ double total_time;
+
+ /* failsafe */
+ int32 wraparound_failsafe_count;
+ int32 interrupts_count;
+} PgStat_CommonCounts;
+
+/* ----------
+ *
+ * PgStat_VacuumRelationCounts
+ *
+ * Additional statistics of vacuum processing over a relation.
+ * pages_removed is the amount by which the physically shrank,
+ * if any (ie the change in its total size on disk)
+ * pages_deleted refer to free space within the index file
+ * ----------
+ */
+typedef struct PgStat_VacuumRelationCounts
+{
+ PgStat_CommonCounts common;
+
+ ExtVacReportType type; /* heap, index, etc. */
+
+ /* ----------
+ *
+ * There are separate metrics of statistic for tables and indexes,
+ * which collect during vacuum.
+ * The union operator allows to combine these statistics
+ * so that each metric is assigned to a specific class of collected statistics.
+ * Such a combined structure was called per_type_stats.
+ * The name of the structure itself is not used anywhere,
+ * it exists only for understanding the code.
+ * ----------
+ */
+ union
+ {
+ struct
+ {
+ int64 tuples_frozen; /* tuples frozen up by vacuum */
+ int64 recently_dead_tuples; /* deleted tuples that are still visible to some transaction */
+ int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */
+ int64 pages_scanned; /* heap pages examined (not skipped by VM) */
+ int64 pages_removed; /* heap pages removed by vacuum "truncation" */
+ int64 pages_frozen; /* pages marked in VM as frozen */
+ int64 pages_all_visible; /* pages marked in VM as all-visible */
+ int64 vm_new_frozen_pages; /* pages marked in VM as frozen */
+ int64 vm_new_visible_pages; /* pages marked in VM as all-visible */
+ int64 vm_new_visible_frozen_pages; /* pages marked in VM as all-visible and frozen */
+ int64 missed_dead_pages; /* pages with missed dead tuples */
+ int64 index_vacuum_count; /* number of index vacuumings */
+ int64 tuples_deleted;
+ } table;
+ struct
+ {
+ int64 tuples_deleted;
+ int64 pages_deleted; /* number of pages deleted by vacuum */
+ } index;
+ } /* per_type_stats */;
+} PgStat_VacuumRelationCounts;
+
+
/* ----------
* PgStat_BackendSubEntry Non-flushed subscription stats.
* ----------
@@ -153,6 +245,9 @@ typedef struct PgStat_TableCounts
PgStat_Counter blocks_fetched;
PgStat_Counter blocks_hit;
+
+ PgStat_Counter rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
} PgStat_TableCounts;
/* ----------
@@ -211,7 +306,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB7
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB8
typedef struct PgStat_ArchiverStats
{
@@ -453,6 +548,9 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter total_autovacuum_time;
PgStat_Counter total_analyze_time;
PgStat_Counter total_autoanalyze_time;
+
+ PgStat_Counter rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
} PgStat_StatTabEntry;
/* ------
@@ -660,10 +758,11 @@ extern void pgstat_unlink_relation(Relation rel);
extern void pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime);
+ TimestampTz starttime, PgStat_VacuumRelationCounts *params);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
+extern void pgstat_report_vacuum_error(Oid tableoid, bool shared, ExtVacReportType m_type);
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
@@ -711,6 +810,17 @@ extern void pgstat_report_analyze(Relation rel,
if (pgstat_should_count_relation(rel)) \
(rel)->pgstat_info->counts.blocks_hit++; \
} while (0)
+/* accumulate unfrozen all-visible and all-frozen pages */
+#define pgstat_count_vm_rev_all_visible(rel) \
+ do { \
+ if (pgstat_should_count_relation(rel)) \
+ (rel)->pgstat_info->counts.rev_all_visible_pages++; \
+ } while (0)
+#define pgstat_count_vm_rev_all_frozen(rel) \
+ do { \
+ if (pgstat_should_count_relation(rel)) \
+ (rel)->pgstat_info->counts.rev_all_frozen_pages++; \
+ } while (0)
extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n);
extern void pgstat_count_heap_update(Relation rel, bool hot, bool newpage);
@@ -838,4 +948,9 @@ extern PGDLLIMPORT PgStat_Counter pgStatTransactionIdleTime;
/* updated by the traffic cop and in errfinish() */
extern PGDLLIMPORT SessionEndType pgStatSessionEndCause;
+/* Hook for plugins to get control in set_rel_pathlist() */
+typedef void (*set_report_vacuum_hook_type) (Oid tableoid, bool shared, PgStat_VacuumRelationCounts *params);
+extern PGDLLIMPORT set_report_vacuum_hook_type set_report_vacuum_hook;
+
+
#endif /* PGSTAT_H */
diff --git a/src/include/utils/elog.h b/src/include/utils/elog.h
index 675f4f5f469..356dadd6b0a 100644
--- a/src/include/utils/elog.h
+++ b/src/include/utils/elog.h
@@ -230,6 +230,7 @@ extern int geterrcode(void);
extern int geterrposition(void);
extern int getinternalerrposition(void);
+extern int geterrelevel(void);
/*----------
* Old-style error reporting API: to be used in this way:
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 35e8aad7701..4731ca2121e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1833,7 +1833,9 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
- pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time
+ pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time,
+ pg_stat_get_rev_all_frozen_pages(c.oid) AS rev_all_frozen_pages,
+ pg_stat_get_rev_all_visible_pages(c.oid) AS rev_all_visible_pages
FROM ((pg_class c
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2232,7 +2234,9 @@ pg_stat_sys_tables| SELECT relid,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
- total_autoanalyze_time
+ total_autoanalyze_time,
+ rev_all_frozen_pages,
+ rev_all_visible_pages
FROM pg_stat_all_tables
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
pg_stat_user_functions| SELECT p.oid AS funcid,
@@ -2284,7 +2288,9 @@ pg_stat_user_tables| SELECT relid,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
- total_autoanalyze_time
+ total_autoanalyze_time,
+ rev_all_frozen_pages,
+ rev_all_visible_pages
FROM pg_stat_all_tables
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
pg_stat_wal| SELECT wal_records,
--
2.34.1
[text/x-patch] 0001-Create-vacuum-extension-statistics.patch (75.1K, 3-0001-Create-vacuum-extension-statistics.patch)
download | inline diff:
From 9b4dd2e845cb8fdc9c1cee09da8665da2bccfd42 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Thu, 4 Sep 2025 18:24:46 +0300
Subject: [PATCH] Create vacuum extension statistics
---
Makefile | 21 +
.../vacuum-extending-in-repetable-read.out | 53 ++
expected/vacuum_index_statistics.out | 183 +++++
expected/vacuum_tables_and_db_statistics.out | 296 ++++++++
spec/vacuum-extending-in-repetable-read.spec | 173 +++++
sql/vacuum_index_statistics.sql | 138 ++++
sql/vacuum_tables_and_db_statistics.sql | 224 ++++++
vacuum_statistics--1.0.sql | 191 ++++++
vacuum_statistics.c | 636 ++++++++++++++++++
vacuum_statistics.control | 4 +
10 files changed, 1919 insertions(+)
create mode 100644 Makefile
create mode 100644 expected/vacuum-extending-in-repetable-read.out
create mode 100644 expected/vacuum_index_statistics.out
create mode 100644 expected/vacuum_tables_and_db_statistics.out
create mode 100644 spec/vacuum-extending-in-repetable-read.spec
create mode 100644 sql/vacuum_index_statistics.sql
create mode 100644 sql/vacuum_tables_and_db_statistics.sql
create mode 100644 vacuum_statistics--1.0.sql
create mode 100644 vacuum_statistics.c
create mode 100644 vacuum_statistics.control
diff --git a/Makefile b/Makefile
new file mode 100644
index 0000000..7fd875e
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,21 @@
+EXTENSION = vacuum_statistics
+EXTVERSION = 1.0
+MODULE_big = vacuum_statistics
+PGFILEDESC = "Vacuum Statistics - extension for storage statistics of vacuum workload"
+OBJS = vacuum_statistics.o
+
+DATA = vacuum_statistics--1.0.sql
+
+REGRESS = vacuum_index_statistics vacuum_tables_and_db_statistics
+ISOLATION = vacuum-extending-in-repetable-read
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/vacuum_statistics
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
\ No newline at end of file
diff --git a/expected/vacuum-extending-in-repetable-read.out b/expected/vacuum-extending-in-repetable-read.out
new file mode 100644
index 0000000..6d96042
--- /dev/null
+++ b/expected/vacuum-extending-in-repetable-read.out
@@ -0,0 +1,53 @@
+unused step name: s2_delete
+Parsed test spec with 2 sessions
+
+starting permutation: s2_insert s2_print_vacuum_stats_table s1_begin_repeatable_read s2_update s2_insert_interrupt s2_vacuum s2_print_vacuum_stats_table s1_commit s2_checkpoint s2_vacuum s2_print_vacuum_stats_table
+step s2_insert: INSERT INTO test_vacuum_stat_isolation(id, ival) SELECT ival, ival%10 FROM generate_series(1,1000) As ival;
+step s2_print_vacuum_stats_table:
+ SELECT
+ vt.relname, vt.tuples_deleted, vt.recently_dead_tuples, vt.missed_dead_tuples, vt.missed_dead_pages, vt.tuples_frozen
+ FROM pg_stat_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+
+relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
+--------------------------+--------------+--------------------+------------------+-----------------+-------------
+test_vacuum_stat_isolation| 0| 0| 0| 0| 0
+(1 row)
+
+step s1_begin_repeatable_read:
+ BEGIN transaction ISOLATION LEVEL REPEATABLE READ;
+ select count(ival) from test_vacuum_stat_isolation where id>900;
+
+count
+-----
+ 100
+(1 row)
+
+step s2_update: UPDATE test_vacuum_stat_isolation SET ival = ival + 2 where id > 900;
+step s2_insert_interrupt: INSERT INTO test_vacuum_stat_isolation values (1,1);
+step s2_vacuum: VACUUM test_vacuum_stat_isolation;
+step s2_print_vacuum_stats_table:
+ SELECT
+ vt.relname, vt.tuples_deleted, vt.recently_dead_tuples, vt.missed_dead_tuples, vt.missed_dead_pages, vt.tuples_frozen
+ FROM pg_stat_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+
+relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
+--------------------------+--------------+--------------------+------------------+-----------------+-------------
+test_vacuum_stat_isolation| 0| 600| 0| 0| 0
+(1 row)
+
+step s1_commit: COMMIT;
+step s2_checkpoint: CHECKPOINT;
+step s2_vacuum: VACUUM test_vacuum_stat_isolation;
+step s2_print_vacuum_stats_table:
+ SELECT
+ vt.relname, vt.tuples_deleted, vt.recently_dead_tuples, vt.missed_dead_tuples, vt.missed_dead_pages, vt.tuples_frozen
+ FROM pg_stat_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+
+relname |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages|tuples_frozen
+--------------------------+--------------+--------------------+------------------+-----------------+-------------
+test_vacuum_stat_isolation| 300| 600| 0| 0| 303
+(1 row)
+
diff --git a/expected/vacuum_index_statistics.out b/expected/vacuum_index_statistics.out
new file mode 100644
index 0000000..4654a53
--- /dev/null
+++ b/expected/vacuum_index_statistics.out
@@ -0,0 +1,183 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+-- conditio sine qua non
+SHOW track_counts; -- must be on
+ track_counts
+--------------
+ on
+(1 row)
+
+\set sample_size 10000
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+SHOW track_vacuum_statistics; -- must be off
+ track_vacuum_statistics
+-------------------------
+ off
+(1 row)
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+DELETE FROM vestat WHERE x % 2 = 0;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- Must be empty.
+SELECT *
+FROM pg_stat_vacuum_indexes vt
+WHERE vt.indexrelname = 'vestat';
+ relid | indexrelid | schemaname | relname | indexrelname | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written | rel_blks_read | rel_blks_hit | pages_deleted | tuples_deleted | wal_records | wal_fpi | wal_bytes | blk_read_time | blk_write_time | delay_time | total_time
+-------+------------+------------+---------+--------------+-----------------+----------------+--------------------+--------------------+---------------+--------------+---------------+----------------+-------------+---------+-----------+---------------+----------------+------------+------------
+(0 rows)
+
+RESET track_vacuum_statistics;
+DROP TABLE vestat CASCADE;
+SET track_vacuum_statistics TO 'on';
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+\set sample_size 10000
+SET vacuum_freeze_min_age = 0;
+SET vacuum_freeze_table_age = 0;
+--SET stats_fetch_consistency = snapshot;
+CREATE TABLE vestat (x int primary key) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+SELECT oid AS ioid from pg_class where relname = 'vestat_pkey' \gset
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.indexrelname,relpages,pages_deleted,tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+ indexrelname | relpages | pages_deleted | tuples_deleted
+--------------+----------+---------------+----------------
+ vestat_pkey | 30 | 0 | 0
+(1 row)
+
+SELECT relpages AS irp
+FROM pg_class c
+WHERE relname = 'vestat_pkey' \gset
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.indexrelname,relpages-:irp = 0 AS relpages,pages_deleted = 0 AS pages_deleted,tuples_deleted > 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+ indexrelname | relpages | pages_deleted | tuples_deleted
+--------------+----------+---------------+----------------
+ vestat_pkey | t | t | t
+(1 row)
+
+SELECT vt.indexrelname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS diWR, wal_bytes > 0 AS diWB
+FROM pg_stat_vacuum_indexes WHERE indexrelname = 'vestat_pkey';
+ diwr | diwb
+------+------
+ t | t
+(1 row)
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- pages_removed must be increased
+SELECT vt.indexrelname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd > 0 AS pages_deleted,tuples_deleted-:itd > 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+ indexrelname | relpages | pages_deleted | tuples_deleted
+--------------+----------+---------------+----------------
+ vestat_pkey | t | t | t
+(1 row)
+
+SELECT vt.indexrelname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr, wal_bytes-:iwb AS diwb, wal_fpi-:ifpi AS difpi
+FROM pg_stat_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+-- WAL advance should be detected.
+SELECT :diwr > 0 AS diWR, :diwb > 0 AS diWB;
+ diwr | diwb
+------+------
+ t | t
+(1 row)
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr2, wal_bytes-:iwb AS diwb2, wal_fpi-:ifpi AS difpi2
+FROM pg_stat_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+-- WAL and other statistics advance should not be detected.
+SELECT :diwr2=0 AS diWR, :difpi2=0 AS iFPI, :diwb2=0 AS diWB;
+ diwr | ifpi | diwb
+------+------+------
+ t | t | t
+(1 row)
+
+SELECT vt.indexrelname,relpages-:irp < 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+ indexrelname | relpages | pages_deleted | tuples_deleted
+--------------+----------+---------------+----------------
+ vestat_pkey | t | t | t
+(1 row)
+
+SELECT vt.indexrelname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stat_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:iwr AS diwr3, wal_bytes-:iwb AS diwb3, wal_fpi-:ifpi AS difpi3
+FROM pg_stat_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+--There are nothing changed
+SELECT :diwr3=0 AS diWR, :difpi3=0 AS iFPI, :diwb3=0 AS diWB;
+ diwr | ifpi | diwb
+------+------+------
+ t | t | t
+(1 row)
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The pages_frozen, pages_scanned values shouldn't be changed
+--
+SELECT vt.indexrelname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stat_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+ indexrelname | relpages | pages_deleted | tuples_deleted
+--------------+----------+---------------+----------------
+ vestat_pkey | f | t | t
+(1 row)
+
+DROP TABLE vestat;
+RESET track_vacuum_statistics;
diff --git a/expected/vacuum_tables_and_db_statistics.out b/expected/vacuum_tables_and_db_statistics.out
new file mode 100644
index 0000000..0300e7b
--- /dev/null
+++ b/expected/vacuum_tables_and_db_statistics.out
@@ -0,0 +1,296 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+SHOW track_counts; -- must be on
+ track_counts
+--------------
+ on
+(1 row)
+
+\set sample_size 10000
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+SHOW track_vacuum_statistics; -- must be off
+ track_vacuum_statistics
+-------------------------
+ off
+(1 row)
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+DELETE FROM vestat WHERE x % 2 = 0;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- Must be empty.
+SELECT relname,total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written,rel_blks_read, rel_blks_hit,
+pages_scanned, pages_removed, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages, missed_dead_pages,
+tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_tuples, index_vacuum_count,
+wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time,delay_time, total_time
+FROM pg_stat_vacuum_tables vt
+WHERE vt.relname = 'vestat';
+ relname | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written | rel_blks_read | rel_blks_hit | pages_scanned | pages_removed | vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | missed_dead_pages | tuples_deleted | tuples_frozen | recently_dead_tuples | missed_dead_tuples | index_vacuum_count | wal_records | wal_fpi | wal_bytes | blk_read_time | blk_write_time | delay_time | total_time
+---------+-----------------+----------------+--------------------+--------------------+---------------+--------------+---------------+---------------+---------------------+----------------------+-----------------------------+-------------------+----------------+---------------+----------------------+--------------------+--------------------+-------------+---------+-----------+---------------+----------------+------------+------------
+ vestat | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
+(1 row)
+
+RESET track_vacuum_statistics;
+DROP TABLE vestat CASCADE;
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+SET track_vacuum_statistics TO on;
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+--SET stats_fetch_consistency = snapshot;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+SELECT oid AS roid from pg_class where relname = 'vestat' \gset
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.relname,vm_new_frozen_pages,tuples_deleted,relpages,pages_scanned,pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | 0 | 0 | 455 | 0 | 0
+(1 row)
+
+SELECT relpages AS rp
+FROM pg_class c
+WHERE relname = 'vestat' \gset
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.relname,vm_new_frozen_pages > 0 AS vm_new_frozen_pages,tuples_deleted > 0 AS tuples_deleted,relpages-:rp = 0 AS relpages,pages_scanned > 0 AS pages_scanned,pages_removed = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | f | t | t | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS dWR, wal_bytes > 0 AS dWB
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
+ dwr | dwb
+-----+-----
+ t | t
+(1 row)
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- pages_removed must be increased
+SELECT vt.relname,vm_new_frozen_pages-:fp > 0 AS vm_new_frozen_pages,tuples_deleted-:td > 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps > 0 AS pages_scanned,pages_removed-:pr > 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | f | t | f | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr, wal_bytes-:hwb AS dwb, wal_fpi-:hfpi AS dfpi
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+-- WAL advance should be detected.
+SELECT :dwr > 0 AS dWR, :dwb > 0 AS dWB;
+ dwr | dwb
+-----+-----
+ t | t
+(1 row)
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr2, wal_bytes-:hwb AS dwb2, wal_fpi-:hfpi AS dfpi2
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+-- WAL and other statistics advance should not be detected.
+SELECT :dwr2=0 AS dWR, :dfpi2=0 AS dFPI, :dwb2=0 AS dWB;
+ dwr | dfpi | dwb
+-----+------+-----
+ t | t | t
+(1 row)
+
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp < 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | t | t | f | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps,pages_removed AS pr
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:hwr AS dwr3, wal_bytes-:hwb AS dwb3, wal_fpi-:hfpi AS dfpi3
+FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
+--There are nothing changed
+SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB;
+ dwr | dfpi | dwb
+-----+------+-----
+ t | t | t
+(1 row)
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The vm_new_frozen_pages, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stat_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+ relname | vm_new_frozen_pages | tuples_deleted | relpages | pages_scanned | pages_removed
+---------+---------------------+----------------+----------+---------------+---------------
+ vestat | t | t | f | t | t
+(1 row)
+
+DROP TABLE vestat CASCADE;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+-- must be empty
+SELECT vm_new_frozen_pages, vm_new_visible_pages, rev_all_frozen_pages,rev_all_visible_pages,vm_new_visible_frozen_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | rev_all_frozen_pages | rev_all_visible_pages | vm_new_visible_frozen_pages
+---------------------+----------------------+----------------------+-----------------------+-----------------------------
+ 0 | 0 | 0 | 0 | 0
+(1 row)
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- backend defreezed pages
+SELECT vm_new_frozen_pages > 0 AS vm_new_frozen_pages,vm_new_visible_pages > 0 AS vm_new_visible_pages,vm_new_visible_frozen_pages > 0 AS vm_new_visible_frozen_pages,rev_all_frozen_pages = 0 AS rev_all_frozen_pages,rev_all_visible_pages = 0 AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | rev_all_frozen_pages | rev_all_visible_pages
+---------------------+----------------------+-----------------------------+----------------------+-----------------------
+ f | t | f | t | t
+(1 row)
+
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv,vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+UPDATE vestat SET x = x + 1001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+SELECT vm_new_frozen_pages > :pf AS vm_new_frozen_pages,vm_new_visible_pages > :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages > :pvf AS vm_new_visible_frozen_pages,rev_all_frozen_pages > :hafp AS rev_all_frozen_pages,rev_all_visible_pages > :havp AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | rev_all_frozen_pages | rev_all_visible_pages
+---------------------+----------------------+-----------------------------+----------------------+-----------------------
+ f | t | f | f | f
+(1 row)
+
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv, vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+-- vacuum freezed pages
+SELECT vm_new_frozen_pages = :pf AS vm_new_frozen_pages,vm_new_visible_pages = :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages = :pvf AS vm_new_visible_frozen_pages, rev_all_frozen_pages = :hafp AS rev_all_frozen_pages,rev_all_visible_pages = :havp AS rev_all_visible_pages
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
+ vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages | rev_all_frozen_pages | rev_all_visible_pages
+---------------------+----------------------+-----------------------------+----------------------+-----------------------
+ t | t | t | t | t
+(1 row)
+
+DROP TABLE vestat CASCADE;
+-- Now check vacuum statistics for current database
+SELECT dbname,
+ db_blks_hit > 0 AS db_blks_hit,
+ total_blks_dirtied > 0 AS total_blks_dirtied,
+ total_blks_written > 0 AS total_blks_written,
+ wal_records > 0 AS wal_records,
+ wal_fpi > 0 AS wal_fpi,
+ wal_bytes > 0 AS wal_bytes,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = current_database();
+ dbname | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------
+ regression_statistic_vacuum_db | t | t | t | t | t | t | t
+(1 row)
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+UPDATE vestat SET x = 10001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+-- Now check vacuum statistics for postgres database from another database
+SELECT dbname,
+ db_blks_hit > 0 AS db_blks_hit,
+ total_blks_dirtied > 0 AS total_blks_dirtied,
+ total_blks_written > 0 AS total_blks_written,
+ wal_records > 0 AS wal_records,
+ wal_fpi > 0 AS wal_fpi,
+ wal_bytes > 0 AS wal_bytes,
+ total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+ dbname | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------
+ regression_statistic_vacuum_db | t | t | t | t | t | t | t
+(1 row)
+
+\c regression_statistic_vacuum_db
+SET track_vacuum_statistics TO on;
+DROP TABLE vestat CASCADE;
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_get_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+ count
+-------
+ 0
+(1 row)
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
+RESET track_vacuum_statistics;
diff --git a/spec/vacuum-extending-in-repetable-read.spec b/spec/vacuum-extending-in-repetable-read.spec
new file mode 100644
index 0000000..13b6c91
--- /dev/null
+++ b/spec/vacuum-extending-in-repetable-read.spec
@@ -0,0 +1,173 @@
+# A number of tests dedicated to verification of the 'Extended Vacuum Statistics'
+# feature.
+# By default, statistics has a volatile nature. So, selection result can depend
+# on a bunch of things. Here some trivial tests are performed that should work
+# in the most cases.
+# Test for checking pages: frozen, scanned, removed, number of tuple_deleted in pgpro_stats_vacuum_tables.
+# Besides, this test check pages scanned, pages removed, tuples_deleted in pgpro_stats_vacuum_tables and
+# wal values statistic collected over vacuum operation as for tables as for indexes.
+
+setup
+{
+ CREATE TABLE vestat (x int primary key) WITH (autovacuum_enabled = off);
+
+ CREATE EXTENSION vacuum_statistics;
+
+ CREATE TABLE vacuum_wal_stats_table
+ (relid int, wal_records int, wal_fpi int, wal_bytes int);
+ insert into vacuum_wal_stats_table (relid)
+ select oid from pg_class c
+ WHERE relname = 'vestat';
+ UPDATE vacuum_wal_stats_table SET
+ wal_records = 0, wal_fpi = 0, wal_bytes = 0;
+
+ CREATE TABLE vacuum_wal_stats_index
+ (relid int, wal_records int, wal_fpi int, wal_bytes int);
+ insert into vacuum_wal_stats_index (relid)
+ select oid from pg_class c
+ WHERE relname = 'vestat_pkey';
+ UPDATE vacuum_wal_stats_index SET
+ wal_records = 0, wal_fpi = 0, wal_bytes = 0;
+
+ SET track_io_timing = on;
+ SHOW track_counts; -- must be on
+ SET track_functions TO 'all';
+
+}
+
+teardown
+{
+ RESET vacuum_freeze_min_age;
+ RESET vacuum_freeze_table_age;
+ DROP TABLE vestat CASCADE;
+ DROP TABLE vacuum_wal_stats_index;
+ DROP TABLE vacuum_wal_stats_table;
+ DROP EXTENSION vacuum_statistics;
+}
+
+session s1
+step s1_set_agressive_vacuum { SET vacuum_freeze_min_age = 0; }
+step s1_insert { INSERT INTO vestat(x) SELECT id FROM generate_series(1,770) As id; }
+step s1_update { UPDATE vestat SET x = x+1; }
+step s1_delete_half_table { DELETE FROM vestat WHERE x % 2 = 0; }
+step s1_delete_full_table { DELETE FROM vestat; }
+step s1_vacuum { VACUUM vestat; }
+step s1_vacuum_full { VACUUM FULL vestat; }
+step s1_vacuum_parallel { VACUUM (PARALLEL 2, INDEX_CLEANUP ON) vestat; }
+step s1_analyze { ANALYZE vestat; }
+step s1_trancate { TRUNCATE vestat; }
+step s1_checkpoint { CHECKPOINT; }
+step s1_print_vacuum_stats_tables
+{
+ SELECT vt.relname,
+ pages_frozen,
+ tuples_deleted,
+ pages_scanned,
+ pages_removed
+ FROM pgpro_stats_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'vestat' AND
+ vt.relid = c.oid;
+}
+
+step s1_print_vacuum_stats_indexes
+{
+ SELECT vt.relname,
+ pages_deleted,
+ tuples_deleted
+ FROM pgpro_stats_vacuum_indexes vt, pg_class c
+ WHERE vt.relname = 'vestat_pkey' AND
+ vt.relid = c.oid;
+}
+
+step s1_save_walls
+{
+ UPDATE vacuum_wal_stats_table SET
+ wal_records = dWR, wal_fpi = dFPI, wal_bytes = dWB
+ FROM (SELECT relid, wal_records AS dWR, wal_fpi AS dFPI, wal_bytes AS dWB
+ FROM pgpro_stats_vacuum_tables vt, pg_class c
+ WHERE vt.relname = 'vestat' AND
+ vt.relid = c.oid) t
+ WHERE
+ t.relid = t.relid;
+
+ UPDATE vacuum_wal_stats_index SET
+ wal_records = iWR, wal_fpi = iFPI, wal_bytes = iWB
+ FROM (SELECT relid, wal_records AS iWR, wal_fpi AS iFPI, wal_bytes AS iWB
+ FROM pgpro_stats_vacuum_indexes vt, pg_class c
+ WHERE vt.relname = 'vestat_pkey' AND
+ vt.relid = c.oid) t
+ WHERE
+ t.relid = t.relid;
+}
+
+step s1_difference
+{
+ SELECT t1.wal_records - t0.wal_records > 0 AS dWR,
+ t1.wal_fpi - t0.wal_fpi > 0 AS dFPI,
+ t1.wal_bytes - t0.wal_bytes > 0 AS dWB
+ FROM vacuum_wal_stats_table t0, pgpro_stats_vacuum_tables t1
+ WHERE t0.relid = t1.relid;
+
+ SELECT t1.wal_records - t0.wal_records > 0 AS iWR,
+ t1.wal_fpi - t0.wal_fpi > 0 AS iFPI,
+ t1.wal_bytes - t0.wal_bytes > 0 AS iWB
+ FROM vacuum_wal_stats_table t0, pgpro_stats_vacuum_tables t1
+ WHERE t0.relid = t1.relid;
+}
+
+permutation
+ s1_insert
+ s1_print_vacuum_stats_tables
+ s1_print_vacuum_stats_indexes
+ s1_set_agressive_vacuum
+ s1_analyze
+ s1_delete_half_table
+ s1_print_vacuum_stats_tables
+ s1_print_vacuum_stats_indexes
+ s1_vacuum
+ s1_print_vacuum_stats_tables
+ s1_print_vacuum_stats_indexes
+ s1_delete_full_table
+ s1_vacuum_parallel
+ s1_print_vacuum_stats_tables
+ s1_print_vacuum_stats_indexes
+ s1_insert
+ s1_update
+ s1_vacuum_full
+ s1_print_vacuum_stats_tables
+ s1_print_vacuum_stats_indexes
+ s1_delete_full_table
+ s1_trancate
+ s1_vacuum
+ s1_print_vacuum_stats_tables
+ s1_print_vacuum_stats_indexes
+
+permutation
+ s1_insert
+ s1_set_agressive_vacuum
+ s1_analyze
+ s1_delete_half_table
+ s1_checkpoint
+ s1_difference
+ s1_save_walls
+ s1_vacuum
+ s1_checkpoint
+ s1_difference
+ s1_save_walls
+ s1_delete_full_table
+ s1_vacuum_parallel
+ s1_checkpoint
+ s1_difference
+ s1_save_walls
+ s1_insert
+ s1_update
+ s1_vacuum_full
+ s1_checkpoint
+ s1_difference
+ s1_save_walls
+ s1_delete_full_table
+ s1_trancate
+ s1_vacuum
+ s1_checkpoint
+ s1_difference
+ s1_save_walls
\ No newline at end of file
diff --git a/sql/vacuum_index_statistics.sql b/sql/vacuum_index_statistics.sql
new file mode 100644
index 0000000..996ea04
--- /dev/null
+++ b/sql/vacuum_index_statistics.sql
@@ -0,0 +1,138 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+-- conditio sine qua non
+SHOW track_counts; -- must be on
+
+\set sample_size 10000
+
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+
+CREATE EXTENSION vacuum_statistics;
+
+SHOW vacuum_statistics.enabled; -- must be on
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+DELETE FROM vestat WHERE x % 2 = 0;
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- Must be empty.
+SELECT *
+FROM pg_stats_vacuum_indexes vt
+WHERE vt.indexrelname = 'vestat';
+
+SELECT oid AS ioid from pg_class where relname = 'vestat_pkey' \gset
+
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.indexrelname,relpages,pages_deleted,tuples_deleted
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+SELECT relpages AS irp
+FROM pg_class c
+WHERE relname = 'vestat_pkey' \gset
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.indexrelname,relpages-:irp = 0 AS relpages,pages_deleted = 0 AS pages_deleted,tuples_deleted > 0 AS tuples_deleted
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+SELECT vt.indexrelname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stats_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS diWR, wal_bytes > 0 AS diWB
+FROM pg_stats_vacuum_indexes WHERE indexrelname = 'vestat_pkey';
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- pages_removed must be increased
+SELECT vt.indexrelname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd > 0 AS pages_deleted,tuples_deleted-:itd > 0 AS tuples_deleted
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+SELECT vt.indexrelname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr, wal_bytes-:iwb AS diwb, wal_fpi-:ifpi AS difpi
+FROM pg_stats_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+
+-- WAL advance should be detected.
+SELECT :diwr > 0 AS diWR, :diwb > 0 AS diWB;
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stats_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables
+SELECT wal_records-:iwr AS diwr2, wal_bytes-:iwb AS diwb2, wal_fpi-:ifpi AS difpi2
+FROM pg_stats_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+
+-- WAL and other statistics advance should not be detected.
+SELECT :diwr2=0 AS diWR, :difpi2=0 AS iFPI, :diwb2=0 AS diWB;
+
+SELECT vt.indexrelname,relpages-:irp < 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+SELECT vt.indexrelname,relpages AS irp,pages_deleted AS ipd,tuples_deleted AS itd
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS iwr,wal_bytes AS iwb,wal_fpi AS ifpi FROM pg_stats_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP ON) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:iwr AS diwr3, wal_bytes-:iwb AS diwb3, wal_fpi-:ifpi AS difpi3
+FROM pg_stats_vacuum_indexes WHERE indexrelname = 'vestat_pkey' \gset
+
+--There are nothing changed
+SELECT :diwr3=0 AS diWR, :difpi3=0 AS iFPI, :diwb3=0 AS diWB;
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The pages_frozen, pages_scanned values shouldn't be changed
+--
+SELECT vt.indexrelname,relpages-:irp = 0 AS relpages,pages_deleted-:ipd = 0 AS pages_deleted,tuples_deleted-:itd = 0 AS tuples_deleted
+FROM pg_stats_vacuum_indexes vt, pg_class c
+WHERE vt.indexrelname = 'vestat_pkey' AND vt.indexrelid = c.oid;
+
+DROP TABLE vestat;
+
+DROP EXTENSION vacuum_statistics;
diff --git a/sql/vacuum_tables_and_db_statistics.sql b/sql/vacuum_tables_and_db_statistics.sql
new file mode 100644
index 0000000..1e81a82
--- /dev/null
+++ b/sql/vacuum_tables_and_db_statistics.sql
@@ -0,0 +1,224 @@
+--
+-- Test cumulative vacuum stats system
+--
+-- Check the wall statistics collected during vacuum operation:
+-- number of frozen and visible pages set by vacuum;
+-- number of frozen and visible pages removed by backend.
+-- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
+--
+
+SHOW track_counts; -- must be on
+\set sample_size 10000
+
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+
+CREATE EXTENSION vacuum_statistics;
+
+SHOW vacuum_statistics.enabled; -- must be on
+
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+CREATE EXTENSION vacuum_statistics;
+
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+
+--SET stats_fetch_consistency = snapshot;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+SELECT oid AS roid from pg_class where relname = 'vestat' \gset
+
+DELETE FROM vestat WHERE x % 2 = 0;
+-- Before the first vacuum execution extended stats view is empty.
+SELECT vt.relname,vm_new_frozen_pages,tuples_deleted,relpages,pages_scanned,pages_removed
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT relpages AS rp
+FROM pg_class c
+WHERE relname = 'vestat' \gset
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- The table and index extended vacuum statistics should show us that
+-- vacuum frozed pages and clean up pages, but pages_removed stayed the same
+-- because of not full table have cleaned up
+SELECT vt.relname,vm_new_frozen_pages > 0 AS vm_new_frozen_pages,tuples_deleted > 0 AS tuples_deleted,relpages-:rp = 0 AS relpages,pages_scanned > 0 AS pages_scanned,pages_removed = 0 AS pages_removed
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stats_vacuum_tables WHERE relname = 'vestat' \gset
+
+-- Look into WAL records deltas.
+SELECT wal_records > 0 AS dWR, wal_bytes > 0 AS dWB
+FROM pg_stats_vacuum_tables WHERE relname = 'vestat';
+
+DELETE FROM vestat;;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- pages_removed must be increased
+SELECT vt.relname,vm_new_frozen_pages-:fp > 0 AS vm_new_frozen_pages,tuples_deleted-:td > 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps > 0 AS pages_scanned,pages_removed-:pr > 0 AS pages_removed
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps, pages_removed AS pr
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr, wal_bytes-:hwb AS dwb, wal_fpi-:hfpi AS dfpi
+FROM pg_stats_vacuum_tables WHERE relname = 'vestat' \gset
+
+-- WAL advance should be detected.
+SELECT :dwr > 0 AS dWR, :dwb > 0 AS dWB;
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stats_vacuum_tables WHERE relname = 'vestat' \gset
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+DELETE FROM vestat WHERE x % 2 = 0;
+-- VACUUM FULL doesn't report to stat collector. So, no any advancements of statistics
+-- are detected here.
+VACUUM FULL vestat;
+-- It is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables
+SELECT wal_records-:hwr AS dwr2, wal_bytes-:hwb AS dwb2, wal_fpi-:hfpi AS dfpi2
+FROM pg_stats_vacuum_tables WHERE relname = 'vestat' \gset
+
+-- WAL and other statistics advance should not be detected.
+SELECT :dwr2=0 AS dWR, :dfpi2=0 AS dFPI, :dwb2=0 AS dWB;
+
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp < 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+SELECT vm_new_frozen_pages AS fp,tuples_deleted AS td,relpages AS rp, pages_scanned AS ps,pages_removed AS pr
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid \gset
+
+-- Store WAL advances into variables
+SELECT wal_records AS hwr,wal_bytes AS hwb,wal_fpi AS hfpi FROM pg_stats_vacuum_tables WHERE relname = 'vestat' \gset
+
+DELETE FROM vestat;
+TRUNCATE vestat;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128, INDEX_CLEANUP OFF) vestat;
+-- it is necessary to check the wal statistics
+CHECKPOINT;
+
+-- Store WAL advances into variables after removing all tuples from the table
+SELECT wal_records-:hwr AS dwr3, wal_bytes-:hwb AS dwb3, wal_fpi-:hfpi AS dfpi3
+FROM pg_stats_vacuum_tables WHERE relname = 'vestat' \gset
+
+--There are nothing changed
+SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB;
+
+--
+-- Now, the table and index is compressed into zero number of pages. Check it
+-- in vacuum extended statistics.
+-- The vm_new_frozen_pages, pages_scanned values shouldn't be changed
+--
+SELECT vt.relname,vm_new_frozen_pages-:fp = 0 AS vm_new_frozen_pages,tuples_deleted-:td = 0 AS tuples_deleted,relpages -:rp = 0 AS relpages,pages_scanned-:ps = 0 AS pages_scanned,pages_removed-:pr = 0 AS pages_removed
+FROM pg_stats_vacuum_tables vt, pg_class c
+WHERE vt.relname = 'vestat' AND vt.relid = c.oid;
+
+DROP TABLE vestat CASCADE;
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+
+-- must be empty
+SELECT vm_new_frozen_pages, vm_new_visible_pages, rev_all_frozen_pages,rev_all_visible_pages,vm_new_visible_frozen_pages
+FROM pg_stats_vacuum_tables, pg_stat_all_tables WHERE pg_stats_vacuum_tables.relname = 'vestat' and pg_stats_vacuum_tables.relid = pg_stat_all_tables.relid;
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- backend defreezed pages
+SELECT vm_new_frozen_pages > 0 AS vm_new_frozen_pages,vm_new_visible_pages > 0 AS vm_new_visible_pages,vm_new_visible_frozen_pages > 0 AS vm_new_visible_frozen_pages,rev_all_frozen_pages = 0 AS rev_all_frozen_pages,rev_all_visible_pages = 0 AS rev_all_visible_pages
+FROM pg_stats_vacuum_tables, pg_stat_all_tables WHERE pg_stats_vacuum_tables.relname = 'vestat' and pg_stats_vacuum_tables.relid = pg_stat_all_tables.relid;
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv,vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stats_vacuum_tables, pg_stat_all_tables WHERE pg_stats_vacuum_tables.relname = 'vestat' and pg_stats_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+
+UPDATE vestat SET x = x + 1001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+SELECT vm_new_frozen_pages > :pf AS vm_new_frozen_pages,vm_new_visible_pages > :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages > :pvf AS vm_new_visible_frozen_pages,rev_all_frozen_pages > :hafp AS rev_all_frozen_pages,rev_all_visible_pages > :havp AS rev_all_visible_pages
+FROM pg_stats_vacuum_tables, pg_stat_all_tables WHERE pg_stats_vacuum_tables.relname = 'vestat' and pg_stats_vacuum_tables.relid = pg_stat_all_tables.relid;
+SELECT vm_new_frozen_pages AS pf, vm_new_visible_pages AS pv, vm_new_visible_frozen_pages AS pvf, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
+FROM pg_stats_vacuum_tables, pg_stat_all_tables WHERE pg_stats_vacuum_tables.relname = 'vestat' and pg_stats_vacuum_tables.relid = pg_stat_all_tables.relid \gset
+
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- vacuum freezed pages
+SELECT vm_new_frozen_pages = :pf AS vm_new_frozen_pages,vm_new_visible_pages = :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages = :pvf AS vm_new_visible_frozen_pages, rev_all_frozen_pages = :hafp AS rev_all_frozen_pages,rev_all_visible_pages = :havp AS rev_all_visible_pages
+FROM pg_stats_vacuum_tables, pg_stat_all_tables WHERE pg_stats_vacuum_tables.relname = 'vestat' and pg_stats_vacuum_tables.relid = pg_stat_all_tables.relid;
+
+DROP TABLE vestat CASCADE;
+
+select count(*) from pg_stats_vacuum_tables where relname = 'vestat';
+
+-- -- Now check vacuum statistics for current database
+-- SELECT dbname,
+-- db_blks_hit > 0 AS db_blks_hit,
+-- total_blks_dirtied > 0 AS total_blks_dirtied,
+-- total_blks_written > 0 AS total_blks_written,
+-- wal_records > 0 AS wal_records,
+-- wal_fpi > 0 AS wal_fpi,
+-- wal_bytes > 0 AS wal_bytes,
+-- total_time > 0 AS total_time
+-- FROM
+-- pg_stat_vacuum_database
+-- WHERE dbname = current_database();
+
+-- -- ensure pending stats are flushed
+-- SELECT pg_stat_force_next_flush();
+
+-- CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+-- INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+-- ANALYZE vestat;
+-- UPDATE vestat SET x = 10001;
+-- VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+-- \c regression_statistic_vacuum_db1;
+-- CREATE EXTENSION vacuum_statistics;
+
+-- -- Now check vacuum statistics for postgres database from another database
+-- SELECT dbname,
+-- db_blks_hit > 0 AS db_blks_hit,
+-- total_blks_dirtied > 0 AS total_blks_dirtied,
+-- total_blks_written > 0 AS total_blks_written,
+-- wal_records > 0 AS wal_records,
+-- wal_fpi > 0 AS wal_fpi,
+-- wal_bytes > 0 AS wal_bytes,
+-- total_time > 0 AS total_time
+-- FROM
+-- pg_stat_vacuum_database
+-- WHERE dbname = 'regression_statistic_vacuum_db';
+
+-- \c regression_statistic_vacuum_db
+
+-- DROP TABLE vestat CASCADE;
+
+-- \c regression_statistic_vacuum_db1;
+-- SELECT count(*)
+-- FROM pg_database d
+-- CROSS JOIN pg_stat_get_vacuum_tables(0)
+-- WHERE oid = 0; -- must be 0
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
\ No newline at end of file
diff --git a/vacuum_statistics--1.0.sql b/vacuum_statistics--1.0.sql
new file mode 100644
index 0000000..bb78f3a
--- /dev/null
+++ b/vacuum_statistics--1.0.sql
@@ -0,0 +1,191 @@
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION vacuum_statistics" to load this file. \quit
+
+-- schema: extvac
+CREATE SCHEMA IF NOT EXISTS extvac;
+
+
+-- In your extension's .sql install script
+CREATE OR REPLACE FUNCTION extvac_reset_entry(
+ dboid oid,
+ relid oid,
+ type int4
+)
+RETURNS void
+AS 'MODULE_PATHNAME', 'extvac_reset_entry'
+LANGUAGE C
+STRICT
+PARALLEL SAFE;
+
+--
+-- Show extended cumulative statistics on a vacuum operation over all tables and
+-- databases of the instance.
+-- Use Invalid Oid "0" as an input relation id to get stat on each table in a
+-- database.
+--
+CREATE FUNCTION pg_stats_get_vacuum_tables(
+ IN dboid oid,
+ IN reloid oid,
+
+ OUT relid oid,
+
+ OUT total_blks_read bigint,
+ OUT total_blks_hit bigint,
+ OUT total_blks_dirtied bigint,
+ OUT total_blks_written bigint,
+
+ OUT wal_records bigint,
+ OUT wal_fpi bigint,
+ OUT wal_bytes numeric,
+
+ OUT blk_read_time double precision,
+ OUT blk_write_time double precision,
+ OUT delay_time double precision,
+ OUT total_time double precision,
+
+ OUT wraparound_failsafe_count integer,
+
+ OUT rel_blks_read bigint,
+ OUT rel_blks_hit bigint,
+
+ OUT tuples_deleted bigint,
+ OUT pages_scanned bigint,
+ OUT pages_removed bigint,
+ OUT vm_new_frozen_pages bigint,
+ OUT vm_new_visible_pages bigint,
+ OUT vm_new_visible_frozen_pages bigint,
+ OUT tuples_frozen bigint,
+ OUT recently_dead_tuples bigint,
+ OUT index_vacuum_count bigint,
+ OUT missed_dead_pages bigint,
+ OUT missed_dead_tuples bigint
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stats_get_vacuum_tables'
+LANGUAGE C
+STRICT
+VOLATILE;
+
+GRANT EXECUTE ON FUNCTION pg_stats_get_vacuum_tables TO PUBLIC;
+
+-- Tables view
+DROP VIEW IF EXISTS pg_stats_vacuum_tables;
+
+CREATE VIEW pg_stats_vacuum_tables AS
+SELECT
+ rel.oid AS relid,
+ ns.nspname AS "schema",
+ rel.relname AS relname,
+
+ stats.total_blks_read,
+ stats.total_blks_hit,
+ stats.total_blks_dirtied,
+ stats.total_blks_written,
+
+ stats.wal_records,
+ stats.wal_fpi,
+ stats.wal_bytes,
+
+ stats.blk_read_time,
+ stats.blk_write_time,
+ stats.delay_time,
+ stats.total_time,
+
+ stats.wraparound_failsafe_count,
+
+ stats.rel_blks_read,
+ stats.rel_blks_hit,
+
+ stats.tuples_deleted,
+ stats.pages_scanned,
+ stats.pages_removed,
+ stats.vm_new_frozen_pages,
+ stats.vm_new_visible_pages,
+ stats.vm_new_visible_frozen_pages,
+ stats.tuples_frozen,
+ stats.recently_dead_tuples,
+ stats.index_vacuum_count,
+ stats.missed_dead_pages,
+ stats.missed_dead_tuples
+FROM pg_class rel
+JOIN pg_namespace ns ON ns.oid = rel.relnamespace
+CROSS JOIN LATERAL pg_stats_get_vacuum_tables(
+ (SELECT oid FROM pg_database WHERE datname = current_database()),
+ rel.oid
+) AS stats
+WHERE rel.relkind = 'r';
+
+
+CREATE FUNCTION pg_stats_get_vacuum_indexes(
+ IN dboid oid,
+ IN reloid oid,
+
+ OUT relid oid,
+
+ OUT total_blks_read bigint,
+ OUT total_blks_hit bigint,
+ OUT total_blks_dirtied bigint,
+ OUT total_blks_written bigint,
+
+ OUT wal_records bigint,
+ OUT wal_fpi bigint,
+ OUT wal_bytes numeric,
+
+ OUT blk_read_time double precision,
+ OUT blk_write_time double precision,
+ OUT delay_time double precision,
+ OUT total_time double precision,
+
+ OUT wraparound_failsafe_count integer,
+
+ OUT rel_blks_read bigint,
+ OUT rel_blks_hit bigint,
+
+ OUT tuples_deleted bigint,
+ OUT pages_deleted bigint
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stats_get_vacuum_indexes'
+LANGUAGE C
+STRICT
+VOLATILE;
+
+GRANT EXECUTE ON FUNCTION pg_stats_get_vacuum_indexes TO PUBLIC;
+
+-- Indexes view
+DROP VIEW IF EXISTS pg_stats_vacuum_indexes;
+
+CREATE VIEW pg_stats_vacuum_indexes AS
+SELECT
+ rel.oid AS relid,
+ ns.nspname AS "schema",
+ rel.relname AS relname,
+
+ stats.total_blks_read,
+ stats.total_blks_hit,
+ stats.total_blks_dirtied,
+ stats.total_blks_written,
+
+ stats.wal_records,
+ stats.wal_fpi,
+ stats.wal_bytes,
+
+ stats.blk_read_time,
+ stats.blk_write_time,
+ stats.delay_time,
+ stats.total_time,
+
+ stats.wraparound_failsafe_count,
+
+ stats.rel_blks_read,
+ stats.rel_blks_hit,
+
+ stats.tuples_deleted,
+ stats.pages_deleted
+FROM pg_class rel
+JOIN pg_namespace ns ON ns.oid = rel.relnamespace
+CROSS JOIN LATERAL pg_stats_get_vacuum_indexes(
+ (SELECT oid FROM pg_database WHERE datname = current_database()),
+ rel.oid
+) AS stats
+WHERE rel.relkind = 'i';
\ No newline at end of file
diff --git a/vacuum_statistics.c b/vacuum_statistics.c
new file mode 100644
index 0000000..bba71f1
--- /dev/null
+++ b/vacuum_statistics.c
@@ -0,0 +1,636 @@
+#include "postgres.h"
+
+#include "pgstat.h"
+#include "fmgr.h"
+#include "miscadmin.h"
+#include "storage/ipc.h"
+#include "storage/lwlock.h"
+#include "storage/shmem.h"
+#include "utils/guc.h"
+#include "utils/hsearch.h"
+#include "utils/memutils.h"
+#include "common/hashfn.h"
+#include "storage/spin.h"
+#include "utils/fmgrprotos.h"
+#include "funcapi.h"
+#include "catalog/objectaccess.h"
+#include "catalog/pg_class.h"
+#include "utils/lsyscache.h"
+
+/* Public module hooks */
+void _PG_init(void);
+#ifdef PG_MODULE_MAGIC
+PG_MODULE_MAGIC;
+#endif
+
+#define SJ_NODENAME "VacuumStatistics"
+
+/* --- GUCs --- */
+static bool evc_enabled = true;
+static int evc_max_entries = 10000;
+
+/* --- Hook chaining --- */
+static shmem_request_hook_type prev_shmem_request_hook = NULL;
+static shmem_startup_hook_type prev_shmem_startup_hook = NULL;
+static set_report_vacuum_hook_type prev_report_vacuum_hook = NULL;
+static object_access_hook_type prev_object_access_hook;
+
+/* --- Names --- */
+#define EVC_STATE_NAME "extvac_shared_state"
+#define EVC_HASH_NAME "extvac_hash"
+#define EVC_TRANCHE_NAME "extvac_tranche"
+
+/* --- Forward declarations --- */
+static Size evc_memsize(void);
+static void evc_shmem_request(void);
+static void evc_shmem_startup(void);
+static void evc_drop_access_hook(ObjectAccessType access,
+ Oid classId,
+ Oid objectId,
+ int subId,
+ void *arg);
+
+/* ---- Key / Entry ---- */
+
+typedef struct ExtVacKey
+{
+ Oid dboid; /* InvalidOid for shared catalogs */
+ Oid relid; /* relation OID */
+ uint8 type; /* ExtVacReportType (heap / index / …) */
+} ExtVacKey;
+
+typedef struct ExtVacEntry
+{
+ /* hash key MUST be first when using HASH_BLOBS */
+ ExtVacKey key;
+
+ /* stats payload */
+ PgStat_VacuumRelationCounts stats;
+
+ /* metadata */
+ TimestampTz first_seen;
+ TimestampTz stats_reset;
+} ExtVacEntry;
+
+typedef struct ExtVacSharedState
+{
+ LWLock lock;
+ LWLock evc_lock_hash;
+
+ dsa_handle evc_dsa_handler;
+ bool evc_changed;
+} ExtVacSharedState;
+
+static HTAB *evc_hash = NULL;
+static ExtVacSharedState *evc = NULL;
+
+static void
+pgstat_report_vacuum_extstats(Oid tableoid, bool shared,
+ PgStat_VacuumRelationCounts *params);
+
+#define ACCUMULATE_FIELD(field) dst->field += src->field;
+
+#define ACCUMULATE_SUBFIELD(substruct, field) \
+ (dst->substruct.field += src->substruct.field)
+
+static inline void
+pgstat_accumulate_common(PgStat_CommonCounts *dst, const PgStat_CommonCounts *src)
+{
+ ACCUMULATE_FIELD(total_blks_read);
+ ACCUMULATE_FIELD(total_blks_hit);
+ ACCUMULATE_FIELD(total_blks_dirtied);
+ ACCUMULATE_FIELD(total_blks_written);
+
+ ACCUMULATE_FIELD(blks_fetched);
+ ACCUMULATE_FIELD(blks_hit);
+
+ ACCUMULATE_FIELD(wal_records);
+ ACCUMULATE_FIELD(wal_fpi);
+ ACCUMULATE_FIELD(wal_bytes);
+
+ ACCUMULATE_FIELD(blk_read_time);
+ ACCUMULATE_FIELD(blk_write_time);
+ ACCUMULATE_FIELD(delay_time);
+ ACCUMULATE_FIELD(total_time);
+
+ ACCUMULATE_FIELD(wraparound_failsafe_count);
+}
+
+static inline void
+pgstat_accumulate_extvac_stats_relations(PgStat_VacuumRelationCounts *dst, const PgStat_VacuumRelationCounts *src)
+{
+ if (dst->type == PGSTAT_EXTVAC_INVALID)
+ dst->type = src->type;
+
+ //Assert(src->type != PGSTAT_EXTVAC_INVALID && src->type != PGSTAT_EXTVAC_DB && src->type == dst->type);
+
+ pgstat_accumulate_common(&dst->common, &src->common);
+
+ ACCUMULATE_SUBFIELD(common, blks_fetched);
+ ACCUMULATE_SUBFIELD(common, blks_hit);
+
+ if (dst->type == PGSTAT_EXTVAC_TABLE)
+ {
+ ACCUMULATE_SUBFIELD(table, tuples_deleted);
+ ACCUMULATE_SUBFIELD(table, pages_scanned);
+ ACCUMULATE_SUBFIELD(table, pages_removed);
+ ACCUMULATE_SUBFIELD(table, vm_new_frozen_pages);
+ ACCUMULATE_SUBFIELD(table, vm_new_visible_pages);
+ ACCUMULATE_SUBFIELD(table, vm_new_visible_frozen_pages);
+ ACCUMULATE_SUBFIELD(table, tuples_frozen);
+ ACCUMULATE_SUBFIELD(table, recently_dead_tuples);
+ ACCUMULATE_SUBFIELD(table, index_vacuum_count);
+ ACCUMULATE_SUBFIELD(table, missed_dead_pages);
+ ACCUMULATE_SUBFIELD(table, missed_dead_tuples);
+ }
+ else if (dst->type == PGSTAT_EXTVAC_INDEX)
+ {
+ ACCUMULATE_SUBFIELD(table, tuples_deleted);
+ ACCUMULATE_SUBFIELD(index, pages_deleted);
+ }
+}
+
+void
+_PG_init(void)
+{
+ /*
+ * In order to create our shared memory area, we have to be loaded via
+ * shared_preload_libraries. If not, fall out without hooking into any of
+ * the main system. (We don't throw error here because it seems useful to
+ * allow the vacuum_statistics functions to be created even when the
+ * module isn't active. The functions must protect themselves against
+ * being called then, however.)
+ */
+ if (!process_shared_preload_libraries_in_progress)
+ return;
+
+
+ /* GUCs */
+ DefineCustomBoolVariable("vacuum_statistics.enabled",
+ "Enable extension vacuum statistics collection.",
+ NULL,
+ &evc_enabled,
+ true,
+ PGC_SUSET, 0,
+ NULL, NULL, NULL);
+
+ DefineCustomIntVariable("vacuum_statistics.max_entries",
+ "Maximum number of hash table entries.",
+ NULL,
+ &evc_max_entries,
+ 10000, /* default */
+ 100, /* min */
+ INT_MAX / 2, /* max */
+ PGC_POSTMASTER, 0,
+ NULL, NULL, NULL);
+
+ MarkGUCPrefixReserved(SJ_NODENAME);
+
+ /* Chain shmem hooks */
+ prev_shmem_request_hook = shmem_request_hook;
+ shmem_request_hook = evc_shmem_request;
+
+ prev_shmem_startup_hook = shmem_startup_hook;
+ shmem_startup_hook = evc_shmem_startup;
+
+ /* If you piggyback on pgstat vacuum report hook, chain it here */
+ prev_report_vacuum_hook = set_report_vacuum_hook;
+ set_report_vacuum_hook = pgstat_report_vacuum_extstats;
+
+ prev_object_access_hook = object_access_hook;
+ object_access_hook = evc_drop_access_hook;
+}
+
+static Size
+evc_memsize(void)
+{
+ Size sz = 0;
+
+ /* shared state header */
+ sz = MAXALIGN(sizeof(ExtVacSharedState));
+
+ /* dynahash buckets + entries */
+ sz = add_size(sz,
+ hash_estimate_size(evc_max_entries, sizeof(ExtVacEntry)));
+
+ return sz;
+}
+
+static void
+evc_shmem_request(void)
+{
+ if (prev_shmem_request_hook)
+ prev_shmem_request_hook();
+
+ /* Ask postmaster for our memory slice */
+ RequestAddinShmemSpace(evc_memsize());
+}
+
+static void
+evc_shmem_startup(void)
+{
+ HASHCTL ctl;
+ bool found;
+
+ if (prev_shmem_startup_hook)
+ prev_shmem_startup_hook();
+
+ evc = NULL;
+ evc_hash = NULL;
+
+ LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
+
+ /* Shared state header */
+ evc = ShmemInitStruct(EVC_STATE_NAME,
+ sizeof(ExtVacSharedState),
+ &found);
+
+ /* First time only: resolve our tranche root (optional to store) */
+ if (!found)
+ {
+ evc->evc_dsa_handler = DSM_HANDLE_INVALID;
+
+ evc->evc_changed = false;
+
+ LWLockInitialize(&evc->lock, LWLockNewTrancheId());
+ LWLockInitialize(&evc->evc_lock_hash, LWLockNewTrancheId());
+ }
+
+ /* dynahash parameters */
+ ctl.keysize = sizeof(ExtVacKey);
+ ctl.entrysize = sizeof(ExtVacEntry);
+ evc_hash = ShmemInitHash(EVC_HASH_NAME, evc_max_entries, evc_max_entries,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+
+ LWLockRelease(AddinShmemInitLock);
+
+ LWLockRegisterTranche(evc->lock.tranche, EVC_TRANCHE_NAME);
+ LWLockRegisterTranche(evc->evc_lock_hash.tranche, EVC_TRANCHE_NAME);
+
+ //if (!IsUnderPostmaster && !found)
+}
+
+static ExtVacEntry *
+evc_store(Oid dboid, Oid relid, bool shared, uint8 type,
+ PgStat_VacuumRelationCounts *counts)
+{
+ bool tblOverflow;
+ HASHACTION action;
+ ExtVacEntry *e;
+ bool found = false;
+ ExtVacKey key = { .dboid = dboid, .relid = relid, .type = type };
+
+ if (!evc_enabled || evc_hash == NULL)
+ return NULL;
+
+ if (shared)
+ dboid = InvalidOid;
+
+ Assert(!LWLockHeldByMe(&evc->evc_lock_hash));
+
+ LWLockAcquire(&evc->evc_lock_hash, LW_EXCLUSIVE);
+
+ tblOverflow = hash_get_num_entries(evc_hash) < evc_max_entries ? false : true;
+
+ if (!tblOverflow)
+ action = tblOverflow ? HASH_FIND : HASH_ENTER;
+
+ e = (ExtVacEntry *) hash_search(evc_hash, &key, action, &found);
+
+ if (!found)
+ {
+ if (action == HASH_FIND)
+ {
+ /*
+ * Hash table is full. To avoid possible problems - don't try to add
+ * more, just exit
+ */
+ LWLockRelease(&evc->evc_lock_hash);
+ ereport(LOG,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("[Vacuum Statistics] Data storage is full. No more data can be added."),
+ errhint("Increase value of evc_max_entries on restart of the instance")));
+ return NULL;
+ }
+
+ memset(&e->stats, 0, sizeof(e->stats));
+ e->stats.type = type;
+ e->first_seen = GetCurrentTimestamp();
+ e->stats_reset = e->first_seen;
+ }
+
+ pgstat_accumulate_extvac_stats_relations(&e->stats, counts);
+
+ evc->evc_changed = true;
+ LWLockRelease(&evc->evc_lock_hash);
+
+ return e;
+}
+
+static void
+pgstat_report_vacuum_extstats(Oid tableoid, bool shared,
+ PgStat_VacuumRelationCounts *params)
+{
+ /* Call ours */
+ Oid dboid = shared ? InvalidOid : MyDatabaseId;
+ evc_store(dboid, tableoid, shared, params->type, params);
+
+ /* Chain to previous if any */
+ if (prev_report_vacuum_hook)
+ prev_report_vacuum_hook(tableoid, shared, params);
+}
+
+static void
+evc_entry_reset(ExtVacEntry *e)
+{
+ Assert(e != NULL);
+
+ LWLockAcquire(&evc->evc_lock_hash, LW_EXCLUSIVE);
+
+ /* wipe stats, but preserve type and key metadata */
+ memset(&e->stats, 0, sizeof(e->stats));
+ e->stats.type = e->key.type; /* relatch type */
+ e->stats_reset = GetCurrentTimestamp();
+
+ LWLockRelease(&evc->evc_lock_hash);
+}
+
+static void
+evc_reset_by_relid(Oid dboid, Oid relid, uint8 type)
+{
+ ExtVacKey key;
+ ExtVacEntry *e;
+
+ if (!evc || !evc_hash)
+ return;
+
+ key.dboid = dboid;
+ key.relid = relid;
+ key.type = type;
+
+ e = (ExtVacEntry *) hash_search(evc_hash, &key, HASH_FIND, NULL);
+ if (e)
+ evc_entry_reset(e);
+}
+
+PG_FUNCTION_INFO_V1(extvac_reset_entry);
+
+Datum
+extvac_reset_entry(PG_FUNCTION_ARGS)
+{
+ Oid dboid = PG_GETARG_OID(0);
+ Oid relid = PG_GETARG_OID(1);
+ int32 type = PG_GETARG_INT32(2);
+
+ evc_reset_by_relid(dboid, relid, (uint8) type);
+
+ PG_RETURN_VOID();
+}
+
+/*
+ * Object access hook
+ */
+static void
+evc_drop_access_hook(ObjectAccessType access,
+ Oid classId,
+ Oid objectId,
+ int subId,
+ void *arg)
+{
+ if (prev_object_access_hook)
+ (*prev_object_access_hook) (access, classId, objectId, subId, arg);
+
+ if (access == OAT_DROP)
+ {
+ char relkind = get_rel_relkind(objectId);
+
+ if(classId == RelationRelationId && subId == 0)
+ {
+ if(relkind == RELKIND_RELATION)
+ evc_reset_by_relid(MyDatabaseId, objectId, PGSTAT_EXTVAC_TABLE);
+ else if (relkind == RELKIND_INDEX)
+ evc_reset_by_relid(MyDatabaseId, objectId, PGSTAT_EXTVAC_INDEX);
+ }
+ }
+}
+
+
+/* Number of output arguments (columns) of vacuum stats for various API versions */
+#define EXTVAC_COMMON_STAT_COLS 12 /* maximum of above */
+
+static void
+tuplestore_put_common(PgStat_CommonCounts *vacuum_ext,
+ Datum *values, bool *nulls, int *i)
+{
+ char buf[256];
+ const int base = *i;
+
+ values[(*i)++] = Int64GetDatum(vacuum_ext->total_blks_read);
+ values[(*i)++] = Int64GetDatum(vacuum_ext->total_blks_hit);
+ values[(*i)++] = Int64GetDatum(vacuum_ext->total_blks_dirtied);
+ values[(*i)++] = Int64GetDatum(vacuum_ext->total_blks_written);
+ values[(*i)++] = Int64GetDatum(vacuum_ext->wal_records);
+ values[(*i)++] = Int64GetDatum(vacuum_ext->wal_fpi);
+
+ /* Convert to numeric, like pg_stat_statements */
+ snprintf(buf, sizeof buf, UINT64_FORMAT, vacuum_ext->wal_bytes);
+ values[(*i)++] = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+
+ values[(*i)++] = Float8GetDatum(vacuum_ext->blk_read_time);
+ values[(*i)++] = Float8GetDatum(vacuum_ext->blk_write_time);
+ values[(*i)++] = Float8GetDatum(vacuum_ext->delay_time);
+ values[(*i)++] = Float8GetDatum(vacuum_ext->total_time);
+ values[(*i)++] = Int32GetDatum(vacuum_ext->wraparound_failsafe_count);
+
+ /* If you meant 12, fix the constant. Otherwise add the missing field. */
+ Assert((*i - base) == EXTVAC_COMMON_STAT_COLS);
+}
+
+#define EXTVAC_HEAP_STAT_COLS 26
+#define EXTVAC_IDX_STAT_COLS 17
+#define EXTVAC_MAX_STAT_COLS Max(EXTVAC_HEAP_STAT_COLS, EXTVAC_IDX_STAT_COLS)
+
+static void
+tuplestore_put_for_relation(Oid relid, Tuplestorestate *tupstore,
+ TupleDesc tupdesc, PgStat_VacuumRelationCounts *vacuum_ext)
+{
+ Datum values[EXTVAC_MAX_STAT_COLS];
+ bool nulls[EXTVAC_MAX_STAT_COLS];
+ int i = 0;
+ memset(nulls, 0, sizeof(nulls));
+
+ values[i++] = ObjectIdGetDatum(relid);
+
+ tuplestore_put_common(&vacuum_ext->common,
+ values, nulls, &i);
+
+ values[i++] = Int64GetDatum(vacuum_ext->common.blks_fetched -
+ vacuum_ext->common.blks_hit);
+ values[i++] = Int64GetDatum(vacuum_ext->common.blks_hit);
+
+ if (vacuum_ext->type == PGSTAT_EXTVAC_TABLE)
+ {
+ values[i++] = Int64GetDatum(vacuum_ext->table.tuples_deleted);
+ values[i++] = Int64GetDatum(vacuum_ext->table.pages_scanned);
+ values[i++] = Int64GetDatum(vacuum_ext->table.pages_removed);
+ values[i++] = Int64GetDatum(vacuum_ext->table.vm_new_frozen_pages);
+ values[i++] = Int64GetDatum(vacuum_ext->table.vm_new_visible_pages);
+ values[i++] = Int64GetDatum(vacuum_ext->table.vm_new_visible_frozen_pages);
+ values[i++] = Int64GetDatum(vacuum_ext->table.tuples_frozen);
+ values[i++] = Int64GetDatum(vacuum_ext->table.recently_dead_tuples);
+ values[i++] = Int64GetDatum(vacuum_ext->table.index_vacuum_count);
+ values[i++] = Int64GetDatum(vacuum_ext->table.missed_dead_pages);
+ values[i++] = Int64GetDatum(vacuum_ext->table.missed_dead_tuples);
+ }
+ else if (vacuum_ext->type == PGSTAT_EXTVAC_INDEX)
+ {
+ values[i++] = Int64GetDatum(vacuum_ext->index.tuples_deleted);
+ values[i++] = Int64GetDatum(vacuum_ext->index.pages_deleted);
+ }
+
+ Assert(i == ((vacuum_ext->type == PGSTAT_EXTVAC_TABLE) ? EXTVAC_HEAP_STAT_COLS : EXTVAC_IDX_STAT_COLS));
+
+ tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+}
+
+/*
+ * Get the vacuum statistics for the heap tables or indexes.
+ * See comment to pgpro_stats_statements() about SQL API.
+ */
+static Datum
+pg_stats_vacuum(FunctionCallInfo fcinfo, ExtVacReportType type)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+ Tuplestorestate *tupstore;
+ TupleDesc tupdesc;
+ Oid dbid = PG_GETARG_OID(0);
+
+ /* Check if caller supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("vacuum statistics: set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("vacuum statistics: materialize mode required, but it is not allowed in this context")));
+
+ /* Switch to long-lived context to create the returned data structures */
+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "vacuum statistics: return type must be a row type");
+
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ if (type == PGSTAT_EXTVAC_INDEX || type == PGSTAT_EXTVAC_TABLE)
+ {
+ Oid relid = PG_GETARG_OID(1);
+ ExtVacEntry *vacuum_ext;
+
+ /* Load table statistics for specified database. */
+
+ if (OidIsValid(relid))
+ {
+ ExtVacKey key;
+
+ if (!evc || !evc_hash)
+ return (Datum) 0;
+
+ key.dboid = dbid;
+ key.relid = relid;
+ key.type = type;
+
+ LWLockAcquire(&evc->evc_lock_hash, LW_SHARED);
+
+ vacuum_ext = (ExtVacEntry *) hash_search(evc_hash, &key, HASH_FIND, NULL);
+
+ if (vacuum_ext == NULL || vacuum_ext->stats.type != type)
+ {
+ /* Table don't exists or isn't an heap relation. */
+ LWLockRelease(&evc->evc_lock_hash);
+ return (Datum) 0;
+ }
+
+ LWLockRelease(&evc->evc_lock_hash);
+
+ tuplestore_put_for_relation(relid, tupstore, tupdesc, &vacuum_ext->stats);
+ }
+ }
+ // else if (type == PGSTAT_EXTVAC_DB)
+ // {
+ // PgStat_CommonCounts *vacuum_ext;
+
+ // vacuum_ext = fetch_dbstat_dbentry(dbid);
+
+ // if (vacuum_ext == NULL)
+ // /* Table doesn't exist or isn't a heap relation */
+ // PG_RETURN_NULL();
+
+ // Datum values[EXTVAC_COMMON_STAT_COLS];
+ // bool nulls[EXTVAC_COMMON_STAT_COLS];
+ // int i = 0;
+
+ // memset(nulls, 0, EXTVAC_COMMON_STAT_COLS * sizeof(bool));
+
+ // values[i++] = ObjectIdGetDatum(dbid);
+
+ // tuplestore_put_common(tupstore, tupdesc, vacuum_ext,
+ // &values, &nulls, &i);
+
+ // tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+
+ // return (Datum) 0;
+ // }
+
+ return (Datum) 0;
+}
+
+PG_FUNCTION_INFO_V1(pg_stats_get_vacuum_tables);
+PG_FUNCTION_INFO_V1(pg_stats_get_vacuum_indexes);
+PG_FUNCTION_INFO_V1(pg_stats_get_vacuum_database);
+
+/*
+ * Get the vacuum statistics for the heap tables.
+ */
+Datum
+pg_stats_get_vacuum_tables(PG_FUNCTION_ARGS)
+{
+ return pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_TABLE);
+
+ PG_RETURN_NULL();
+}
+
+/*
+ * Get the vacuum statistics for the indexes.
+*/
+Datum
+pg_stats_get_vacuum_indexes(PG_FUNCTION_ARGS)
+{
+ return pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_INDEX);
+
+ PG_RETURN_NULL();
+}
+
+/*
+ * Get the vacuum statistics for the databases.
+ */
+Datum
+pg_stats_get_vacuum_database(PG_FUNCTION_ARGS)
+{
+ return pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_DB);
+
+ PG_RETURN_NULL();
+}
\ No newline at end of file
diff --git a/vacuum_statistics.control b/vacuum_statistics.control
new file mode 100644
index 0000000..1c72754
--- /dev/null
+++ b/vacuum_statistics.control
@@ -0,0 +1,4 @@
+comment = 'vacuum statistics'
+default_version = '1.0'
+module_pathname = '$libdir/vacuum_statistics'
+relocatable = true
\ No newline at end of file
--
2.34.1
view thread (53+ 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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Vacuum statistics
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