Using mostly default parameters with 9.3 we are seeing reasonable performance with our heavy transaction-based application.
However, when we are upgraded to 9.6 we are seeing horrible performance with the same loads using the same hardware.
Gathering the configuration details, but I think I've spotted something that might point to the problem. Our application has a repeated pattern of inserting a customer invoice and details sending a message to another application which reads the transaction, inserts it on another database server, and the deletes the original.
In looking at pg_stat_activity I notice under the same load, autovacuum seems to be running way more often under 9.6 and hanging around longer in the activity queue. Also, I notice that IO run queue is like 10x deeper and write latency balloons from < 10ms to around 300/400msec. Clearly something is slamming the IO channel.
Granted I see there are many new autovacuum variables that likely have to be tuned, Also, we've really got to redesign the application logic, it seems moronic to have rapid insert/deletes in a handful of tables that is likely to overwhelm autovacuum for no good reason.
What I am really asking to confirm is after describing the situation is it reasonable to focus on (in the short term) tuning autovacuum to increase performance or does this not make sense given the workload and I should look elsewhere?
Thanks.
