re: SQL 201: Have you VACUUMed your tables lately? VIEW POST

FULL DISCUSSION
 

Autovacuum is nice, but it is not the best solution to rely upon once things start to grow.

You need to do two things:

  1. tune the parameters per table
  2. manually vacuum for some tables

Tuning Parameters Per Table

As you mentioned the auto vacuum kicks in when 20% + 50 tuples have changes. This is a bad setting for small tables which change a lot, as it will result in a lot of vacuum activity for that table.

For example a user table is generally not that big, but it might change a lot if you keep track of a last activity of the user.

So for small tables with a lot of changes you should increase the scale factor, or maybe the base threshold.

Manually vacuum for some tables

For large tables which do see quite a bit of changes you might not want to rely on auto vacuum too much. Even though the overhead is small you probably do not want large tables to be vacuumed during "business hours".

It is best to create a cron job which explicitly calls the vacuum on these tables outside the business hours. This would result in more vacuum actions on the table than the autovacuum would generally consider. But given the size of these tables it is not really a bad thing.

ps, you might want to monitor the n_dead_tup column of pg_stat_user_tables. As this gives an indication as how much garbage there is for that table. If you plot this value together with the vacuum timestamps and n_live_tup column, you can get an idea how to tune vacuuming for that table.

 

That’s great advice, thank you for such a thoughtful comment.

I’ve been really enjoying learning all the tricks from our DBA to keep things running smoothly

code of conduct - report abuse