public inbox for [email protected]help / color / mirror / Atom feed
Postgres Parameters 7+ messages / 5 participants [nested] [flat]
* Postgres Parameters @ 2025-11-10 23:28 Sam Stearns <[email protected]> 0 siblings, 3 replies; 7+ messages in thread From: Sam Stearns @ 2025-11-10 23:28 UTC (permalink / raw) To: Pgsql-admin <[email protected]> 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 <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres Parameters @ 2025-11-11 01:16 Ron Johnson <[email protected]> parent: Sam Stearns <[email protected]> 2 siblings, 0 replies; 7+ messages in thread From: Ron Johnson @ 2025-11-11 01:16 UTC (permalink / raw) To: Pgsql-admin <[email protected]> On Mon, Nov 10, 2025 at 6:28 PM Sam Stearns <[email protected]> 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! ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres Parameters @ 2025-11-11 08:57 Laurenz Albe <[email protected]> parent: Sam Stearns <[email protected]> 2 siblings, 0 replies; 7+ messages in thread From: Laurenz Albe @ 2025-11-11 08:57 UTC (permalink / raw) To: Sam Stearns <[email protected]>; Pgsql-admin <[email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres Parameters @ 2025-11-11 11:19 Fabrice Chapuis <[email protected]> parent: Sam Stearns <[email protected]> 2 siblings, 1 reply; 7+ messages in thread From: Fabrice Chapuis @ 2025-11-11 11:19 UTC (permalink / raw) To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]> 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 <[email protected]> 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 > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres Parameters @ 2025-11-11 15:24 Sam Stearns <[email protected]> parent: Fabrice Chapuis <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Sam Stearns @ 2025-11-11 15:24 UTC (permalink / raw) To: Fabrice Chapuis <[email protected]>; Laurenz Albe <[email protected]>; Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Henry Ashu <[email protected]> Awesome stuff. Thank you, everyone! Sam On Tue, Nov 11, 2025 at 3:19 AM Fabrice Chapuis <[email protected]> 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 <[email protected]> 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 >> >> >> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >> >> -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: Postgres Parameters @ 2025-11-12 02:26 [email protected] <[email protected]> parent: Sam Stearns <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: [email protected] @ 2025-11-12 02:26 UTC (permalink / raw) To: Sam Stearns <[email protected]>; Fabrice Chapuis <[email protected]>; Laurenz Albe <[email protected]>; Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Henry Ashu <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: Postgres Parameters @ 2025-11-12 15:12 Sam Stearns <[email protected]> parent: [email protected] <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Sam Stearns @ 2025-11-12 15:12 UTC (permalink / raw) To: [email protected] <[email protected]>; +Cc: Fabrice Chapuis <[email protected]>; Laurenz Albe <[email protected]>; Ron Johnson <[email protected]>; Pgsql-admin <[email protected]>; Henry Ashu <[email protected]> Thank you, Zhenwei. On Tue, Nov 11, 2025 at 6:26 PM [email protected] < [email protected]> 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 <[email protected]> > *Date:* 2025-11-11 23:24 > *To:* Fabrice Chapuis <[email protected]>; Laurenz Albe > <[email protected]>; Ron Johnson <[email protected]> > *CC:* Pgsql-admin <[email protected]>; Henry Ashu > <[email protected]> > *Subject:* Re: Postgres Parameters > Awesome stuff. Thank you, everyone! > > Sam > > > On Tue, Nov 11, 2025 at 3:19 AM Fabrice Chapuis <[email protected]> > 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 <[email protected]> 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 >>> >>> >>> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >>> >>> > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-11-12 15:12 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-11-10 23:28 Postgres Parameters Sam Stearns <[email protected]> 2025-11-11 01:16 ` Ron Johnson <[email protected]> 2025-11-11 08:57 ` Laurenz Albe <[email protected]> 2025-11-11 11:19 ` Fabrice Chapuis <[email protected]> 2025-11-11 15:24 ` Sam Stearns <[email protected]> 2025-11-12 02:26 ` [email protected] <[email protected]> 2025-11-12 15:12 ` Sam Stearns <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox