Subject: Re: Lowering the default wal_blocksize to 4K

From: Andres Freund <[email-masked]>
Date: Mon, 16 Feb 2026 16:13:37 -0500

Hi,

On 2026-02-16 10:04:37 +0200, Andy Pogrebnoi wrote:
> > On Oct 10, 2023, at 02:08, Andres Freund <[email-masked]> wrote:
> >
> > Hi,
> >
> > I've mentioned this to a few people before, but forgot to start an actual
> > thread. So here we go:
> >
> > I think we should lower the default wal_blocksize / XLOG_BLCKSZ to 4096, from
> > the current 8192.
>
> I prepared a patch in case we want to move with the default 4kb XLOG_BLCKSZ.

I think we should.

> Regarding reducing the page headers' size, the benefits of 4Kb wal_blocks
> outweight disadvantages of the proportionally bigger header in my opinion.

I agree.

> Since we recycle WAL segments, the added size won't go to the disk usage but
> rather cause a bit more freqent segment.

I don't think that's a valid argument though, how much WAL needs to be
archived is a relevant factor.

> > One thing I noticed is that our auto-configuration of wal_buffers leads to
> > different wal_buffers settings for different XLOG_BLCKSZ, which doesn't seem
> > great.
>
> I don't think it's an issue as wal_buffers are in block units, not bytes. Even
> though the auto-tuned number may change, the total amount of bytes still remains
> the same with different XLOG_BLCKSZ.

Given the way the auto-tuning works, I don't think that's true:

/*
 * Auto-tune the number of XLOG buffers.
 *
 * The preferred setting for wal_buffers is about 3% of shared_buffers, with
 * a maximum of one XLOG segment (there is little reason to think that more
 * is helpful, at least so long as we force an fsync when switching log files)
 * and a minimum of 8 blocks (which was the default value prior to PostgreSQL
 * 9.1, when auto-tuning was added).
 *
 * This should not be called until NBuffers has received its final value.
 */
static int
XLOGChooseNumBuffers(void)
{
	int			xbuffers;

	xbuffers = NBuffers / 32;
	if (xbuffers > (wal_segment_size / XLOG_BLCKSZ))
		xbuffers = (wal_segment_size / XLOG_BLCKSZ);
	if (xbuffers < 8)
		xbuffers = 8;
	return xbuffers;
}

If NBuffers / 32 < wal_segment_size / XLOG_BLCKSZ, the chosen xbuffers value
does not depend on XLOG_BLCKSZ.

To me the code only makes sense if you assume that NBuffers / 32 gives you a
value in the same domain as data blocks, otherwise NBuffers / 32 is not the
approximation of %3 that the comment talks about.

I think the code just needs to be fixed to multiply NBuffers * BLCKSZ and then
divide that by XLOG_BLCKSZ.

>
> > For some example numbers, I ran a very simple insert workload with a varying
> > number of clients with both a wal_blocksize=4096 and wal_blocksize=8192
> > cluster, and measured the amount of bytes written before/after.
>
> I've also run some simple tests on my local machine (Ubuntu in Vagrant on M1
> Mac). I run a sysbench write-only load for 20s with different amounts of threads
> (and tables equal to the number of threads num) and measured disk writes with
> iostat. I recreated tables and did a checkpoint before each run. These are my
> results:
>
> 8Kb XLOG_BLCKSZ
> ====
> Threads          tps        kB_wrtn
> 1             535.34         207288
> 5            1457.24         591708
> 10           1441.85         574700
> 15            823.98         388732
>
> 4Kb XLOG_BLCKSZ
> ====
> Threads          tps        kB_wrtn
> 1             542.02         153544
> 5            1556.83         393444
> 10           1288.00         339648
> 15            975.32         255708

The reduction in bytes written is rather impressive...

> I will run more benchmarks on proper hardware. For example, interesting what
> happens to performance with >4K writes. But what else do you think has to be
> done to move this patch forward?

I think the auto-tuning bit above needs to be fixed, and it's probably worth
manually testing a pg_upgrade from 8kB XLOG_BLCKSZ to 4kB. It should work, but
...

I think we otherwise should just go for it.

Greetings,

Andres Freund

---

From: Michael Paquier <[email-masked]>
Date: Tue, 17 Feb 2026 16:26:29 +0900

> The reduction in bytes written is rather impressive...

It's a pretty nice improvement for a change as simple as that, yes.
+1.
--
Michael

---

From: Andrew Pogrebnoi <[email-masked]>
Date: Tue, 17 Feb 2026 22:06:14 +0200

Hi,

> archived is a relevant factor.

Yes, indeed.

> divide that by XLOG_BLCKSZ.

You are right, my bad, fixed (v2-0002).

> manually testing a pg_upgrade from 8kB XLOG_BLCKSZ to 4kB. It should
work, but

pg_upgrade ran with no issues, and the database started with the new (4kB)
XLOG_BLCKSZ

I also found and fixed some more mentions of 8kB as the default for
XLOG_BLCKSZ in the
documentation (v2-0001).

---
Cheers,
Andy

---

From: Andy Pogrebnoi <[email-masked]>
Date: Wed, 18 Feb 2026 15:26:24 +0200

Hi,

The Windows tests are failing on `Assert("check_GUC_init(hentry->gucvar)")`
for wal_writer_flush_after [1]. It doesn't make much sense to me as both
load- and C-value for the wal_writer_flush_after GUC are the same constant:

src/backend/utils/misc/guc_parameters.dat:
{ name => 'wal_writer_flush_after', type => 'int', context => 'PGC_SIGHUP',
group => 'WAL_SETTINGS',
  short_desc => 'Amount of WAL written out by WAL writer that triggers a
flush.',
  flags => 'GUC_UNIT_XBLOCKS',
  variable => 'WalWriterFlushAfter',
  boot_val => 'DEFAULT_WAL_WRITER_FLUSH_AFTER',
  min => '0',
  max => 'INT_MAX',
},

src/include/postmaster/walwriter.h:
int WalWriterFlushAfter = DEFAULT_WAL_WRITER_FLUSH_AFTER;

This constant was introduced to fix the same issue [2], but I suppose no
one checked Windows builds. Windows clearly has an old 8kB value for
WalWriterFlushAfter during the check. I suppose it is something with the
CI/build. But I have zero experience with building anything for Windows, so
any tips on where to look are welcome.

*And apologies for the dreadful formatting in my previous email; the client
plays tricks on me after I hit Send.

[1] https://cirrus-ci.com/task/6286650038288384?logs=test_world#L2728-L2729
[2]
https://www.postgresql.org/message-id/CA+hUKGLNmLV=VrT==[email-masked]

---
Cheers,
Andy

---

From: Andres Freund <[email-masked]>
Date: Wed, 18 Feb 2026 11:32:28 -0500

Hi,

> any tips on where to look are welcome.

The fact that the test do pass for msvc (which does not use ccache), but not
for mingw (which uses ccache), does point towards some caching issue.

We've had some caching problems on mingw before.

I don't fully know what's going on, but there clearly is something wrong with
ccache here when using precompiled headers, I can reproduce incomplete
rebuilds with ccache's direct mode on linux, cross building for windows (first
thing I tried). If I rebuild with a changed xlog blocksize, I get *some* cache
hits building the backend, despite pg_config.h having changed.

I think the problem is that ccache seems to not record a dependency to the pch
file in its dependencies if the pch file is included via -include, if the
included header and the header.h.gch file aren't in the same directory . That
leads to the pch file getting rebuilt, but then ccache just uses the older
cached result for the .c file getting built, because it does not recognize it
would need to be rebuilt.

I'll go and report that to the ccache folks.

Greetings,

Andres Freund

---

From: John Naylor <[email-masked]>
Date: Wed, 25 Mar 2026 18:10:08 +0700

On Wed, Feb 18, 2026 at 8:26 PM Andy Pogrebnoi
<[email-masked]> wrote:
> The Windows tests are failing on `Assert("check_GUC_init(hentry->gucvar)")` for wal_writer_flush_after [1]. It doesn't make much sense to me as both load- and C-value for the wal_writer_flush_after GUC are the same constant:

FWIW, they just started passing a couple days ago:

https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F6490

-- 
John Naylor
Amazon Web Services

---

From: Andres Freund <[email-masked]>
Date: Wed, 25 Mar 2026 10:02:08 -0400

Hi,

> I'll go and report that to the ccache folks.

I did that and forgot to update this thread:
https://github.com/ccache/ccache/issues/1686

Unfortunately not much has happened on that front. But in the course of the
investigation I discovered the gcc flag "fpch-deps". I think we should inject
that in our build, if supported by the compiler. I don't really see a downside
and it does avoid the issue at hand, based on my experiments.

Greetings,

Andres Freund