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