pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: database size after a DELETE and VACUUM
Date: Sun, 03 Jul 2005 13:49:11 -0400
Message-ID: <m3k6k7rd9k.fsf@knuth.cbbrowne.com> (raw)
References: <OF10E93ADC.96387340-ON85257031.004879C3-85257031.004A3C0E@apcc.com>

After a long battle with technology, Mike.Samson@apcc.com, an earthling, wrote:
> I need a reliable way to determine the overall size of our database.
> Recently, I started using the database_size() function from the dbsize
> contrib.
>
> Everything seems to work great until I need to delete records.
> After I DELETE records, the pg_autovacuum daemon kicks in an runs a VACUUM.
>
> Once the VACUUM finishes, I run database_size() again and the size does not
> decrease.
>
> I would expect the database size to decrease after the VACUUM.

I wouldn't. VACUUM doesn't decrease database size; it only reclaims
empty pages for re-use.

After a VACUUM, there will be empty space ready to be reused by later
activity.

> I also don't want to run a VACUUM FULL because this kills the
> performance of the system.

Fair enough...

It is, however, only VACUUM FULL that will actually decrease the size
of the database.

With a regimen of running VACUUM reasonably often is that if the
database isn't steadily growing, you'll find that the size stabilizes,
with there being some portion of unused space that will accomodate
upcoming UPDATE/INSERT activity.

If the database *is* steadily growing, well, the unused space will
soon be used :-).

You might want to look in the contrib area for a function called
"pgstattuple" which does a detailed analysis, for any requested table,
as to what portion is live versus dead.
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://cbbrowne.com/info/rdbms.html
THAT COMMAND IS NOT KNOWN TO THIS PROGRAM.
MAYBE YOU SHOULD LOG IN? (TYPE HELP FOR DETAILS)



view thread (3+ messages)

Message-ID: <m3k6k7rd9k.fsf@knuth.cbbrowne.com>
Permalink:  ../m3k6k7rd9k.fsf@knuth.cbbrowne.com/
Also on:    postgresql.org/message-id/m3k6k7rd9k.fsf@knuth.cbbrowne.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: database size after a DELETE and VACUUM
  In-Reply-To: <m3k6k7rd9k.fsf@knuth.cbbrowne.com>
  References: <OF10E93ADC.96387340-ON85257031.004879C3-85257031.004A3C0E@apcc.com> <m3k6k7rd9k.fsf@knuth.cbbrowne.com>

* 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