postgreSQL Autovacuum launcher
In the last post we discussed about checkpoint process, in this tutorial we will discuss about PostgreSQL: Autovacuum launcher process.
postgreSQLpostgreSQL Autovacuum launcher autovacuum
launcher process
Autovacuum launcher is an optional process and it is enabled by default in PostgreSQL. This process automates the execution of vacuum and analyzes commands based on a few parameters.
If autovacuum is set, then it will wake up every autovacuum_naptime seconds, and decide whether to run VACUUM, ANALYZE, or both.
But what is vacuum?
You can read in detail about vacuum in PostgreSQL
The following are the relevant parameters that can be set in postgresql.conf file for autovacuum.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
#------------------------------------------------------------------------------ # AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ #autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit #------------------------------------------------------------------------------ |
PostgreSQL implements Multi version Concurrency Control (MVCC), which allows users to read data at the same time as writers make changes. This is an important feature for
concurrency in database applications, as it can allow the following:
- Better performance because of fewer locks
- Greatly reduced deadlocking
- Simplified application design and management
Case study on Vacuum and vacuumfull:
- Create a test table
123456789testdb=>testdb=> create table vacuumtest(id integer);CREATE TABLEtestdb=>testdb=> SELECT pg_relation_filepath('vacuumtest');pg_relation_filepath----------------------base/29906/57420(1 row) - Insert some records into the table.
|
1 2 3 4 5 6 7 8 |
testdb=> INSERT INTO vacuumtest SELECT generate_series (1,10000); INSERT 0 10000 testdb=> testdb=> SELECT pg_total_relation_size('vacuumtest'); pg_total_relation_size ------------------------ 393216 (1 row) |
- Delete few records and check the size again after vacuuming.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
testdb=> delete from vacuumtest where id > 4000; DELETE 6000 testdb=> testdb=> vacuum vacuumtest; VACUUM testdb=> testdb=> SELECT pg_total_relation_size('vacuumtest'); pg_total_relation_size ------------------------ 180224 (1 row) |
- Test vacuumfull and confirm that filepath is changing for every vacuumfull.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
testdb=> SELECT pg_relation_filepath('vacuumtest'); pg_relation_filepath ---------------------- base/29906/57420 (1 row) testdb=> testdb=> vacuum full vacuumtest; VACUUM testdb=> testdb=> SELECT pg_total_relation_size('vacuumtest'); pg_total_relation_size ------------------------ 3522560 (1 row) testdb=> SELECT pg_relation_filepath('vacuumtest'); pg_relation_filepath ---------------------- base/29906/57423 (1 row) |
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
