On 7/5/19 3:16 AM, Simon T wrote:[snip]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. 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 | 3501305052 10916 | appdb | app | idle in transaction | 3501305052 27935 | appdb | app | idle in transaction | 3501305052 24500 | appdb | postgres | active | 3501305052 10914 | appdb | app | active | 3501305052 20671 | appdb | postgres | active | 3501305052 11817 | appdb | app | active | 3501305052 1988 | appdb | app | active | 3501305052 15041 | appdb | postgres | active | 3501305052 9916 | appdb | postgres | active | 3501305052 10912 | appdb | app | idle in transaction | 3501305052 10909 | 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. |
