Thread: [GENERAL] Modified rows are not marked as dead and as such vacuum is unable toclean them up
[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.
Re: [GENERAL] Modified rows are not marked as dead and as such vacuum is unable to clean them up
=?UTF-8?B?VGFtw6FzIEFuZHLDoXMgS8OhbG3DoW4=?= <hello@tamaskalman.com> writes: > 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, Have you checked for uncommitted prepared transactions? If "select * from pg_prepared_xacts" finds anything, that's probably your problem. regards, tom lane
Re: [GENERAL] Modified rows are not marked as dead and as such vacuumis unable to clean them up
Tamás András Kálmán <hello@tamaskalman.com> writes:
> 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,
Have you checked for uncommitted prepared transactions?
If "select * from pg_prepared_xacts" finds anything, that's
probably your problem.
regards, tom lane