The database keeps transaction logs in the pg_wal directory.

The max size of this directory is defined by the config setting max_wal_size.

The database will on schedule flush those transactions logs. It does this through a mechanism known as checkpointing.

The database spreads the cost of flushing those over time to reduce resource contention. This is determined by the config setting checkpoint_timeout and checkpoint_completion_target.

If your database is transactionally heavy or has heavy periods it can produce transactions log at a rate faster than it can checkpoint.

This causes the flushes to happen more frequently which can cause resources to be consumed unnecessarily.

To help prevent this you can increase the size allowed in this directory by adjusting the max_wal_size setting.

Answer from Gurmokh on Stack Overflow
🌐
Crunchy Data Blog
crunchydata.com › blog › tuning-your-postgres-database-for-high-write-loads
Tuning Your Postgres Database for High Write Loads | Crunchy Data Blog
October 14, 2020 - As these systems receive increased traffic load beyond their original proof-of-concept sizes, one issue may be observed in the Postgres logs as the following: LOG: checkpoints are occurring too frequently (9 seconds apart) HINT: Consider increasing the configuration parameter "max_wal_size".
Discussions

How should I update max_wal_size using fly postgres?
I keep getting this in my logs: LOG: checkpoints are occurring too frequently (2 seconds apart) HINT: Consider increasing the configuration parameter "max_wal_size". I tried: flyctl postgres config update --max-wal-size But got Error: unknown flag: --max-wal-size Then I tried in PSQL: =# ALTER ... More on community.fly.io
🌐 community.fly.io
3
0
December 18, 2023
Timescale Docker Container max_wal_size
Is there a way to set max_wal_size in the docker container? If so, what is a good value to set it at? I'm seeing the below in splunk: LOG: checkpoints are occurring too frequently (15 seconds a... More on github.com
🌐 github.com
3
March 2, 2018
Consider increasing the configuration parameter "max_wal_size".
Hello. Any advice what does it mean and how can I fix it (if requires): LOG: checkpoints are occurring too frequently (29 seconds apart) HINT: Consider increasing the configuration parameter "max_wal_size". Hw: i5 (7400-3Ghz) cpu with 40... More on github.com
🌐 github.com
0
July 7, 2022
Consider increasing the configuration parameter "max_wal_size"
Hi, I’m trying change the smtp address of my smtp email server. I edit my app.yml, change the address, :wq to save, and try rebuild the app. In a specific point, the system warn me this error. Using spork-rails 4.0.0 0: spork-rails (4.0.0) from /var/www/discourse/vendor/bundle/ruby/2.3.0... More on meta.discourse.org
🌐 meta.discourse.org
2
0
July 12, 2016
Top answer
1 of 2
4

max_wal_size controls the total size of all WAL segments, not the size of each segment.

So with a value of 2GB, you are saying that about 128 of those segments can be created before they get re-used.

The hint is about increasing the total number of files (hence the mentioning of max_wal_size), it's not about the size of each segment (file).


Up to Postgres 10, the size of each segment is fixed to 16MB

Starting with Postgres 11, you can change the size of each segment but only when you initialize a new database cluster (but again: that's not what that hint is about).

2 of 2
2

Because of the "title"...

Particular WAL file size can also be changed since PostgreSQL 11 not only using initdb command when creating cluster, but also later when cluster is already running using pg_resetwal command.

  1. Check current WAL file size setting (default is 16 MB).

    a) Using parameter.

    psql -c "show wal_segment_size"

    b) Check actual WAL file size.

    ls -lh $PGDATA/pg_wal/

    c) With select.

    SELECT name, size FROM pg_ls_waldir() where name not like '%history' and name not like '%backup';

  2. Stop PostgreSQL cluster (on Red Hat).

    systemctl stop postgresql-15.service

  3. Change WAL file size.

    pg_resetwal -D $PGDATA --wal-segsize=64

    Size is in MB. Default is 16. Parameter can be set between 1 and 1024.

    Note: Command on Red Hat 8 is at path: /usr/pgsql-15/bin/pg_resetwal

  4. Start PostgreSQL cluster (on Red Hat).

    systemctl start postgresql-15.service

  5. Repeat step 1 and to see new WAL file setting.

Additional info:

  • official PostgreSQL documentation search for "wal-segsize"
  • The WAL segment size becomes changeable in PostgreSQL 11
🌐
EnterpriseDB
enterprisedb.com › blog › tuning-maxwalsize-postgresql
Tuning max_wal_size in PostgreSQL | EDB
March 17, 2023 - Tuning of the max_wal_size configuration parameter in PostgreSQL can have a profound effect on performance, but it's often overlooked or not given the priority it deserves in favour of other parameters, such as those that are easier to understand. In this blog we'll discuss what the parameter does, how to tune it, and how to monitor the system to ensure the tuning is effective.
🌐
PostgreSQL
postgresql.org › docs › current › wal-configuration.html
PostgreSQL: Documentation: 18: 28.5. WAL Configuration
February 26, 2026 - If checkpoints happen closer together than checkpoint_warning seconds, a message will be output to the server log recommending increasing max_wal_size. Occasional appearance of such a message is not cause for alarm, but if it appears often then ...
🌐
Fly.io
community.fly.io › questions / help
How should I update max_wal_size using fly postgres? - Questions / Help - Fly.io
December 18, 2023 - I tried: flyctl postgres config update --max-wal-size But got Error: unknown flag: --max-wal-size Then I tried in PSQL: =# ALTER SYSTEM SET max_wal_size = '500MB'; ERROR: could not fsync file "postgresql.auto.conf": Invalid argument Any ideas?
🌐
GitHub
github.com › timescale › timescaledb › issues › 452
Timescale Docker Container max_wal_size · Issue #452 · timescale/timescaledb
March 2, 2018 - HINT: Consider increasing the configuration parameter "max_wal_size".
Author   SimplySeth
🌐
PostgreSQL
postgresqlco.nf › en › doc › param › max_wal_size
PostgreSQL Documentation: max_wal_size parameter
max_wal_size: Min: 2 (2MB), Max: 2147483647 (2147483647MB), Default: 1024 (1GB), Context: sighup, Needs restart: false • Sets the WAL size that triggers a checkpoint.
Find elsewhere
🌐
Fujitsu PostgreSQL
postgresql.fastware.com › postgresql-insider-tun-db-ove
PostgreSQL Insider - Tuning - Database tuning
If the following message is output in the log message, it means that checkpoints occur frequently, so consider increasing the max_wal_size value. LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter "max_wal_size"
🌐
GitHub
github.com › cardano-foundation › cardano-rosetta › issues › 483
Consider increasing the configuration parameter "max_wal_size". · Issue #483 · cardano-foundation/cardano-rosetta
July 7, 2022 - Hello. Any advice what does it mean and how can I fix it (if requires): LOG: checkpoints are occurring too frequently (29 seconds apart) HINT: Consider increasing the configuration parameter "max_wal_size". Hw: i5 (7400-3Ghz) cpu with 40...
Author   cscsabai
🌐
Google Groups
groups.google.com › g › google-cloud-sql-discuss › c › FG05EQOf-5c
Increasing max_wal_size
You are right - we don't provide a way to edit postgresql.conf directly, and max_wal_size is not one of parameters we expose through flags.
🌐
Discourse
meta.discourse.org › support
Consider increasing the configuration parameter "max_wal_size" - Support - Discourse Meta
July 12, 2016 - Hi, I’m trying change the smtp address of my smtp email server. I edit my app.yml, change the address, :wq to save, and try rebuild the app. In a specific point, the system warn me this error. Using spork-rails 4.0.0 0: spork-rails (4.0.0) from /var/www/discourse/vendor/bundle/ruby/2.3.0...
🌐
GitHub
github.com › bitnami › charts › issues › 12255
[bitnami/postgresql] postgresql.conf located in a preserved PVC appears to override values-derived configmap postgresql.conf · Issue #12255 · bitnami/charts
September 1, 2022 - 2022-09-01 20:45:50.525 UTC [14] LOG: checkpoints are occurring too frequently (4 seconds apart) 2022-09-01 20:45:50.525 UTC [14] HINT: Consider increasing the configuration parameter "max_wal_size".
Author   waTeim
🌐
GitHub
github.com › input-output-hk › cardano-db-sync › issues › 909
Postgres complains about max_wal_size · Issue #909 · IntersectMBO/cardano-db-sync
October 23, 2021 - LOG: checkpoints are occurring too frequently (17 seconds apart) HINT: Consider increasing the configuration parameter "max_wal_size".
Author   danbigthink
Top answer
1 of 1
20

Not uncommon during a whole-DB restore because that's an exceptionally huge operation. If you see this during normal operation, consider raising your setting for checkpoint_segments permanently, just like the error message hints.

You might go to the trouble of setting checkpoint_segments higher just before the restore and then lower it again. This is even what the manual suggests (including an explanation):

Temporarily increasing the checkpoint_segments configuration variable can also make large data loads faster. This is because loading a large amount of data into PostgreSQL will cause checkpoints to occur more often than the normal checkpoint frequency (specified by the checkpoint_timeout configuration variable). Whenever a checkpoint occurs, all dirty pages must be flushed to disk. By increasing checkpoint_segments temporarily during bulk data loads, the number of checkpoints that are required can be reduced.

Related answer with more details:

  • Configuring PostgreSQL for write performance

Postgres 9.5

The upcoming new release has a smarter approach. Quoting the beta release notes:

Replace configuration parameter checkpoint_segments with min_wal_size and max_wal_size (Heikki Linnakangas)

This allows the allocation of a large number of WAL files without keeping them if they are not needed. Thus the default for max_wal_size has been increased to 1GB.

Aside: the number of views is barely relevant, those do not contain any data, just the "recipe", i.e.: the query and some attributes of the view. For the question at hand, basically only the total size of the backup file matters.

🌐
Esri Community
community.esri.com › t5 › arcgis-enterprise-questions › arcgis-datastore-database-level-logs-consider › td-p › 1262375
ArcGIS Datastore database level logs - Consider increasing the configuration parameter 'max_wal_size'.
February 28, 2023 - Failed to execute). Upon further troubleshooting, we have found log messages in datastore database logs stating "Checkpoints are occurring too frequently" Hint: Consider increasing the configuration parameter 'max_wal_size'. Attached are the datastore database-level log messages.
🌐
pganalyze
pganalyze.com › docs › log-insights › wal-checkpoints › W42
W42: Checkpoints too frequent · pganalyze
LOG: checkpoints are occurring too frequently (18 seconds apart) HINT: Consider increasing the configuration parameter "max_wal_size".
🌐
GoodRequest
goodrequest.com › blog › how-to-improve-postgres-performance-optimizing-databases-and-queries
How to improve PostgreSQL performance? Try optimizing database and queries! | GoodRequest
March 1, 2023 - ‍-- Check WAL and checkpoints ... ... To improve the performance for Bulk data operations, it's necessary to increase the size of max_wal_size, checkpoint_flush_after, checkpoint_completion_target and checkpoint_timeout...