DB‎ > ‎Postgres‎ > ‎

Postgresql table level auto autovacuum

Thank you for visiting this page, this page has been update in another link PostgreSQL table level autovacuum setup
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.

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.

So, 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.

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);

Check the setting.
#\d+ <tablename>

4. 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';


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

http://www.postgresql.org/docs/9.2/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-THRESHOLD





Comments