Feedback-ID: id4a34324:Fastmail
Date: Mon, 16 Feb 2026 16:13:37 -0500
From: Andres Freund <andres@anarazel.de>
To: Andy Pogrebnoi <andrew.pogrebnoi@percona.com>
Cc: pgsql-hackers@postgresql.org, Heikki Linnakangas <hlinnaka@iki.fi>, 
	Robert Haas <robertmhaas@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, 
	Matthias van de Meent <boekewurm+postgres@gmail.com>
Subject: Re: Lowering the default wal_blocksize to 4K
Message-ID: <x56pxq6jpuftn6ear3uwbz5tyb4r37itv5zetvcucq3td57apc@yq7jyka7tszv>
References: <20231009230805.funj5ipoggjyzjz6@awork3.anarazel.de>
 <E25A9AD2-EAD3-4372-AFD2-2627E4D5E3C5@percona.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <E25A9AD2-EAD3-4372-AFD2-2627E4D5E3C5@percona.com>
Archived-At: <https://www.postgresql.org/message-id/x56pxq6jpuftn6ear3uwbz5tyb4r37itv5zetvcucq3td57apc%40yq7jyka7tszv>
Precedence: bulk

Hi,

On 2026-02-16 10:04:37 +0200, Andy Pogrebnoi wrote:
> > On Oct 10, 2023, at 02:08, Andres Freund <andres@anarazel.de> 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