postgres Autovacuum Autoanalyze - ghdrako/doc_snipets GitHub Wiki

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

PostgreSQL database tables are auto-vacuumed by default when 20% of the rows plus 50 rows are inserted, updated, or deleted. Tables are auto-analyzed when a threshold is met for 10% of the rows plus 50 rows. For example, a table with 10000 rows is not auto-vacuumed until 2050 rows are inserted, updated, or deleted. That same table is auto-analyzed when 1050 rows are inserted, updated, or deleted.

Autovacuum does not recover the disk space taken up by dead tuples.

information about the last time the ebk schema tables were vacuumed or analyzed:

select relname,last_vacuum, last_autovacuum, last_analyze, vacuum_count, autovacuum_count,
  last_autoanalyze from pg_stat_user_tables where schemaname = 'ebk' order by relname ASC;

autovacuum_vacuum_cost_limit, which has a default value of 200. Each autovacuum thread is assigned a cost limit using this 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:

parameters mean is this:

  • When a vacuum thread finds the data page that it’s supposed to clean in the shared buffer, the cost is 1.
  • 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.

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

PostgreSQL will start autovacuum on a table when:

pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor)  + autovacuum_vacuum_threshold

NOTE:For larger installations (that is, thousands of workloads), we recommend that you customize the settings as follows on individual tables to ensure optimal performance for data access:

  • The scale factor should be set to zero for both vacuum and analyze auto-vacuum settings.
  • The threshold should be set to 1000 for both the vacuum and analyze threshold settings.
ALTER TABLE ebk.tab1 SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE ebk.workload_cost SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE ebk.workload_cost SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE ebk.workload_cost SET (autovacuum_analyze_threshold = 1000);

Autoanalyze autoanalyze also uses two parameters that decide when autovacuum will also trigger an autoanalyze:

  • autovacuum_analyze_threshold: this has a default value of 50
  • autovacuum_analyze_scale_factor: this has a default value of 0.1

Modifying the autovacuum_analyze_threshold setting for a table.

ALTER TABLE <table_name> 
SET (autovacuum_analyze_threshold = <threshold rows>)

Manual analyze

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

Turn off autovacume for table

CREATE TABLE vac( id integer, s char(100) ) 
WITH (autovacuum_enabled = off);

Python

def vacuum(self):
    old_isolation_level = self.conn.isolation_level
    self.conn.set_isolation_level(0)
    query = "VACUUM FULL"
    self._doQuery(query)
    self.conn.set_isolation_level(old_isolation_level)

Java

final Statement st2 = connection.createStatement();
st2.executeUpdate("VACUUM FULL ANALYZE VERBOSE");
st2.close();
⚠️ **GitHub.com Fallback** ⚠️