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.
How should I update max_wal_size using fly postgres?
Timescale Docker Container max_wal_size
Consider increasing the configuration parameter "max_wal_size".
Consider increasing the configuration parameter "max_wal_size"
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).
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.
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';Stop PostgreSQL cluster (on Red Hat).
systemctl stop postgresql-15.serviceChange WAL file size.
pg_resetwal -D $PGDATA --wal-segsize=64Size 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
Start PostgreSQL cluster (on Red Hat).
systemctl start postgresql-15.serviceRepeat 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