PostgreSQL 9.6.12 autovacuum constantly running on system tables

I come from a SQL Server, Oracle, Sybase DBA background but I am now looking into an AWS Aurora cluster running PostgreSQL 9.6.12 and have noticed something which I think is odd, but maybe it's not, which is why I am here to ask the question. I have looked everywhere but can not find an answer. The default autovacuum and autoanalyze values are still set. Autovacuum does seem to get around to doing what it needs to do on application tables, eventually, but what I have noticed is that it seems to spend most of its time frequently vacuuming and analysing a small set of system tables. They are:

• pg_type

• pg_shdepend

• pg_attribute

• pg_class

• pg_depend

I am seeing this both through AWS Performance Insights data and also through direct queries to the database instance using this code:

```
WITH rel_set AS
(
SELECT
oid,
CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_threshold=', 2), ',', 1)
WHEN '' THEN NULL
ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_threshold=', 2), ',', 1)::BIGINT
END AS rel_av_anal_threshold,
CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
WHEN '' THEN NULL
ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
END AS rel_av_vac_threshold,
CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_scale_factor=', 2), ',', 1)
WHEN '' THEN NULL
ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_scale_factor=', 2), ',', 1)::NUMERIC
END AS rel_av_anal_scale_factor,
CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)
WHEN '' THEN NULL
ELSE split_part(split_part(array_to_string(reloptions, ','), 'au

The catalog tables that you mention as being vacuumed all the time indicate that indeed tables (or, less likely, composite data types) are created and destroyed all the time:

• pg_class is the table of tables (and other relations)

• pg_attributes is the table of columns

• pg_type contains types, and for every table a composite type with the same name is created

• pg_depend contains dependencies between the table and — for example — the associated type.

• pg_shdepend contains the dependency between a table and the owning role.

The table most at risk of bloat is pg_attribute.

Since you are using a hosted database, you probably neither have superuser access, nor can you use the pgstattuple extension that would allow you to determine exactly how bloated the table is.

But you can find out its size:

SELECT pg_total_relation_size('pg_attribute');

Also, you can find out how many dead (deleted) tuples there are:

SELECT n_live_tup, n_dead_tup
FROM pg_stat_sys_tables
WHERE relname = 'pg_attribute';

Any dead tuple you see there is evidence that columns are deleted. So if this number keeps increasing, you have a proof that you developers are mistaken (perhaps it is not a temporary table, but a regular table).

To keep the problem at bay, make autovacuum as aggressive as you can:

autovacuum_vacuum_cost_delay = 0

StackExchange Database Administrators Q#261158, answer score: 2