Re: WAL compression setting after PostgreSQL LZ4 default change

Re: WAL compression setting after PostgreSQL LZ4 default change

On Thu, 2026-07-16 at 11:08 -0400, Andres Freund wrote:
> On 2026-07-16 17:00:58 +0200, Tomas Vondra wrote:
> > * the proposal is to change what 'on' means - currently it's always
> >   pglz, but the proposal would change this to the "best" available
> >   algorithm (so lz4)
> > 
> > * the wal_compression default would remain "off"
> > 
> > I think this proposal makes sense, and at least Michael and Christoph
> > seem to agree with that.
> 
> +1 from me too.
> 
> I've seen way too many incidents where wal_compression was very useful but due
> to the use of pglz slowed down crucial, single threaded, parts of the workload
> down so much that it had to be disabled again.  We should not make it our
> users responsibility to know that the 'on' maps to an almost unusably slow
> compression algorithm.

+1

It would be compelling to make this change for v19, so that it is in line
with the change for "default_toast_compression".  But I guess it is too late
in the cycle for that.

Yours,
Laurenz Albe

Re: WAL compression setting after PostgreSQL LZ4 default change

On 8/3/26 15:36, Hannu Krosing wrote:
> For the CPU usage vs compression ratio discussion, please remember
> that for both lz4 and zstd this depends on certain "compression
> effort" flags, which we do not expose to customers (we claim we
> already choose the best ones for them :) )
> 

I'm not sure we really make such claims. AFAIK we simply use the default
compression levels, specified by the lz4/zstd library, and assume the
authors picked the right trade offs. I think that's sensible.

> To have a good basis for any recommendation someone could run a bunch
> of tests with different compression level flags and compare the
> results for CPU usage and compression ration for 8kB pages.
> 

That might be an interesting experiment, but (a) I suspect there'll be a
fair amount of dependency on the data set used (e.g. what kind of data
is loaded into the DB), and (b) we'd pretty close to release data.

I don't think we want to be messing with the compression levels, so for
PG19 we're stuck with the current ones. Which means we should pick the
default compression algorithm based on that.


regards

-- 
Tomas Vondra

Re: WAL compression setting after PostgreSQL LZ4 default change

Hi,

On 7/8/26 08:37, wenhui qiu wrote:
> Hi 
> 
>     > I would like to see "on" to be moved to some better value (likely lz4,
>     > like toast compression), and if some day, there is yet a better
>     > choice, we should move it again.
>     Yes, After PostgreSQL 19, |lz4| became a required dependency unless
>     you compile it yourself with |--without-lz4|. In most
>     environments, |lz4| is already installed, so along this code path
>     the effective choice is essentially fixed to |lz4|.At cluster scale,
>     it would make more sense to expose this parameter in the frontend as
>     a boolean setting, rather than letting users choose from multiple
>     compression options.
> 
> 
> Thanks 

I've been reminded of this proposal by a message in another thread [1],
which suggests this proposal (to adjust the wal_compression default) was
not accepted.

Which seems strange to me, and I suspect it's a misunderstanding. From
the discussion in this thread it seems to me that:

* the proposal is to change what 'on' means - currently it's always
pglz, but the proposal would change this to the "best" available
algorithm (so lz4)

* the wal_compression default would remain "off"

I think this proposal makes sense, and at least Michael and Christoph
seem to agree with that.

Fujii Masao disagrees, arguing to leave "on" as it is:

> Yes, so +1 to leaving "on" as it is. Making its meaning depend on
> the build options seems very confusing. Users who want lz4 or zstd
> can simply specify those methods explicitly instead of using "on".

I understand the sentiment, but ISTM the argument "if you want lz4 or
zstd, you can specify that" can just as well be used in favor of the
proposal (because users have a way to eliminate the confusion).

The core question is whether we should stick to "on" meaning "pglz"
forever, because it once did. Or whether we should treat "on" as a
generic "do compression", and the database should be able to choose the
best supported algorithm.

My opinion is "on" should be a generic "pick compression" option, giving
the database the mandate to pick an algorithm. A user who wants a
particular algorithm can specify that.

The only reason why "on" means "pglz" is that initially that was the
only supported algorithm (until PG 15). But if we supported multiple
algorithms from the beginning, would we do it that way? I don't think
so. We'd either not have "on" at all, or it'd pick the best algorithm.


regards


[1]
https://postgr.es/m/CAGjGUAL6=2=gGTx_f4XeMpcJ0p8QMm+i8mX5rbC7VJXKbOGyNA@mail.gmail.com

-- 
Tomas Vondra

Re: WAL compression setting after PostgreSQL LZ4 default change

> On 9 Aug 2026, at 19:25, Christoph Berg  wrote:

> I would put zstd before lz4 in both places as it's preferred over it.

I agree with this, and the patch LGTM other than that.

--
Daniel Gustafsson

Re: WAL compression setting after PostgreSQL LZ4 default change

On 7/31/26 17:50, Christoph Berg wrote:
> Re: Andrey Borodin
>> FWIW on 19beta2, one client: a seqscan setting hint bits on a freshly
>> checkpointed 380MB table with wal_log_hints on, so ~47k full-page images
>> and little else.  Best of three: off 765ms, pglz 1402ms, lz4 444ms, zstd
>> 797ms, writing 382/105/107/67 MB of WAL (fsync off, so the I/O saving is
>> understated).  lz4 is the only setting faster than no compression at all,
>> while pglz costs nearly double for the size lz4 gives away for free.  zstd
>> compresses a third better but pays for it in CPU, partly by allocating a
>> ZSTD_CCtx per FPI - see the nearby thread "Compression of bigger WAL
>> records".  +1 for "on" meaning lz4 where available.
> 
> v4 moves lz4 first.
> 
> (I did not include any reason in the commit message about choosing lz4
> over zstd yet.)
> 
> One minor bit: The patch now has
> 
> #if defined(USE_LZ4)
> #define DEFAULT_WAL_COMPRESSION·WAL_COMPRESSION_LZ4
> #elif defined(USE_ZSTD)
> #define DEFAULT_WAL_COMPRESSION·WAL_COMPRESSION_ZSTD
> 
> when the old one had
> #ifdef USE_ZSTD
> #elif defined(USE_LZ4)
> 
> I left it at "#if defined(USE_LZ4)" because that looks more
> symmetrical.
> 

Thanks. FWIW I've brought this up with the RMT, and there seem to be no
objections to getting this done for PG19. Unless someone objects in the
next couple days, I'll get it committed.

As for the zstd vs. lz4 - the patch put zstd first mostly because
Michael prefers it over lz4. I don't think either of these is superior.
AFAIK the algorithms make different trade offs - zstd tends to compress
better, but uses (substantially) more CPU. I think it makes sense to
default to lz4, it's clearly better than pglz, with low CPU usage.

regards

-- 
Tomas Vondra

Re: WAL compression setting after PostgreSQL LZ4 default change

On Fri, Jul 03, 2026 at 04:38:06PM +0800, Japin Li wrote:
> On Fri, 03 Jul 2026 at 11:58, Fujii Masao  wrote:
>> On Wed, Jul 1, 2026 at 8:45 AM Michael Paquier  wrote:
>>>
>>> On Tue, Jun 30, 2026 at 06:25:09PM +0800, wenhui qiu wrote:
>>> > The recent PostgreSQL commit changes the default TOAST compression to lz4
>>> > when LZ4 support is available, based on the rationale that LZ4 is generally
>>> > more efficient than pglz in terms of CPU usage and compression
>>> > ratio.  Given that, should we also consider changing the default
>>> > compression method used by wal_compression = on from pglz to lz4?
>>>
>>> "on" is just a backward-compatible value, so we could let it as-is.
>>
>> Yes, so +1 to leaving "on" as it is. Making its meaning depend on
>> the build options seems very confusing. Users who want lz4 or zstd
>> can simply specify those methods explicitly instead of using "on".
> 
> Understood.  I'll withdraw this from the CommitFest.

I disagree.  The whole argument is that most users don't bother
changing the defaults.  Here, a change between lz4/zstd and pglz is
like day and night; folks are going to see improvements all the time
if we switch to a better default when we can, in terms of CPU, memory
and IO.
--
Michael

Re: WAL compression setting after PostgreSQL LZ4 default change

Re: Jelte Fennema-Nio
> A small correction though: "on" isn't the default. The default is
> "off". So this would only impact users who changed the setting in
> their cluster to "on" at some point in the past when lz4/zstd support
> did not exist yet. So we wouldn't be changing the default. We'd be
> changing the behavior for people who changed their default setting at
> some point.

If someone just wants compression and not bother with the ugly details
of compression algorithms, they might be inclined to just say "on".
By labelling one of the choices with this "easy" value, we are
effectively promoting pglz as a good choice. (I'm definitely one of
those people, if you give me too many options, I will look for some
hint what to do or walk away. Now 3 isn't "too many" but you get the
idea.)

I would like to see "on" to be moved to some better value (likely lz4,
like toast compression), and if some day, there is yet a better
choice, we should move it again.

Christoph

Re: WAL compression setting after PostgreSQL LZ4 default change

On Tue, Jun 30, 2026 at 06:25:09PM +0800, wenhui qiu wrote:
> The recent PostgreSQL commit changes the default TOAST compression to lz4
> when LZ4 support is available, based on the rationale that LZ4 is generally
> more efficient than pglz in terms of CPU usage and compression
> ratio.  Given that, should we also consider changing the default
> compression method used by wal_compression = on from pglz to lz4?

"on" is just a backward-compatible value, so we could let it as-is.

> Currently, wal_compression = on still maps to pglz, while lz4 has to be
> selected explicitly with:
> 
> wal_compression = lz4
> If LZ4 is now considered stable and preferable enough to become the default
> for TOAST compression, it may be worth aligning WAL compression behavior as
> well, or at least discussing whether on should continue to imply pglz.

If I were switch the default, for me zstd goes first, lz4 is a close
second, and pglz is the last of its class.  The only reason why we
have not chosen zstd for TOAST is the fact that we don't support it
(trickier to add support for it as we have to preserve on-disk 
compatibility for inline compressed TOAST entries).

In short, lz4 is available in many environments, but I'm also ready to
bet that zstd is equally available in these environments.
--
Michael

Re: WAL compression setting after PostgreSQL LZ4 default change

On Thu, Jul 16, 2026 at 05:00:58PM +0200, Tomas Vondra wrote:
> My opinion is "on" should be a generic "pick compression" option, giving
> the database the mandate to pick an algorithm. A user who wants a
> particular algorithm can specify that.
>
> The only reason why "on" means "pglz" is that initially that was the
> only supported algorithm (until PG 15). But if we supported multiple
> algorithms from the beginning, would we do it that way? I don't think
> so. We'd either not have "on" at all, or it'd pick the best algorithm.

I have worked on both things.  I cannot go back in time, but I suspect
that I would have picked up pglz as default for "on" out of safety as
a start point.

A few releases later, where I know that many deployments are using
either lz4 or zstd and nobody has complained back, I see a much better
argument in changing the behavior of "on" to mean "zstd" -> "lz4" ->
"pglz" in order of priority, based on how efficient these algorithms
are known to be.
--
Michael

Re: WAL compression setting after PostgreSQL LZ4 default change

I still hope that we can make the switch for PG 19 so we don't have
two different versions flipping the toast and wal compression
algorithms.

Attached is v3 of wenhui's patch with some editorial changes in the
documentation and a commit message.

Christoph

Re: WAL compression setting after PostgreSQL LZ4 default change

Re: Andrey Borodin
> FWIW on 19beta2, one client: a seqscan setting hint bits on a freshly
> checkpointed 380MB table with wal_log_hints on, so ~47k full-page images
> and little else.  Best of three: off 765ms, pglz 1402ms, lz4 444ms, zstd
> 797ms, writing 382/105/107/67 MB of WAL (fsync off, so the I/O saving is
> understated).  lz4 is the only setting faster than no compression at all,
> while pglz costs nearly double for the size lz4 gives away for free.  zstd
> compresses a third better but pays for it in CPU, partly by allocating a
> ZSTD_CCtx per FPI - see the nearby thread "Compression of bigger WAL
> records".  +1 for "on" meaning lz4 where available.

v4 moves lz4 first.

(I did not include any reason in the commit message about choosing lz4
over zstd yet.)

One minor bit: The patch now has

#if defined(USE_LZ4)
#define DEFAULT_WAL_COMPRESSION·WAL_COMPRESSION_LZ4
#elif defined(USE_ZSTD)
#define DEFAULT_WAL_COMPRESSION·WAL_COMPRESSION_ZSTD

when the old one had
#ifdef USE_ZSTD
#elif defined(USE_LZ4)

I left it at "#if defined(USE_LZ4)" because that looks more
symmetrical.

Christoph

Re: WAL compression setting after PostgreSQL LZ4 default change

Re: Tomas Vondra
> +        The supported methods are off, on,
> +        lz4 (if PostgreSQL was compiled with ),
> +        zstd (if PostgreSQL was compiled with ), and
> +        pglz.
> +        The value on selects the first of zstd,
> +        lz4, pglz that is available.

>  #wal_compression = off                  # enables compression of full-page writes;
> -                                        # off, pglz (or "on"), lz4, or zstd
> +                                        # off, on, lz4, zstd, or pglz (on means the first
> +                                        # of zstd, lz4 and pglz, supported by the build)

I would put zstd before lz4 in both places as it's preferred over it.

Christoph

Re: WAL compression setting after PostgreSQL LZ4 default change

On Thu, Aug 06, 2026 at 04:31:44PM +0200, Hannu Krosing wrote:
> On Thu, Aug 6, 2026 at 2:11 PM Tomas Vondra  wrote:
> >
> > The executive summary is that lz4/zstd perform about the same, but zstd
> > has a clearly better compression ratio. I withdraw my initial plan to
> > default to lz4, and I agree we should default to zstd. (By "default" I
> > mean that "on" would use that algorithm, but the GUC would still default
> > to 'off' as before. Maybe we should reconsinder that for PG20, though?)
> 
> Do you have any ideas why recovery on Azure is 10-20% faster for lz4
> (and surprisingly also pglz) than for zstd ?
> 
> ---
> Hannu

Hi,

I know that lz4 has much higher decompression speed but I have no idea
why it would also be faster for pglz. Do they actually use it or call
lz4 under the hood?

My two cents is that since cloud CPU is much more expensive than cloud
storage we should pick the option that minimizes it. In the application
that I used, it was mostly to pull historic data so the key performance
metric was rapid decompression speed. It was the difference between a
sub-second screen draw and not. I would tend to lz4 even with the better
compression ratio from zstd.

Regards,
Ken

Re: WAL compression setting after PostgreSQL LZ4 default change

On Wed, 8 Jul 2026 at 00:26, Michael Paquier  wrote:
> I disagree.  The whole argument is that most users don't bother
> changing the defaults.  Here, a change between lz4/zstd and pglz is
> like day and night; folks are going to see improvements all the time
> if we switch to a better default when we can, in terms of CPU, memory
> and IO.

A small correction though: "on" isn't the default. The default is
"off". So this would only impact users who changed the setting in
their cluster to "on" at some point in the past when lz4/zstd support
did not exist yet. So we wouldn't be changing the default. We'd be
changing the behavior for people who changed their default setting at
some point.

To be clear, I agree with you that that's a reasonable thing to do; I
don't think any of those people actually want to burn CPU for no
reason. So they'd all be happy if we stopped doing that. pglz
basically has no right to be used anywhere in production systems IMO.

Re: WAL compression setting after PostgreSQL LZ4 default change

Re: WAL compression setting after PostgreSQL LZ4 default change

Re: WAL compression setting after PostgreSQL LZ4 default change

Re: WAL compression setting after PostgreSQL LZ4 default change

Re: WAL compression setting after PostgreSQL LZ4 default change

On Wed, Jul 1, 2026 at 8:45 AM Michael Paquier  wrote:
>
> On Tue, Jun 30, 2026 at 06:25:09PM +0800, wenhui qiu wrote:
> > The recent PostgreSQL commit changes the default TOAST compression to lz4
> > when LZ4 support is available, based on the rationale that LZ4 is generally
> > more efficient than pglz in terms of CPU usage and compression
> > ratio.  Given that, should we also consider changing the default
> > compression method used by wal_compression = on from pglz to lz4?
>
> "on" is just a backward-compatible value, so we could let it as-is.

Yes, so +1 to leaving "on" as it is. Making its meaning depend on
the build options seems very confusing. Users who want lz4 or zstd
can simply specify those methods explicitly instead of using "on".

Regards,

-- 
Fujii Masao

Re: WAL compression setting after PostgreSQL LZ4 default change

On 7/31/26 23:44, Tomas Vondra wrote:
> As for the zstd vs. lz4 - the patch put zstd first mostly because
> Michael prefers it over lz4. I don't think either of these is superior.
> AFAIK the algorithms make different trade offs - zstd tends to compress
> better, but uses (substantially) more CPU. I think it makes sense to
> default to lz4, it's clearly better than pglz, with low CPU usage.

+1 I think lz4 is the more sensible default due to the low CPU usage.

-- 
Andreas Karlsson
Percona

Re: WAL compression setting after PostgreSQL LZ4 default change

Hi,

On 2026-07-16 17:00:58 +0200, Tomas Vondra wrote:
> On 7/8/26 08:37, wenhui qiu wrote:
> > Hi 
> > 
> >     > I would like to see "on" to be moved to some better value (likely lz4,
> >     > like toast compression), and if some day, there is yet a better
> >     > choice, we should move it again.
> >     Yes, After PostgreSQL 19, |lz4| became a required dependency unless
> >     you compile it yourself with |--without-lz4|. In most
> >     environments, |lz4| is already installed, so along this code path
> >     the effective choice is essentially fixed to |lz4|.At cluster scale,
> >     it would make more sense to expose this parameter in the frontend as
> >     a boolean setting, rather than letting users choose from multiple
> >     compression options.
> > 
> > 
> > Thanks 
> 
> I've been reminded of this proposal by a message in another thread [1],
> which suggests this proposal (to adjust the wal_compression default) was
> not accepted.
> 
> Which seems strange to me, and I suspect it's a misunderstanding. From
> the discussion in this thread it seems to me that:
> 
> * the proposal is to change what 'on' means - currently it's always
> pglz, but the proposal would change this to the "best" available
> algorithm (so lz4)
> 
> * the wal_compression default would remain "off"
> 
> I think this proposal makes sense, and at least Michael and Christoph
> seem to agree with that.

+1 from me too.


I've seen way too many incidents where wal_compression was very useful but due
to the use of pglz slowed down crucial, single threaded, parts of the workload
down so much that it had to be disabled again.  We should not make it our
users responsibility to know that the 'on' maps to an almost unusably slow
compression algorithm.


The compatibility concerns are *much* higher for toast compression than for
wal compression, given that toast compression means that you have to build
with the chosen compression pretty much forever, given that the compressed
datum survive pg_upgrade. Whereas wal compression doesn't survive pg_upgrade (
and you probably could even get away with just running with
wal_compression=off for a while and then removing support for the relevant
compression method).

Greetings,

Andres Freund

Re: WAL compression setting after PostgreSQL LZ4 default change

On Fri, 03 Jul 2026 at 11:58, Fujii Masao  wrote:
> On Wed, Jul 1, 2026 at 8:45 AM Michael Paquier  wrote:
>>
>> On Tue, Jun 30, 2026 at 06:25:09PM +0800, wenhui qiu wrote:
>> > The recent PostgreSQL commit changes the default TOAST compression to lz4
>> > when LZ4 support is available, based on the rationale that LZ4 is generally
>> > more efficient than pglz in terms of CPU usage and compression
>> > ratio.  Given that, should we also consider changing the default
>> > compression method used by wal_compression = on from pglz to lz4?
>>
>> "on" is just a backward-compatible value, so we could let it as-is.
>
> Yes, so +1 to leaving "on" as it is. Making its meaning depend on
> the build options seems very confusing. Users who want lz4 or zstd
> can simply specify those methods explicitly instead of using "on".
>

Understood.  I'll withdraw this from the CommitFest.

> Regards,
>
> -- 
> Fujii Masao

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

Re: WAL compression setting after PostgreSQL LZ4 default change

On Tue, 30 Jun 2026 at 12:51, Christoph Berg  wrote:
> Re: wenhui qiu
>> The recent PostgreSQL commit changes the default TOAST compression to lz4
>> when LZ4 support is available, based on the rationale that LZ4 is generally
>> more efficient than pglz in terms of CPU usage and compression ratio.Given
>> that, should we also consider changing the default compression method used
>> by wal_compression = on from pglz to lz4?
>
> +1 from me. I would prefer this patch over the doc-only one that I
> sent in the other thread.
>
> This should go into PG19 so we don't have two different versions that
> flip the default from pglz to lz4.
>

+1.  LGTM.

> Christoph

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.