Feedback-ID: id4a34324:Fastmail
Date: Thu, 9 Apr 2026 12:50:48 -0400
From: Andres Freund <andres@anarazel.de>
To: Evgeny Voropaev <evgeny.voropaev@tantorlabs.com>
Cc: Tomas Vondra <tomas@vondra.me>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Andrey Borodin <x4mmm@yandex-team.ru>
Subject: Re: Compress prune/freeze records with Delta Frame of Reference
 algorithm
Message-ID: <d4gecdvma2s36vy3chrzjhvd5lwxjvff7yecgpqo5zd5nsgv24@w3jt7z62xrgh>
References: <acf7e923-7ad6-42ed-b16a-df15e876d249@tantorlabs.com>
 <wps75qdtwpykdt4zatfh2u2hi3zju4drdzqi2zh7uy3x4ooivv@kc2fxfz7lx3e>
 <e4d4fef2-165c-4d27-8366-18cfef5d6484@tantorlabs.com>
 <5a2f3df2-a736-4ada-8aa3-aa6e20b2e067@vondra.me>
 <3fc9f40b-2c4f-49c7-bf1c-570c5b9e6e9e@tantorlabs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3fc9f40b-2c4f-49c7-bf1c-570c5b9e6e9e@tantorlabs.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d4gecdvma2s36vy3chrzjhvd5lwxjvff7yecgpqo5zd5nsgv24%40w3jt7z62xrgh>
Precedence: bulk

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