postgresql_management

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
postgresql_management [2024/07/17 20:06] – [VACUUMING] andonovjpostgresql_management [2024/07/18 04:41] (current) – [VACUUMING] andonovj
Line 467: Line 467:
  
 =====VACUUMING===== =====VACUUMING=====
-VACUUM Tips and Best Practices 
-Tried and tested tips and best practices for PostgreSQL vacuuming 
 Vacuuming should be part of routine database maintenance, and there are optimal ways to go about it. Below are some performance tips for PostgreSQL vacuuming. Vacuuming should be part of routine database maintenance, and there are optimal ways to go about it. Below are some performance tips for PostgreSQL vacuuming.
 Don’t run manual VACUUM or ANALYZE without reason. Don’t run manual VACUUM or ANALYZE without reason.
Line 504: Line 502:
     ALTER TABLE <table_name> SET (autovacuum_analyze_threshold = <threshold rows>)     ALTER TABLE <table_name> SET (autovacuum_analyze_threshold = <threshold rows>)
  
-    Fine-tune Autovacuum workers+Fine-tune Autovacuum workers
 Another parameter often overlooked is autovacuum_max_workers, with a default value of 3. Autovacuum is not a single process but a number of individual vacuum threads running in parallel. The reason for specifying multiple workers is to ensure that vacuuming large tables isn’t holding up vacuuming smaller tables and user sessions. The autovacuum_max_workers parameter tells PostgreSQL to spin up the number of autovacuum worker threads to do the cleanup. Another parameter often overlooked is autovacuum_max_workers, with a default value of 3. Autovacuum is not a single process but a number of individual vacuum threads running in parallel. The reason for specifying multiple workers is to ensure that vacuuming large tables isn’t holding up vacuuming smaller tables and user sessions. The autovacuum_max_workers parameter tells PostgreSQL to spin up the number of autovacuum worker threads to do the cleanup.
 A common practice by PostgreSQL DBAs is to increase the number of maximum worker threads to speed up autovacuum. This doesn’t work as all the threads share the same autovacuum_vacuum_cost_limit, which has a default value of 200. Each autovacuum thread is assigned a cost limit using the formula shown below: A common practice by PostgreSQL DBAs is to increase the number of maximum worker threads to speed up autovacuum. This doesn’t work as all the threads share the same autovacuum_vacuum_cost_limit, which has a default value of 200. Each autovacuum thread is assigned a cost limit using the formula shown below:
Line 522: Line 520:
 A better way is to tune these parameters for individual tables only when necessary. For example, if the autovacuum of a large transactional table is taking too long, the table may be temporarily configured to use its own vacuum cost limit and cost delays. The cost limit and delay will override the system-wide values set in postgresql.conf. A better way is to tune these parameters for individual tables only when necessary. For example, if the autovacuum of a large transactional table is taking too long, the table may be temporarily configured to use its own vacuum cost limit and cost delays. The cost limit and delay will override the system-wide values set in postgresql.conf.
 The code snippet below shows how to configure individual tables. The code snippet below shows how to configure individual tables.
-ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>) + 
-ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>)+  * ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>) 
 +  * ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>) 
 Using the first parameter will ensure the autovacuum thread assigned to the table performs more work before going to sleep. Lowering the autovacuum_vacuum_cost_delay will also mean the thread sleeps for less time. Using the first parameter will ensure the autovacuum thread assigned to the table performs more work before going to sleep. Lowering the autovacuum_vacuum_cost_delay will also mean the thread sleeps for less time.
 Get more best practice tips from our professional team of PostgreSQL experts: Get more best practice tips from our professional team of PostgreSQL experts:
  
 ===Examples=== ===Examples===
  • postgresql_management.1721246785.txt.gz
  • Last modified: 2024/07/17 20:06
  • by andonovj