Message-ID: <942e247261151cddf5d57cf7797d6bf3c9b067a7.camel@cybertec.at>
Subject: Re: A Question about FPW and WAL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Dawn Yu <boyyuxiao1987@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Mon, 03 Apr 2023 11:38:30 +0200
In-Reply-To: 
 <CALiuKDHL-5h4DWYKiCXpQHJ8UHEG7mKj38eJVGWeT=nf604bLQ@mail.gmail.com>
References: 
	<CALiuKDHL-5h4DWYKiCXpQHJ8UHEG7mKj38eJVGWeT=nf604bLQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/942e247261151cddf5d57cf7797d6bf3c9b067a7.camel%40cybertec.at>
Precedence: bulk

On Mon, 2023-04-03 at 14:51 +0800, Dawn Yu wrote:
> Recently=EF=BC=8Cwe come=C2=A0up with a same=C2=A0problem=EF=BC=8Cwhen th=
e database make the checkpoint,
> the=C2=A0size of wal directory=C2=A0increase very fast and=C2=A0 take one=
 hundred percent disk
> usage,we found the problem of pg wal write amplification is very serious.
> By analyzing the wal log, in some scenarios, most of the space is wasted =
when
> maintaining the full page of the index. Has the community considered modi=
fying
> this part of the relevant code to make the full page better,The setting o=
f FPW
> function is divided into two parts, the full-page write of the data recor=
d and
> the full-page write of the index block, and the switch functions can be s=
et
> separately.
> By default, index full-page write is disabled. If the index is broken, yo=
u can
> rebuild the index to solve it. Although the security of index data cannot=
 be
> guaranteed, this setting can greatly reduce the problem of storage space =
explosion
> caused by wal write amplification.

I would feel funny about a parameter like that.

There are a couple of things you can do today:

- increase "max_wal_size" and "checkpoint_timeout" to get checkpoints less =
often

- activate "wal_compression", so that the FPW are compressed.  Best is to u=
se v15,
  where you can use "lz4" or "zstd" compression, which are much faster.

Yours,
Laurenz Albe