How to control automatic database maintenance operations in PostgreSQL 15?

How to control automatic database maintenance operations in PostgreSQL 15?


In PostgreSQL 15, automatic database maintenance is controlled by several configuration parameters. Here are a few examples:

  1. maintenance_work_mem – this parameter controls the amount of memory used for maintenance operations such as creating and rebuilding indexes, vacuuming, and creating statistics.
  2. autovacuum_vacuum_scale_factor – this parameter controls the proportion of table size that must be changed before autovacuum is triggered.
  3. autovacuum_vacuum_cost_limit – this parameter controls the amount of I/O and CPU that can be used before autovacuum is halted.
  4. autovacuum_analyze_scale_factor – this parameter controls the proportion of table size that must be changed before autovacuum analyzes the table.
  5. autovacuum_analyze_threshold – this parameter controls the minimum number of rows that must be changed before autovacuum analyzes the table.
  6. bgwriter_lru_maxpages – this parameter controls the number of dirty pages that can be held in memory before the background writer starts flushing them to disk.

You can tune these parameters in the postgresql.conf file to control the automatic maintenance process in PostgreSQL 15 and adjust it to the resources and workload of your specific use case.

About Shiv Iyer 466 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.