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