Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vJCY3-00EMqH-1n for pgsql-admin@arkaria.postgresql.org; Wed, 12 Nov 2025 15:14:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vJCY1-00CEOP-0s for pgsql-admin@arkaria.postgresql.org; Wed, 12 Nov 2025 15:14:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vJCY0-00CEOH-2Z for pgsql-admin@lists.postgresql.org; Wed, 12 Nov 2025 15:14:12 +0000 Received: from mx0a-0039f802.pphosted.com ([205.220.164.45]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vJCXw-007Mb2-2t for pgsql-admin@lists.postgresql.org; Wed, 12 Nov 2025 15:14:11 +0000 Received: from pps.filterd (m0209981.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.11/8.18.1.11) with ESMTP id 5AC7AUeE1039496 for ; Wed, 12 Nov 2025 07:14:05 -0800 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dat.com; h=cc :content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=ppdk230404; bh=CkOVNMaCpiBVr+lkPHz8KHu PjhBnUxJDPfbczY2IXds=; b=sMlPXzoHXWAr/Y4d8dBfZINns4yYR9D6nzXU5zc Hb5rQWFvb0hGmtgBK8xdIJLai3FTnAGpb2t2R8BY2D1zXYSZmBtmc5rslTgYXTi9 Vw/QtgFI0Ef9Y3QnlAOh4DUOfXtKUTPfyCFkwq3c+53g9fnajAFKAvinrKC4mcbz F8T1Vm9LDyrE4/zqpPNura+uSxdh8VleuASXcC5FaxjIwfVG1YytRcqHv83r6D4L giAv6VMr9f9Hl5arqjcPCAnERho17o1ElXvOLcyZYzaynms4/i/mdiqmxgWzKX8e HFQV5eBW7aZUBkEid7mlKOp4yYjnRvLW7/80AWecr/zBwRw== Received: from mail-ed1-f69.google.com (mail-ed1-f69.google.com [209.85.208.69]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 4aa4605c2d-1 (version=TLSv1.3 cipher=TLS_AES_128_GCM_SHA256 bits=128 verify=NOT) for ; Wed, 12 Nov 2025 07:13:50 -0800 (PST) Received: by mail-ed1-f69.google.com with SMTP id 4fb4d7f45d1cf-64095177aedso1577428a12.2 for ; Wed, 12 Nov 2025 07:13:20 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1762960349; x=1763565149; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=CkOVNMaCpiBVr+lkPHz8KHuPjhBnUxJDPfbczY2IXds=; b=ZUXvQvzcOrDGvl+MFIlhx7+g1W4NDsACYIpsN3nicCT4Zpb6GZOEVcBH2E4nR9qXQW ujvHPOTiY7cMJPEBk3ULfc9PHcJ7bKBGf+XecTprErZmlLaCdkgZtflhYlocoHd1jvzi bHADGEpzAMFpSrRF8UYM/ygKXqQDrRLkTwdlQSeA8u69YaEnjO2LNn1KpHBoTbOIA1sd rnCeHpa2k9uOdVg7BfdHR8JNNhD7r0vTQsWM21zQ0Jfrn3yfqmZEbt4IW8h6UUK5zFsY XOjdRS5z/sr6aWnej3P4gzMoYpnxOAGyeS5XB0k7T4Cc77xEIp1WHHZJ9iRh4lBudB8i 01Cw== X-Forwarded-Encrypted: i=1; AJvYcCXuUbdphoa1l6m3JmdGUOWngaE6D6b27QDZ+qMcnaQKGHMzRda2FvDuGC0eXBLSE9oM5o9yuht57t2Puw==@lists.postgresql.org X-Gm-Message-State: AOJu0Ywrj6yF4RhLY3a4ZcGnJ7tqgF2odXc+mB0GutHwnIILg/yEmygy P05/VS5lAaQCq92rId7uHV0Hb/mSJkOLCUobplF2Hh1eYyCLqbLRHJvPQu3bn15D4J18Mt8stcm CFr0Q4hYlIbpXV8cMCm3wkVRxnnylngQAjDGrTQaE4ornCCEBVZCrJ7uLybdveE/z+RtR/t1F+I Tzp8Y5Gxj7357WMFat7fSeb8iAQWm/uSpixR3SzXo65TPVTERqdQUP4ToUbABNCMjNpb3SrQ8= X-Gm-Gg: ASbGncuANY2y5/2otHZAhd96l2mLiczOeteGBiQ9fyZBFm9Z/1fnTcGZYkkVEp/nxyd tjMQSy8b0pLfpQU2YK6XziNjbIVlHSlRRqOJlWdm+bqAXoUPY3VUVPy5bzpqyrGz2rJwzUXbf1w T9wFN2HFgcVasse7Mzg4/S+szrsvK96qKChub8ERhRC2ZXVVpJlyrn X-Received: by 2002:a17:907:3d05:b0:b6d:6f46:9047 with SMTP id a640c23a62f3a-b7331b6c0admr329593066b.59.1762960348792; Wed, 12 Nov 2025 07:12:28 -0800 (PST) X-Google-Smtp-Source: AGHT+IFa3jfJBAoSbzEJ3AlV3UMatw+oK8NVfRAYJRsfI5jcAq16Lke3dRs57BEWzPsax4e95F/WjygU/CKDw+TeOdw= X-Received: by 2002:a17:907:3d05:b0:b6d:6f46:9047 with SMTP id a640c23a62f3a-b7331b6c0admr329590266b.59.1762960348288; Wed, 12 Nov 2025 07:12:28 -0800 (PST) MIME-Version: 1.0 References: <202511121026451863102@sfere-elec.com> In-Reply-To: <202511121026451863102@sfere-elec.com> From: Sam Stearns Date: Wed, 12 Nov 2025 07:12:16 -0800 X-Gm-Features: AWmQ_bldPB2I0-Nnb4Y5rNDwNlyWxnAxgZV6ypNAboQfIDTC3ijSvgNJ2UV42Ck Message-ID: Subject: Re: Re: Postgres Parameters To: "zhenwei.li@sfere-elec.com" Cc: Fabrice Chapuis , Laurenz Albe , Ron Johnson , Pgsql-admin , Henry Ashu Content-Type: multipart/alternative; boundary="0000000000005029110643673123" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1121,Hydra:6.1.9,FMLib:17.12.100.49 definitions=2025-11-12_04,2025-11-11_03,2025-10-01_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005029110643673123 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you, Zhenwei. On Tue, Nov 11, 2025 at 6:26=E2=80=AFPM 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_re= q, 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=E2=80=AFAM Fabrice Chapuis > 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=E2=80=AFAM Sam Stearns 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 > > > > --=20 Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com --0000000000005029110643673123 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you, Zhenwei.

On Tue, Nov 11, 2= 025 at 6:26=E2=80=AFPM zhenwei= .li@sfere-elec.com <zhe= nwei.li@sfere-elec.com> wrote:
I found that PostgreSQL 15.=E2=80=8A3 doesn't support the pg_stat_check= pointer view. We can use pg_stat_bgwriter instead. The SQL is as follows: S= ELECT checkpoints_timed AS num_timed, -- Checkpoints triggered by timeout c= heckpoints_req AS num_requested,
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.
=C2=A0
ZjQcmQRYFpfptBannerEnd
I found that PostgreSQL 15.3 doesn't suppor= t the pg_stat_checkpointer view. We can use pg_stat_bgwr= iter instead. The SQL is as follows:
SELEC=
T
  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,=20
    2
  ) AS requested_pct  -- % of checkpoints that were request-triggered
FROM pg_stat_bgwriter;

=C2=A0
From:=C2=A0Sam Stearns
Date:=C2=A02025-11-11=C2=A023:24
Subject:=C2=A0Re:= Postgres Parameters
Awesome stuff.=C2=A0 Thank yo= u, everyone!

Sam


On Tue, Nov 11, 202= 5 at 3:19=E2=80=AFAM 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:=E2=80=8A:=E2=80=8Anu= meric / NULLIF(num_timed + num_requested,
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.
=C2=A0
ZjQcmQRYFpfptBannerEnd
Hi,

Y= ou could also use this query, if the ratio is low, that means there is no p= ressure on wal size and you can keep the max_wal_size value as it is.
=

SELECT
=C2=A0 num_timed,
= =C2=A0 num_requested,
=C2=A0 roun= d((num_requested::numeric / NULLIF(num_timed + num_requested, 0)) * 100, 2)=
=C2=A0 =C2=A0 AS requested_pct
FROM pg_stat_checkpointer;<= /div>
+-----------+---------------+-----------= ----+
| num_timed | num_requested= | requested_pct |
+-----------+-= --------------+---------------+
|= =C2=A0 =C2=A0 =C2=A0 3502 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0146 |= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 4.00 |
+-----------+---------------+---------------+
(1 row)

Regards,

Fa= brice


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

Could someone advise on how to determine the correct settings for the fol= lowing, please?:

  • = checkpoint_timeout
  • max_wal_size

= Thank you,

Sam

--

Samuel Stearns
Team Lead - Database<= span style=3D"font-size:11pt;font-family:Inter,sans-serif;color:rgb(102,102= ,102);background-color:transparent;vertical-align:baseline">
c: 971 762 6879 |<= span style=3D"font-size:11pt;font-family:Inter,sans-serif;color:rgb(102,102= ,102);background-color:transparent;vertical-align:baseline"> o: 971 762 687= 9 | DAT.com




--

Samuel Stearns
Team Lead - Databas= e
c: 971 76= 2 6879 = | o: 97= 1 762 6879 | D= AT.com




= --

Samuel Stearns
Team Lead - D= atabase=
c: = 971 762 6879 |= o: 971 762 6879 | DAT.com