Postgresql – Why write ahead logs in PostgreSQL are generated every second

configurationpostgresqltransaction-log

PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute.

Is it possible to make them occur less frequently?

Is it possible to increase the WAL file size, so that fewer WALs will be generated in one minute?

Best Answer

Start with the manual page on Write Ahead Log

wal_writer_delay (integer) Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting wal_writer_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the postgresql.conf file or on the server command line.

Other options on that page can also have an effect. Also consider that many small writes can easily be maintained but one big write can cause delays and increase the data lost if the power goes out.

Related Question