We're, in general, pretty carefull with our DB, as it contains important data.
Most rollback is issued by application (which processes all data inside transactions).
p.s. Time is in UTC (GMT+0)
<THIS_DB>=# select min(xact_start) from pg_stat_activity where state<>'idle';
min
-------------------------------
2017-03-14 15:36:05.432139+00
(1 row)
<THIS_DB>=# select * from pg_stat_activity where state<>'idle' order by xact_start limit 1;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | backend_xid | backend_xmin | query
---------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+-------------+--------------+--------------------------------------------------
4906146 | <THIS_DB> | 37235 | 10 | pgsql | | | | | 2017-03-14 05:55:43.287128+00 | 2017-03-14 15:36:05.432139+00 | 2017-03-14 15:36:05.432139+00 | 2017-03-14 15:36:05.432141+00 | f | active | | 1621959045 | autovacuum: VACUUM public.stats_y2017_m3_d13_hk2
(1 row)
Quite a large quantity of rollbacks there. In your initial email the longest running transaction was an autovacuum task wasn't it? Are you sure there are no other long running transactions?
Whats the output of:
select min(xact_start) from pg_stat_activity where state<>'idle';
select * from pg_stat_activity where state<>'idle' order by xact_start limit 1;
--
___________________________
С наилучшими пожеланиями,
Антон Тарабрин
With best regards,
Anton Tarabrin