Feedback-ID: id4a34324:Fastmail
Date: Tue, 26 Sep 2023 08:19:43 -0700
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	Pg Hackers <pgsql-hackers@postgresql.org>,
	Jeff Davis <pgsql@j-davis.com>
Subject: Re: Eager page freeze criteria clarification
Message-ID: <20230926151943.gn4itbeckwfpbbsw@alap3.anarazel.de>
References: <CAAKRu_axe6eM2u5nNH_Lk1tsYM=U8m7y-MXyhVuc7CwQ2QSURQ@mail.gmail.com>
 <CAH2-Wz=QQsFkH1g2tpkoT61OG+p6=QmRGE-0wPKcvkC93Vz6KA@mail.gmail.com>
 <CAAKRu_Z-AAanGqsLX0ecgK2kv1MSvATxV-F4-cCJQDZu9=TPzA@mail.gmail.com>
 <CAAKRu_b8BTWKxtTyayRmdjbh+3VQ-fnFYc2kDRKH62wyp8HUGA@mail.gmail.com>
 <CA+TgmoZoCWXoZ8uu8QimTwM3e_b7wCz=EwCvPKpLng=14rR-8g@mail.gmail.com>
 <20230906050900.ig5d55fpiwsjqtil@awork3.anarazel.de>
 <CA+TgmobvHws+ECTE-4pbw0Uw4ORA7-Kc6WH-C6fJ2zoZ8CKwfQ@mail.gmail.com>
 <20230908040713.qlc4sojzjd4amqt4@awork3.anarazel.de>
 <CA+TgmobfVSPWWdW7ji+efwQf05_8VXUKqakt9UXjMbAkuOCFpw@mail.gmail.com>
 <CAH2-Wzme6k4f3U7dk7DU0OR9H3gsSRtDF-BfQijSF9tXWPup5w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAH2-Wzme6k4f3U7dk7DU0OR9H3gsSRtDF-BfQijSF9tXWPup5w@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20230926151943.gn4itbeckwfpbbsw%40alap3.anarazel.de>
Precedence: bulk

Hi,

On 2023-09-25 14:16:46 -0700, Peter Geoghegan wrote:
> On Mon, Sep 25, 2023 at 11:45 AM Robert Haas <robertmhaas@gmail.com> wrote:
> I'm surprised that there hasn't been any discussion of the absolute
> amount of system-wide freeze debt on this thread. If 90% of the pages
> in the entire database are frozen, it'll generally be okay if we make
> the wrong call by freezing lazily when we shouldn't. This is doubly
> true within small to medium sized tables, where the cost of catching
> up on freezing cannot ever be too bad (concentrations of unfrozen
> pages in one big table are what really hurt users).

I generally agree with the idea of using "freeze debt" as an input - IIRC I
proposed using the number of frozen pages vs number of pages as the input to
the heuristic in one of the other threads about the topic a while back. I also
think we should read a portion of all-visible-but-not-frozen pages during
non-aggressive vacuums to manage the freeze debt.

However, I'm not at all convinced doing this on a system wide level is a good
idea. Databases do often contain multiple types of workloads at the same
time. E.g., we want to freeze aggressively in a database that has the bulk of
its size in archival partitions but has lots of unfrozen data in an active
partition. And databases have often loads of data that's going to change
frequently / isn't long lived, and we don't want to super aggressively freeze
that, just because it's a large portion of the data.

Greetings,

Andres Freund