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

Re: Index bloat of 4x

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bill Moran <wmoran(at)collaborativefusion(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Index bloat of 4x
Date: 2007-01-31 04:15:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Tom Lane wrote:
> Bill Moran <wmoran(at)collaborativefusion(dot)com> writes:
> > The entire database was around 28M prior to the upgrades, etc.  Immediately
> > after the upgrades, it was ~270M.  Following a vacuum full, it dropped to
> > 165M.  Following a database-wide reindex, it dropped to 30M.
> As Alvaro said, vacuum full doesn't shrink indexes but in fact bloats them.
> (Worst case, they could double in size, if the vacuum moves every row;
> there's an intermediate state where there have to be index entries for
> both old and new copies of each moved row, to ensure things are
> consistent if the vacuum crashes right there.)
> So the above doesn't sound too unlikely.  Perhaps we should recommend
> vac full + reindex as standard cleanup procedure.  Longer term, maybe
> teach vac full to do an automatic reindex if it's moved more than X% of
> the rows.  Or forget the current vac full implementation entirely, and
> go over to something acting more like CLUSTER ...

TODO already has:

	* Improve speed with indexes
	  For large table adjustments during VACUUM FULL, it is faster to
	  reindex rather than update the index.  Also, index updates can
	  bloat the index.

  Bruce Momjian   bruce(at)momjian(dot)us

  + If your life is a hard drive, Christ can be your backup. +

In response to

pgsql-general by date

Next:From: David FetterDate: 2007-01-31 04:29:13
Subject: Re: Any Plans for cross database queries on the same server?
Previous:From: Tom LaneDate: 2007-01-31 04:15:20
Subject: Re: Any Plans for cross database queries on the same server?

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