agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedWAL compression setting after PostgreSQL LZ4 default change
44+ messages / 14 participants
[nested] [flat]
* WAL compression setting after PostgreSQL LZ4 default change
@ 2026-06-30 10:25 wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 10:51 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 44+ messages in thread
From: wenhui qiu @ 2026-06-30 10:25 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Japin Li <japinli@hotmail.com>; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>; Michael Paquier <michael@paquier.xyz>
HI
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?
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.
Thanks
Attachments:
[application/octet-stream] v001-wal-compression-on-lz4.patch (2.9K, ../../CAGjGUAL1b=Mwd1SCvLbo+fivEr9KDpFcu4jmqKCZXwT=6CiiGQ@mail.gmail.com/3-v001-wal-compression-on-lz4.patch)
download | inline diff:
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 009295f20e9..c7107dec232 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3634,6 +3634,8 @@ include_dir 'conf.d'
was compiled with <option>--with-lz4</option>) and
<literal>zstd</literal> (if <productname>PostgreSQL</productname>
was compiled with <option>--with-zstd</option>).
+ The value <literal>on</literal> selects <literal>lz4</literal>, if
+ available; otherwise, it selects <literal>pglz</literal>.
The default value is <literal>off</literal>.
Only superusers and users with the appropriate <literal>SET</literal>
privilege can change this setting.
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 290ccbc543e..9efd34aafea 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -484,13 +484,13 @@ static const struct config_enum_entry wal_compression_options[] = {
#ifdef USE_ZSTD
{"zstd", WAL_COMPRESSION_ZSTD, false},
#endif
- {"on", WAL_COMPRESSION_PGLZ, false},
+ {"on", DEFAULT_WAL_COMPRESSION, false},
{"off", WAL_COMPRESSION_NONE, false},
- {"true", WAL_COMPRESSION_PGLZ, true},
+ {"true", DEFAULT_WAL_COMPRESSION, true},
{"false", WAL_COMPRESSION_NONE, true},
- {"yes", WAL_COMPRESSION_PGLZ, true},
+ {"yes", DEFAULT_WAL_COMPRESSION, true},
{"no", WAL_COMPRESSION_NONE, true},
- {"1", WAL_COMPRESSION_PGLZ, true},
+ {"1", DEFAULT_WAL_COMPRESSION, true},
{"0", WAL_COMPRESSION_NONE, true},
{NULL, 0, false}
};
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ac38cddaaf9..d3e060b2111 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -263,6 +263,7 @@
# (change requires restart)
#wal_compression = off # enables compression of full-page writes;
# off, pglz, lz4, zstd, or on
+ # (on = lz4 if available, else pglz)
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 437b4f32349..4dad4e47cbf 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -87,6 +87,16 @@ typedef enum WalCompression
WAL_COMPRESSION_ZSTD,
} WalCompression;
+/*
+ * Choose an appropriate default WAL compression method for wal_compression=on.
+ * If LZ4 is compiled in, use it; otherwise, use pglz.
+ */
+#ifdef USE_LZ4
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_LZ4
+#else
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_PGLZ
+#endif
+
/* Recovery states */
typedef enum RecoveryState
{
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-06-30 10:51 ` Christoph Berg <myon@debian.org>
2026-06-30 13:48 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
1 sibling, 1 reply; 44+ messages in thread
From: Christoph Berg @ 2026-06-30 10:51 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: pgsql-hackers; Japin Li <japinli@hotmail.com>; John Naylor <johncnaylorls@gmail.com>; Michael Paquier <michael@paquier.xyz>
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.
Christoph
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 10:51 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-06-30 13:48 ` Japin Li <japinli@hotmail.com>
0 siblings, 0 replies; 44+ messages in thread
From: Japin Li @ 2026-06-30 13:48 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>; Michael Paquier <michael@paquier.xyz>
On Tue, 30 Jun 2026 at 12:51, Christoph Berg <myon@debian.org> 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.
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-06-30 23:45 ` Michael Paquier <michael@paquier.xyz>
2026-07-01 01:53 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
1 sibling, 2 replies; 44+ messages in thread
From: Michael Paquier @ 2026-06-30 23:45 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: pgsql-hackers; Japin Li <japinli@hotmail.com>; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../akRVDdkH1ntCpTBv@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
@ 2026-07-01 01:53 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-03 02:18 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
1 sibling, 1 reply; 44+ messages in thread
From: wenhui qiu @ 2026-07-01 01:53 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: pgsql-hackers; Japin Li <japinli@hotmail.com>; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>
Hi Michael
>
> > 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.
> Thanks for the feedback.
>
> Yes, I did consider zstd as well. I agree that zstd can provide a better
> compression ratio than lz4, and if we only look at compression ratio, zstd
> is
> a very attractive choice.
>
> The reason I started with lz4 was mainly to keep this change aligned with
> the
> recent TOAST compression change. Since lz4 has already been accepted there
> as
> the preferred default when available, I thought using the same choice for
> wal_compression=on would make the proposal smaller, more consistent, and
> easier
> to evaluate. so
>
> Second, WAL compression happens on the WAL insertion path for full-page
> images,
> so compression speed and CPU overhead matter a lot. lz4 is generally a
> good fit
> for that trade-off: it still reduces WAL volume, while keeping compression
> latency and CPU cost lower than zstd in many workloads.
>
> That said, I understand the argument for preferring zstd when it is
> available.
> I have prepared an alternative version where wal_compression=on selects
> zstd
> first, then lz4 if zstd is not available, and finally pglz as the fallback
>
Thanks
Attachments:
[application/octet-stream] v002-wal-compression-on-zstd-lz4.patch (3.1K, ../../CAGjGUAJaGmTwSSiF2so69Pjmjo4zwhsA+dmcUGRmqM856K9pBA@mail.gmail.com/3-v002-wal-compression-on-zstd-lz4.patch)
download | inline diff:
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 009295f20e9..2282c9e99b8 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3634,6 +3634,9 @@ include_dir 'conf.d'
was compiled with <option>--with-lz4</option>) and
<literal>zstd</literal> (if <productname>PostgreSQL</productname>
was compiled with <option>--with-zstd</option>).
+ The value <literal>on</literal> selects <literal>zstd</literal>, if
+ available; otherwise, it selects <literal>lz4</literal>, if available;
+ otherwise, it selects <literal>pglz</literal>.
The default value is <literal>off</literal>.
Only superusers and users with the appropriate <literal>SET</literal>
privilege can change this setting.
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 290ccbc543e..9efd34aafea 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -484,13 +484,13 @@ static const struct config_enum_entry wal_compression_options[] = {
#ifdef USE_ZSTD
{"zstd", WAL_COMPRESSION_ZSTD, false},
#endif
- {"on", WAL_COMPRESSION_PGLZ, false},
+ {"on", DEFAULT_WAL_COMPRESSION, false},
{"off", WAL_COMPRESSION_NONE, false},
- {"true", WAL_COMPRESSION_PGLZ, true},
+ {"true", DEFAULT_WAL_COMPRESSION, true},
{"false", WAL_COMPRESSION_NONE, true},
- {"yes", WAL_COMPRESSION_PGLZ, true},
+ {"yes", DEFAULT_WAL_COMPRESSION, true},
{"no", WAL_COMPRESSION_NONE, true},
- {"1", WAL_COMPRESSION_PGLZ, true},
+ {"1", DEFAULT_WAL_COMPRESSION, true},
{"0", WAL_COMPRESSION_NONE, true},
{NULL, 0, false}
};
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ac38cddaaf9..43cb28fce48 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -263,6 +263,8 @@
# (change requires restart)
#wal_compression = off # enables compression of full-page writes;
# off, pglz, lz4, zstd, or on
+ # (on = zstd if available, else lz4 if
+ # available, else pglz)
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 437b4f32349..a7c46352b30 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -87,6 +87,19 @@ typedef enum WalCompression
WAL_COMPRESSION_ZSTD,
} WalCompression;
+/*
+ * Choose an appropriate default WAL compression method for wal_compression=on.
+ * Prefer zstd when compiled in; otherwise use lz4 if available, falling back
+ * to pglz.
+ */
+#ifdef USE_ZSTD
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_ZSTD
+#elif defined(USE_LZ4)
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_LZ4
+#else
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_PGLZ
+#endif
+
/* Recovery states */
typedef enum RecoveryState
{
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-01 01:53 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-07-03 02:18 ` Japin Li <japinli@hotmail.com>
0 siblings, 0 replies; 44+ messages in thread
From: Japin Li @ 2026-07-03 02:18 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>
On Wed, 01 Jul 2026 at 09:53, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
> Hi Michael
>
> > 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.
> Thanks for the feedback.
>
> Yes, I did consider zstd as well. I agree that zstd can provide a better
> compression ratio than lz4, and if we only look at compression ratio, zstd is
> a very attractive choice.
>
> The reason I started with lz4 was mainly to keep this change aligned with the
> recent TOAST compression change. Since lz4 has already been accepted there as
> the preferred default when available, I thought using the same choice for
> wal_compression=on would make the proposal smaller, more consistent, and easier
> to evaluate. so
>
> Second, WAL compression happens on the WAL insertion path for full-page images,
> so compression speed and CPU overhead matter a lot. lz4 is generally a good fit
> for that trade-off: it still reduces WAL volume, while keeping compression
> latency and CPU cost lower than zstd in many workloads.
>
> That said, I understand the argument for preferring zstd when it is available.
> I have prepared an alternative version where wal_compression=on selects zstd
> first, then lz4 if zstd is not available, and finally pglz as the fallback
>
> Thanks
Thanks for updating the patch. I've registered it in the CommitFest [1].
[1] https://commitfest.postgresql.org/patch/6979/
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
@ 2026-07-03 02:58 ` Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
1 sibling, 1 reply; 44+ messages in thread
From: Fujii Masao @ 2026-07-03 02:58 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; pgsql-hackers; Japin Li <japinli@hotmail.com>; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>
On Wed, Jul 1, 2026 at 8:45 AM Michael Paquier <michael@paquier.xyz> 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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
@ 2026-07-03 08:38 ` Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 44+ messages in thread
From: Japin Li @ 2026-07-03 08:38 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; wenhui qiu <qiuwenhuifx@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>
On Fri, 03 Jul 2026 at 11:58, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Wed, Jul 1, 2026 at 8:45 AM Michael Paquier <michael@paquier.xyz> 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.
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
@ 2026-07-07 22:26 ` Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
0 siblings, 1 reply; 44+ messages in thread
From: Michael Paquier @ 2026-07-07 22:26 UTC (permalink / raw)
To: Japin Li <japinli@hotmail.com>; +Cc: Fujii Masao <masao.fujii@gmail.com>; wenhui qiu <qiuwenhuifx@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>
On Fri, Jul 03, 2026 at 04:38:06PM +0800, Japin Li wrote:
> On Fri, 03 Jul 2026 at 11:58, Fujii Masao <masao.fujii@gmail.com> wrote:
>> On Wed, Jul 1, 2026 at 8:45 AM Michael Paquier <michael@paquier.xyz> 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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../ak19GnnQE46n6myy@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
@ 2026-07-07 22:46 ` Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 44+ messages in thread
From: Jelte Fennema-Nio @ 2026-07-07 22:46 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; wenhui qiu <qiuwenhuifx@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>; Christoph Berg <myon@debian.org>
On Wed, 8 Jul 2026 at 00:26, Michael Paquier <michael@paquier.xyz> 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.
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
@ 2026-07-07 22:58 ` Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 1 reply; 44+ messages in thread
From: Christoph Berg @ 2026-07-07 22:58 UTC (permalink / raw)
To: Jelte Fennema-Nio <postgres@jeltef.nl>; +Cc: Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; wenhui qiu <qiuwenhuifx@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-07-08 06:37 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 1 reply; 44+ messages in thread
From: wenhui qiu @ 2026-07-08 06:37 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; wenhui qiu <qiuwenhuifx@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-07-16 15:00 ` Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-16 22:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 44+ messages in thread
From: Tomas Vondra @ 2026-07-16 15:00 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; Christoph Berg <myon@debian.org>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-07-16 15:08 ` Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-31 12:55 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
1 sibling, 2 replies; 44+ messages in thread
From: Andres Freund @ 2026-07-16 15:08 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Christoph Berg <myon@debian.org>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
@ 2026-07-18 20:28 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
1 sibling, 1 reply; 44+ messages in thread
From: Laurenz Albe @ 2026-07-18 20:28 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; Tomas Vondra <tomas@vondra.me>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Christoph Berg <myon@debian.org>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
@ 2026-07-20 06:23 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 44+ messages in thread
From: wenhui qiu @ 2026-07-20 06:23 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Andres Freund <andres@anarazel.de>; Tomas Vondra <tomas@vondra.me>; Christoph Berg <myon@debian.org>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
HI
> > 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.
>
> V2 path does exactly that.
>
> > 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.
>
> The feature was frozen in April. It won't be too late to include it in
> Version 20—better late than never.
>
>
Thanks
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-07-31 12:35 ` Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 1 reply; 44+ messages in thread
From: Christoph Berg @ 2026-07-31 12:35 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Tomas Vondra <tomas@vondra.me>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
Attachments:
[text/x-diff] v3-0001-Change-wal_compression-on-to-the-first-of-zstd-lz.patch (5.5K, ../../amyWkBMR4hKVUZFv@msg.df7cb.de/2-v3-0001-Change-wal_compression-on-to-the-first-of-zstd-lz.patch)
download | inline diff:
From 66379fc0738a86daeb4e9ba5b0bc0d00f4770813 Mon Sep 17 00:00:00 2001
From: Christoph Berg <myon@debian.org>
Date: Fri, 31 Jul 2026 14:24:23 +0200
Subject: [PATCH v3] Change wal_compression=on to the first of zstd, lz4, pglz
Previously, wal_compression=on was an alias for pglz, with the idea that
users could make an informed choice to pick a better option. But in
practice, users either don't want to make that choice (they just want
WAL compression), can't really pick the best option (they are setting
the default in an environment where someone else is running the
workload), or simply don't know that there are other options
(previously, "on" was the only choice available).
In all these cases, mapping "on" to something that isn't pglz is better.
It is strictly slower and does not compress better. We pick zstd here.
Users can still make the informed choice and switch to lz4 if their
workload works better there, but zstd is still better than pglz in that
case.
Like for the default TOAST compression, the actual default "on" value
depends on if PostgreSQL was compiled with the compression algorithms.
The first of zstd, lz4, pglz is picked. (Only the latter is always
available, but practically, builds will have at least one of the better
algorithms.)
---
doc/src/sgml/config.sgml | 12 ++++++------
src/backend/utils/misc/guc_tables.c | 8 ++++----
src/backend/utils/misc/postgresql.conf.sample | 3 ++-
src/include/access/xlog.h | 13 +++++++++++++
4 files changed, 25 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index aa7b1bd75d2..9bfe0443848 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3667,12 +3667,12 @@ include_dir 'conf.d'
<xref linkend="guc-full-page-writes"/> is on, during a base backup,
etc.).
A compressed page image will be decompressed during WAL replay.
- The supported methods are <literal>pglz</literal>,
- <literal>lz4</literal> (if <productname>PostgreSQL</productname>
- was compiled with <option>--with-lz4</option>) and
- <literal>zstd</literal> (if <productname>PostgreSQL</productname>
- was compiled with <option>--with-zstd</option>).
- The value <literal>on</literal> is a historical spelling of <literal>pglz</literal>.
+ The supported methods are <literal>off</literal>, <literal>on</literal>,
+ <literal>zstd</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-zstd</option>)
+ <literal>lz4</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-lz4</option>), and
+ <literal>pglz</literal>.
+ The value <literal>on</literal> selects the first of <literal>zstd</literal>,
+ <literal>lz4</literal>, <literal>pglz</literal> that is available.
The default value is <literal>off</literal>.
Only superusers and users with the appropriate <literal>SET</literal>
privilege can change this setting.
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 1ec460b6a82..e0b21053287 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -484,13 +484,13 @@ static const struct config_enum_entry wal_compression_options[] = {
#ifdef USE_ZSTD
{"zstd", WAL_COMPRESSION_ZSTD, false},
#endif
- {"on", WAL_COMPRESSION_PGLZ, false},
+ {"on", DEFAULT_WAL_COMPRESSION, false},
{"off", WAL_COMPRESSION_NONE, false},
- {"true", WAL_COMPRESSION_PGLZ, true},
+ {"true", DEFAULT_WAL_COMPRESSION, true},
{"false", WAL_COMPRESSION_NONE, true},
- {"yes", WAL_COMPRESSION_PGLZ, true},
+ {"yes", DEFAULT_WAL_COMPRESSION, true},
{"no", WAL_COMPRESSION_NONE, true},
- {"1", WAL_COMPRESSION_PGLZ, true},
+ {"1", DEFAULT_WAL_COMPRESSION, true},
{"0", WAL_COMPRESSION_NONE, true},
{NULL, 0, false}
};
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 7958653077b..9d178fb09c1 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -262,7 +262,8 @@
#wal_log_hints = off # also do full page writes of non-critical updates
# (change requires restart)
#wal_compression = off # enables compression of full-page writes;
- # off, pglz (or "on"), lz4, or zstd
+ # off, on, zstd, lz4, or pglz (on = zstd if
+ # available, else lz4 if available, else pglz)
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 4dd98624204..9259b3461c7 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -87,6 +87,19 @@ typedef enum WalCompression
WAL_COMPRESSION_ZSTD,
} WalCompression;
+/*
+ * Choose an appropriate default WAL compression method for wal_compression=on.
+ * Prefer zstd when compiled in; otherwise use lz4 if available, falling back
+ * to pglz.
+ */
+#ifdef USE_ZSTD
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_ZSTD
+#elif defined(USE_LZ4)
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_LZ4
+#else
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_PGLZ
+#endif
+
/* Recovery states */
typedef enum RecoveryState
{
--
2.53.0
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-07-31 13:04 ` Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 44+ messages in thread
From: Tomas Vondra @ 2026-07-31 13:04 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On 7/31/26 14:35, Christoph Berg wrote:
> 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.
>
Yeah. I wonder if it's too late for that, but perhaps not. I wouldn't
mind committing it, it's not a huge/complex patch. But RMT should
probably have a say in this.
> Attached is v3 of wenhui's patch with some editorial changes in the
> documentation and a commit message.
>
The one thing I don't quite like on the patch is that it actually
defaults to zstd before lz4. Which seems to not quite match the intent
to have both toast and wal default to the same thing. I don't have a
strong preference for either algorithm, but I also don't see why to put
zstd before lz4. I'd do it the other way around, i.e. lz4->zstd->pglz.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-07-31 13:10 ` Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
0 siblings, 1 reply; 44+ messages in thread
From: Christoph Berg @ 2026-07-31 13:10 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
Re: Tomas Vondra
> The one thing I don't quite like on the patch is that it actually
> defaults to zstd before lz4. Which seems to not quite match the intent
> to have both toast and wal default to the same thing. I don't have a
> strong preference for either algorithm, but I also don't see why to put
> zstd before lz4. I'd do it the other way around, i.e. lz4->zstd->pglz.
About the intent, I see it more as "PG19 is the version that switched
away from pglz". It could be lz4 for both, or it could be lz4 for
TOAST and zstd for WAL.
There is some value in having both the same, but I'd defer to what the
others are saying.
I don't have a preference either way. This was just the last thing
expressed here. (If we can't agree on that point, could we please
still make the change and just toss a coin about this "bit"? :D)
Christoph
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-07-31 14:58 ` Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 44+ messages in thread
From: Andrey Borodin @ 2026-07-31 14:58 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Tomas Vondra <tomas@vondra.me>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
> On 31 Jul 2026, at 18:10, Christoph Berg <myon@debian.org> wrote:
>
> zstd for WAL
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.
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
@ 2026-07-31 15:50 ` Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 1 reply; 44+ messages in thread
From: Christoph Berg @ 2026-07-31 15:50 UTC (permalink / raw)
To: Andrey Borodin <x4mmm@yandex-team.ru>; +Cc: Tomas Vondra <tomas@vondra.me>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
Attachments:
[text/x-diff] v4-0001-Change-wal_compression-on-to-the-first-of-lz4-zst.patch (5.5K, ../../amzEUaGtr8JsMVtR@msg.df7cb.de/2-v4-0001-Change-wal_compression-on-to-the-first-of-lz4-zst.patch)
download | inline diff:
From d23256ab8c8ce76a671be5415b5a51f3ce9fd8f1 Mon Sep 17 00:00:00 2001
From: Christoph Berg <myon@debian.org>
Date: Fri, 31 Jul 2026 14:24:23 +0200
Subject: [PATCH v4] Change wal_compression=on to the first of lz4, zstd, pglz
Previously, wal_compression=on was an alias for pglz, with the idea that
users could make an informed choice to pick a better option. But in
practice, users either don't want to make that choice (they just want
WAL compression), can't really pick the best option (they are setting
the default in an environment where someone else is running the
workload), or simply don't know that there are other options
(previously, "on" was the only choice available).
In all these cases, mapping "on" to something that isn't pglz is better.
It is strictly slower and does not compress better. We pick lz4 here.
Users can still make the informed choice and switch to zstd if their
workload works better there, but lz4 is still better than pglz in that
case.
Like for the default TOAST compression, the actual default "on" value
depends on if PostgreSQL was compiled with the compression algorithms.
The first of lz4, zstd, pglz is picked. (Only the latter is always
available, but practically, builds will have at least one of the better
algorithms.)
---
doc/src/sgml/config.sgml | 12 ++++++------
src/backend/utils/misc/guc_tables.c | 8 ++++----
src/backend/utils/misc/postgresql.conf.sample | 3 ++-
src/include/access/xlog.h | 13 +++++++++++++
4 files changed, 25 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index aa7b1bd75d2..39ec64a611a 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3667,12 +3667,12 @@ include_dir 'conf.d'
<xref linkend="guc-full-page-writes"/> is on, during a base backup,
etc.).
A compressed page image will be decompressed during WAL replay.
- The supported methods are <literal>pglz</literal>,
- <literal>lz4</literal> (if <productname>PostgreSQL</productname>
- was compiled with <option>--with-lz4</option>) and
- <literal>zstd</literal> (if <productname>PostgreSQL</productname>
- was compiled with <option>--with-zstd</option>).
- The value <literal>on</literal> is a historical spelling of <literal>pglz</literal>.
+ The supported methods are <literal>off</literal>, <literal>on</literal>,
+ <literal>lz4</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-lz4</option>),
+ <literal>zstd</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-zstd</option>), and
+ <literal>pglz</literal>.
+ The value <literal>on</literal> selects the first of <literal>lz4</literal>,
+ <literal>zstd</literal>, <literal>pglz</literal> that is available.
The default value is <literal>off</literal>.
Only superusers and users with the appropriate <literal>SET</literal>
privilege can change this setting.
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 1ec460b6a82..e0b21053287 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -484,13 +484,13 @@ static const struct config_enum_entry wal_compression_options[] = {
#ifdef USE_ZSTD
{"zstd", WAL_COMPRESSION_ZSTD, false},
#endif
- {"on", WAL_COMPRESSION_PGLZ, false},
+ {"on", DEFAULT_WAL_COMPRESSION, false},
{"off", WAL_COMPRESSION_NONE, false},
- {"true", WAL_COMPRESSION_PGLZ, true},
+ {"true", DEFAULT_WAL_COMPRESSION, true},
{"false", WAL_COMPRESSION_NONE, true},
- {"yes", WAL_COMPRESSION_PGLZ, true},
+ {"yes", DEFAULT_WAL_COMPRESSION, true},
{"no", WAL_COMPRESSION_NONE, true},
- {"1", WAL_COMPRESSION_PGLZ, true},
+ {"1", DEFAULT_WAL_COMPRESSION, true},
{"0", WAL_COMPRESSION_NONE, true},
{NULL, 0, false}
};
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 7958653077b..ff108e3d3a5 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -262,7 +262,8 @@
#wal_log_hints = off # also do full page writes of non-critical updates
# (change requires restart)
#wal_compression = off # enables compression of full-page writes;
- # off, pglz (or "on"), lz4, or zstd
+ # off, on, lz4, zstd, or pglz (on = lz4 if
+ # available, else zstd if available, else pglz)
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 4dd98624204..8daf24d93cf 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -87,6 +87,19 @@ typedef enum WalCompression
WAL_COMPRESSION_ZSTD,
} WalCompression;
+/*
+ * Choose an appropriate default WAL compression method for wal_compression=on.
+ * Prefer lz4 when compiled in; otherwise use zstd if available, falling back
+ * to pglz.
+ */
+#if defined(USE_LZ4)
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_LZ4
+#elif defined(USE_ZSTD)
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_ZSTD
+#else
+#define DEFAULT_WAL_COMPRESSION WAL_COMPRESSION_PGLZ
+#endif
+
/* Recovery states */
typedef enum RecoveryState
{
--
2.53.0
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-07-31 21:44 ` Tomas Vondra <tomas@vondra.me>
2026-07-31 22:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
0 siblings, 2 replies; 44+ messages in thread
From: Tomas Vondra @ 2026-07-31 21:44 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-07-31 22:50 ` Christoph Berg <myon@debian.org>
2026-08-01 01:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-08-01 08:42 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
1 sibling, 2 replies; 44+ messages in thread
From: Christoph Berg @ 2026-07-31 22:50 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
Re: Tomas Vondra
> 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.
Thanks!
> 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.
Yes, lz4 makes sense to me now. zstd is the "can I tune it harder" pick.
Christoph
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 22:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-08-01 01:44 ` Japin Li <japinli@hotmail.com>
1 sibling, 0 replies; 44+ messages in thread
From: Japin Li @ 2026-08-01 01:44 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Tomas Vondra <tomas@vondra.me>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On Sat, 01 Aug 2026 at 00:50, Christoph Berg <myon@debian.org> wrote:
> Re: Tomas Vondra
>> 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.
>
> Thanks!
>
>> 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.
>
> Yes, lz4 makes sense to me now. zstd is the "can I tune it harder" pick.
>
How about adding a comment or documentation to clarify the rationale for
choosing lz4 over zstd?
> Christoph
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 22:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-08-01 08:42 ` Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 44+ messages in thread
From: Michael Paquier @ 2026-08-01 08:42 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; Tomas Vondra <tomas@vondra.me>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On Sat, Aug 01, 2026 at 12:50:45AM +0200, Christoph Berg wrote:
> Re: Tomas Vondra
>> 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.
>
> Yes, lz4 makes sense to me now. zstd is the "can I tune it harder" pick.
In my experience, users care more about I/O than CPU in this area, and
zstd means less I/O, especially with the rising cost of memory and
disk lately, even if it costs with a bit more CPU to compression more.
Then, let's look at a few things out there:
- At [1], Azure defaults to zstd.
- At [2], AWS/RDS defaults to zstd since v15.
- At [3], Google Cloud is off by default.
Even after all these arguments, I guess it's your call if you wish to
apply this patch and change the default to be different than what I
know are the major players in the market. I just see no lz4. lz4 is
better than pglz for sure. :)
[1]: https://learn.microsoft.com/en-us/azure/postgresql/parameters/parameters-write-ahead-log-settings?pi...
[2]: https://aws.amazon.com/blogs/database/impactful-features-in-postgresql-15/
[3]: https://docs.cloud.google.com/sql/docs/postgres/flags?hl=en
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../am2xgwQz5umg2QZz@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-03 08:26 ` Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
1 sibling, 1 reply; 44+ messages in thread
From: Andreas Karlsson @ 2026-08-03 08:26 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
@ 2026-08-03 13:36 ` Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 1 reply; 44+ messages in thread
From: Hannu Krosing @ 2026-08-03 13:36 UTC (permalink / raw)
To: Andreas Karlsson <andreas@proxel.se>; +Cc: Tomas Vondra <tomas@vondra.me>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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 :) )
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.
On Mon, Aug 3, 2026 at 10:26 AM Andreas Karlsson <andreas@proxel.se> wrote:
>
> 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
>
>
>
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
@ 2026-08-03 20:36 ` Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
0 siblings, 1 reply; 44+ messages in thread
From: Tomas Vondra @ 2026-08-03 20:36 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; +Cc: Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-04 07:03 ` Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 1 reply; 44+ messages in thread
From: Hannu Krosing @ 2026-08-04 07:03 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On Mon, Aug 3, 2026 at 10:36 PM Tomas Vondra <tomas@vondra.me> wrote:
>
> 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.
Yeah, I re-checked and all the claims I remembered were from mailing
list discussions, not official docs.
> 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.
It's a bit more complicated than that - we use zstd level 1 while the
default for the zstd command-line utility is level 3
We should keep this in mind when running any manual compression tests.
My recollection from running some manual tests a year or two ago is
that on compression side there was a fair bit of overlap in speed
between lz4 and zstd when looking across many compression levels.
However, lz4 was a clear winner in decompression speeds, being 2-3
times faster than zstd.
----
Hannu Krosing
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
@ 2026-08-04 10:01 ` Tomas Vondra <tomas@vondra.me>
2026-08-04 11:15 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 2 replies; 44+ messages in thread
From: Tomas Vondra @ 2026-08-04 10:01 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; +Cc: Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On 8/4/26 09:03, Hannu Krosing wrote:
> On Mon, Aug 3, 2026 at 10:36 PM Tomas Vondra <tomas@vondra.me> wrote:
>>
>> 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.
>
> Yeah, I re-checked and all the claims I remembered were from mailing
> list discussions, not official docs.
>
>> 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.
>
> It's a bit more complicated than that - we use zstd level 1 while the
> default for the zstd command-line utility is level 3
>
> We should keep this in mind when running any manual compression tests.
>
Are you sure? Because I see xloginsert.c does this:
len = ZSTD_compress(dest, COMPRESS_BUFSIZE, source, orig_len,
ZSTD_CLEVEL_DEFAULT);
and ZSTD_CLEVEL_DEFAULT (=3) is the constant from zstd library. So at
least for WAL we should be using the "normal" compression level.
> My recollection from running some manual tests a year or two ago is
> that on compression side there was a fair bit of overlap in speed
> between lz4 and zstd when looking across many compression levels.
>
> However, lz4 was a clear winner in decompression speeds, being 2-3
> times faster than zstd.
>
Seems plausible.
I'm running some simple tests, to get a better idea how the algorithms
compare. I have no intention to tune the current compression levels.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-04 11:15 ` Hannu Krosing <hannuk@google.com>
1 sibling, 0 replies; 44+ messages in thread
From: Hannu Krosing @ 2026-08-04 11:15 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On Tue, Aug 4, 2026 at 12:01 PM Tomas Vondra <tomas@vondra.me> wrote:
>
> On 8/4/26 09:03, Hannu Krosing wrote:
> > On Mon, Aug 3, 2026 at 10:36 PM Tomas Vondra <tomas@vondra.me> wrote:
...
> > It's a bit more complicated than that - we use zstd level 1 while the
> > default for the zstd command-line utility is level 3
> >
> > We should keep this in mind when running any manual compression tests.
> >
>
> Are you sure? Because I see xloginsert.c does this:
>
> len = ZSTD_compress(dest, COMPRESS_BUFSIZE, source, orig_len,
> ZSTD_CLEVEL_DEFAULT);
>
> and ZSTD_CLEVEL_DEFAULT (=3) is the constant from zstd library. So at
> least for WAL we should be using the "normal" compression level.
Yes, you are right.
I was confused by development-time list discussions which mostly
talked about using level=1, even though the default was 3 (or even 6)
---
Hannu
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-06 12:11 ` Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
1 sibling, 1 reply; 44+ messages in thread
From: Tomas Vondra @ 2026-08-06 12:11 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; +Cc: Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On 8/4/26 12:01, Tomas Vondra wrote:
> On 8/4/26 09:03, Hannu Krosing wrote:
>> On Mon, Aug 3, 2026 at 10:36 PM Tomas Vondra <tomas@vondra.me> wrote:
>>>
>>> On 8/3/26 15:36, Hannu Krosing wrote:
>> ...
>>
>> My recollection from running some manual tests a year or two ago is
>> that on compression side there was a fair bit of overlap in speed
>> between lz4 and zstd when looking across many compression levels.
>>
>> However, lz4 was a clear winner in decompression speeds, being 2-3
>> times faster than zstd.
>>
>
> Seems plausible.
>
> I'm running some simple tests, to get a better idea how the algorithms
> compare. I have no intention to tune the current compression levels.
>
OK, so here's some results of the benchmarks I ran to get a better idea
how the compression algorithms / levels compare.
I was not interested in microbenchmarks comparing the algorithms in
isolation on synthetic data, but in more realistic tests. I ran two
tests, on OLTP (pgbench -N) and TPC-H data sets.
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?)
The test scripts (and results) are available here:
* https://github.com/tvondra/compression-charts-oltp
* https://github.com/tvondra/compression-charts-tpch
Both tests compare these compression options:
- off
- pglz
- lz4 with acceleration 1 5 10 30 50 (default is "1")
- zstd with level 1 3 5 10 16 (default is "3")
Attached is a WIP patch adding "wal_compression_level" GUC to allow
setting the level (only for testing, not meant for commit).
For OLTP, the script does this (for each compression algo/level):
1) sets up a new cluster, with a replica (and shuts the replica down)
2) initializes a pgbench database
3) reindexes the database
4) pgbench -N (with fixed number of transactions)
5) disables FPW
6) pgbench -N (with fixed number of transactions)
7) starts replica, measures recovery time
For TPC-H, it's similar:
1) sets up a new cluster, with a replica (and shuts the replica down)
2) loads 10GB TPC-H data set
3) ALTER to create primary keys etc.
4) creates secondary indexes
5) VACUUM FREEZE
6) starts replica, measures recovery time
For each interesting step, the script measures:
- amount of WAL written
- duration
- CPU usage (from vmstat)
- throughput (for pgbench)
I ran this on 4 local/cloud machines, with different CPUs etc.:
- ryzen 9900X (~2024, local)
- xeon e5-2699v4 (~2016, local)
- azure-1 (Xeon 6973P-C, cloud)
- azure-2 (EPYC 9V74, cloud)
There are some differences (esp. the old Xeon behaves a bit differently,
but let's aim for new CPUs).
Note: All of this was with local storage and local replication. I expect
remote storage/replication to benefit from higher compression ratios. I
also ran the tests with fsync=off, but the behavior is about the same.
Attached is a couple PDFs plotting a couple representative results. I
use scatter plots, with WAL vs. duration / CPU usage / throughput. that
allows comparing by the compression ratio and the other parameter.
For lz4/zstd, the charts show a couple data points connected by a line,
for the various compression levels.
I'm not going to discuss the charts one by one, that'd make this message
way too long and repetitive. But let me share a couple observations that
apply to all of them:
* zstd has a clearly better compression ratio - it often cuts the WAL
amount in half, compared to lz4 (with default compression levels)
* zstd has higher CPU usage, but the difference is tiny (within 1% for
the whole machine), so I consider this difference irrelevant
* both zstd and lz4 are "comparable" with pglz in terms of compression
ratio (lz4 a little worse, zstd a little better), and much better in
timing (both lz4/zstd are much faster, for reasonable levels)
* for lz4, the "acceleration" is pretty useless - it reduces the
compression ratio, but the speedup is negligible (so not worth it)
* for zstd, the "level" makes a bigger difference, but 1-5 is about the
same (same compression ratio/duration), but higher levels are not worth
really it (level=16 makes it 6x slower, for saving ~25% space)
So to conclude:
* zstd seems like the right default for 'on' - clearly better than pglz,
both in compression and CPU costs
* lz4 is a good choice too - significantly faster than pglz, but a bit
worse compression ratio
* the default zstd compression level (i.e. 3) is a good choice - there's
not much difference between 1 to 3, higher values don't seem like a good
trade off (diminishing returns)
* the LZ4 acceleration seems rather useless, not faster, but much worse
compression ratio
Based on this, I agree with the original suggestion to default to
zstd > lz4 > pglz
I don't think we need to "tune" the compression levels for either of the
algorithms. The defaults seem pretty reasonable.
regards
--
Tomas Vondra
Attachments:
[application/pdf] oltp-pgbench-throughput-wal-1000-on.pdf (18.0K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/2-oltp-pgbench-throughput-wal-1000-on.pdf)
download
[application/pdf] oltp-recovery-duration-wal-1000-on.pdf (17.8K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/3-oltp-recovery-duration-wal-1000-on.pdf)
download
[application/pdf] oltp-reindex-duration-wal-1000-on.pdf (17.5K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/4-oltp-reindex-duration-wal-1000-on.pdf)
download
[application/pdf] tpch-recovery-duration-wal-10-on.pdf (17.9K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/5-tpch-recovery-duration-wal-10-on.pdf)
download
[application/pdf] tpch-vacuum-cpu-wal-10-on.pdf (18.4K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/6-tpch-vacuum-cpu-wal-10-on.pdf)
download
[application/pdf] tpch-vacuum-duration-wal-10-on.pdf (17.9K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/7-tpch-vacuum-duration-wal-10-on.pdf)
download
[application/pdf] tpch-indexes-duration-wal-10-on.pdf (17.7K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/8-tpch-indexes-duration-wal-10-on.pdf)
download
[text/x-patch] wal-compress-level.patch (3.4K, ../../3ebe2c65-7fe5-49cb-ad49-84c1d0cf567b@vondra.me/9-wal-compress-level.patch)
download | inline diff:
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index f8b939853e9..f4ad7b98400 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -129,6 +129,7 @@ bool EnableHotStandby = false;
bool fullPageWrites = true;
bool wal_log_hints = false;
int wal_compression = WAL_COMPRESSION_NONE;
+int wal_compression_level = -1;
char *wal_consistency_checking_string = NULL;
bool *wal_consistency_checking = NULL;
bool wal_init_zero = true;
diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c
index f2e10b82b7d..fb42f9684e2 100644
--- a/src/backend/access/transam/xloginsert.c
+++ b/src/backend/access/transam/xloginsert.c
@@ -1053,8 +1053,20 @@ XLogCompressBackupBlock(const PageData *page, uint16 hole_offset, uint16 hole_le
case WAL_COMPRESSION_LZ4:
#ifdef USE_LZ4
- len = LZ4_compress_default(source, dest, orig_len,
- COMPRESS_BUFSIZE);
+
+ /* LZ4 default compression level is 1 */
+ if (wal_compression_level == -1)
+ wal_compression_level = 1;
+
+ /*
+ * LZ4 has compression level 1-65537, but we cap it as 22, same
+ * as zstd (good enough for our testing)
+ */
+ wal_compression_level = Max(1, Min(100, wal_compression_level));
+
+ len = LZ4_compress_fast(source, dest, orig_len,
+ COMPRESS_BUFSIZE,
+ wal_compression_level);
if (len <= 0)
len = -1; /* failure */
#else
@@ -1064,8 +1076,15 @@ XLogCompressBackupBlock(const PageData *page, uint16 hole_offset, uint16 hole_le
case WAL_COMPRESSION_ZSTD:
#ifdef USE_ZSTD
+ /* ZSTD default compression level is 3 */
+ if (wal_compression_level == -1)
+ wal_compression_level = 3;
+
+ /* ZSTD has compression level 1-22 */
+ wal_compression_level = Max(1, Min(22, wal_compression_level));
+
len = ZSTD_compress(dest, COMPRESS_BUFSIZE, source, orig_len,
- ZSTD_CLEVEL_DEFAULT);
+ wal_compression_level);
if (ZSTD_isError(len))
len = -1; /* failure */
#else
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index adb72361ce0..505c7d1627b 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -3464,6 +3464,14 @@
options => 'wal_compression_options',
},
+{ name => 'wal_compression_level', type => 'int', context => 'PGC_SUSET', group => 'WAL_SETTINGS',
+ short_desc => 'compression level for wal_compression zstd/lz4',
+ variable => 'wal_compression_level',
+ boot_val => '-1',
+ min => '-1',
+ max => '100',
+},
+
{ name => 'wal_consistency_checking', type => 'string', context => 'PGC_SUSET', group => 'DEVELOPER_OPTIONS',
short_desc => 'Sets the WAL resource managers for which WAL consistency checks are done.',
long_desc => 'Full-page images will be logged for all data blocks and cross-checked against the results of WAL replay.',
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 4dd98624204..3949bf3fa8c 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -48,6 +48,7 @@ extern PGDLLIMPORT bool EnableHotStandby;
extern PGDLLIMPORT bool fullPageWrites;
extern PGDLLIMPORT bool wal_log_hints;
extern PGDLLIMPORT int wal_compression;
+extern PGDLLIMPORT int wal_compression_level;
extern PGDLLIMPORT bool wal_init_zero;
extern PGDLLIMPORT bool wal_recycle;
extern PGDLLIMPORT bool *wal_consistency_checking;
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-06 14:31 ` Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
0 siblings, 1 reply; 44+ messages in thread
From: Hannu Krosing @ 2026-08-06 14:31 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On Thu, Aug 6, 2026 at 2:11 PM Tomas Vondra <tomas@vondra.me> 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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
@ 2026-08-06 14:50 ` Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 1 reply; 44+ messages in thread
From: Ken Marshall @ 2026-08-06 14:50 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; +Cc: Tomas Vondra <tomas@vondra.me>; Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On Thu, Aug 06, 2026 at 04:31:44PM +0200, Hannu Krosing wrote:
> On Thu, Aug 6, 2026 at 2:11 PM Tomas Vondra <tomas@vondra.me> 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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
@ 2026-08-06 15:32 ` Tomas Vondra <tomas@vondra.me>
2026-08-08 19:30 ` Re: WAL compression setting after PostgreSQL LZ4 default change Daniel Gustafsson <daniel@yesql.se>
2026-08-09 13:34 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 2 replies; 44+ messages in thread
From: Tomas Vondra @ 2026-08-06 15:32 UTC (permalink / raw)
To: Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; +Cc: Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; Andrey Borodin <x4mmm@yandex-team.ru>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On 8/6/26 16:50, Ken Marshall wrote:
> On Thu, Aug 06, 2026 at 04:31:44PM +0200, Hannu Krosing wrote:
>> On Thu, Aug 6, 2026 at 2:11 PM Tomas Vondra <tomas@vondra.me> 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 ?
>>
I don't, but I'll do a couple more runs with for these parameters
(zstd/3, lz4/1 and pglz), to see how stable the results are.
Might be interesting to run this on other cloud instances, but I don't
have access to those at the moment.
Could this be due to difference in decompression speed? I've seen
figures like 2GB/s for zstd vs. 4GB/s for lz4. We're we'd dealing with
~45GB of WAL, so that'd be 25 vs. 10 seconds, which is not far from the
~20s difference in recovery time. Then again, it only affects azure, so
there's some hw influence.
>> ---
>> 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?
>
I don't understand what this means by "calling 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.
>
Even on the two azure systems, the tests still use local NVMe disks, not
the cloud storage. But I agree that with cloud storage, it's likely
better to prefer higher compression ratio, even if it costs a bit more
CPU (but the difference in CPU usage seem tiny).
FWIW this only affects recovery/replication, not interactive tasks.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-08 19:30 ` Daniel Gustafsson <daniel@yesql.se>
2026-08-08 20:57 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
1 sibling, 1 reply; 44+ messages in thread
From: Daniel Gustafsson @ 2026-08-08 19:30 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Tomas Vondra <tomas@vondra.me>; Andrey Borodin <x4mmm@yandex-team.ru>; Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
> On 7 Aug 2026, at 22:27, Christoph Berg <myon@debian.org> wrote:
>
> Re: Tomas Vondra
>> The question is what does this mean for this thread, i.e. for the
>> question what should 'on' translate to. I still think zstd seems like a
>> better default, despite this one result where lz4 does a bit better.
>> Especially if we know it the difference can be mostly eliminated.
>
> To me, this doesn't read like a question but a confirmation that zstd
> is the correct pick. They are close now, we tend to prefer zstd, and
> now we know how to make zstd even better in PG 20, then it looks clear
> that zstd is the "on" to go so we aren't switching twice.
I tend to agree with this reasoning.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-08 19:30 ` Re: WAL compression setting after PostgreSQL LZ4 default change Daniel Gustafsson <daniel@yesql.se>
@ 2026-08-08 20:57 ` Jelte Fennema-Nio <postgres@jeltef.nl>
0 siblings, 0 replies; 44+ messages in thread
From: Jelte Fennema-Nio @ 2026-08-08 20:57 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Christoph Berg <myon@debian.org>; Tomas Vondra <tomas@vondra.me>; Andrey Borodin <x4mmm@yandex-team.ru>; Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On Sat, Aug 8, 2026, 21:30 Daniel Gustafsson <daniel@yesql.se> wrote:
> > On 7 Aug 2026, at 22:27, Christoph Berg <myon@debian.org> wrote:
> >
> > Re: Tomas Vondra
> >> The question is what does this mean for this thread, i.e. for the
> >> question what should 'on' translate to. I still think zstd seems like a
> >> better default, despite this one result where lz4 does a bit better.
> >> Especially if we know it the difference can be mostly eliminated.
> >
> > To me, this doesn't read like a question but a confirmation that zstd
> > is the correct pick. They are close now, we tend to prefer zstd, and
> > now we know how to make zstd even better in PG 20, then it looks clear
> > that zstd is the "on" to go so we aren't switching twice.
>
> I tend to agree with this reasoning.
>
+1
>
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-09 13:34 ` Tomas Vondra <tomas@vondra.me>
2026-08-09 17:25 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
1 sibling, 1 reply; 44+ messages in thread
From: Tomas Vondra @ 2026-08-09 13:34 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Andrey Borodin <x4mmm@yandex-team.ru>; Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; Christoph Berg <myon@debian.org>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
Here's a v5, with mostly minor changes compared to v4.
The main change is that it prefers zstd over lz4, per the discussion
about benchmark results. This is what v3 did.
I did not like the DEFAULT_WAL_COMPRESSION name, because that seems to
imply default for the wal_compression GUC. Which is misleading. I've
renamed it to WAL_COMPRESSION_ON.
Updated/reworded the commit message a bit. Feel free to suggest
adjustments. I've included only two people as reviewers - there's been a
lot of discussion about what "on" should mean, but I don't think that
counts as reviews.
regards
--
Tomas Vondra
Attachments:
[text/x-patch] v5-0001-Change-wal_compression-on-to-the-first-of-zstd-lz.patch (6.3K, ../../9444bb52-136d-46e1-9989-ab514f127dc1@vondra.me/2-v5-0001-Change-wal_compression-on-to-the-first-of-zstd-lz.patch)
download | inline diff:
From 0f1af038dea6beff4197b5d17d4152ed35eb7498 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Sun, 9 Aug 2026 14:52:19 +0200
Subject: [PATCH v5] Change wal_compression=on to the first of zstd, lz4, pglz
Previously, wal_compression=on was an alias for pglz, with the
assummption that users could make an informed choice to pick a better
option. But in practice, users rarely got to that second step.
Many users don't want to be choosing algorithms - they just want WAL
compression, and expect that to work well. The configuration parameter
is set by an administrator, who does not control the workload, and so is
not in a position to evaluate the options anyway. And finally, the
algorithms make different trade offs between speed and compression
ratio, and there's no obvious best choice.
In fact, some users may not even realize there are other options, as
previously "on" was the only choice available.
This change maps "on" to non-pglz options, supported by the build. Both
lz4 and zstd are faster, and achieve comparable (or better) compression
ratio. We prefer zstd over lz4 - per our testing the better compression
ratio pays for the lower (de)compression speed.
Like for TOAST compression, the value depends on algorithms supported by
the PostgreSQL build, with lz4 and zstd being optional. But most builds
will have at least one of these external libraries. If neither zstd or
lz4 is supported, we fallback to pglz.
This only affects what "on" means. Users can still make the informed
choice and explicitly select a compression algorithm if it works better
for their system. The default value for "wal_compression" remains "off."
Initial proposal and patch by wenhui qiu, reviews and patch adjustments
by Christoph Berg. A number of other people participated in the
discussion. Benchmarks by me.
Backpatch to 19.
Author: wenhui qiu <qiuwenhuifx@gmail.com>
Reviewed-by: Christoph Berg <myon@debian.org>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGjGUAL1b=Mwd1SCvLbo+fivEr9KDpFcu4jmqKCZXwT=6CiiGQ@mail.gmail.com
Backpatch-through: 19
---
doc/src/sgml/config.sgml | 12 ++++++------
src/backend/utils/misc/guc_tables.c | 8 ++++----
src/backend/utils/misc/postgresql.conf.sample | 3 ++-
src/include/access/xlog.h | 13 +++++++++++++
4 files changed, 25 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 236ee067f40..3ca1bdac81d 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3667,12 +3667,12 @@ include_dir 'conf.d'
<xref linkend="guc-full-page-writes"/> is on, during a base backup,
etc.).
A compressed page image will be decompressed during WAL replay.
- The supported methods are <literal>pglz</literal>,
- <literal>lz4</literal> (if <productname>PostgreSQL</productname>
- was compiled with <option>--with-lz4</option>) and
- <literal>zstd</literal> (if <productname>PostgreSQL</productname>
- was compiled with <option>--with-zstd</option>).
- The value <literal>on</literal> is a historical spelling of <literal>pglz</literal>.
+ The supported methods are <literal>off</literal>, <literal>on</literal>,
+ <literal>lz4</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-lz4</option>),
+ <literal>zstd</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-zstd</option>), and
+ <literal>pglz</literal>.
+ The value <literal>on</literal> selects the first of <literal>zstd</literal>,
+ <literal>lz4</literal>, <literal>pglz</literal> that is available.
The default value is <literal>off</literal>.
Only superusers and users with the appropriate <literal>SET</literal>
privilege can change this setting.
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 1ec460b6a82..afd50e80f92 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -484,13 +484,13 @@ static const struct config_enum_entry wal_compression_options[] = {
#ifdef USE_ZSTD
{"zstd", WAL_COMPRESSION_ZSTD, false},
#endif
- {"on", WAL_COMPRESSION_PGLZ, false},
+ {"on", WAL_COMPRESSION_ON, false},
{"off", WAL_COMPRESSION_NONE, false},
- {"true", WAL_COMPRESSION_PGLZ, true},
+ {"true", WAL_COMPRESSION_ON, true},
{"false", WAL_COMPRESSION_NONE, true},
- {"yes", WAL_COMPRESSION_PGLZ, true},
+ {"yes", WAL_COMPRESSION_ON, true},
{"no", WAL_COMPRESSION_NONE, true},
- {"1", WAL_COMPRESSION_PGLZ, true},
+ {"1", WAL_COMPRESSION_ON, true},
{"0", WAL_COMPRESSION_NONE, true},
{NULL, 0, false}
};
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 493f57409e1..b536823bf25 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -262,7 +262,8 @@
#wal_log_hints = off # also do full page writes of non-critical updates
# (change requires restart)
#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)
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 4dd98624204..338d68d7424 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -87,6 +87,19 @@ typedef enum WalCompression
WAL_COMPRESSION_ZSTD,
} WalCompression;
+/*
+ * Choose an appropriate default WAL compression method for wal_compression=on.
+ * Prefer zstd when compiled in; otherwise use lz4 if available, falling back
+ * to pglz.
+ */
+#if defined(USE_ZSTD)
+#define WAL_COMPRESSION_ON WAL_COMPRESSION_ZSTD
+#elif defined(USE_LZ4)
+#define WAL_COMPRESSION_ON WAL_COMPRESSION_LZ4
+#else
+#define WAL_COMPRESSION_ON WAL_COMPRESSION_PGLZ
+#endif
+
/* Recovery states */
typedef enum RecoveryState
{
--
2.55.0
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-09 13:34 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-09 17:25 ` Christoph Berg <myon@debian.org>
2026-08-10 08:54 ` Re: WAL compression setting after PostgreSQL LZ4 default change Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 44+ messages in thread
From: Christoph Berg @ 2026-08-09 17:25 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Michael Paquier <michael@paquier.xyz>; Andrey Borodin <x4mmm@yandex-team.ru>; Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
Re: Tomas Vondra
> + The supported methods are <literal>off</literal>, <literal>on</literal>,
> + <literal>lz4</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-lz4</option>),
> + <literal>zstd</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-zstd</option>), and
> + <literal>pglz</literal>.
> + The value <literal>on</literal> selects the first of <literal>zstd</literal>,
> + <literal>lz4</literal>, <literal>pglz</literal> 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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-09 13:34 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-09 17:25 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
@ 2026-08-10 08:54 ` Daniel Gustafsson <daniel@yesql.se>
2026-08-12 09:34 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
0 siblings, 1 reply; 44+ messages in thread
From: Daniel Gustafsson @ 2026-08-10 08:54 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Tomas Vondra <tomas@vondra.me>; Michael Paquier <michael@paquier.xyz>; Andrey Borodin <x4mmm@yandex-team.ru>; Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
> On 9 Aug 2026, at 19:25, Christoph Berg <myon@debian.org> 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
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-09 13:34 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-09 17:25 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-08-10 08:54 ` Re: WAL compression setting after PostgreSQL LZ4 default change Daniel Gustafsson <daniel@yesql.se>
@ 2026-08-12 09:34 ` Tomas Vondra <tomas@vondra.me>
2026-08-13 03:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 1 reply; 44+ messages in thread
From: Tomas Vondra @ 2026-08-12 09:34 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; Christoph Berg <myon@debian.org>; +Cc: Michael Paquier <michael@paquier.xyz>; Andrey Borodin <x4mmm@yandex-team.ru>; Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; wenhui qiu <qiuwenhuifx@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
FYI I've pushed the v5 with some minor tweaks, and backpatched it to 19.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Re: WAL compression setting after PostgreSQL LZ4 default change Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-03 08:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 12:11 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Re: WAL compression setting after PostgreSQL LZ4 default change Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Re: WAL compression setting after PostgreSQL LZ4 default change Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-09 13:34 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-08-09 17:25 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-08-10 08:54 ` Re: WAL compression setting after PostgreSQL LZ4 default change Daniel Gustafsson <daniel@yesql.se>
2026-08-12 09:34 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-08-13 03:31 ` wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 0 replies; 44+ messages in thread
From: wenhui qiu @ 2026-08-13 03:31 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Daniel Gustafsson <daniel@yesql.se>; Christoph Berg <myon@debian.org>; Michael Paquier <michael@paquier.xyz>; Andrey Borodin <x4mmm@yandex-team.ru>; Ken Marshall <ktm@rice.edu>; Hannu Krosing <hannuk@google.com>; Andreas Karlsson <andreas@proxel.se>; Laurenz Albe <laurenz.albe@cybertec.at>; Andres Freund <andres@anarazel.de>; Jelte Fennema-Nio <postgres@jeltef.nl>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
Hi Tomas Vondra
> FYI I've pushed the v5 with some minor tweaks, and backpatched it to 19.
> Thanks so much for pushing this patch! Your thorough testing was key to
> making this submission possible.
>
>
>
>
Thank
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Re: WAL compression setting after PostgreSQL LZ4 default change Andres Freund <andres@anarazel.de>
@ 2026-07-31 12:55 ` Tomas Vondra <tomas@vondra.me>
1 sibling, 0 replies; 44+ messages in thread
From: Tomas Vondra @ 2026-07-31 12:55 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Christoph Berg <myon@debian.org>; Jelte Fennema-Nio <postgres@jeltef.nl>; Michael Paquier <michael@paquier.xyz>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
On 7/16/26 17:08, Andres Freund wrote:
> 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).
>
Ack. What do you think about doing this for PG19? Or do you think that
ship has sailed at this point?
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 44+ messages in thread
* Re: WAL compression setting after PostgreSQL LZ4 default change
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 23:45 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-03 02:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Re: WAL compression setting after PostgreSQL LZ4 default change Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Re: WAL compression setting after PostgreSQL LZ4 default change Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Re: WAL compression setting after PostgreSQL LZ4 default change Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Re: WAL compression setting after PostgreSQL LZ4 default change Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` Re: WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Re: WAL compression setting after PostgreSQL LZ4 default change Tomas Vondra <tomas@vondra.me>
@ 2026-07-16 22:00 ` Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 44+ messages in thread
From: Michael Paquier @ 2026-07-16 22:00 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Christoph Berg <myon@debian.org>; Jelte Fennema-Nio <postgres@jeltef.nl>; Japin Li <japinli@hotmail.com>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; John Naylor <johncnaylorls@gmail.com>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../allUaU_1C1-sSwh8@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 44+ messages in thread
end of thread, other threads:[~2026-08-13 03:31 UTC | newest]
Thread overview: 44+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-30 10:25 WAL compression setting after PostgreSQL LZ4 default change wenhui qiu <qiuwenhuifx@gmail.com>
2026-06-30 10:51 ` Christoph Berg <myon@debian.org>
2026-06-30 13:48 ` Japin Li <japinli@hotmail.com>
2026-06-30 23:45 ` Michael Paquier <michael@paquier.xyz>
2026-07-01 01:53 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-03 02:18 ` Japin Li <japinli@hotmail.com>
2026-07-03 02:58 ` Fujii Masao <masao.fujii@gmail.com>
2026-07-03 08:38 ` Japin Li <japinli@hotmail.com>
2026-07-07 22:26 ` Michael Paquier <michael@paquier.xyz>
2026-07-07 22:46 ` Jelte Fennema-Nio <postgres@jeltef.nl>
2026-07-07 22:58 ` Christoph Berg <myon@debian.org>
2026-07-08 06:37 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-16 15:00 ` Tomas Vondra <tomas@vondra.me>
2026-07-16 15:08 ` Andres Freund <andres@anarazel.de>
2026-07-18 20:28 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-20 06:23 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:35 ` Christoph Berg <myon@debian.org>
2026-07-31 13:04 ` Tomas Vondra <tomas@vondra.me>
2026-07-31 13:10 ` Christoph Berg <myon@debian.org>
2026-07-31 14:58 ` Andrey Borodin <x4mmm@yandex-team.ru>
2026-07-31 15:50 ` Christoph Berg <myon@debian.org>
2026-07-31 21:44 ` Tomas Vondra <tomas@vondra.me>
2026-07-31 22:50 ` Christoph Berg <myon@debian.org>
2026-08-01 01:44 ` Japin Li <japinli@hotmail.com>
2026-08-01 08:42 ` Michael Paquier <michael@paquier.xyz>
2026-08-03 08:26 ` Andreas Karlsson <andreas@proxel.se>
2026-08-03 13:36 ` Hannu Krosing <hannuk@google.com>
2026-08-03 20:36 ` Tomas Vondra <tomas@vondra.me>
2026-08-04 07:03 ` Hannu Krosing <hannuk@google.com>
2026-08-04 10:01 ` Tomas Vondra <tomas@vondra.me>
2026-08-04 11:15 ` Hannu Krosing <hannuk@google.com>
2026-08-06 12:11 ` Tomas Vondra <tomas@vondra.me>
2026-08-06 14:31 ` Hannu Krosing <hannuk@google.com>
2026-08-06 14:50 ` Ken Marshall <ktm@rice.edu>
2026-08-06 15:32 ` Tomas Vondra <tomas@vondra.me>
2026-08-08 19:30 ` Daniel Gustafsson <daniel@yesql.se>
2026-08-08 20:57 ` Jelte Fennema-Nio <postgres@jeltef.nl>
2026-08-09 13:34 ` Tomas Vondra <tomas@vondra.me>
2026-08-09 17:25 ` Christoph Berg <myon@debian.org>
2026-08-10 08:54 ` Daniel Gustafsson <daniel@yesql.se>
2026-08-12 09:34 ` Tomas Vondra <tomas@vondra.me>
2026-08-13 03:31 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-07-31 12:55 ` Tomas Vondra <tomas@vondra.me>
2026-07-16 22:00 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox