Обсуждение: Postgres Parameters

Поиск

Список

Период

Сортировка

Postgres Parameters

От

Sam Stearns

Дата:

11 ноября, 02:28:21

Howdy,

Could someone advise on how to determine the correct settings for the following, please?:

checkpoint_timeout
max_wal_size

Thank you,

Sam

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

Re: Postgres Parameters

От

Ron Johnson

Дата:

11 ноября, 04:16:50

On Mon, Nov 10, 2025 at 6:28 PM Sam Stearns <sam.stearns@dat.com> wrote:

Howdy,

Could someone advise on how to determine the correct settings for the following, please?:

checkpoint_timeout
max_wal_size

https://postgresqlco.nf/doc/en/param/ is a great web site.

How busy are your systems?

I sometimes set max_wal_size to be 6GB or 12GB, and sometimes the default. As https://www.postgresql.org/docs/17/runtime-config-wal.html#GUC-MAX-WAL-SIZE says, "This is a soft limit; WAL size can exceed max_wal_size under special circumstances, such as heavy load". Leave it at the default, and then search the log files for complaints about checkpointing too often. No complaints? Leave at the default. Complaints? Bump it up.

My standard checkpoint_timeout is 15 minutes, for Unremembered Reasons. Things work, so I have no need to change.

If the disk with our WAL files crashes (not very likely), we probably lose at most 15 minutes of data.

When doing a multi-TB pg_restore to an empty database, I bump checkpoint_time to 30m and max_wal_size to 32GB (and use dangerous settings like fsync=off).

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

Re: Postgres Parameters

От

Laurenz Albe

Дата:

11 ноября, 11:57:13

On Mon, 2025-11-10 at 15:28 -0800, Sam Stearns wrote:
> Could someone advise on how to determine the correct settings for the following, please?:
>
>  * checkpoint_timeout
>  * max_wal_size

Leave "checkpoint_timeout" as it is, and increase "max_wal_size" to 10GB.
Wait, and if pg_stat_checkpointer tells you that many checkpoints are *not*
triggered by timeout, increase "max_wal_size".

Yours,
Laurenz Albe

Re: Postgres Parameters

От

Fabrice Chapuis

Дата:

11 ноября, 14:19:45

Hi,

You could also use this query, if the ratio is low, that means there is no pressure on wal size and you can keep the max_wal_size value as it is.

SELECT

num_timed,

num_requested,

round((num_requested::numeric / NULLIF(num_timed + num_requested, 0)) * 100, 2)

AS requested_pct

FROM pg_stat_checkpointer;

+-----------+---------------+---------------+

| num_timed | num_requested | requested_pct |

+-----------+---------------+---------------+

| 3502 | 146 | 4.00 |

+-----------+---------------+---------------+

(1 row)

Regards,

Fabrice

On Tue, Nov 11, 2025 at 12:28 AM Sam Stearns <sam.stearns@dat.com> wrote:

Howdy,

Could someone advise on how to determine the correct settings for the following, please?:

checkpoint_timeout
max_wal_size

Thank you,

Sam

--
Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

Re: Postgres Parameters

От

Sam Stearns

Дата:

11 ноября, 18:24:49

Awesome stuff. Thank you, everyone!

Sam

On Tue, Nov 11, 2025 at 3:19 AM Fabrice Chapuis <fabrice636861@gmail.com> wrote:

Hi, You could also use this query, if the ratio is low, that means there is no pressure on wal size and you can keep the max_wal_size value as it is. SELECT num_timed, num_requested, round((num_requested: : numeric / NULLIF(num_timed + num_requested,
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd
Hi,

You could also use this query, if the ratio is low, that means there is no pressure on wal size and you can keep the max_wal_size value as it is.

SELECT
num_timed,
num_requested,
round((num_requested::numeric / NULLIF(num_timed + num_requested, 0)) * 100, 2)
AS requested_pct
FROM pg_stat_checkpointer;
+-----------+---------------+---------------+
| num_timed | num_requested | requested_pct |
+-----------+---------------+---------------+
| 3502 | 146 | 4.00 |
+-----------+---------------+---------------+
(1 row)

Regards,

Fabrice

On Tue, Nov 11, 2025 at 12:28 AM Sam Stearns <sam.stearns@dat.com> wrote:
Howdy,

Could someone advise on how to determine the correct settings for the following, please?:

checkpoint_timeout
max_wal_size

Thank you,

Sam

--
Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

Re: Re: Postgres Parameters

От

"zhenwei.li@sfere-elec.com"

Дата:

12 ноября, 05:26:45

I found that PostgreSQL 15.3 doesn't support the pg_stat_checkpointer view. We can use pg_stat_bgwriter instead. The SQL is as follows:

SELECT  checkpoints_timed AS num_timed,  -- Checkpoints triggered by timeout  checkpoints_req AS num_requested,  -- Checkpoints triggered by requests (like when WAL space is tight)  round(    (checkpoints_req::numeric / NULLIF(checkpoints_timed + checkpoints_req, 0)) * 100,     2  ) AS requested_pct  -- % of checkpoints that were request-triggered
FROM pg_stat_bgwriter;

From: Sam Stearns
Date: 2025-11-11 23:24
To: Fabrice Chapuis; Laurenz Albe; Ron Johnson
CC: Pgsql-admin; Henry Ashu
Subject: Re: Postgres Parameters
Awesome stuff. Thank you, everyone!

Sam

On Tue, Nov 11, 2025 at 3:19 AM Fabrice Chapuis <fabrice636861@gmail.com> wrote:
Hi, You could also use this query, if the ratio is low, that means there is no pressure on wal size and you can keep the max_wal_size value as it is. SELECT num_timed, num_requested, round((num_requested: : numeric / NULLIF(num_timed + num_requested,
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd
Hi,

You could also use this query, if the ratio is low, that means there is no pressure on wal size and you can keep the max_wal_size value as it is.

SELECT
num_timed,
num_requested,
round((num_requested::numeric / NULLIF(num_timed + num_requested, 0)) * 100, 2)
AS requested_pct
FROM pg_stat_checkpointer;
+-----------+---------------+---------------+
| num_timed | num_requested | requested_pct |
+-----------+---------------+---------------+
| 3502 | 146 | 4.00 |
+-----------+---------------+---------------+
(1 row)

Regards,

Fabrice

On Tue, Nov 11, 2025 at 12:28 AM Sam Stearns <sam.stearns@dat.com> wrote:
Howdy,

Could someone advise on how to determine the correct settings for the following, please?:

checkpoint_timeout
max_wal_size

Thank you,

Sam

--
Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

--
Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

Re: Re: Postgres Parameters

От

Sam Stearns

Дата:

12 ноября, 18:12:16

Thank you, Zhenwei.

On Tue, Nov 11, 2025 at 6:26 PM zhenwei.li@sfere-elec.com <zhenwei.li@sfere-elec.com> wrote:

I found that PostgreSQL 15. 3 doesn't support the pg_stat_checkpointer view. We can use pg_stat_bgwriter instead. The SQL is as follows: SELECT checkpoints_timed AS num_timed, -- Checkpoints triggered by timeout checkpoints_req AS num_requested,
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd
I found that PostgreSQL 15.3 doesn't support the pg_stat_checkpointer view. We can use pg_stat_bgwriter instead. The SQL is as follows:
SELECT  checkpoints_timed AS num_timed,  -- Checkpoints triggered by timeout  checkpoints_req AS num_requested,  -- Checkpoints triggered by requests (like when WAL space is tight)  round(    (checkpoints_req::numeric / NULLIF(checkpoints_timed + checkpoints_req, 0)) * 100,     2  ) AS requested_pct  -- % of checkpoints that were request-triggered
FROM pg_stat_bgwriter;
From: Sam Stearns
Date: 2025-11-11 23:24
To: Fabrice Chapuis; Laurenz Albe; Ron Johnson
CC: Pgsql-admin; Henry Ashu
Subject: Re: Postgres Parameters
Awesome stuff. Thank you, everyone!

Sam

On Tue, Nov 11, 2025 at 3:19 AM Fabrice Chapuis <fabrice636861@gmail.com> wrote:
Hi, You could also use this query, if the ratio is low, that means there is no pressure on wal size and you can keep the max_wal_size value as it is. SELECT num_timed, num_requested, round((num_requested: : numeric / NULLIF(num_timed + num_requested,
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd
Hi,

You could also use this query, if the ratio is low, that means there is no pressure on wal size and you can keep the max_wal_size value as it is.

SELECT
num_timed,
num_requested,
round((num_requested::numeric / NULLIF(num_timed + num_requested, 0)) * 100, 2)
AS requested_pct
FROM pg_stat_checkpointer;
+-----------+---------------+---------------+
| num_timed | num_requested | requested_pct |
+-----------+---------------+---------------+
| 3502 | 146 | 4.00 |
+-----------+---------------+---------------+
(1 row)

Regards,

Fabrice

On Tue, Nov 11, 2025 at 12:28 AM Sam Stearns <sam.stearns@dat.com> wrote:
Howdy,

Could someone advise on how to determine the correct settings for the following, please?:

checkpoint_timeout
max_wal_size

Thank you,

Sam

--
Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

--
Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Postgres Parameters

Postgres Parameters

Re: Postgres Parameters

Re: Postgres Parameters

Re: Postgres Parameters

Re: Postgres Parameters

Re: Re: Postgres Parameters

Re: Re: Postgres Parameters