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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
