We've noticed the performance of our platform drop in recent weeks so I've run the following:
select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables where relname like 'core_%';
And noticed that our primary table hadn't been autovacuumed for more than a week. So last week I ran:
vacuum analyse verbose TABLENAME
Which seemed to help, but we've run into the same issue again now. Upon closer inspection, a lot of the tables have either never been analysed (auto or otherwise) and aside from the manual
vacuum analyse run last week, none of the tables have been manually vacuumed, and a lot of the other tables haven't been autovacuumed since, at best, a few days ago, and at worse a few weeks ago.
My understanding of the terms is as follows:
- vacuum: Clears out deleted records from disk
- analyse: Updates the query planner
postgres.conf, the autovacuum property is commented out, but the documentation states that this is on by default, so my presumption is that even though it's commented out, it should still be on?
Can someone explain why the tables wouldn't be getting vacuumed and analysed frequently and more specifically, would these values not getting updated actually have that much of an impact on the system?
Info: Postgres 9.1 OS: Ubuntu 12.04
SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; Table | Size | External Size -----------------+------+--------------- "Primary Table" | 27G | 8232M