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 | 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.
