0001-Create-vacuum-extension-statistics.patch
text/x-patch
Filename: 0001-Create-vacuum-extension-statistics.patch
Type: text/x-patch
Part: 1
Message:
Re: Vacuum statistics
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0001
Subject: Create vacuum extension statistics
| File | + | − |
|---|---|---|
| expected/vacuum-extending-in-repetable-read.out | 53 | 0 |
| expected/vacuum_index_statistics.out | 183 | 0 |
| expected/vacuum_tables_and_db_statistics.out | 296 | 0 |
| Makefile | 21 | 0 |
| spec/vacuum-extending-in-repetable-read.spec | 173 | 0 |
| sql/vacuum_index_statistics.sql | 138 | 0 |
| sql/vacuum_tables_and_db_statistics.sql | 224 | 0 |
| vacuum_statistics--1.0.sql | 191 | 0 |
| vacuum_statistics.c | 636 | 0 |
| vacuum_statistics.control | 4 | 0 |
From 9b4dd2e845cb8fdc9c1cee09da8665da2bccfd42 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybakina@postgrespro.ru>
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