Modified rows are not marked as dead and as such vacuum is unable to clean them up

[Tamás András Kálmán <hello@xxxxxxxxxxxxxxx>]

Hello Dear List,

This is my first post here. 

we have a PostgreSQL 9.3.4 database with multiple large tables, that keep growing.

Looking at the pg_stat of  the front runner table that is currently about 1 Gb  big, it seems, that stale row data is not marked as dead after updates, the number of actual rows returned by count() is around 700k, but n_live_tup has reached 10 million today (every day about 2/3 of the table entries is updated and it reached this state after 2 weeks of running):

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.