vacuum necessity and effectivness

Question

gurus!

Please share sql-queries which you are using to estimate that vacuum is mandatory and see how it was effective. It should show something like unused space or existing/deleted rows ratio, etc.

Have you active log_autovacuum_min_duration in your conf ? Set it to 0 and you'll have informations in your log. Now, do you need vaccum, the answer is 99% yes, a part you exactly know what happen in you db you always need vacuum. Look at https://wiki.postgresql.org/wiki/Show_database_bloat to estimate the bloat. — Rodolphe, Feb 18 '18 at 14:28

score 0 · Answer 1 · answered Feb 18 '18 at 20:09

This will show you which tables need vacuuming:

SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_all_tables
WHERE n_live_tup > 0
  AND 10 * n_dead_tup > n_live_tup
ORDER BY n_dead_tup / n_live_tup DESC;

This is similar to how autovacuum determines which tables should be vacuumed.

So what you should do is enable autovacuum, tune it if necessary, and let it do the job for you.

Then you don't have to worry about questions like this.

vacuum necessity and effectivness

1 Answers1