Re: json datatype and table bloat?

Following up.

I don't see any rolled back transactions in the logs.

The part that troubles me really is that vacuum full doesn't actually fix
the problem. If there were bad data that had been corrected via mass
updates, I'd expect the bloat issue to be fixed by a vacuum full.

When I run the vacuum back to back, this is what I get:

db_name=# VACUUM FULL VERBOSE table_schema.table_name;
INFO: vacuuming "table_schema.table_name"
INFO: "table_name": found 2 removable, 29663 nonremovable row versions in
1754 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.07s/0.10u sec elapsed 0.30 sec.
VACUUM
db_name=# VACUUM FULL VERBOSE table_schema.table_name;
INFO: vacuuming "table_schema.table_name"
INFO: "table_name": found 0 removable, 29663 nonremovable row versions in
1754 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.09s/0.09u sec elapsed 0.32 sec.
VACUUM

I think the question to address may be: "Why does the check_postgres query
think there should only be 334 pages instead of 1754?"

The tbloat and wastedbytes calculations provided in the query from this
page: http://wiki.postgresql.org/wiki/Show_database_bloat seems to
correlate with the wasted bytes reported by nagios though.

Greg Haase

On Tue, Oct 29, 2013 at 7:06 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Tue, Oct 29, 2013 at 5:38 AM, Chris Travers <chris(dot)travers(at)gmail(dot)com>
> wrote:
> >
> >
> >
> > On Mon, Oct 28, 2013 at 4:17 PM, Gregory Haase <haaseg(at)onefreevoice(dot)com>
> > wrote:
> >>
> >> I have a table that is triggering my nagios database bloat alert
> >> regularly. Usually, I have to give it the vacuum full more than once to
> get
> >> it under the threshold. Today I tried repeatedly and cannot get the
> alert to
> >> resolve.
> >>
> >> I had a discussion with one of the primary developers about how the
> table
> >> is utilized, and it turns out they are basically only ever inserting
> into
> >> it. This sort of flies in the face of conventional wisdom about bloat
> being
> >> caused by frequent updates and deletes.
> >
> >
> > As I understand it, vacuuming only removes the tuples removed by delete
> and
> > update operations.
>
> well, or by rolled back transactions. we we have to wonder if OP has
> a lot of queries trying to insert and failing. maybe check the log?
>
> merlin
>