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 <wells(dot)oliver(at)gmail(dot)com
> <mailto:wells(dot)oliver(at)gmail(dot)com>> 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 <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Wells Oliver <wells(dot)oliver(at)gmail(dot)com
> <mailto:wells(dot)oliver(at)gmail(dot)com>> 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
> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>
>
>
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>

--
Angular momentum makes the world go 'round.