Request a Demo
Home   >   mailing lists

Re: Re: Postgres Parameters - Mailing list pgsql-admin

From zhenwei.li@sfere-elec.com
Subject Re: Re: Postgres Parameters
Date
Msg-id 202511121026451863102@sfere-elec.com
Whole thread Raw
In response to Postgres Parameters  (Sam Stearns <sam.stearns@dat.com>)
Responses Re: Re: Postgres Parameters
List pgsql-admin
Tree view
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;

 
Date: 2025-11-11 23:24
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


pgsql-admin by date:

Previous
From: Sam Stearns
Date:
Subject: Re: Postgres Parameters
Next
From: Abdullah Ergin
Date:
Subject: Monitoring PostgreSQL Copy Operations