Re: Diagnosing a massive toast file

"An open transaction" is the first place to look.

On 8/5/19 12:03 PM, Wells Oliver wrote:
> 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?
>
> On Mon, Aug 5, 2019 at 10:00 AM Wells Oliver <[email protected] 
> <mailto:[email protected]>> wrote:
>
>     Appreciate it, guys. I understand it being large isn't itself a
>     problem, but relative to history and the lack of real changes, it's
>     just strange and I'd like to better understand what is going on...
>
>     I tracked it down to a specific table, and then doing a VACUUM FULL
>     ANALYZE on that table yields: 108765 dead row versions cannot be
>     removed yet.
>
>     Which strikes me as odd. Any reading I can do to better understand why
>     so many (relative to the overall table size) dead rows cannot be removed?
>
>
>     On Mon, Aug 5, 2019 at 9:49 AM Tom Lane <[email protected]
>     <mailto:[email protected]>> wrote:
>
>         Wells Oliver <[email protected]
>         <mailto:[email protected]>> writes:
>         > Yeah, trying to figure out what actual table is clearly in need
>         of a vacuum
>         > b/c of the size of that toast table.
>
>         Something like
>
>         select relname from pg_class
>         where reltoastrelid = 'pg_toast.pg_toast_NNN'::regclass;
>
>         (or, if you have potential duplicate relnames, select
>         oid::regclass ...)
>
>         The mere fact that it's big does not indicate a problem, though.
>
>                                 regards, tom lane
>
>
>
>     -- 
>     Wells Oliver
>     [email protected] <mailto:[email protected]>
>
>
>
> -- 
> Wells Oliver
> [email protected] <mailto:[email protected]>

-- 
Angular momentum makes the world go 'round.