Re: Postgresql 'eats' all mi data partition

From: Christopher Browne <cbbrowne(at)libertyrms(dot)info>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Postgresql 'eats' all mi data partition
Date: 2003-09-26 16:16:50
Message-ID: 60llsb4j9p.fsf@dev6.int.libertyrms.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

fjcarlos(at)correo(dot)insp(dot)mx (Javier Carlos) writes:
> I did a vacuumbdb after the updates, and the space usage didn't down to
> something reasonable. For example, I had a 250MB database, then I did about
> 300 query updates, and mi partition growed up until fill all mi data partition
> space of 15GB. After that I did an vacuumdb and only the space down 100MB.
> After that I DROPPED the database, and the space down ALL the 15GB; It's very
> weird, don't you think?

You may not have been expecting it, but it is no great surprise to me.

If you vacuumed after every ~10-20 of those query updates, then the
size shouldn't have gotten nearly that high, and performance would
likely have been quite a bit better.

In effect, every time you update substantially all of the data, you
should _definitely_ do a vacuum, as it will be quite likely to "reap"
a whole table's worth of dead tuples.

A VACUUM FULL will cut the size down absolutely, at the cost of
blocking updates during the vacuum. If you run "ordinary VACUUMs"
along the way, they aren't as effective at cutting down on the space
used, but are non-blocking. Probably it's better to regularly do
"ordinary VACUUMs."
--
"cbbrowne","@","libertyrms.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2003-09-26 16:24:04 Re: pg 7.4beta1 doc bug: vacuum not updated
Previous Message Nayib Kiuhan 2003-09-26 06:15:13 plperl: No such file or directory. (Solaris 9, PGSQL 7.4, OpenSSL, Perl)