Postgres Pro
Downloads
Home   >   mailing lists

Re: Diagnosing a massive toast file - Mailing list pgsql-admin

From Avinash Kumar
Subject Re: Diagnosing a massive toast file
Date
Msg-id CAN0TujcdPFwWUAJvkpNfRHD6thg-28HuxyZN4XghnH8ojhhg_Q@mail.gmail.com
Whole thread Raw
In response to Re: Diagnosing a massive toast file  (Wells Oliver <wells.oliver@gmail.com>)
List pgsql-admin
Tree view


On Mon, Aug 5, 2019 at 2:43 PM Wells Oliver <wells.oliver@gmail.com> wrote:
Thanks, that was it exactly. PGAdmin session opened for a week. Argh. Gotta have some conversations with some folks.

Do you guys have any kind of regular monitoring in place to flag users who don't politely close their connections?
pg_stat_activity view would do the trick for you. 
Search for the connections that are running long for more than a few hours ? or days ?
See if any idle in transactions that have now() - state_change, more than a few mins ? or hours ? or days ?
 

On Mon, Aug 5, 2019 at 10:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> As a follow up, n_dead_tup from pg_stat_sys_tables for this TOAST table is
> 7447444, live tuples, 623982, and tup_del 20823469. vacuum_count is 0.

> Why can't I free those rows up?

Old open transaction somewhere (possibly a prepared transaction?).
Or a replication slot that's holding back the xmin horizon due to
not keeping up.

                        regards, tom lane


--


--
9000799060

pgsql-admin by date:

Previous
From: Wells Oliver
Date:
Subject: Re: Diagnosing a massive toast file
Next
From: Igor Neyman
Date:
Subject: RE: Diagnosing a massive toast file