PostgreSQL performance tuning for self-hosted runZero

When running the runZero console on premise, the performance of your PostgreSQL database can make a significant difference to system performance — particularly for data export. Here are a few configuration parameters you will probably want to adjust:

• The default for shared_buffers is 128kB. The recommendation from the PostgreSQL developers is that if performance is a concern, start by making it around 25% of available RAM. (If your database server is a VM, that will either be 25% of the virtual hardware’s RAM, or 25% of the physical host’s RAM, whichever is smaller.)

• For a system doing lots of writes, max_wal_size should probably be around 20GB. If this parameter is too small, you will get warning messages in the PostgreSQL log suggesting that it be increased.

• For a system storing database data on a RAID array, you can set effective_io_concurrency to be the number of physical drives, excluding parity drives.

• If the database is running on fast NVMe/SSD drives, there are two settings you can adjust to tell PostgreSQL that random access to data is no more expensive than sequential: random_page_cost and seq_page_cost. The default is to assume that random access is 40× as expensive. Setting both values to 1 will tell the database that they have equal cost, and indexes will be used more often.