Differences

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

--- postgresql_management [2024/07/17 20:05] – [VACUUMING] andonovj
+++ postgresql_management [2024/07/18 04:41] (current) – [VACUUMING] andonovj
@@ Line 467: / Line 467: @@
 =====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.
 Don’t run manual VACUUM or ANALYZE without reason.
@@ Line 504: / Line 502: @@
     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.
 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:
-    individual thread’s cost_limit = autovacuum_vacuum_cost_limit / autovacuum_max_workers
-    The cost of work done by an autovacuum thread is calculated using three parameters:
+individual thread’s cost_limit = autovacuum_vacuum_cost_limit / autovacuum_max_workers
-        vacuum_cost_page_hit: this has a default value of 1
-        vacuum_cost_page_miss: this has a default value of 10
+The cost of work done by an autovacuum thread is calculated using three parameters:
-        vacuum_cost_page_dirty: this has a default value of 20
+  - vacuum_cost_page_hit: this has a default value of 1
-    What these parameters mean is this:
+  - vacuum_cost_page_miss: this has a default value of 10
-        When a vacuum thread finds the data page that it’s supposed to clean in the shared buffer, the cost is 1.
+  - vacuum_cost_page_dirty: this has a default value of 20
-        If the data page is not in the shared buffer but the OS cache, the cost will be 10.
-        If the page has to be marked dirty because the vacuum thread had to delete dead rows, the cost will be 20.
+What these parameters mean is this:
-    An increased number of worker threads will lower the cost limit for each thread. As each thread is assigned a lower cost limit, it will go to sleep more often as the cost threshold is easily reached, ultimately causing the whole vacuum process to run slow. We recommend increasing the autovacuum_vacuum_cost_limit to a higher value, like 2000, and then adjusting the maximum number of worker threads.
+When a vacuum thread finds the data page that it’s supposed to clean in the shared buffer, the cost is 1.
-    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.
+If the data page is not in the shared buffer but the OS cache, the cost will be 10.
-    The code snippet below shows how to configure individual tables.
+If the page has to be marked dirty because the vacuum thread had to delete dead rows, the cost will be 20.
-    ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>)
+An increased number of worker threads will lower the cost limit for each thread. As each thread is assigned a lower cost limit, it will go to sleep more often as the cost threshold is easily reached, ultimately causing the whole vacuum process to run slow. We recommend increasing the autovacuum_vacuum_cost_limit to a higher value, like 2000, and then adjusting the maximum number of worker threads.
-    ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>)
+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.
-    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.
+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>)
+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:
 ===Examples===