This article will show you how to determine when your database tables were last vacuumed, auto-vacuumed, analyzed, and auto-analyzed on a PostgresSQL database. Performing these operations are critical in keeping the database optimized and performant.
Table of Contents
What is the purpose of PostgreSQL Vacuum?
PostgreSQL’s “VACUUM” is a maintenance operation designed to reclaim storage occupied by expired tuples (rows) in a table, which become obsolete as a result of update and delete operations. Without a periodic VACUUM operation, these outdated rows would continue to consume disk space and could lead to database performance issues.
In essence, the VACUUM command removes these expired tuples and frees up space for reuse. Moreover, it also updates data statistics used by the PostgreSQL query planner to optimize query execution.
Two types of VACUUM operations exist – standard VACUUM and VACUUM FULL. The standard VACUUM frees space for reuse within the database, but does not return it to the operating system. On the other hand, VACUUM FULL compacts the table by writing a complete new version of it, thereby reclaiming more space but at the cost of exclusive locking the table during the operation.
What is the purpose of PostgreSQL Analyze?
PostgreSQL’s “ANALYZE” is a command that’s used to collect statistics about the contents of tables in the database, which are subsequently used by the PostgreSQL query planner to help determine the most efficient execution plans for queries.
When data is inserted, updated, or deleted in tables, the distribution of data can change significantly, which may make previously optimized query plans less efficient. Running the ANALYZE command helps PostgreSQL understand the current state of the tables and their data distribution.
ANALYZE can be run on its own, or as a part of the VACUUM command when used as VACUUM ANALYZE. This command updates statistics by sampling a subset of the table’s data. It calculates values like the total number of rows, the number of distinct values in a column, and the correlation between the physical order of table rows and the order of data in indexed columns. These statistics guide PostgreSQL in choosing the optimal query execution plan.
Find Last Auto-Vacuum, Vacuum, Auto-Analyzed Analyzed Date
Login to the PostgresSQL command-line interface
psql -U [username] [database_name]Run the following query:
select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;Frequently Asked Questions (FAQ)
How do I Vacuum a PostgreSQL Database?
To run PostgreSQL’s VACUUM command on a database, open a SQL command line (such as psql) and connect to your database. Then, execute the VACUUM command on the desired table like this: VACUUM (VERBOSE, ANALYZE) table_name;. Replace “table_name” with your actual table’s name. For a full database vacuum, just use VACUUM;. Remember, you must have the necessary permissions to execute VACUUM, typically requiring superuser, database owner, or table owner privileges.