From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: surprised to find bloat in insert-only table
Date: Fri, 03 Jun 2011 12:02:02 -0400
Message-ID: <28332.1307116922@sss.pgh.pa.us> (raw)
In-Reply-To: <isathr$kig$1@dough.gmane.org>
References: <isathr$kig$1@dough.gmane.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mark@summersault.com
Subject: Re: surprised to find bloat in insert-only table
In-Reply-To: <28332.1307116922@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox