public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andres Freund <[email protected]>
To: Evgeny Voropaev <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andrey Borodin <[email protected]>
Subject: Re: Compress prune/freeze records with Delta Frame of Reference algorithm
Date: Thu, 9 Apr 2026 12:50:48 -0400
Message-ID: <d4gecdvma2s36vy3chrzjhvd5lwxjvff7yecgpqo5zd5nsgv24@w3jt7z62xrgh> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<wps75qdtwpykdt4zatfh2u2hi3zju4drdzqi2zh7uy3x4ooivv@kc2fxfz7lx3e>
<[email protected]>
<[email protected]>
<[email protected]>
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
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Compress prune/freeze records with Delta Frame of Reference algorithm
In-Reply-To: <d4gecdvma2s36vy3chrzjhvd5lwxjvff7yecgpqo5zd5nsgv24@w3jt7z62xrgh>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox