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

Re: database 1.2G, pg_dump 73M?!

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ross Boylan <RossBoylan(at)stanfordalumni(dot)org>
Cc: Joris Dobbelsteen <joris(at)familiedobbelsteen(dot)nl>, pgsql-general(at)postgresql(dot)org
Subject: Re: database 1.2G, pg_dump 73M?!
Date: 2008-03-30 20:28:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Ross Boylan <RossBoylan(at)stanfordalumni(dot)org> writes:
> reindexing had a huge effect.

So the indexes were indeed bloated.  There are some known usage patterns
in which regular vacuum isn't very good at reclaiming space in b-tree
indexes.  For example if you make daily entries in an index by date and
later remove all but the last-of-the-month entry --- this leaves a few
entries on every index page and we don't have code to collapse that,
short of reindexing.

However what seems more likely is that you're getting burnt by excessive
use of VACUUM FULL.  V.F., far from shrinking indexes, tends to bloat
them.  Recommended practice is to use plain VACUUM often enough that you
don't need VACUUM FULL.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Ivan Sergio BorgonovoDate: 2008-03-30 20:38:03
Subject: returning array from function or "structured error"
Previous:From: Joris DobbelsteenDate: 2008-03-30 19:22:45
Subject: Re: database 1.2G, pg_dump 73M?!

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