Modifying Database Parameters

Purpose

Modify database parameter settings to improve server performance.

Procedure

Modify the database parameters as follows. The default configuration file path is /pgsql/postgresql.conf. Change the path based on your requirements.

listen_addresses = '*'
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 800
max_wal_size = 20GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
max_connections = 4096
checkpoint_timeout = 60min
full_page_writes = off
max_files_per_process = 100000
max_prepared_transactions = 2048
shared_buffers = 150GB
wal_buffers = 1GB
work_mem = 1GB
log_min_messages = FATAL
synchronous_commit = on
fsync = on
maintenance_work_mem = 2GB
vacuum_cost_limit = 10000
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 20s
autovacuum_vacuum_scale_factor = 0.002
autovacuum_analyze_scale_factor = 0.001

**Table 1** Database parameters
Parameter	Description	Tuning Suggestion
max_connections	Maximum number of concurrent connections allowed by the client	Set this parameter based on your requirements.
bgwriter_delay	Sleep time of the background database write process	Set this parameter to the minimum value: 10 ms
bgwriter_lru_maxpages	Maximum number of dirty data blocks written to external files by a background database write process each time	-
max_wal_size	Maximum size of a WAL x_log file	Set this parameter to a larger value to prevent frequent checkpoints.
checkpoint_completion_target	Target of checkpoint completion, as a fraction (N%) of total time between two checkpoints	Set this parameter to a larger value to prevent affecting the performance test.
checkpoint_timeout	Checkpoint interval	-
full_page_writes	A full page is written to WAL when it is first modified after a checkpoint.	If full_page_writes is set to off, only the modified part of the page is written to drives. This improves performance but may increase the risk of data damage when the system breaks down. You are advised to set this parameter to on in the production environment.
shared_buffers	Size of the memory that can be used by PostgreSQL to cache data	Recommended value: no more than 60% of the physical memory. You can change the value based on your requirements.
wal_buffers	Log buffer size	-
work_mem	Size of the memory buffer used by the sorting operation of the database and the hash table	-
log_min_messages	Level of messages that are written to the database log	-
fsync	Whether data is synchronized to drives	This function is enabled by default. If it is disabled, data reliability will be affected.
synchronous_commit	Whether to enable synchronous log submission	This function is enabled by default. If it is disabled, data reliability will be affected.
maintenance_work_mem	Memory used for database maintenance operations including VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY	-
autovacuum_max_workers	Maximum number of autovacuum worker processes	-
autovacuum_naptime	Interval between two autovacuum operations	-

Parent topic: Database Tuning