[GENERAL] Modified rows are not marked as dead and as such vacuum is unable toclean them up

avmgmt=# select * from pg_stat_user_tables where relname = 'button';

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 |          last_vac

uum          |        last_autovacuum        | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count

-------+-------------------+------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+------------------

-------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------

63500 | system_data | button |       30 |     38112282 | 14373411 |     235636001 |      4392 |  10838978 |      2259 |        278851 |   10409289 |     549289 | 2017-07-06 00:01:

50.710945+02 | 2017-07-06 06:22:21.040109+02 |              | 2017-07-06 06:16:07.687727+02 |           16 |               31 |             0 |                33

(1 row)

There are no active connections in transaction. Autovacuum is enabled and there is a nightly vacuum cron job as well, but it doesn't have an effect on these rows in n_live_tup, they just seems to keep accumulating.

Stopping the application and restarting PSQL followed up with a a FULL VACUUM on these tables works, then it starts to grow again.

Let me know if you have any ideas why PostgreSQL is holding onto those rows, if there is some particular piece of information, I can share it.

Many thanks for your help in advance!

Best,

Tamas Kalman.