Postgres 96 improvements on autovacuum locks

Postgres 9.6 has introduced a lot of features like parallel query and improvements in the foreign data wrapper. But the one I liked the most if the letting loose of the locks when settings autovacuum options per table.

These are explained more clearly here: 

In layman terms, setting these options can now be done without exclusively locking the table and will be able to execute on live production database regardless whether you have traffic or not.

ALTER TABLE __table_name__ SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE __table_name__ SET (autovacuum_vacuum_threshold = 5000)
ALTER TABLE __table_name__ SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE __table_name__ SET (autovacuum_analyze_threshold = 5000);

​

Here's an example output after analyzing tables that needs vacuum:

SELECT relname, n_dead_tup, last_vacuum, last_autovacuum FROM
pg_catalog.pg_stat_all_tables
WHERE n_dead_tup > 0 ORDER BY 2 DESC nulls last limit 30

​

relname	n_dead_tup	last_vacuum	last_autovacuum
users	4913		2017-02-01 09:27:53.323267+00
user_battles	4897	2017-01-26 01:19:27.805452+00	2017-02-01 14:37:14.828148+00
user_table_xxx	4848	2017-01-25 23:46:27.03666+00	2017-02-01 14:20:31.988554+00
user_table_yyy	4705		2017-02-01 08:37:51.772997+00
user_money	4696	2017-01-27 02:27:01.159931+00	2017-02-01 14:39:00.696119+00
user_cards	4509	2017-01-27 02:36:02.021486+00	2017-02-01 14:56:17.4869+00
user_actions	4340		2017-02-01 14:28:00.189851+00
user_events	4243		2017-02-01 14:47:53.653568+00

​

As you have noticed, the tables doesn't go beyond 5000 n_dead_tup which is our configured options on the the above tables. If you happen to have your naptime to:

autovacuum_naptime = 15

​

Every 15 secs, you can see the top of the lists to be chopped down. This is really good compared to normal vacuuming at scheduled intervals (e.g. cron) as it won't be able to handle sudden burst of dead tuples.

Also, if you have been using default autovacuum options and whenever you query the pg_stat_activity and you see a long autovacuum activity.

select * FROM pg_stat_activity where query like '%VACUUM%';
--- OR
select * FROM pg_stat_activity where usename ='rdsadmin'; --- For AWS RDS

​

It means that the autovacuum is not aggressive enough and actually throttled lightly compared to running manual vacuum. To remedy this and obtain same aggressive/performance as manual vacuum you can set the following options to:

autovacuum_vacuum_cost_delay = -1

​

Reference: Docs

If you are still running on Postgres < 9.6, setting per table autovacuum options may require a downtime as it locks the table.  I suggest running aggressive manual vacuums via cron jobs to do the work.