PostgreSQL Parameters

shared_buffers

This is the most important parameter which impacts the performance directly. It basically sets the amount of memory the database server uses for shared memory buffers.

25-40% of server memory.

**max_wal_size

This parameter sets the WAL to grow to between checkpoints. Where shared_buffers is set to higher values , max_wal_size should be increased as well.

10GB-100GB

huge_pages

With huge pages set in memory it will create 2MB pages and not the standard 4KB page size. This improves virtual memory management where the kernel keeps a table containing a mapping of virtual memory addresses to physical addresses. With each transaction, the kernel needs to load related mapping and if the page size is small then lot of pages must be loaded. This causes a decrease in performance and thus it is recommended to set hub_pages to on.

on

wal_level

This sets the amount of information written to the write-ahead-log.

Performance : "minimal"

Data Integrity : "replica"

synchronous_commit

This parameter specifies whether each transaction or WAL records are to be written to disk before the command returns success to the client.

Performance : "off"

Data Integrity : "on"

**checkpoint_timeout

This parameter controls the maximum time between automatic checkpoints.

For production it is recommended to set for 5 minutes. For performance-based systems set it to 30 minutes or more.

Performance : > 30 minutes

Data Integrity : < 5 minutes

Check_point_completion target

This parameter specifies the time spent flushing dirty buffers during checkpoint. Setting this will slow down the checkpoint IO as checkpoints will take longer.

0.2-0.5.

effective _io_concurrency

This sets the number of concurrent disk I/O operations in PostgreSQL. Increasing this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel. Increasing this value can increase query performance.

256-512

wal_sync_method

This parameter determines which method to use for WAL updates out to disk.

fdatasync()

Max_worker_processes

Max_parallel_workers

These parameters help PostgreSQL to scale vertically, especially when parallel queries are run. It is best to keep both the parameters to the same value.

Set to the same as the number of physical cores in the database server.

wal_compression

This parameter controls the compression of full-page writes written ito the write-ahead-log.

off