v2-0002-skip-vacuuming-the-table-if-last-time-vacuum-perc.patch
application/octet-stream
Filename: v2-0002-skip-vacuuming-the-table-if-last-time-vacuum-perc.patch
Type: application/octet-stream
Part: 0
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 v2-0002
Subject: skip vacuuming the table if last time vacuum percent less than 10
| File | + | − |
|---|---|---|
| src/backend/postmaster/autovacuum.c | 16 | 0 |
| src/backend/utils/activity/pgstat_relation.c | 7 | 0 |
| src/include/pgstat.h | 1 | 0 |
From 54196b001ca8df9e621811d5204d956851784683 Mon Sep 17 00:00:00 2001
From: Junwang Zhao <zhjwpku@gmail.com>
Date: Thu, 1 May 2025 10:14:08 +0000
Subject: [PATCH v2 2/2] skip vacuuming the table if last time vacuum percent
less than 10
---
src/backend/postmaster/autovacuum.c | 16 ++++++++++++++++
src/backend/utils/activity/pgstat_relation.c | 7 +++++++
src/include/pgstat.h | 1 +
3 files changed, 24 insertions(+)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 2452d893533..06b91f31d9e 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -3093,6 +3093,22 @@ relation_needs_vacanalyze(Oid relid,
float4 reltuples = classForm->reltuples;
int32 relpages = classForm->relpages;
int32 relallfrozen = classForm->relallfrozen;
+ float4 last_remove_tuples_percent = tabentry->last_autovacuum_removed_tuples_percent;
+ TimestampTz last_autovacuum_time = tabentry->last_autovacuum_time;
+
+ /*
+ * If the last autovacuum removed tuples is less than 10% of the
+ * current dead tuples, then skip vacuuming this table for some time.
+ */
+ if (!TimestampDifferenceExceedsSeconds(last_autovacuum_time,
+ GetCurrentTimestamp(),
+ autovacuum_naptime) &&
+ last_remove_tuples_percent < 10.0)
+ {
+ *doanalyze = false;
+ *dovacuum = false;
+ return;
+ }
vactuples = tabentry->dead_tuples;
instuples = tabentry->ins_since_vacuum;
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index eeb2d43cb10..d712ec18199 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -217,6 +217,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
Oid dboid = (shared ? InvalidOid : MyDatabaseId);
TimestampTz ts;
PgStat_Counter elapsedtime;
+ PgStat_Counter old_dead_tuples;
if (!pgstat_track_counts)
return;
@@ -232,6 +233,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
tabentry = &shtabentry->stats;
+ old_dead_tuples = tabentry->dead_tuples;
tabentry->live_tuples = livetuples;
tabentry->dead_tuples = deadtuples;
@@ -250,6 +252,11 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
if (AmAutoVacuumWorkerProcess())
{
tabentry->last_autovacuum_time = ts;
+ if (old_dead_tuples > deadtuples)
+ tabentry->last_autovacuum_removed_tuples_percent =
+ 100 * (old_dead_tuples - deadtuples) / old_dead_tuples;
+ else
+ tabentry->last_autovacuum_removed_tuples_percent = 0;
tabentry->autovacuum_count++;
tabentry->total_autovacuum_time += elapsedtime;
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 378f2f2c2ba..417699e1fc2 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -443,6 +443,7 @@ typedef struct PgStat_StatTabEntry
TimestampTz last_vacuum_time; /* user initiated vacuum */
PgStat_Counter vacuum_count;
TimestampTz last_autovacuum_time; /* autovacuum initiated */
+ PgStat_Counter last_autovacuum_removed_tuples_percent;
PgStat_Counter autovacuum_count;
TimestampTz last_analyze_time; /* user initiated */
PgStat_Counter analyze_count;
--
2.39.5