Re: Table not cleaning up drom dead tuples

> This tables is original ones, it doesn't have any activity now. We copied data to NEW tables and trying to solve root of the problem
>
> <THIS_DB> - target database where broken tables are located
>
>
> ----- VACUUM FULL VERBOSE
> <THIS_DB>=# VACUUM (FULL, VERBOSE) __orders_y2017_m2_to_drop;
> INFO: vacuuming "public.__orders_y2017_m2_to_drop"
> INFO: "__orders_y2017_m2_to_drop": found 0 removable, 3179076 nonremovable row versions in 551423 pages
> DETAIL: 1778770 dead row versions cannot be removed yet.
> CPU 30.92s/102.66u sec elapsed 184.69 sec.
>
> <THIS_DB>=# VACUUM (FULL, VERBOSE) __orders_y2017_m3_to_drop;
> INFO: vacuuming "public.__orders_y2017_m3_to_drop"
> INFO: "__orders_y2017_m3_to_drop": found 0 removable, 9103104 nonremovable row versions in 1520371 pages
>
> DETAIL: 8396820 dead row versions cannot be removed yet.
> CPU 65.00s/284.03u sec elapsed 399.66 sec.
>
>
> ----- DB INFO
> <THIS_DB>=# select * from pg_stat_user_tables where relname in ('__orders_y2017_m3_to_drop', '__orders_y2017_m2_to_drop');
> relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
> -----------+------------+---------------------------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
> 179718008 | public | __orders_y2017_m2_to_drop | 5615 | 7934041177 | 328044580 | 7979850698 | 0 | 3065776 | 0 | 25685 | 3082885 | 1759481 | 0 | 2017-03-14 11:57:40.388527+00 | 2017-03-14 07:37:50.907757+00 | 2017-03-14 11:57:42.656628+00 | 2017-03-13 16:15:55.60846+00 | 5 | 96 | 4 | 15
> 207347508 | public | __orders_y2017_m3_to_drop | 1128 | 794959804 | 129799001 | 1292952066 | 706089 | 8377499 | 0 | 118035 | 8937540 | 8406385 | 0 | 2017-03-14 11:57:58.026816+00 | 2017-03-14 10:09:08.597031+00 | 2017-03-14 11:57:59.117331+00 | 2017-03-14 04:11:11.370923+00 | 4 | 11 | 4 | 7
> (2 rows)
>
> <THIS_DB>=# select * from pg_stat_database;
> datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_read_time | blk_write_time | stats_reset
> -----------+--------------------+-------------+-------------+---------------+-----------+--------------+---------------+--------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+-------------------------------
> 4906146 | <THIS_DB> | 62 | 24781721 | 5888121 | 492125811 | 348274702788 | 1127846911908 | 250049066062 | 413981238 | 188610068 | 397036 | 0 | 53 | 7507001344 | 1 | 0 | 0 | 2017-03-06 02:33:26.466458+00
>
> 113713583 | sentry | 0 | 350030 | 342 | 11574 | 33444698 | 22519113 | 10577975 | 2438 | 27672 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 2017-03-06 02:33:24.156858+00
>
> 148539615 | test | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
> 161510793 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
> (8 rows)

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;