There are three main parameters in wal_level: minimal, archive and hot_standby
1. Minimal is the default value, which only writes the information required for crashes or burst shutdowns (not recommended).
2. archive is the log required to increase wal archive (most commonly used).
3. hot_standby adds the information required to run read-only queries on the standby server, which is generally used when real stream replication.
Supplement: Postgresql WAL related parameters
Configuration File
# - Settings -
wal_level = minimal # minimal, replica, orlogical
# (changerequires restart)
#fsync = on # flush data to disk for crash safety
# (turningthis off can cause
# unrecoverable datacorruption)
#synchronous_commit =on # synchronization level;
# off, local,remote_write, remote_apply
,or on
#wal_sync_method =fsync # the default is thefirst option
# supported by theoperating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
full_page_writes =on # recover from partial page writes
#wal_compression =off # enable compression of full-pagewrites
#wal_log_hints =off # also do full pagewrites of non-critic
al updates
# (change requiresrestart)
#wal_buffers = -1 # min 32kB, -1 sets basedon shared_buffers
# (change requiresrestart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#wal_writer_flush_after= 1MB # 0 disables
#commit_delay = 0 # range 0-100000, inmicroseconds
#commit_siblings =5 # range 1-1000
# - Checkpoints -
#checkpoint_timeout =5min # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target= 0.5 # checkpoint target duration,0.0 - 1.0
#checkpoint_flush_after= 0 # 0 disables #default is 256kB on linux, 0 otherwise
#checkpoint_warning =30s # 0 disables
wal_level
Controls the level of wal storage. wal_level determines how much information is written to the WAL. The default value is minimal, where only the required information recovered from a crash or immediate shutdown is written. replica adds wal archive information and includes information required by the read-only server. (Newly added in 9.6, merge the previous version of archive and hot_standby)
fsync
This parameter directly controls whether the log is written to disk first. The default value is ON (write first). When this value is turned on, it indicates that the system must wait for WAL to complete when updating data is written to disk. This parameter can be configured as OFF. Update data to write to disk without waiting for WAL to complete writes. There is no waiting time. Obviously, the following work can be done earlier, saving time and improving performance. The direct hidden danger is that it cannot guarantee that the recent transactions can be restored when the system crashes, and it cannot guarantee the authenticity and correctness of the relevant data.
synchronous_commit
This parameter indicates whether to wait for WAL to complete before returning status information to the user transaction. The default value is ON, indicating that the transaction status information must be returned after the WAL is completed. Configuring the OFF value can provide faster feedback to transaction state. Since parameters only control the state feedback of transactions, there is no risk for data consistency. But the state information of the transaction affects the entire state of the database. This parameter can be flexibly configured, and transactions that do not have strict requirements for business can be configured as OFF, which can bring considerable improvements to the performance of the system.
wal_sync_method
The control method of WAL writes to disk, the default value is fsync. Optional values: open_datasync, fdatasync, fsync_writethrough, fsync, open_sync. Generally, the default value is only used. For optional configurations of bare devices or file systems, the convenience brought by Fsync in actual use is very limited.
full_page_writes
The parameter indicates whether the entire page is written to WAL. The data in the data processing process in postgresql is only stored in memory and WAL. The entire page in memory contains update commits and no commits. If the entire page is not written into WAL, the data recorded in WAL is not enough to achieve complete recovery during media recovery (to put it bluntly, it is impossible to roll back the transaction during media recovery).
wal_buffers
Memory space used to store WAL data, minimum 32K.
wal_writer_delay
Interval time of the WAL writer process. The default value is 200ms. Accurate configuration should be based on the health of your own system. If the time is too long, it may cause insufficient memory of the WAL buffer; if it is too small, it will cause continuous writing of WAL, which is also a great test for disk IO.
wal_writer_flush_after
When the number of bytes of wal write exceeds the configured threshold (wal_writer_flush_after), fsync is triggered, the default value is 1MB. If set to 0, turn off this feature (the new parameters added in version 9.6)
commit_delay
Indicates the time when a submitted data is stored in the WAL buffer, unit ms, the default value is 0, no delay is required. A non-0 value indicates that WALs with multiple transactions may be written to disk at the same time. If set to non-0, it means that a transaction will not be written to WAL immediately after committing it, but will still be stored in the WAL buffer. This is very unfavorable for subsequent transactions to apply for WAL buffer, especially during peak periods when there are many transactions submitted, which may cause insufficient memory of WAL buffer. If the memory is large enough, the parameter value can be extended as much as possible, so that writing to the data center can reduce the IO of the system and improve performance. Also if the crash data is at this time, it is in danger of loss.
commit_siblings
This parameter also determines the validity of commit_delay. The default value of the system is 5. It means that when a transaction issues a commit request, the number of transactions being executed in the database is greater than 5, the transaction will wait for a period of time (the value of commit_delay), and conversely, the transaction will be directly written to WAL.
checkpoint_timeout
The maximum interval time to generate a checkpoint.
checkpoint_completion_target
The parameter indicates the completion target of checkpoint. The system default value is 0.5, which means that each checkpoint needs to be completed within 50% of the checkpoints interval time.
PostgreSQL 9.5 abandons the checkpoint_segments parameter, and introduces the max_wal_size and min_wal_size parameters. The max_wal_size and checkpoint_completion_target parameters are used to control how many XLOGs are generated and checkpoints are triggered. The min_wal_size and max_wal_size parameters are used to control which XLOGs can be used in a loop.
min_wal_sizeMinimum wal space
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.