Re: json datatype and table bloat?

Tom is correct: Vacuum verbose shows that their is an associated toast
table. Neither the check_postgres.pl script or the query on
http://wiki.postgresql.org/wiki/Show_database_bloat appear to take this
into consideration. Both rely on null_frac and avg_width from pg_stats to
estimate how big the table should be. I'm not sure how you would factor the
toast table into that estimate.

-G

On Tue, Oct 29, 2013 at 2:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gregory Haase <haaseg(at)onefreevoice(dot)com> writes:
> > I've isolated the problem to the json field not showing up in pg_stats,
> > which affects the calculation of the avg row size in the bloat query.
>
> > I'm not sure if this is a json issue or some other kind of issue.
>
> Possibly your "bloat query" is failing to consider the toast table
> associated with this table? If the json values are large they'd
> mostly be in the toast table not the main table.
>
> (It's unfortunate that VACUUM FULL doesn't tell you about what's
> in the toast table. I'd try just VACUUM VERBOSE here, without the
> FULL, to get more info.)
>
> regards, tom lane
>