Re: Vacuum and freeing dead rows

On 7/5/19 3:16 AM, Simon T wrote:

Hi,

I have a very heavily updated table in a Postgres 9.6.10 database with
lots of disk bloat. Every row is updated about once a minute, and
little to no inserts. Approx 18k rows total. The table has bloated
from ~1700 KB to about 6 GB over a few weeks time. I'm trying to
understand why vacuum hasn't made dead rows available for re-use.

[snip]

And in case it is relevant:

appdb=# SELECT pid, datname, usename, state, backend_xmin
appdb-# FROM pg_stat_activity
appdb-# WHERE backend_xmin IS NOT NULL
appdb-# ORDER BY age(backend_xmin) DESC; pid | datname | usename | state | backend_xmin
-------+---------------+----------+---------------------+--------------10921 | appdb | app | idle in transaction | 3501305052

"idle IN TRANSACTION" is never good.  Transactions should always be as short as possible.

 10919 | appdb | app | idle in transaction | 350130505210916 | appdb | app | idle in transaction | 350130505227935 | appdb | app | idle in transaction | 350130505224500 | appdb | postgres | active | 350130505210914 | appdb | app | active | 350130505220671 | appdb | postgres | active | 350130505211817 | appdb | app | active | 3501305052 1988 | appdb | app | active | 350130505215041 | appdb | postgres | active | 3501305052 9916 | appdb | postgres | active | 350130505210912 | appdb | app | idle in transaction | 350130505210909 | appdb | app | idle in transaction | 3501305052
(13 rows)

Add backend_start to that query.  I'd kill any idle transactions are more than 30 minutes old.  (Of course, since they're "idle IN TRANSACTION", you'd lose stuff.