Re: Eager page freeze criteria clarification

Hi,

On 2023-09-07 22:29:04 -0700, Peter Geoghegan wrote:
> On Thu, Sep 7, 2023 at 9:45 PM Andres Freund <andres@anarazel.de> wrote:
> > I.e. setting an, otherwise unmodified, page all-visible won't trigger an FPI
> > if checksums are disabled, but will FPI with checksums enabled. I think that's
> > a substantial difference in WAL volume for insert-only workloads...
> 
> Note that all RDS Postgres users get page-level checksums. Overall,
> the FPI trigger mechanism is going to noticeably improve performance
> characteristics for many users. Simple and crude though it is.

You mean the current heuristic or some new heuristic we're coming up with in
this thread?  If the former, unfortunately I think the current heuristic often
won't trigger in cases where freezing would be fine, e.g. on an insert-mostly
(or hot pruned) workload with some read accesses.  If the tuples are already
hint-bitted, there's no FPI during heap_page_prune(), and thus we don't freeze
- even though we *do* subsequently trigger an FPI, while setting all-visible.

See e.g. the stats for the modified version of the scenario, where there the
table is hint-bitted that I have since posted:
https://postgr.es/m/20230908053634.hyn46pugqp4lsiw5%40awork3.anarazel.de

There we freeze neither with nor without checksums, despite incurring FPIs
when checksums are enabled.


> > Type                                           N      (%)          Record size      (%)             FPI size
(%)       Combined size      (%)
 
> > ----                                           -      ---          -----------      ---             --------
---       -------------      ---
 
> > XLOG/FPI_FOR_HINT                          44253 ( 33.34)              2168397 (  7.84)            361094232
(100.00)           363262629 ( 93.44)
 
> > Transaction/INVALIDATION                       1 (  0.00)                   78 (  0.00)                    0 (
0.00)                  78 (  0.00)
 
> > Standby/INVALIDATIONS                          1 (  0.00)                   90 (  0.00)                    0 (
0.00)                  90 (  0.00)
 
> > Heap2/FREEZE_PAGE                          44248 ( 33.33)             22876120 ( 82.72)                    0 (
0.00)            22876120 (  5.88)
 
> > Heap2/VISIBLE                              44248 ( 33.33)              2610642 (  9.44)                16384 (
0.00)             2627026 (  0.68)
 
> > Heap/INPLACE                                   1 (  0.00)                  188 (  0.00)                    0 (
0.00)                 188 (  0.00)
 
> >                                         --------                      --------                      --------
             --------
 
> > Total                                     132752                      27655515 [7.11%]             361110616
[92.89%]           388766131 [100%]
 
> >
> > In realistic tables, where rows are wider than a single int, FPI_FOR_HINT
> > dominates even further, as the FREEZE_PAGE would be smaller if there weren't
> > 226 tuples on each page...
> 
> If FREEZE_PAGE WAL volume is really what holds back further high level
> improvements in this area, then it can be worked on directly -- it's
> not a fixed cost. It wouldn't be particularly difficult, in fact.

Agreed!


> These are records that still mostly consist of long runs of contiguous
> page offset numbers. They're ideally suited for compression using some
> kind of simple variant of run length encoding. The freeze plan
> deduplication stuff in 16 made a big difference, but it's still not
> very hard to improve matters here.

Yea, even just using ranges of offsets should help in a lot of cases.

Greetings,

Andres Freund