Hi, On 2023-09-07 22:29:04 -0700, Peter Geoghegan wrote: > On Thu, Sep 7, 2023 at 9:45 PM Andres Freund <and...@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
