From: Mark Stosberg <mark@summersault.com>
To: pgsql-admin@postgresql.org
Subject: Re: surprised to find bloat in insert-only table
Date: Fri, 03 Jun 2011 13:03:31 -0400
Message-ID: <isb453$uhp$1@dough.gmane.org> (raw)
In-Reply-To: <28332.1307116922@sss.pgh.pa.us>
References: <isathr$kig$1@dough.gmane.org>
<28332.1307116922@sss.pgh.pa.us>
Thanks for reply, Tom.
> 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 table size is reported as: 4036 MB according to:
select pg_size_pretty(pg_table_size('table_name'));
Compared to 1669 MB reported as table bloat in the 'bloat' view. So,
the bloat is about 40% of the total size.
For an index, it's 410 MB of bloat, vs 1669 MB for an index size.
One thing that looks suspicious is that the exact same number of bytes
is being report for the table as well as each index.
> 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.
Thanks for the clarifications! In summary, it sounds like there is some
bloat that I need to live with, but at least with partitioning the
amount of bloat will stay relatively constant.
Mark
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: mark@summersault.com
Subject: Re: surprised to find bloat in insert-only table
In-Reply-To: <isb453$uhp$1@dough.gmane.org>
* 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