Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How To Enable PostgreSQL table level autovacuum

Not much need to address how important vacuum is in postgresql, it's simple to enable it, but when you enable autovacuum, you may need to have a look at table level autovacuum, particularly if you are in a environment that tables have very different sizes.
First of all, PostgreSQL autovacuum is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples. These checks use the statistics collection facility; therefore, autovacuum cannot be used unless track_counts is set to true.
In the default configuration, autovacuuming is enabled and the related configuration parameters are appropriately set.Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed (this also applies to those tables whose freeze max age has been modified via storage parameters; see below). Otherwise, if the number of tuples obsoleted since the last VACUUM exceeds the "vacuum threshold", the table is vacuumed.
The vacuum threshold is defined as:
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples. The number of obsolete tuples is obtained from the statistics collector; it is a semi-accurate count updated by each UPDATE and DELETE operation. (It is only semi-accurate because some information might be lost under heavy load.) If the relfrozenxid value of the table is more than vacuum_freeze_table_age transactions old, the whole table is scanned to freeze old tuples and advance relfrozenxid, otherwise only pages that have been modified since the last vacuum are scanned.
For analyze, a similar condition is used: the threshold, defined as:
analyze threshold = analyze base threshold + analyze scale factor * number of tuples
is compared to the total number of tuples inserted, updated, or deleted since the last ANALYZE.
Base on the vacuum and analyze threshod global setting, you can easily find that in a large scaling postgres environment, more likely, large tables won't get effect vacuumed, in the main time, small tables may get too much, hence, table level autovacuum setting is needed.
Here are steps to check and set table level autovacuum.

1. See what tables have a high number of 'n_dead_tup'

# select relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup 
  from pg_stat_all_tables where n_dead_tup > 0;

2. See when the tables have been last autoanalzye and autovacuum

SELECT relname, last_autoanalyze, last_autovacuum FROM pg_stat_all_tables;

3. To change the autovacuum and autoanalyze parameter at the table level

# ALTER TABLE <tablename>
SET (autovacuum_enabled = true,
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_analyze_scale_factor = 0.005);

4. Check the autovacuum and autoanalyze setting.

#\d+ <tablename>

5. To check if autovacuum of the table is running in PostgreSQL

#select usename, current_query, client_addr, client_port from pg_stat_activity 
 where datname='dbname';

Here are detailed description about threshold and scale factors.

autovacuum_vacuum_threshold (integer)Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.
autovacuum_analyze_threshold (integer)Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.
autovacuum_vacuum_scale_factor (floating point)Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.
autovacuum_analyze_scale_factor (floating point)Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size). This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

Other parameters related with autovacuum are listed in this page, such as max_workers, naptime etc.. Very easy to understand and adjust.

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL