Database Reference
In-Depth Information
How it works...
We can compare the number of dead row versions, shown as
n_dead_tup
against the
required threshold,
av_threshold
.
The above query doesn't take into account table-specific autovacuum thresholds. It could
do if you really need that, though 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 insert, updates and deletes, 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 VACUUMs altogether,
or at least for long periods. If the ratio is low, then you will need to execute VACUUMs or
autovacuums more frequently. Note that the ratio never reaches 1.0, so if you have better
than 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. 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 try to
decrease the fillfactor storage parameter for the table. Remember that this will only be
important on your most active tables. Seldom-touched tables don't need much tuning.
So, to recap: non-HOT updates cause indexes to bloat. The following query is useful in
investigating index size, and how that 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;
