To: Mark Stosberg <mark@summersault.com>
cc: pgsql-admin@postgresql.org
Subject: Re: surprised to find bloat in insert-only table 
In-reply-to: <isathr$kig$1@dough.gmane.org> 
References: <isathr$kig$1@dough.gmane.org>
Comments: In-reply-to Mark Stosberg <mark@summersault.com>
	message dated "Fri, 03 Jun 2011 11:10:51 -0400"
Date: Fri, 03 Jun 2011 12:02:02 -0400
Message-ID: <28332.1307116922@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Stosberg <mark@summersault.com> writes:
> I recently set up partitioning on a table that sees heavy insert
> traffic. There are never updates or deletes, we just drop the partitions
> later.

> It's my understanding that bloat can only appear through updates or
> deletes, but these partitions are reported to have significant bloat in
> them. Where else can this come from and how I can I reduce it?

> I'm using a "bloat" view with 9.0.3 which might the same as this one:

> https://wiki.postgresql.org/wiki/Show_database_bloat

> This shows that each index on the table has over a gig of bloat:

> select distinct(iname), wastedbytes, wastedsize from bloat where
> wastedbytes > 0 order by  wastedbytes DESC limit 20;

> And this query also shows the same amount of bloat in the table itself:

> select distinct(tablename), wastedbytes, wastedsize from bloat where
> wastedbytes > 0 order by  wastedbytes DESC limit 10;

It's hard to evaluate that without knowing what the actual table/index
sizes are, or IOW what is the reported bloat on a percentage basis?

The view you mention isn't tremendously accurate --- AFAICS it isn't
accounting for alignment padding between fields, page headers, and some
other things.  And it will consider the unused space on a page to be
"bloat" even if it's too small to fit another tuple.  So expecting the
number to be zero is hopelessly optimistic.  Also, indexes generally
don't even try to pack pages completely full, so a larger percentage of
unused space is to be expected for them.

			regards, tom lane