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%' ; select * FROM pg_stat_activity where usename = 'rdsadmin' ; ​

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 ​

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.