Hi all,
in the scope of improving the autovacuum algorithm, this patch proposes
to maintain a histogram of *smallest prunable xid per page* for each
relation. It allows to estimate the number of pages that would
be pruned by vacuum for a given cutoff.
The *smallest prunable xid per page* is prune_xid in each page
header. The value of prune_xid is not always consistent with the
contents of the page, but this patch does not try to improve on this. We
suppose that the current accuracy of prune_xid is good enough.
The histogram lives in PgStat_StatTabEntry, and so it makes use of
pgstat machinery. In particular, there is a per-backend transient
histogram that is merged into the main shared histogram using
pgstat_report_vacuum() or pgstat_relation_flush_cb().
This histogram uses a fixed size data structure but its bounds are
dynamic. Over time, some bins are merged to give space for a fresh new
bin that covers the ever increasing xids. The maintenance of the
histogram bounds is a relatively expensive operation, whereas a simple
update of the bins count is very efficient. So, we may arrange to do the
expensive operation out of the hot paths.
In order to collect data, we keep track of prune_xid in (1) access heap
prune, (2) vacuum and (3) heap_{delete,update,insert}. Adding stuff in
(3) might raise eyebrows since it is a cost per tuple. However, we only
do something if the page prune_xid changes and, finally, it is virtually
a cost per page.
You can give a try using pgbench like this.
``` shell
pgbench -i
pgbench -T 30
```
``` psql
\set tt pgbench_accounts
\i src/test/regress/sql/prune_xid_aux_check.sql
```
The functions pg_stat_get_prune_xid_{freqs,bounds} return the prune_xid
histogram for a given relation. In the second part of the script above,
we use *pageinspect* to check the correctness of the computed
histogram. In my tests, not always, a small annoying difference shows
up. Actually, it is really annoying, I'm struggling with it and I hope
someone helps me to find the missing bits.
Regarding performance, I've not observed a sensible difference using
pgbench but I certainly don't have a good setup for benchmarking. I
could observe, using *perf*, that the function
pgstat_update_relation_prune_xid_histogram(), which collects data in
almost all cases, has a overall time much lower than
pgstat_count_heap_update() for example. I've looked at perf data using
pgbench and using huge batch updates.
The initial version of this work proposed a histogram of dead tuples
xmax for each relation. After some suggestions in the discord hackers
channel, I've understood that a page wise info can be more useful for
the autovacuum planning. There is more detailed information in the file
patch-notes.{org,md} and, of course, in the code itself.
The attached patch is based on REL_18_BETA1. Sorry for not sending a
complete, rounded patch. But I feel that I really need some feedback at
this point. Above all, I'd like to know if someone is interested in
using this information to improve the autovacuum algorithm. Otherwise,
we cannot justify this patch.
Looking forward to any kind of feedback.
Best Regards,
Renan Fonseca
>From 7ef019112a58294e4e348b7782e79da31a0d68d7 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Mon, 19 May 2025 23:20:53 +0200
Subject: [PATCH v1 01/12] prune_xid-hist: patch description
---
patch-notes.md | 107 ++++++++++++++++++++++++++++++++++++++++++++++++
patch-notes.org | 52 +++++++++++++++++++++++
2 files changed, 159 insertions(+)
create mode 100644 patch-notes.md
create mode 100644 patch-notes.org
diff --git a/patch-notes.md b/patch-notes.md
new file mode 100644
index 00000000000..3017babf688
--- /dev/null
+++ b/patch-notes.md
@@ -0,0 +1,107 @@
+
+# Table of Contents
+
+1. [Scope](#org39b3746)
+ 1. [Current State](#org547d550)
+ 2. [Proposed Solution](#orgb8c7698)
+ 1. [Histogram bounds](#org63620ef)
+ 2. [Histogram freqs](#org8edf572)
+2. [Details](#orga6d053d)
+ 1. [Non transactional nature of pd\_prune\_xid](#org65c9853)
+ 2. [Moving from per-backend stats to shared stats](#org1d8f9b9)
+ 3. [Histogram balance](#orgbfd44a2)
+ 4. [Histogram merging](#org62985bc)
+ 5. [Final Solution](#orgdcc9d1d)
+
+
+
+<a id="org39b3746"></a>
+
+# Scope
+
+We want to estimate how many pages will be affected by a vacuum operation.
+
+
+<a id="org547d550"></a>
+
+## Current State
+
+Vacuum can remove dead tuples whose xid (xmax) is smaller than **oldest\_nonremovable**, which keeps moving forward over time.
+
+Every page has a header field **pd\_prune\_xid** that contains the oldest xid among the dead tuples in the page.
+
+
+<a id="orgb8c7698"></a>
+
+## Proposed Solution
+
+We initialize and maintain a histogram of pd\_prune\_xid's whose bounds evolve dynamically in pace with **oldest\_nonremovable**. There is one histogram per relation and it lives in pgstats catalog.
+
+
+<a id="org63620ef"></a>
+
+### Histogram bounds
+
+The highest bin is the only one whose upper bound changes at the moment of increasing/decreasing the counter. If the pd\_prune\_xid is higher then the highest upper bound, we update it. On the other extreme, we have an implicit open lower bound since we don't track the lower bounds.
+
+All intermediate bounds are modified at **histogram balance** time. At this moment, two consecutive bins can be merged, shifting remaining bins to the left, creating a fresh highest bin. See futher for the exact conditions on which the merge happens.
+
+
+<a id="org8edf572"></a>
+
+### Histogram freqs
+
+Whenever a pd\_prune\_xid is modified, keep track of old and new values, reporting to pgstats. Then, increase the bin for the new value and decrease the bin for the old value.
+
+
+<a id="orga6d053d"></a>
+
+# Details
+
+
+<a id="org65c9853"></a>
+
+## Non transactional nature of pd\_prune\_xid
+
+pd\_prune\_xid is updated non-transactionally. It implies that it may not be consistent with the actual lowest prunable tuple in the page. Currently, postgres assumes that the transaction will be commited to compute the value of pd\_prune\_xid. In this initial version, we don't intend to improve on this. The goal is to provide a fully consistent histogram regarding existing pd\_prune\_xid values.
+
+
+<a id="org1d8f9b9"></a>
+
+## Moving from per-backend stats to shared stats
+
+Being non transactional makes for a more direct flow from data collection up to shared pgstats. We collect data into an transient histogram in the per-backend pgstats. Then, on pg\_relation\_flush we transfer data from the transient histogram to the final histogram in shared pgstats.
+
+It is also at this moment that we balance the shared histogram, eventually merging some bins.
+
+
+<a id="orgbfd44a2"></a>
+
+## Histogram balance
+
+We balance the shared histogram in two situations: (1) the counter of the highest bin is too high; (2) **oldest\_nonremovable** is higher then the second upper bound.
+
+The transient histograms are always initialized with the shared histogram bounds. During the lifetime of a transient histogram, the shared histogram may be balanced. So, at the moment of merging back the transient into the shared histogram we need to be careful to not introduce additional inconsistencies.
+
+
+<a id="org62985bc"></a>
+
+## Histogram merging
+
+When moving freqs from the transient into the shared histogram there are two special cases to pay attention: (1) two or more intermediate bins in the shared histogram were merged into one bin; (2) the highest bin was split into two or more bins.
+
+Case (1) is not really a problem. We are fitting high resolution data into a lower resolution grid.
+
+The situation is quite the opposite in (2): the highest bin from the transient histogram covers one or more bins in the shared histogram. There is no way make this operation without loosing information. But if we do it carefully, we can guarantee some useful properties. In particular, we want to use the shared histogram to answer "How many page can be pruned now?". And we will be able to say "**At least** N pages can be pruned now."
+
+In order to guarantee this lower bound, at the moment of moving the freqs from one bin into two or more bins, we need to choose the highest overlapping bin in the target histogram. This means that we may consider some pd\_prune\_xid's to be higher than their actual value, but never the opposite. The situation is a little bit trickier because we can have negative counts in the transient histograms. In these cases, we should consider the lowest overlapping bin in the target histogram.
+
+So, in order to handle positive and negative counts in the transient histogram, we have an additional counter for the negative counts of the highest bin. We don't need to handle separetely the intermediate bin freqs.
+
+
+<a id="orgdcc9d1d"></a>
+
+## Final Solution
+
+The user can simply query pgstats to have an updated view of how many pages are prunable now and later.
+
diff --git a/patch-notes.org b/patch-notes.org
new file mode 100644
index 00000000000..1db311105a9
--- /dev/null
+++ b/patch-notes.org
@@ -0,0 +1,52 @@
+#+OPTIONS: ^:nil :toc
+
+* Scope
+We want to estimate how many pages will be affected by a vacuum operation.
+
+** Current State
+
+Vacuum can remove dead tuples whose xid (xmax) is smaller than *oldest_nonremovable*, which keeps moving forward over time.
+
+Every page has a header field *pd_prune_xid* that contains the oldest xid among the dead tuples in the page.
+
+** Proposed Solution
+
+We initialize and maintain a histogram of pd_prune_xid's whose bounds evolve dynamically in pace with *oldest_nonremovable*. There is one histogram per relation and it lives in pgstats catalog.
+
+*** Histogram bounds
+The highest bin is the only one whose upper bound changes at the moment of increasing/decreasing the counter. If the pd_prune_xid is higher then the highest upper bound, we update it. On the other extreme, we have an implicit open lower bound since we don't track the lower bounds.
+
+All intermediate bounds are modified at *histogram balance* time. At this moment, two consecutive bins can be merged, shifting remaining bins to the left, creating a fresh highest bin. See futher for the exact conditions on which the merge happens.
+
+*** Histogram freqs
+Whenever a pd_prune_xid is modified, keep track of old and new values, reporting to pgstats. Then, increase the bin for the new value and decrease the bin for the old value.
+
+* Details
+
+** Non transactional nature of pd_prune_xid
+pd_prune_xid is updated non-transactionally. It implies that it may not be consistent with the actual lowest prunable tuple in the page. Currently, postgres assumes that the transaction will be commited to compute the value of pd_prune_xid. In this initial version, we don't intend to improve on this. The goal is to provide a fully consistent histogram regarding existing pd_prune_xid values.
+
+** Moving from per-backend stats to shared stats
+Being non transactional makes for a more direct flow from data collection up to shared pgstats. We collect data into an transient histogram in the per-backend pgstats. Then, on pg_relation_flush we transfer data from the transient histogram to the final histogram in shared pgstats.
+
+It is also at this moment that we balance the shared histogram, eventually merging some bins.
+
+** Histogram balance
+We balance the shared histogram in two situations: (1) the counter of the highest bin is too high; (2) *oldest_nonremovable* is higher then the second upper bound.
+
+The transient histograms are always initialized with the shared histogram bounds. During the lifetime of a transient histogram, the shared histogram may be balanced. So, at the moment of merging back the transient into the shared histogram we need to be careful to not introduce additional inconsistencies.
+
+** Histogram merging
+When moving freqs from the transient into the shared histogram there are two special cases to pay attention: (1) two or more intermediate bins in the shared histogram were merged into one bin; (2) the highest bin was split into two or more bins.
+
+Case (1) is not really a problem. We are fitting high resolution data into a lower resolution grid.
+
+The situation is quite the opposite in (2): the highest bin from the transient histogram covers one or more bins in the shared histogram. There is no way make this operation without loosing information. But if we do it carefully, we can guarantee some useful properties. In particular, we want to use the shared histogram to answer "How many page can be pruned now?". And we will be able to say "*At least* N pages can be pruned now."
+
+In order to guarantee this lower bound, at the moment of moving the freqs from one bin into two or more bins, we need to choose the highest overlapping bin in the target histogram. This means that we may consider some pd_prune_xid's to be higher than their actual value, but never the opposite. The situation is a little bit trickier because we can have negative counts in the transient histograms. In these cases, we should consider the lowest overlapping bin in the target histogram.
+
+So, in order to handle positive and negative counts in the transient histogram, we have an additional counter for the negative counts of the highest bin. We don't need to handle separetely the intermediate bin freqs.
+
+
+** Final Solution
+The user can simply query pgstats to have an updated view of how many pages are prunable now and later.
--
2.47.0
>From 8de48b53ff1779a30c8ea28f2fa57f702e2ceab2 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Tue, 20 May 2025 13:35:39 +0200
Subject: [PATCH v1 02/12] prune_xid-hist: create data structures
---
src/backend/access/heap/vacuumlazy.c | 1 +
src/include/pgstat.h | 21 ++++++++++++++++++++-
src/tools/pgindent/typedefs.list | 2 ++
3 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index f28326bad09..8565f6dd215 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -355,6 +355,7 @@ typedef struct LVRelState
int64 live_tuples; /* # live tuples remaining */
int64 recently_dead_tuples; /* # dead, but not yet removable */
int64 missed_dead_tuples; /* # removable, but not removed */
+ PruneXidHistogramTransient prune_xid_hist;
/* State maintained by heap_vac_scan_next_block() */
BlockNumber current_block; /* last block returned */
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 378f2f2c2ba..d48f4742beb 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -111,6 +111,21 @@ typedef struct PgStat_BackendSubEntry
PgStat_Counter conflict_count[CONFLICT_NUM_TYPES];
} PgStat_BackendSubEntry;
+#define PRUNE_XID_HIST_NBINS 5
+
+typedef struct PruneXidHistogram
+{
+ PgStat_Counter freqs[PRUNE_XID_HIST_NBINS];
+ TransactionId bounds[PRUNE_XID_HIST_NBINS];
+} PruneXidHistogram;
+
+typedef struct PruneXidHistogramTransient
+{
+ PgStat_Counter freqs[PRUNE_XID_HIST_NBINS];
+ PgStat_Counter neg_freq;
+ TransactionId bounds[PRUNE_XID_HIST_NBINS];
+} PruneXidHistogramTransient;
+
/* ----------
* PgStat_TableCounts The actual per-table counts kept by a backend
*
@@ -153,6 +168,8 @@ typedef struct PgStat_TableCounts
PgStat_Counter blocks_fetched;
PgStat_Counter blocks_hit;
+
+ PruneXidHistogramTransient prune_xid_hist;
} PgStat_TableCounts;
/* ----------
@@ -211,7 +228,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB7
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB8
typedef struct PgStat_ArchiverStats
{
@@ -437,6 +454,8 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter mod_since_analyze;
PgStat_Counter ins_since_vacuum;
+ PruneXidHistogram prune_xid_hist;
+
PgStat_Counter blocks_fetched;
PgStat_Counter blocks_hit;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e5879e00dff..02406a0776e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2334,6 +2334,8 @@ PruneFreezeResult
PruneReason
PruneState
PruneStepResult
+PruneXidHistogram
+PruneXidHistogramTransient
PsqlScanCallbacks
PsqlScanQuoteType
PsqlScanResult
--
2.47.0
>From e69ef7c303d410e53a410af03cfe87d15d99417a Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Thu, 22 May 2025 11:36:36 +0200
Subject: [PATCH v1 03/12] prune_xid-hist: pgstat prune_xid histogram interface
---
src/include/pgstat.h | 6 ++++++
1 file changed, 6 insertions(+)
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index d48f4742beb..698b42bf66b 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -737,6 +737,12 @@ extern void pgstat_count_heap_delete(Relation rel);
extern void pgstat_count_truncate(Relation rel);
extern void pgstat_update_heap_dead_tuples(Relation rel, int delta);
+extern const PgStat_Counter *pgstat_get_prune_xid_histogram_freqs(PgStat_StatTabEntry *tabentry);
+extern const TransactionId *pgstat_get_prune_xid_histogram_bounds(PgStat_StatTabEntry *tabentry);
+extern void pgstat_update_transient_prune_xid_histogram(PruneXidHistogramTransient *hist, TransactionId old_xid, TransactionId new_xid);
+extern void pgstat_update_relation_prune_xid_histogram(Relation rel, TransactionId old_xid, TransactionId new_xid);
+extern void pgstat_update_shared_prune_xid_histogram(PgStat_StatTabEntry *tabentry, const PruneXidHistogramTransient *source);
+
extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
void *recdata, uint32 len);
extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
--
2.47.0
>From dac53835276d448f2f5d8447af4601d80ddf4456 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Thu, 22 May 2025 10:34:35 +0200
Subject: [PATCH v1 04/12] prune_xid-hist: add catalog functions
---
src/backend/utils/adt/pgstatfuncs.c | 48 +++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +++++
2 files changed, 56 insertions(+)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 97af7c6554f..1df9bda3354 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -29,6 +29,7 @@
#include "storage/proc.h"
#include "storage/procarray.h"
#include "utils/acl.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/timestamp.h"
@@ -70,6 +71,53 @@ PG_STAT_GET_RELENTRY_INT64(blocks_hit)
/* pg_stat_get_dead_tuples */
PG_STAT_GET_RELENTRY_INT64(dead_tuples)
+/* pg_stat_get_prune_xid_freqs */
+Datum
+pg_stat_get_prune_xid_freqs(FunctionCallInfo fcinfo)
+{
+ Oid relid = DatumGetObjectId((fcinfo->args[0].value));
+ Datum *result;
+ PgStat_StatTabEntry *tabentry;
+ int i;
+ const PgStat_Counter *freqs;
+
+ tabentry = pgstat_fetch_stat_tabentry(relid);
+ if (tabentry == ((void *) 0))
+ /* OID not found */
+ PG_RETURN_ARRAYTYPE_P(construct_empty_array(XIDOID));
+
+ freqs = pgstat_get_prune_xid_histogram_freqs(tabentry);
+ result = palloc((PRUNE_XID_HIST_NBINS) * sizeof(Datum));
+ for (i = 0; i < PRUNE_XID_HIST_NBINS; i++)
+ result[i] = Int64GetDatum(freqs[i]);
+
+ PG_RETURN_ARRAYTYPE_P(construct_array_builtin(result, PRUNE_XID_HIST_NBINS, XIDOID));
+}
+
+/* pg_stat_get_prune_xid_bounds */
+Datum
+pg_stat_get_prune_xid_bounds(FunctionCallInfo fcinfo)
+{
+ Oid relid = DatumGetObjectId((fcinfo->args[0].value));
+ Datum *result;
+ PgStat_StatTabEntry *tabentry;
+ int i;
+ const TransactionId *bounds;
+
+ tabentry = pgstat_fetch_stat_tabentry(relid);
+ if (tabentry == ((void *) 0))
+ /* OID not found */
+ PG_RETURN_ARRAYTYPE_P(construct_empty_array(INT8OID));
+
+ bounds = pgstat_get_prune_xid_histogram_bounds(tabentry);
+ result = palloc(PRUNE_XID_HIST_NBINS * sizeof(Datum));
+ for (i = 0; i < PRUNE_XID_HIST_NBINS; i++)
+ result[i] = TransactionIdGetDatum(bounds[i]);
+
+ PG_RETURN_ARRAYTYPE_P(construct_array_builtin(result, PRUNE_XID_HIST_NBINS, INT8OID));
+}
+
+
/* pg_stat_get_ins_since_vacuum */
PG_STAT_GET_RELENTRY_INT64(ins_since_vacuum)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62beb71da28..9628f4c72f6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5561,6 +5561,14 @@
proname => 'pg_stat_get_dead_tuples', provolatile => 's', proparallel => 'r',
prorettype => 'int8', proargtypes => 'oid',
prosrc => 'pg_stat_get_dead_tuples' },
+{ oid => '8410', descr => 'statistics: histogram of prune_xid',
+ proname => 'pg_stat_get_prune_xid_freqs', provolatile => 's', proparallel => 'r',
+ prorettype => 'anyarray', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_prune_xid_freqs' },
+{ oid => '8411', descr => 'statistics: histogram of prune_xid',
+ proname => 'pg_stat_get_prune_xid_bounds', provolatile => 's', proparallel => 'r',
+ prorettype => 'anyarray', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_prune_xid_bounds' },
{ oid => '3177',
descr => 'statistics: number of tuples changed since last analyze',
proname => 'pg_stat_get_mod_since_analyze', provolatile => 's',
--
2.47.0
>From 1a061f2201e753f0fb4ad6961d21288347665466 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Tue, 27 May 2025 16:39:57 +0200
Subject: [PATCH v1 05/12] prune_xid-hist: core functions
---
src/backend/utils/activity/pgstat_relation.c | 251 +++++++++++++++++++
1 file changed, 251 insertions(+)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 28587e2916b..33d6a384929 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -20,6 +20,7 @@
#include "access/twophase_rmgr.h"
#include "access/xact.h"
#include "catalog/catalog.h"
+#include "storage/procarray.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
#include "utils/rel.h"
@@ -460,6 +461,256 @@ pgstat_update_heap_dead_tuples(Relation rel, int delta)
}
}
+// TODO: use TransactionIdPrecedes and TransactionIdFollows in all xact comparisons
+static int
+find_last_containing_bin(TransactionId *bounds, TransactionId xid)
+{
+ int bin;
+
+ for (bin = PRUNE_XID_HIST_NBINS - 1; bin > 0; bin--)
+ if (bounds[bin - 1] == InvalidTransactionId || xid > bounds[bin - 1])
+ break;
+ return bin;
+}
+
+static int
+find_first_containing_bin(TransactionId *bounds, TransactionId xid)
+{
+ int bin;
+
+ for (bin = 0; bin < PRUNE_XID_HIST_NBINS - 1; bin++)
+ if (bounds[bin] != InvalidTransactionId && xid < bounds[bin])
+ break;
+ return bin;
+}
+
+
+static void
+hist_prune_xid_update(PruneXidHistogramTransient *hist, TransactionId xid, PgStat_Counter delta)
+{
+ int bin;
+
+ if (xid == InvalidTransactionId)
+ return;
+
+ bin = find_last_containing_bin(hist->bounds, xid);
+
+ if (bin == PRUNE_XID_HIST_NBINS - 1)
+ {
+ /* handle open bin */
+ if (delta > 0)
+ hist->freqs[bin] += delta;
+ else
+ hist->neg_freq += delta;
+ hist->bounds[bin] = Max(hist->bounds[bin], xid);
+ }
+ else
+ {
+ hist->freqs[bin] += delta;
+ }
+}
+
+
+/*
+ * At certain moments, we know that freqs cannot be negative. Then, we can
+ * rectify the histogram by moving the negative counts forward. Both
+ * histograms, before and after this operation, respect the lower bound
+ * guarantees. But the resultant is a tighter bound.
+ */
+static void
+hist_prune_xid_rectify(PruneXidHistogram *hist)
+{
+ PgStat_Counter excess = 0;
+ int bin;
+
+ for (bin = 0; bin < PRUNE_XID_HIST_NBINS - 1; bin++)
+ {
+ hist->freqs[bin] += excess;
+ excess = 0;
+
+ if (hist->freqs[bin] < 0)
+ {
+ excess = hist->freqs[bin];
+ hist->freqs[bin] = 0;
+ }
+ }
+ hist->freqs[bin] += excess;
+}
+
+static void
+hist_shift_left(PruneXidHistogram *hist, int from_bin)
+{
+ PgStat_Counter *freqs = hist->freqs;
+ TransactionId *bounds = hist->bounds;
+ int bin;
+
+ Assert(from_bin >= 0);
+
+ for (bin = from_bin; bin < PRUNE_XID_HIST_NBINS - 1; bin++)
+ {
+ bounds[bin] = bounds[bin + 1];
+ freqs[bin] = freqs[bin + 1];
+ }
+ bounds[bin] = 0;
+ freqs[bin] = 0;
+ return;
+}
+
+/*
+ * Merge intermediate adjacent bins in order to make space for a new open bin.
+ */
+static void
+hist_prune_xid_balance(PruneXidHistogram *hist)
+{
+ PgStat_Counter *freqs = hist->freqs;
+ TransactionId *bounds = hist->bounds;
+ int bin;
+
+ /* do nothing if open bin is empty */
+ if (freqs[PRUNE_XID_HIST_NBINS - 1] == 0)
+ return;
+ if (freqs[PRUNE_XID_HIST_NBINS - 1] == freqs[PRUNE_XID_HIST_NBINS - 2])
+ return;
+
+ /* if histogram is partially fulfilled, just shift bins */
+ if (bounds[0] == 0)
+ {
+ hist_shift_left(hist, 0);
+ return;
+ }
+
+ /*
+ * if second bin upper bound xid is smaller than oldest non removable xid,
+ * merge first two bins
+ */
+ if (freqs[1] < GetOldestNonRemovableTransactionId(NULL))
+ {
+ freqs[0] += freqs[1];
+ bounds[0] = bounds[1];
+ hist_shift_left(hist, 1);
+ return;
+ }
+
+ /*
+ * if open bin is too "big", merge two "small" adjacent bins.
+ */
+ {
+ PgStat_Counter open_bin_freq = freqs[PRUNE_XID_HIST_NBINS - 1];
+ PgStat_Counter smallest_pair_freq = 0;
+ int smallest_bin = 0;
+
+ for (bin = 0; bin < PRUNE_XID_HIST_NBINS - 1; bin++)
+ if (freqs[bin] + freqs[bin + 1] > smallest_pair_freq)
+ {
+ smallest_pair_freq = freqs[bin] + freqs[bin + 1];
+ smallest_bin = bin;
+ }
+ if (open_bin_freq > smallest_pair_freq)
+ {
+ freqs[smallest_bin] += freqs[smallest_bin + 1];
+ bounds[smallest_bin] = bounds[smallest_bin + 1];
+ hist_shift_left(hist, smallest_bin + 1);
+ }
+ return;
+ }
+}
+
+/* update transient histogram */
+void
+pgstat_update_transient_prune_xid_histogram(PruneXidHistogramTransient *hist, TransactionId old_xid, TransactionId new_xid)
+{
+ if (old_xid == new_xid)
+ return;
+ hist_prune_xid_update(hist, old_xid, -1);
+ hist_prune_xid_update(hist, new_xid, 1);
+}
+
+/* update per-backend prune_xid_histogram */
+void
+pgstat_update_relation_prune_xid_histogram(Relation rel, TransactionId old_xid, TransactionId new_xid)
+{
+ if (pgstat_should_count_relation(rel))
+ {
+ PgStat_TableStatus *pgstat_info = rel->pgstat_info;
+ PruneXidHistogramTransient *hist = &pgstat_info->counts.prune_xid_hist;
+
+ Assert(old_xid != new_xid);
+ hist_prune_xid_update(hist, old_xid, -1);
+ hist_prune_xid_update(hist, new_xid, 1);
+
+ }
+}
+
+
+/*
+ * This function updates prune_xid histogram of respective table in
+ * shared PgStat_StatTabEntry using information from auxiliary histogram.
+ */
+void
+pgstat_update_shared_prune_xid_histogram(PgStat_StatTabEntry *tabentry, const PruneXidHistogramTransient *source)
+{
+ int bin_source,
+ bin_target;
+ PgStat_Counter delta;
+ TransactionId xid;
+ PruneXidHistogram *target = &tabentry->prune_xid_hist;
+
+ /* process main, positive, freqs */
+ for (bin_source = 0; bin_source < PRUNE_XID_HIST_NBINS; bin_source++)
+ {
+ delta = source->freqs[bin_source];
+ xid = source->bounds[bin_source];
+
+ if (xid == InvalidTransactionId || delta == 0)
+ /* skip bins in partially filled histograms */
+ continue;
+
+ bin_target = find_last_containing_bin(target->bounds, xid);
+ target->freqs[bin_target] += delta;
+
+ if (bin_target == PRUNE_XID_HIST_NBINS - 1)
+ target->bounds[bin_target] = Max(target->bounds[bin_target], xid);
+ }
+
+ /* process negative freq */
+ xid = source->bounds[PRUNE_XID_HIST_NBINS - 2] + 1;
+ bin_target = find_first_containing_bin(target->bounds, xid);
+ target->freqs[bin_target] += source->neg_freq;
+
+ /* finally, balance histogram */
+ hist_prune_xid_balance(target);
+ hist_prune_xid_rectify(target);
+}
+
+static void
+pgstat_init_local_prune_xid_histogram(PgStat_TableStatus *lstats, PgStat_EntryRef *entry_ref)
+{
+ int bin;
+ PgStatShared_Relation *shtabstats;
+
+ shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats;
+
+ for (bin = 0; bin < PRUNE_XID_HIST_NBINS; bin++)
+ {
+ lstats->counts.prune_xid_hist.bounds[bin] = shtabstats->stats.prune_xid_hist.bounds[bin];
+ lstats->counts.prune_xid_hist.freqs[bin] = 0;
+ }
+ lstats->counts.prune_xid_hist.neg_freq = 0;
+}
+
+const PgStat_Counter *
+pgstat_get_prune_xid_histogram_freqs(PgStat_StatTabEntry *tabentry)
+{
+ return tabentry->prune_xid_hist.freqs;
+}
+
+const TransactionId *
+pgstat_get_prune_xid_histogram_bounds(PgStat_StatTabEntry *tabentry)
+{
+ return tabentry->prune_xid_hist.bounds;
+}
+
+
/*
* Support function for the SQL-callable pgstat* functions. Returns
* the collected statistics for one table or NULL. NULL doesn't mean
--
2.47.0
>From a2d9490ad86993789b63cf5dc4d232dfb574b9d6 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Tue, 27 May 2025 16:27:02 +0200
Subject: [PATCH v1 06/12] prune_xid-hist: initialize shared histogram on
TRUNCATE
---
src/backend/utils/activity/pgstat_relation.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 33d6a384929..7c86e00c334 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -1114,6 +1114,7 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
tabentry->live_tuples = 0;
tabentry->dead_tuples = 0;
tabentry->ins_since_vacuum = 0;
+ MemSet(&tabentry->prune_xid_hist, 0, sizeof(PruneXidHistogram));
}
tabentry->live_tuples += lstats->counts.delta_live_tuples;
--
2.47.0
>From 85a27a0707b9a02c1e849fb3f7486539d366b9aa Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Tue, 27 May 2025 16:50:31 +0200
Subject: [PATCH v1 07/12] prune_xid-hist: initialize local histogram
---
src/backend/utils/activity/pgstat_relation.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 7c86e00c334..06d97d3e0fb 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -1178,6 +1178,7 @@ pgstat_prep_relation_pending(Oid rel_id, bool isshared)
pending = entry_ref->pending;
pending->id = rel_id;
pending->shared = isshared;
+ pgstat_init_local_prune_xid_histogram(pending, entry_ref);
return pending;
}
--
2.47.0
>From e97e77f780fcc6329d57c3864d2549e7636585ca Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Tue, 20 May 2025 13:43:23 +0200
Subject: [PATCH v1 08/12] prune_xid-hist: collect data from
heap_{delete,update,insert}
---
src/backend/access/heap/heapam.c | 22 ++++++++++++++++++----
src/include/storage/bufpage.h | 12 ++++++++++++
2 files changed, 30 insertions(+), 4 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index c1a4de14a59..08a3ecfcdbb 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -2748,6 +2748,8 @@ heap_delete(Relation relation, ItemPointer tid,
{
TM_Result result;
TransactionId xid = GetCurrentTransactionId();
+ TransactionId old_prune_xid;
+ bool prune_xid_changed = false;
ItemId lp;
HeapTupleData tp;
Page page;
@@ -3015,7 +3017,7 @@ l1:
* the subsequent page pruning will be a no-op and the hint will be
* cleared.
*/
- PageSetPrunable(page, xid);
+ PageSetPrunableReporting(page, xid, old_prune_xid, prune_xid_changed);
if (PageIsAllVisible(page))
{
@@ -3148,6 +3150,9 @@ l1:
pgstat_count_heap_delete(relation);
+ if (prune_xid_changed)
+ pgstat_update_relation_prune_xid_histogram(relation, old_prune_xid, xid);
+
if (old_key_tuple != NULL && old_key_copied)
heap_freetuple(old_key_tuple);
@@ -3216,6 +3221,8 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
{
TM_Result result;
TransactionId xid = GetCurrentTransactionId();
+ TransactionId old_prune_xid;
+ bool prune_xid_changed = false;
Bitmapset *hot_attrs;
Bitmapset *sum_attrs;
Bitmapset *key_attrs;
@@ -4029,7 +4036,7 @@ l2:
* not to optimize for aborts. Note that heap_xlog_update must be kept in
* sync if this decision changes.
*/
- PageSetPrunable(page, xid);
+ PageSetPrunableReporting(page, xid, old_prune_xid, prune_xid_changed);
if (use_hot_update)
{
@@ -4144,6 +4151,9 @@ l2:
pgstat_count_heap_update(relation, use_hot_update, newbuf != buffer);
+ if (prune_xid_changed)
+ pgstat_update_relation_prune_xid_histogram(relation, old_prune_xid, xid);
+
/*
* If heaptup is a private copy, release it. Don't forget to copy t_self
* back to the caller's image, too.
@@ -6139,6 +6149,9 @@ void
heap_abort_speculative(Relation relation, ItemPointer tid)
{
TransactionId xid = GetCurrentTransactionId();
+ TransactionId prune_xid;
+ TransactionId old_prune_xid;
+ bool prune_xid_changed = false;
ItemId lp;
HeapTupleData tp;
Page page;
@@ -6198,13 +6211,12 @@ heap_abort_speculative(Relation relation, ItemPointer tid)
Assert(TransactionIdIsValid(TransactionXmin));
{
TransactionId relfrozenxid = relation->rd_rel->relfrozenxid;
- TransactionId prune_xid;
if (TransactionIdPrecedes(TransactionXmin, relfrozenxid))
prune_xid = relfrozenxid;
else
prune_xid = TransactionXmin;
- PageSetPrunable(page, prune_xid);
+ PageSetPrunableReporting(page, prune_xid, old_prune_xid, prune_xid_changed);
}
/* store transaction information of xact deleting the tuple */
@@ -6271,6 +6283,8 @@ heap_abort_speculative(Relation relation, ItemPointer tid)
/* count deletion, as we counted the insertion too */
pgstat_count_heap_delete(relation);
+ if (prune_xid_changed)
+ pgstat_update_relation_prune_xid_histogram(relation, old_prune_xid, prune_xid);
}
/*
diff --git a/src/include/storage/bufpage.h b/src/include/storage/bufpage.h
index aeb67c498c5..8a0774dc271 100644
--- a/src/include/storage/bufpage.h
+++ b/src/include/storage/bufpage.h
@@ -452,6 +452,18 @@ do { \
TransactionIdPrecedes(xid, ((PageHeader) (page))->pd_prune_xid)) \
((PageHeader) (page))->pd_prune_xid = (xid); \
} while (0)
+#define PageSetPrunableReporting(page, xid, old_xid, changed) \
+do { \
+ Assert(TransactionIdIsNormal(xid)); \
+ if (!TransactionIdIsValid(((PageHeader) (page))->pd_prune_xid) || \
+ TransactionIdPrecedes(xid, ((PageHeader) (page))->pd_prune_xid)) \
+ { \
+ old_xid = ((PageHeader) (page))->pd_prune_xid; \
+ ((PageHeader) (page))->pd_prune_xid = (xid); \
+ changed = true; \
+ } \
+} while (0)
+
#define PageClearPrunable(page) \
(((PageHeader) (page))->pd_prune_xid = InvalidTransactionId)
--
2.47.0
>From 928155a2db09d7a7ee6ece9b203b9c22b65e9d57 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Fri, 30 May 2025 21:45:08 +0200
Subject: [PATCH v1 09/12] prune_xid-hist: collect data from opportunistic
pruning
---
src/backend/access/heap/pruneheap.c | 5 +++++
1 file changed, 5 insertions(+)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index a8025889be0..44a8c2790a3 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -285,6 +285,11 @@ heap_page_prune_opt(Relation relation, Buffer buffer)
/* And release buffer lock */
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
+ if (prune_xid != ((PageHeader) page)->pd_prune_xid)
+ pgstat_update_relation_prune_xid_histogram(relation,
+ prune_xid,
+ ((PageHeader) page)->pd_prune_xid);
+
/*
* We avoid reuse of any free space created on the page by unrelated
* UPDATEs/INSERTs by opting to not update the FSM at this point. The
--
2.47.0
>From ae8edcf90f57ecc782f4299ebd2080a1ca1a8b20 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Fri, 30 May 2025 13:34:49 +0200
Subject: [PATCH v1 10/12] prune_xid-hist: collect data from vacuum
---
src/backend/access/heap/vacuumlazy.c | 6 ++++++
1 file changed, 6 insertions(+)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 8565f6dd215..afd57e31f5b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -1955,6 +1955,8 @@ lazy_scan_prune(LVRelState *vacrel,
Relation rel = vacrel->rel;
PruneFreezeResult presult;
int prune_options = 0;
+ TransactionId old_xid;
+ TransactionId new_xid;
Assert(BufferGetBlockNumber(buf) == blkno);
@@ -1977,10 +1979,12 @@ lazy_scan_prune(LVRelState *vacrel,
if (vacrel->nindexes == 0)
prune_options |= HEAP_PAGE_PRUNE_MARK_UNUSED_NOW;
+ old_xid = ((PageHeader) page)->pd_prune_xid;
heap_page_prune_and_freeze(rel, buf, vacrel->vistest, prune_options,
&vacrel->cutoffs, &presult, PRUNE_VACUUM_SCAN,
&vacrel->offnum,
&vacrel->NewRelfrozenXid, &vacrel->NewRelminMxid);
+ new_xid = ((PageHeader) page)->pd_prune_xid;
Assert(MultiXactIdIsValid(vacrel->NewRelminMxid));
Assert(TransactionIdIsValid(vacrel->NewRelfrozenXid));
@@ -2049,6 +2053,8 @@ lazy_scan_prune(LVRelState *vacrel,
vacrel->live_tuples += presult.live_tuples;
vacrel->recently_dead_tuples += presult.recently_dead_tuples;
+ pgstat_update_transient_prune_xid_histogram(&vacrel->prune_xid_hist, old_xid, new_xid);
+
/* Can't truncate this page */
if (presult.hastup)
vacrel->nonempty_pages = blkno + 1;
--
2.47.0
>From 9168759091ac2011fa747b9f242f0708db8835a9 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Tue, 20 May 2025 13:46:01 +0200
Subject: [PATCH v1 11/12] prune_xid-hist: transfer data to shared histogram
---
src/backend/access/heap/vacuumlazy.c | 1 +
src/backend/utils/activity/pgstat_relation.c | 5 ++++-
src/include/pgstat.h | 2 +-
3 files changed, 6 insertions(+), 2 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index afd57e31f5b..a3698593156 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -940,6 +940,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
Max(vacrel->new_live_tuples, 0),
vacrel->recently_dead_tuples +
vacrel->missed_dead_tuples,
+ &vacrel->prune_xid_hist,
starttime);
pgstat_progress_end_command();
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 06d97d3e0fb..ee49a2816e1 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -210,7 +210,7 @@ pgstat_drop_relation(Relation rel)
void
pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime)
+ const PruneXidHistogramTransient *prune_xid_hist, TimestampTz starttime)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
@@ -261,6 +261,8 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->total_vacuum_time += elapsedtime;
}
+ pgstat_update_shared_prune_xid_histogram(tabentry, prune_xid_hist);
+
pgstat_unlock_entry(entry_ref);
/*
@@ -1119,6 +1121,7 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
tabentry->live_tuples += lstats->counts.delta_live_tuples;
tabentry->dead_tuples += lstats->counts.delta_dead_tuples;
+ pgstat_update_shared_prune_xid_histogram(tabentry, &lstats->counts.prune_xid_hist);
tabentry->mod_since_analyze += lstats->counts.changed_tuples;
/*
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 698b42bf66b..2d577fefa28 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -679,7 +679,7 @@ extern void pgstat_unlink_relation(Relation rel);
extern void pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- TimestampTz starttime);
+ const PruneXidHistogramTransient *prune_xid_hist, TimestampTz starttime);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
--
2.47.0
>From a69c1c693abe205bf5131e193056dc06406393a4 Mon Sep 17 00:00:00 2001
From: "Renan A. Fonseca" <renanfons...@gmail.com>
Date: Fri, 30 May 2025 16:17:22 +0200
Subject: [PATCH v1 12/12] prune_xid-hist: tests
---
src/test/regress/sql/prune_xid_aux_check.sql | 29 +++++++++
src/test/regress/sql/prune_xid_hist.sql | 62 ++++++++++++++++++++
2 files changed, 91 insertions(+)
create mode 100644 src/test/regress/sql/prune_xid_aux_check.sql
create mode 100644 src/test/regress/sql/prune_xid_hist.sql
diff --git a/src/test/regress/sql/prune_xid_aux_check.sql b/src/test/regress/sql/prune_xid_aux_check.sql
new file mode 100644
index 00000000000..da2797c0e4e
--- /dev/null
+++ b/src/test/regress/sql/prune_xid_aux_check.sql
@@ -0,0 +1,29 @@
+
+select pg_stat_force_next_flush();
+analyze :tt; select relpages as tt_relpages from pg_class where relname=:'tt' \gset
+
+select pg_stat_get_prune_xid_freqs(:'tt'::regclass)::text::text[];
+
+with estimate as (
+ select bounds, sum(freqs::int) over (order by bounds::int) estimated_cumsum
+ from
+ unnest( pg_stat_get_prune_xid_bounds(:'tt'::regclass)::text::text[])
+ with ordinality as t1(bounds,ord)
+ natural join
+ unnest( pg_stat_get_prune_xid_freqs(:'tt'::regclass)::text::text[])
+ with ordinality as t2(freqs,ord)
+ order by bounds)
+, pageinspect_vals as (
+ select prune_xid
+ from generate_series(1,:tt_relpages) as t(n),
+ page_header(get_raw_page(:'tt', n-1))
+ where prune_xid::text!='0')
+
+select bounds,
+ estimated_cumsum,
+ (select count(*) from pageinspect_vals where prune_xid::text::int<=bounds::int)
+from estimate
+where bounds::int>0
+order by bounds::int;
+
+
diff --git a/src/test/regress/sql/prune_xid_hist.sql b/src/test/regress/sql/prune_xid_hist.sql
new file mode 100644
index 00000000000..3f87afc8f85
--- /dev/null
+++ b/src/test/regress/sql/prune_xid_hist.sql
@@ -0,0 +1,62 @@
+-- test UPDATE
+create table t1(a int) with (autovacuum_enabled='off');
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+
+insert into t1 (select generate_series(1,10000));
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+
+update t1 set a=a+1 where a % 3 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 1 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 7 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 9 = 0;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+update t1 set a=a+1 where a % 3 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 1 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 7 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 9 = 0;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+
+
+\set tt t1
+\ir prune_xid_aux_check.sql
+
+
+-- test DELETE
+create table t2(a int) with (autovacuum_enabled='off');
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+insert into t2 (select generate_series(1,10000));
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+delete from t2 where a%2=1;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+\set tt t2
+\ir prune_xid_aux_check.sql
+
+delete from t2 where a%2=0;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+insert into t2 (select generate_series(1,10000));
+delete from t2; select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+
+\set tt t2
+\ir prune_xid_aux_check.sql
+
+
+
+-- test VACUUM
+vacuum t1;
+\set tt t1
+\ir prune_xid_aux_check.sql
+
+vacuum t2;
+\set tt t2
+\ir prune_xid_aux_check.sql
+
+
+
+drop table t1;
+drop table t2;
--
2.47.0
