Re: Eager page freeze criteria clarification

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.

> 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.
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.

--
Peter Geoghegan