public inbox for [email protected]
help / color / mirror / Atom feedpgsql: Add relallfrozen to pg_class
5+ messages / 3 participants
[nested] [flat]
* pgsql: Add relallfrozen to pg_class
@ 2025-03-03 16:20 Melanie Plageman <[email protected]>
2025-03-03 16:44 ` Re: pgsql: Add relallfrozen to pg_class Álvaro Herrera <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Melanie Plageman @ 2025-03-03 16:20 UTC (permalink / raw)
To: [email protected]
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, together with relallvisible, 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.
A future commit will use relallfrozen to trigger more frequent vacuums
on insert-focused workloads with significant volume of frozen data.
Bump catalog version
Author: Melanie Plageman <[email protected]>
Reviewed-by: Nathan Bossart <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
Reviewed-by: Corey Huinker <[email protected]>
Reviewed-by: Greg Sabino Mullane <[email protected]>
Discussion: https://postgr.es/m/flat/CAAKRu_aj-P7YyBz_cPNwztz6ohP%2BvWis%3Diz3YcomkB3NpYA--w%40mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/99f8f3fbbc8f743290844e8c676d39dad11c5d5d
Modified Files
--------------
doc/src/sgml/catalogs.sgml | 20 ++++++
src/backend/access/heap/vacuumlazy.c | 18 ++++--
src/backend/catalog/heap.c | 2 +
src/backend/catalog/index.c | 12 +++-
src/backend/commands/analyze.c | 12 ++--
src/backend/commands/cluster.c | 5 ++
src/backend/commands/vacuum.c | 6 ++
src/backend/statistics/relation_stats.c | 29 +++++++--
src/backend/utils/cache/relcache.c | 2 +
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_class.h | 3 +
src/include/commands/vacuum.h | 1 +
src/test/regress/expected/stats_import.out | 99 +++++++++++++++++++-----------
src/test/regress/sql/stats_import.sql | 49 ++++++++++-----
14 files changed, 190 insertions(+), 70 deletions(-)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Add relallfrozen to pg_class
2025-03-03 16:20 pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
@ 2025-03-03 16:44 ` Álvaro Herrera <[email protected]>
2025-03-03 16:48 ` Re: pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
2025-03-03 16:50 ` Re: pgsql: Add relallfrozen to pg_class Andres Freund <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Álvaro Herrera @ 2025-03-03 16:44 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: [email protected]
On 2025-Mar-03, Melanie Plageman wrote:
> 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, together with relallvisible, 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.
I'm confused about this. Why was the new value added to pg_class
instead of to the pgstat system? I don't think relallvisible is a good
precedent, because as you write here, that one is used for planning,
which has different requirements. For vacuuming metrics we rely on
pgstat.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Add relallfrozen to pg_class
2025-03-03 16:20 pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
2025-03-03 16:44 ` Re: pgsql: Add relallfrozen to pg_class Álvaro Herrera <[email protected]>
@ 2025-03-03 16:48 ` Melanie Plageman <[email protected]>
2025-03-03 16:52 ` Re: pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Melanie Plageman @ 2025-03-03 16:48 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: [email protected]
On Mon, Mar 3, 2025 at 11:44 AM Álvaro Herrera <[email protected]> wrote:
>
> On 2025-Mar-03, Melanie Plageman wrote:
>
> > relallfrozen, together with relallvisible, 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.
>
> I'm confused about this. Why was the new value added to pg_class
> instead of to the pgstat system? I don't think relallvisible is a good
> precedent, because as you write here, that one is used for planning,
> which has different requirements. For vacuuming metrics we rely on
> pgstat.
We use relpages and reltuples from pg_class in
relation_needs_vacanalyze() in the same way relallfrozen is being used
here.
If we don't want relallfrozen in pg_class then there is no reason we
wouldn't also move relallvisible out of pg_class too.
- Melanie
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Add relallfrozen to pg_class
2025-03-03 16:20 pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
2025-03-03 16:44 ` Re: pgsql: Add relallfrozen to pg_class Álvaro Herrera <[email protected]>
2025-03-03 16:48 ` Re: pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
@ 2025-03-03 16:52 ` Melanie Plageman <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Melanie Plageman @ 2025-03-03 16:52 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: [email protected]
On Mon, Mar 3, 2025 at 11:48 AM Melanie Plageman
<[email protected]> wrote:
>
> On Mon, Mar 3, 2025 at 11:44 AM Álvaro Herrera <[email protected]> wrote:
> >
> > I'm confused about this. Why was the new value added to pg_class
> > instead of to the pgstat system? I don't think relallvisible is a good
> > precedent, because as you write here, that one is used for planning,
> > which has different requirements. For vacuuming metrics we rely on
> > pgstat.
>
> We use relpages and reltuples from pg_class in
> relation_needs_vacanalyze() in the same way relallfrozen is being used
> here.
Sorry reltuples only (not relpages).
- Melanie
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Add relallfrozen to pg_class
2025-03-03 16:20 pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
2025-03-03 16:44 ` Re: pgsql: Add relallfrozen to pg_class Álvaro Herrera <[email protected]>
@ 2025-03-03 16:50 ` Andres Freund <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Andres Freund @ 2025-03-03 16:50 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Melanie Plageman <[email protected]>; [email protected]
Hi,
On 2025-03-03 17:44:54 +0100, Álvaro Herrera wrote:
> On 2025-Mar-03, Melanie Plageman wrote:
>
> > 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, together with relallvisible, 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.
>
> I'm confused about this. Why was the new value added to pg_class
> instead of to the pgstat system? I don't think relallvisible is a good
> precedent, because as you write here, that one is used for planning,
> which has different requirements. For vacuuming metrics we rely on
> pgstat.
We do so because updating the other stats would cause too much churn - the
number of dead tuples etc changes rather rapidly. That's not the case
here. Having reliable information after a crash (where we loose pgstats) that
95% of the table is actually frozen, rather than not seems good.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-03-03 16:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-03 16:20 pgsql: Add relallfrozen to pg_class Melanie Plageman <[email protected]>
2025-03-03 16:44 ` Álvaro Herrera <[email protected]>
2025-03-03 16:48 ` Melanie Plageman <[email protected]>
2025-03-03 16:52 ` Melanie Plageman <[email protected]>
2025-03-03 16:50 ` Andres Freund <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox