Re: Compress prune/freeze records with Delta Frame of Reference algorithm

On 2026-04-08 20:34:42 +0800, Evgeny Voropaev wrote:
> Tomas, Andreus, Andrey, hello!
>
> > A ~170kB patch really should present some numbers
> > quantifying the expected benefit. It doesn't need to be a real workload
> > from production, but something plausible enough. Even some basic
> > back-of-the-envelope calculations might be enough to show the promise.
>
> The patch results in reduction of WAL total size by:
>     81% during vacuuming a table having no index,
>     and by 55% during vacuuming a table having an index.
>
> The numbers are the next:
>
> === VACUUM (table with no index) ===
> -------------------- ----------------- ----------------- -----------
>                        DFOR off, bytes    DFOR on, bytes   Reduction
> -------------------- ----------------- ----------------- -----------
> WAL total size                 6743149           1184446         82%
> Prune records size             6710185           1159723        5.8x
> -------------------- ----------------- ----------------- -----------
>
> === VACUUM (table with index) ===
> -------------------- ----------------- ----------------- -----------
>                        DFOR off, bytes    DFOR on, bytes   Reduction
> -------------------- ----------------- ----------------- -----------
> WAL total size                20394208           8907090         56%
> Prune records size             6812850           1225944        5.6x
> -------------------- ----------------- ----------------- -----------

These numbers make the impact sound bigger than I think it really is:

- They neglect that the insert generates ~183MB of WAL, the delete ~161MB
  without indexes and ~243MB / 161MB with.  In contrast to that 6.7Mb isn't
  particularly significant.

- Workloads deleting almost all records in the table but leaving some in to
  prevent truncation aren't particularly common.

- The narrowness of the rows (~30 bytes, with row header) makes the wins much
  bigger than they'd be in realistic cases

- The workload doesn't involve any FPIs. It's much more common to have
  vacuum's occur later and trigger FPIs.

  Heh. In this case FPIs actually would *reduce* the overhead of the current
  code, because the page is so empty after all the deletes that the FPI uses
  less space than the update . It's 4.1MB when not using indexes and not using
  wal compression and 1MB with wal compression.

  Seems we could get a fair bit of benefit by just using a heuristic to switch
  to an FPI when there are enough changes.

  I think that'd just be a few lines.

Greetings,

Andres Freund