The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/runtime-config-wal.html Description:
The PostgreSQL manual contains the following paragraph https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT about the archive_timeout: > archive_timeout (integer) > Note that archived files that are closed early due to a forced switch are still the same length as completely full files. Therefore, it is unwise to use a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable. The description mentions that a short archive_timeout would cause storage cost but miss the potential performance loss of a short archive_timeout. Also, while it seems setting the archive_mode to off will disable the effect of this parameter, it is not the case. Therefore, I think it would be great to elaborate this description further: > "A short archive_timeout would also lead to performance penalties if the wal_sync_method is open_sync. Turning the archive_mode to off would not mitigate the performance loss" Rationale: I test the archive_timeout on PostgreSQL 11 with a small table (50MB) on Ubuntu 18.04. I run the sysbench with an insert-intensive workload. I tweak the configuration parameters below: - wal_sync_method = open_sync - archive_mode = on - archive_command = 'cp %p /path-to-data/pg_archive/%f' - archive_timeout = 1/5/10/30/60 When the archive_timeout is 1, the average latency is 35.61 ms. When the archive_timeout is 5, the average latency is 8.05ms. When the archive_timeout is 10, the average latency is 6.8 ms. When the archive_timeout is 30, the average latency is 5.3 ms. When the archive_timeout is 60, the average latency is 4.77 ms. When the archive_timeout is 0, the average latency is 4.71 ms. The result shows that if the archive_timeout is short and the wal_sync_method is open_sync, the system will suffer significant performance degradation. The performance regression will not be mitigated by closing the archive_mode. If I change the wal_sync_method to other options, the average latency doesn't change significantly when I change the archive_timeout. Some guidance books like PostgreSQL 9 Administration Cookbook suggests a relative short value like 30 seconds. It seems that some users in practice will use a low timeout value like 5 seconds: e.g., https://dba.stackexchange.com/questions/194629/file-creation-dates-of-archived-wal-files-not-in-line-with-archive-timeout-in-po, perhaps because they are not worried much about the storage cost. Thus, I think it would be helpful to include the caveat about the performance impact in the documentation as well.