Re: detection of VACUUM in progress

Bohdan Linda wrote:
> On Tue, Aug 30, 2005 at 06:07:24PM +0200, Michael Fuhr wrote:
>
>>tables, and a VACUUM might start or complete immediately after you
>>issue the query but before you read the results). This method is
>>therefore unreliable.
>
>
> I intend to do the VACUUM FULL during quiet hours, thus the chance of
> fitting exactly to the time that VACUUM started and it is not reflected in
> the tables is quite small. And even if it would happend, very likely it will
> affect only one user, who may get around hitting "refresh" button.
>
>>
>>What problem are you trying to solve? If we knew what you're really
>>trying to do then we might be able to make suggestions.
>
>
> I have database, which gets around 240 000 new lines each day and about the
> same is also deleted each day. The table has something around 8M lines in
> average and simple query takes about 70s to complete(V210 1x
> UltraSPARC-IIIi). As this time is quite high, I need "defragment" database
> on daily basis. These queries get visualized in web application. My
> problem is, how to make the web application aware that maintainace (VACUUM
> FULL) is in place, but the database is not down. I really would not like
> to do it via extra status table, while sometimes it may happend, that
> someone will run VACUUM FULL ad-hoc-ly in good-faith and will forget to
> update the status table.
>

From the postgresql manual
http://www.postgresql.org/docs/8.0/interactive/maintenance.html :
" The standard form of VACUUM is best used with the goal of maintaining
a fairly level steady-state usage of disk space. If you need to return
disk space to the operating system you can use VACUUM FULL — but what's
the point of releasing disk space that will only have to be allocated
again soon? Moderately frequent standard VACUUM runs are a better
approach than infrequent VACUUM FULL runs for maintaining
heavily-updated tables."

From this I conclude that an ordinary VACUUM is sufficent to your
purpose cause you insert/delete almost the same amount of data daily.

But then again I can be mistaken so if anyone can back me up here or
throw the manual on me will be nice ;P

Cheers