We can compare the number of dead row versions, shown as n_dead_tup against the required threshold, av_threshold.
The preceding query doesn't take into account table-specific autovacuum thresholds. It could do so if you really need it, but the main purpose of the query is to give us information to understand what is happening, and then set the parameters accordingly—not the other way around.
Notice that the table query shows insertions, updates, and deletions, so you can understand your workload better. There is also something named the hot_update_ratio. This shows the fraction of updates that take advantage of the HOT feature, which allows a table to self-vacuum as the table changes. If that ratio is high, then you may avoid VACUUM activities altogether or at least for long periods. If the ratio is low, then you will need to execute VACUUM commands or autovacuums more frequently. Note that the ratio never reaches 1.0, so if you have it above 0.95, then that is very good and you need not think about it further.
HOT updates take place when the UPDATE statement does not change any of the column values that are indexed by any index, and there is enough free space in the disk page where the updated row is located. If you change even one column that is indexed by just one index, then it will be a non-HOT update, and there will be a performance hit. So careful selection of indexes can improve update performance and reduce the need for maintenance. Also, if HOT updates do occur, though not often enough for your liking, you might want to try to decrease the fillfactor storage parameter for the table to make more space for them. Remember that this will be important only on your most active tables. Seldom touched tables don't need much tuning.
To recap, non-HOT updates cause indexes to bloat. The following query is useful in investigating the index size and how it changes over time. It runs fairly quickly, and can be used to monitor whether your indexes are changing in size over time:
SELECT
nspname,relname,
round(100 * pg_relation_size(indexrelid) /
pg_relation_size(indrelid)) / 100
AS index_ratio,
pg_size_pretty(pg_relation_size(indexrelid))
AS index_size,
pg_size_pretty(pg_relation_size(indrelid))
AS table_size
FROM pg_index I
LEFT JOIN pg_class C ON (C.oid = I.indexrelid)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND
C.relkind='i' AND
pg_relation_size(indrelid) > 0;
Another route is to use the pgstattuple contrib module which provides very detailed statistics. You can scan tables using pgstattuple(), as follows:
test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc');
The output will look like the following:
-[ RECORD 1 ]------+-------
table_len | 458752
tuple_count | 1470
tuple_len | 438896
tuple_percent | 95.67
dead_tuple_count | 11
dead_tuple_len | 3157
dead_tuple_percent | 0.69
free_space | 8932
free_percent | 1.95
The downside of pgstattuple is that it derives exact statistics by scanning the whole table and literally counting everything. If you have time to scan the table, you may as well vacuum the whole table anyway. So a better idea is to use pgstattuple_approx(), which is much, much faster, and yet is still fairly accurate. It works by accessing the table's visibility map first and then only scanning the pages that need VACUUM, so I recommend you use it in all cases for checking tables (there is no equivalent for indexes):
postgres=# select * from pgstattuple_approx('pgbench_accounts');
-[ RECORD 1 ]--------+-----------------
table_len | 268591104
scanned_percent | 0
approx_tuple_count | 1001738
approx_tuple_len | 137442656
approx_tuple_percent | 51.1717082037088
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
approx_free_space | 131148448
approx_free_percent | 48.8282917962912
You can also scan indexes using pgstatindex(), as follows:
postgres=> SELECT * FROM pgstatindex('pg_cast_oid_index');
-[ RECORD 1 ]------+------
version | 2
tree_level | 0
index_size | 8192
root_block_no | 1
internal_pages | 0
leaf_pages | 1
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 50.27
leaf_fragmentation | 0