Learn

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 VACUUM and 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 pg_stat_all_tables.

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 books

SELECT relname, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_all_tables WHERE relname = 'books';

relname last_vacuum last_autovacuum last_analyze
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_vacuum and last_analyze columns we can see that several minutes later a user manually ran a VACUUM ANALYZE

pg_stat_all_tables contains quite a few useful fields; you can see a full list of the fields this table contains here.

Instructions

1.

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';
schemaname relname last_vacuum last_autovacuum last_analyze
mock orders NULL NULL NULL

First, let’s run ANALYZE on mock.orders. ANALYZE doesn’t produce an output, so we’ll have confirm the statistics have been updated in a separate query.

2.

Check pg_stat_all_tables for the same statistics shown to you in part 1. Are the results the same?

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?