In previous lessons we’ve discussed the
VACUUM command and it’s limitations. One nice property about
VACUUM is that it allows space to be reused. If tables are vacuumed frequently enough, the disk usage of a table will stay relatively steady because updates will never get “too far ahead” of the required space on disk. To ensure that vacuuming isn’t left completely to the database users, PostgreSQL has a feature called
autovacuum enabled on most databases by default. When using autovacuum, PostgreSQL periodically checks for tables that have had a large number of inserted, updated or deleted tuples that could be vacuumed to improve performance. When
autovacuum is enabled and finds such a table, a
VACUUM ANALYZE command is run. This statement is a combination of two separate operations.
VACUUM, which manages the dead tuples in a database table
ANALYZE, which is a statement that allows PostgreSQL look at a table and gather information about contents. PostgreSQL then stores this data internally and uses it to ensure that queries are planned in the most efficient way given the structure of the table.
You can leave running
ANALYZE statements to the
autovacuum process if you’ like. However, you can also run it yourself with
VACUUM ANALYZE <table name>; or just
ANALYZE <table name> if you haven’t made large inserts or updates and would like to update
You can monitor the last vacuum or
autovacuum by querying the table
pg_stat_all_tables for vacuum and analyze statistics.
pg_stat_all_tables is table that contains internal PostgreSQL statistics; you can query for a specific table’s statistics by filtering on the column
relname (i.e. relation name).
For example, the following query could be use to check statistics for a table named
SELECT relname, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_all_tables WHERE relname = 'books';
|books||2021-02-01 00:13:14||2021-02-01 00:00:00||2021-02-01 00:13:14|
In the example above, from the
last_autovacuum column we can tell that a
autovaccum ran at midnight on 2021-02-01. From the
last_analyze columns we can see that several minutes later a user manually ran a
pg_stat_all_tables contains quite a few useful fields; you can see a full list of the fields this table contains here.
We have a newly created table in the database. Querying
pg_stat_all_tables to check the table’s vacuum statistics returns the following:
SELECT schemaname, relname, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_all_tables WHERE relname = 'orders';
First, let’s run
ANALYZE doesn’t produce an output, so we’ll have confirm the statistics have been updated in a separate query.
pg_stat_all_tables for the same statistics shown to you in part 1. Are the results the same?