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.
--
Angular momentum makes the world go 'round.
