PostgreSQL configuration file optimization

PostgreSQL configuration file optimization

Configuration file

The default configuration profile is the postgresql.conf file that is saved in the/etc/postgresql/version/main directory

If you want to see if the parameter modification takes effect, you can use Psql to connect to the database and use the <show option name > to view it.
If you want to modify shared_buffers, you may need to execute commands under Ubuntu <sysctl-w>managing Kernel Resources

Main options

Option Default value indicates whether the reason for optimization
Max_connections100 the maximum number of allowed client connections No because 100 connections are sufficient during the test
Fsyncon forcing data to be updated synchronously to disk because the system has a large IO pressure, in order to better test the impact of other configurations, change parameters to Off
SHARED_BUFFERS24MB determines how much memory can be used by PostgreSQL to cache data (1/4 of the recommended memory), which increases the value of IO when the IO pressure is high
WORK_MEM1MB making internal sorting and some complex queries done in this buffer helps improve the speed of sorting operations, and reduces IO
The EFFECTIVE_CACHE_SIZE128MB optimizer assumes that a query can use the maximum memory, and shared_buffers regardless (1/2 of the recommended memory) is set slightly larger and the optimizer prefers to use index scans instead of sequential scans
MAINTENANCE_WORK_MEM16MB the memory defined here is only used when a more resource-intensive command call is made, such as vacuum, to increase the value, and to speed up the execution of the command.
WAL_BUFFER768KB Log buffer size can be reduced IO, if you encounter a relatively large number of concurrent short transactions, should be used with Commit_delay
Checkpoint_segments3 set the maximum number of Wal logs (a log size of 16M) is the default 48M cache is a serious bottleneck, basically set to 10 or more
checkpoint_completion_target0.5 indicates that the completion time of the checkpoint is done in the n% of two checkpoint intervals to reduce the average write overhead
After the COMMIT_DELAY0 transaction is committed, the log is written to the time interval of wal_buffer written to disk on the Wal log. Need to mate Commit_sibling is capable of writing multiple transactions at once, reducing IO and improving performance
COMMIT_SIBLINGS5 sets the number of concurrent transactions that trigger Commit_delay, which is configured to reduce IO and improve performance based on the number of concurrent transactions

PostgreSQL configuration file optimization