From 1437cb0669e3f2aea13ca34fab7618ecb58a8f14 Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Thu, 20 Feb 2025 19:22:55 -0500 Subject: [PATCH v7 1/2] Add relallfrozen to pg_class Add relallfrozen, an estimate of the number of pages marked all-frozen in the visibility map. pg_class already has relallvisible, an estimate of the number of pages in the relation marked all-visible in the visibility map. This is used primarily for planning. relallfrozen, however, is useful for estimating the outstanding number of all-visible but not all-frozen pages in the relation for the purposes of scheduling manual VACUUMs and tuning vacuum freeze parameters. In the future, this field could be used to trigger more frequent vacuums on insert-focused workloads with significant volume of frozen data. Reviewed-by: Nathan Bossart Reviewed-by: Greg Sabino Mullane Discussion: https://postgr.es/m/flat/Z7ZUWje-e1e_fKeu%40nathan#18ec7b0a585a16a58fe317b4ec42efe0 --- doc/src/sgml/catalogs.sgml | 22 ++++++++++++++ src/backend/access/heap/vacuumlazy.c | 17 ++++++++--- src/backend/catalog/heap.c | 2 ++ src/backend/catalog/index.c | 18 ++++++++++-- src/backend/commands/analyze.c | 19 +++++++++---- src/backend/commands/cluster.c | 5 ++++ src/backend/commands/vacuum.c | 6 ++++ src/backend/statistics/relation_stats.c | 38 +++++++++++++++++++++++-- src/backend/utils/cache/relcache.c | 2 ++ src/include/catalog/pg_class.h | 3 ++ src/include/commands/vacuum.h | 1 + 11 files changed, 117 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index ee59a7e15d0..e371cd905f2 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2066,6 +2066,28 @@ SCRAM-SHA-256$<iteration count>:&l + + + relallfrozen int4 + + + Number of pages that are marked all-frozen in the table's visibility + map. This is only an estimate used for triggering autovacuums. It is + updated by VACUUM, + ANALYZE, and a few + DDL commands such as CREATE + INDEX. Every all-frozen page must also be marked + all-visible in the visibility map, so internal processes will always + update relallfrozen to a value less than or + equal to relallvisible. However, if either + field is updated manually, it is possible for + relallfrozen to exceed + relallvisible. This will be corrected the + next time these estimates are updated internally. + + + + reltoastrelid oid diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 1af18a78a2b..ad01d7a2150 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -623,7 +623,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, minmulti_updated; BlockNumber orig_rel_pages, new_rel_pages, - new_rel_allvisible; + new_rel_allvisible, + new_rel_allfrozen; PGRUsage ru0; TimestampTz starttime = 0; PgStat_Counter startreadtime = 0, @@ -898,10 +899,17 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, * pg_class.relpages to */ new_rel_pages = vacrel->rel_pages; /* After possible rel truncation */ - visibilitymap_count(rel, &new_rel_allvisible, NULL); + visibilitymap_count(rel, &new_rel_allvisible, &new_rel_allfrozen); if (new_rel_allvisible > new_rel_pages) new_rel_allvisible = new_rel_pages; + /* + * An all-frozen block _must_ be all-visible. As such, clamp the count of + * all-frozen blocks to the count of all-visible blocks. + */ + if (new_rel_allfrozen > new_rel_allvisible) + new_rel_allfrozen = new_rel_allvisible; + /* * Now actually update rel's pg_class entry. * @@ -910,7 +918,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, * scan every page that isn't skipped using the visibility map. */ vac_update_relstats(rel, new_rel_pages, vacrel->new_live_tuples, - new_rel_allvisible, vacrel->nindexes > 0, + new_rel_allvisible, new_rel_allfrozen, + vacrel->nindexes > 0, vacrel->NewRelfrozenXid, vacrel->NewRelminMxid, &frozenxid_updated, &minmulti_updated, false); @@ -3720,7 +3729,7 @@ update_relstats_all_indexes(LVRelState *vacrel) vac_update_relstats(indrel, istat->num_pages, istat->num_index_tuples, - 0, + 0, 0, false, InvalidTransactionId, InvalidMultiXactId, diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 956f196fc95..7ef6f0f1cba 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -924,6 +924,7 @@ InsertPgClassTuple(Relation pg_class_desc, values[Anum_pg_class_relpages - 1] = Int32GetDatum(rd_rel->relpages); values[Anum_pg_class_reltuples - 1] = Float4GetDatum(rd_rel->reltuples); values[Anum_pg_class_relallvisible - 1] = Int32GetDatum(rd_rel->relallvisible); + values[Anum_pg_class_relallfrozen - 1] = Int32GetDatum(rd_rel->relallfrozen); values[Anum_pg_class_reltoastrelid - 1] = ObjectIdGetDatum(rd_rel->reltoastrelid); values[Anum_pg_class_relhasindex - 1] = BoolGetDatum(rd_rel->relhasindex); values[Anum_pg_class_relisshared - 1] = BoolGetDatum(rd_rel->relisshared); @@ -994,6 +995,7 @@ AddNewRelationTuple(Relation pg_class_desc, new_rel_reltup->relpages = 0; new_rel_reltup->reltuples = -1; new_rel_reltup->relallvisible = 0; + new_rel_reltup->relallfrozen = 0; /* Sequences always have a known size */ if (relkind == RELKIND_SEQUENCE) diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index cdabf780244..ac4d43fa3d3 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -2794,8 +2794,8 @@ FormIndexDatum(IndexInfo *indexInfo, * hasindex: set relhasindex to this value * reltuples: if >= 0, set reltuples to this value; else no change * - * If reltuples >= 0, relpages and relallvisible are also updated (using - * RelationGetNumberOfBlocks() and visibilitymap_count()). + * If reltuples >= 0, relpages, relallvisible, and relallfrozen are also + * updated (using RelationGetNumberOfBlocks() and visibilitymap_count()). * * NOTE: an important side-effect of this operation is that an SI invalidation * message is sent out to all backends --- including me --- causing relcache @@ -2813,6 +2813,7 @@ index_update_stats(Relation rel, bool update_stats; BlockNumber relpages = 0; /* keep compiler quiet */ BlockNumber relallvisible = 0; + BlockNumber relallfrozen = 0; Oid relid = RelationGetRelid(rel); Relation pg_class; ScanKeyData key[1]; @@ -2852,7 +2853,13 @@ index_update_stats(Relation rel, relpages = RelationGetNumberOfBlocks(rel); if (rel->rd_rel->relkind != RELKIND_INDEX) - visibilitymap_count(rel, &relallvisible, NULL); + { + visibilitymap_count(rel, &relallvisible, &relallfrozen); + + /* An all-frozen block must be all-visible in the VM */ + if (relallfrozen > relallvisible) + relallfrozen = relallvisible; + } } /* @@ -2925,6 +2932,11 @@ index_update_stats(Relation rel, rd_rel->relallvisible = (int32) relallvisible; dirty = true; } + if (rd_rel->relallfrozen != (int32) relallfrozen) + { + rd_rel->relallfrozen = (int32) relallfrozen; + dirty = true; + } } /* diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index cd75954951b..9c4755a96d5 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -630,12 +630,18 @@ do_analyze_rel(Relation onerel, VacuumParams *params, */ if (!inh) { - BlockNumber relallvisible; + BlockNumber relallvisible = 0; + BlockNumber relallfrozen = 0; if (RELKIND_HAS_STORAGE(onerel->rd_rel->relkind)) - visibilitymap_count(onerel, &relallvisible, NULL); - else - relallvisible = 0; + visibilitymap_count(onerel, &relallvisible, &relallfrozen); + + /* + * An all-frozen block _must_ be all-visible. As such, clamp the count + * of all-frozen blocks to the count of all-visible blocks. + */ + if (relallfrozen > relallvisible) + relallfrozen = relallvisible; /* * Update pg_class for table relation. CCI first, in case acquirefunc @@ -646,6 +652,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params, relpages, totalrows, relallvisible, + relallfrozen, hasindex, InvalidTransactionId, InvalidMultiXactId, @@ -662,7 +669,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params, vac_update_relstats(Irel[ind], RelationGetNumberOfBlocks(Irel[ind]), totalindexrows, - 0, + 0, 0, false, InvalidTransactionId, InvalidMultiXactId, @@ -678,7 +685,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params, */ CommandCounterIncrement(); vac_update_relstats(onerel, -1, totalrows, - 0, hasindex, InvalidTransactionId, + 0, 0, hasindex, InvalidTransactionId, InvalidMultiXactId, NULL, NULL, in_outer_xact); diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 99193f5c886..54a08e4102e 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -1226,6 +1226,7 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class, int32 swap_pages; float4 swap_tuples; int32 swap_allvisible; + int32 swap_allfrozen; swap_pages = relform1->relpages; relform1->relpages = relform2->relpages; @@ -1238,6 +1239,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class, swap_allvisible = relform1->relallvisible; relform1->relallvisible = relform2->relallvisible; relform2->relallvisible = swap_allvisible; + + swap_allfrozen = relform1->relallfrozen; + relform1->relallfrozen = relform2->relallfrozen; + relform2->relallfrozen = swap_allfrozen; } /* diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 0239d9bae65..e81c9a8aba3 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1427,6 +1427,7 @@ void vac_update_relstats(Relation relation, BlockNumber num_pages, double num_tuples, BlockNumber num_all_visible_pages, + BlockNumber num_all_frozen_pages, bool hasindex, TransactionId frozenxid, MultiXactId minmulti, bool *frozenxid_updated, bool *minmulti_updated, @@ -1476,6 +1477,11 @@ vac_update_relstats(Relation relation, pgcform->relallvisible = (int32) num_all_visible_pages; dirty = true; } + if (pgcform->relallfrozen != (int32) num_all_frozen_pages) + { + pgcform->relallfrozen = (int32) num_all_frozen_pages; + dirty = true; + } /* Apply DDL updates, but not inside an outer transaction (see above) */ diff --git a/src/backend/statistics/relation_stats.c b/src/backend/statistics/relation_stats.c index 046661d7c3f..e80fd52a876 100644 --- a/src/backend/statistics/relation_stats.c +++ b/src/backend/statistics/relation_stats.c @@ -27,6 +27,7 @@ #define DEFAULT_RELPAGES Int32GetDatum(0) #define DEFAULT_RELTUPLES Float4GetDatum(-1.0) #define DEFAULT_RELALLVISIBLE Int32GetDatum(0) +#define DEFAULT_RELALLFROZEN Int32GetDatum(0) /* * Positional argument numbers, names, and types for @@ -39,6 +40,7 @@ enum relation_stats_argnum RELPAGES_ARG, RELTUPLES_ARG, RELALLVISIBLE_ARG, + RELALLFROZEN_ARG, NUM_RELATION_STATS_ARGS }; @@ -48,6 +50,7 @@ static struct StatsArgInfo relarginfo[] = [RELPAGES_ARG] = {"relpages", INT4OID}, [RELTUPLES_ARG] = {"reltuples", FLOAT4OID}, [RELALLVISIBLE_ARG] = {"relallvisible", INT4OID}, + [RELALLFROZEN_ARG] = {"relallfrozen", INT4OID}, [NUM_RELATION_STATS_ARGS] = {0} }; @@ -67,7 +70,9 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel, bool inplace) float reltuples = DEFAULT_RELTUPLES; bool update_reltuples = false; int32 relallvisible = DEFAULT_RELALLVISIBLE; + int32 relallfrozen = DEFAULT_RELALLFROZEN; bool update_relallvisible = false; + bool update_relallfrozen = false; bool result = true; if (!PG_ARGISNULL(RELPAGES_ARG)) @@ -120,6 +125,21 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel, bool inplace) update_relallvisible = true; } + if (!PG_ARGISNULL(RELALLFROZEN_ARG)) + { + relallfrozen = PG_GETARG_INT32(RELALLFROZEN_ARG); + + if (relallfrozen < 0) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relallfrozen cannot be < 0"))); + result = false; + } + else + update_relallfrozen = true; + } + stats_check_required_arg(fcinfo, relarginfo, RELATION_ARG); reloid = PG_GETARG_OID(RELATION_ARG); @@ -169,6 +189,11 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel, bool inplace) pgcform->relallvisible = relallvisible; dirty = true; } + if (update_relallfrozen && pgcform->relallfrozen != relallfrozen) + { + pgcform->relallfrozen = relallfrozen; + dirty = true; + } if (dirty) systable_inplace_update_finish(inplace_state, ctup); @@ -182,9 +207,9 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel, bool inplace) TupleDesc tupdesc = RelationGetDescr(crel); HeapTuple ctup; Form_pg_class pgcform; - int replaces[3] = {0}; - Datum values[3] = {0}; - bool nulls[3] = {0}; + int replaces[4] = {0}; + Datum values[4] = {0}; + bool nulls[4] = {0}; int nreplaces = 0; ctup = SearchSysCache1(RELOID, ObjectIdGetDatum(reloid)); @@ -219,6 +244,13 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel, bool inplace) nreplaces++; } + if (update_relallfrozen && relallfrozen != pgcform->relallfrozen) + { + replaces[nreplaces] = Anum_pg_class_relallfrozen; + values[nreplaces] = Int32GetDatum(relallfrozen); + nreplaces++; + } + if (nreplaces > 0) { HeapTuple newtup; diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 398114373e9..d1ae761b3f6 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -1928,6 +1928,7 @@ formrdesc(const char *relationName, Oid relationReltype, relation->rd_rel->relpages = 0; relation->rd_rel->reltuples = -1; relation->rd_rel->relallvisible = 0; + relation->rd_rel->relallfrozen = 0; relation->rd_rel->relkind = RELKIND_RELATION; relation->rd_rel->relnatts = (int16) natts; @@ -3885,6 +3886,7 @@ RelationSetNewRelfilenumber(Relation relation, char persistence) classform->relpages = 0; /* it's empty until further notice */ classform->reltuples = -1; classform->relallvisible = 0; + classform->relallfrozen = 0; } classform->relfrozenxid = freezeXid; classform->relminmxid = minmulti; diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index f0d612ca487..fa96ba07bf4 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -68,6 +68,9 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat /* # of all-visible blocks (not always up-to-date) */ int32 relallvisible BKI_DEFAULT(0); + /* # of all-frozen blocks (not always up-to-date) */ + int32 relallfrozen BKI_DEFAULT(0); + /* OID of toast table; 0 if none */ Oid reltoastrelid BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_class); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 1571a66c6bf..baacc63f590 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -349,6 +349,7 @@ extern void vac_update_relstats(Relation relation, BlockNumber num_pages, double num_tuples, BlockNumber num_all_visible_pages, + BlockNumber num_all_frozen_pages, bool hasindex, TransactionId frozenxid, MultiXactId minmulti, -- 2.34.1