Skip site navigation (1) Skip section navigation (2)

Re: surprised to find bloat in insert-only table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Stosberg <mark(at)summersault(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: surprised to find bloat in insert-only table
Date: 2011-06-03 16:02:02
Message-ID: 28332.1307116922@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Mark Stosberg <mark(at)summersault(dot)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

In response to

Responses

pgsql-admin by date

Next:From: Campbell, LanceDate: 2011-06-03 16:15:04
Subject: Re: viewing results in terminal on RedHat 6.1
Previous:From: Mark StosbergDate: 2011-06-03 15:10:51
Subject: surprised to find bloat in insert-only table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group