Sobes.tech
Middle

What PostgreSQL parameters do you know for performance tuning?

sobes.tech AI

Answer from AI

Performance tuning of PostgreSQL often involves adjusting the following parameters:

  • shared_buffers — the amount of memory allocated for caching data. It is usually recommended to set it to about 25-40% of the available RAM.
  • work_mem — the amount of memory allocated for sorting and hashing operations per query. Increasing it can speed up complex queries, but too large a value may lead to memory shortages.
  • maintenance_work_mem — memory for maintenance operations such as VACUUM, CREATE INDEX.
  • effective_cache_size — an estimate of the memory available for file system caching, affecting the query planner.
  • max_parallel_workers_per_gather — the number of parallel workers for executing queries.
  • random_page_cost — the cost of a random disk page read, influencing the execution plan choice.
  • checkpoint_segments (replaced by max_wal_size in newer versions) — the size of WAL to control checkpoint frequency.
  • autovacuum parameters — settings for automatic table cleanup and analysis to prevent bloat and maintain statistics.

Configuration depends on the specific workload and hardware, so monitoring and gradual adjustments are important.