Thread: vacuuming and freed disk space

From: pgadmin.12.ptechjohn@spamgourmet.com
Reply-to: pgadmin.12.ptechjohn@spamgourmet.com

i am looking at our database table maintenance. we are running a "space
watch" task that looks at available disk space and removes old records
when/if a certain threshold is passed.

i've noticed that if i run a "bulk delete" followed by a vacuum the
actual disk space is usually not recovered. if i use "vacuum full"
instead of just plain "vacuum" the space is recovered (i am using df
to look at the partition usage statistics). also, if i run just plain
"vacuum" and wait around for a bit, eventually the space will be recovered.
i haven't nailed down whether this is because of other usage or something
that the server does...

so, the questions:
1) is there a way to recover the disk space without doing a "vacuum full"?
i don't want to do the table lock if i don't have to.
2) *is* the postgresql server periodically recovering that disk space? if
so, how often does it do this, and does it lock the table when it does?
3) anyone have any other good ideas on how to implement this sort of
"space watch" functionality?

thanks.
john

On Tue, Apr 29, 2003 at 10:33:56AM -0400, john wrote:

> to look at the partition usage statistics). also, if i run just plain
> "vacuum" and wait around for a bit, eventually the space will be recovered.

What do you mean by "wait around for a bit"?  Do you mean that after
several vacuum cycles, you get your space back?


--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

In article <20030429143356.C5EFD3F04@guthrie.charm.net>,
john <jimmer@guthrie.charm.net> wrote:
>i am looking at our database table maintenance. we are running a "space
>watch" task that looks at available disk space and removes old records
>when/if a certain threshold is passed.

I'm wondering:  why?

If you just regularly expired old information anyway, PG will reuse the
data, so you should end up with a steady state.  If really paranoid you can
monitor various PG stats, I believe, and use that to dry when to
delete/vacuum.  But your current approach seemed a little odd to me (a bit
too non-deterministic is my gut feeling).

mrc

--
     Mike Castle      dalgoda@ix.netcom.com      www.netcom.com/~dalgoda/
    We are all of us living in the shadow of Manhattan.  -- Watchmen
fatal ("You are in a maze of twisty compiler features, all different"); -- gcc