Statistical information about tables in a PostgreSQL Database.

This query to the PostgreSQL database retrieves statistical information about tables located in the ‘public’ schema. The query helps assess the activity and state of these tables, which is useful for performance optimization and maintenance planning.

select 
 schemaname, 
 relname, 
 n_tup_ins, 
 n_tup_upd, 
 n_tup_del, 
 n_live_tup, 
 n_dead_tup, 
 last_vacuum, 
 last_autovacuum, 
 last_analyze, 
 last_autoanalyze 
from 
 pg_stat_all_tables 
where 
 schemaname = 'public'
order by 
 n_dead_tup desc;

Here is a detailed description of each column in the query:

schemaname: The name of the schema where the tables are located. In this case, it is filtered to ‘public’, which is the standard schema for user data in PostgreSQL.
relname: The name of the table within the schema.
n_tup_ins: The number of tuples (rows of data) that have been inserted into the table.
n_tup_upd: The number of tuples that have been updated in the table.
n_tup_del: The number of tuples that have been deleted from the table.
n_live_tup: The number of “live” tuples in the table. “Live” tuples are those that are available for current transactions or will be available for future transactions.
n_dead_tup: The number of “dead” tuples in the table. “Dead” tuples are those that have been marked as deleted or obsolete due to updates and need to be cleaned up in an automatic or manual VACUUM process.
last_vacuum: The date and time of the last manual VACUUM operation performed for this table.
last_autovacuum: The date and time of the last automatic VACUUM operation for this table.
last_analyze: The date and time of the last manual ANALYZE operation performed for this table, which helps update the statistics for the query optimizer.
last_autoanalyze: The date and time of the last automatic ANALYZE operation for this table.

Sorting the query results by n_dead_tup in descending order helps identify tables that may urgently need maintenance through a VACUUM operation. Tables with a high number of “dead” tuples may experience reduced performance due to the excessive volume of outdated data occupying storage and affecting the speed of query execution.

To collect DB statistics and to do vacuum (regular, not FULL) on all the objects of all the DBs of the PostgreSQL DB instance run the following command:

vacuumdb -h <db_host> -p <db_port> -U <db_user> -j 4 -z -v -a

To collect DB statistics and to do vacuum (regular, not FULL) on all the objects of some specific database of the PostgreSQL DB instance run the following command:

vacuumdb -h <db_host> -p <db_port> -U <db_user> -j 4 -z -v <db_name>