Hi,

Are there guidelines (or any empirical data) available how to determine
how often a table should be vacuumed for optimum performance or is this
an experience / trial-and-error thing?

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hi,

From: http://www.postgresql.org/docs/7.4/i...ql-vacuum.html


"VACUUM reclaims storage occupied by deleted tuples. In normal
PostgreSQLoperation, tuples that are deleted or obsoleted by an update
are not
physically removed from their table; they remain present until a VACUUM is
done. Therefore it's necessary to do VACUUM periodically, especially on
frequently-updated tables."

"The "vacuum analyze" form additionally collects statistics on the
disbursion of columns in the database, which the optimizer uses when it
calculates just how to execute queries. The availability of this data can
make a tremendous difference in the execution speed of queries. This command
can also be run from cron, but it probably makes more sense to run this
command as part of your nightly backup procedure - if "vacuum" is going to
screw up the database, you'd prefer it to happen immediately after (not
before!) you've made a backup! The "vacuum" command is very reliable, but
conservatism is the key to good system management. So, if you're using the
export procedure described above, you don't need to do this extra step".

All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a
VACUUM, therefore the necessity to execute at least one time to the day
normally of dawn if its database will be very great .

[],s

Marcelo Costa
Secretaria Executiva de Educação do Pará
Amazonia - Pará - Brazil

2006/11/26, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>:
>
> Hi,
>
> Are there guidelines (or any empirical data) available how to determine
> how often a table should be vacuumed for optimum performance or is this
> an experience / trial-and-error thing?
>
> TIA
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> web: www.askesis.nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>



--
Marcelo Costa

On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote:
> All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a
> VACUUM

Just a minor clarification here: INSERT does not create dead rows, only
UPDATE and DELETE do. Thus, if you only insert rows, you do not need to
vacuum (although you probably need to analyze).

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Sorry,

realy you are correct.

[],s

Marcelo Costa.

2006/11/26, Steinar H. Gunderson <sgunderson@bigfoot.com>:
>
> On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote:
> > All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need
> a
> > VACUUM
>
> Just a minor clarification here: INSERT does not create dead rows, only
> UPDATE and DELETE do. Thus, if you only insert rows, you do not need to
> vacuum (although you probably need to analyze).
>
> /* Steinar */
> --
> Homepage: http://www.sesse.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>



--
Marcelo Costa

On 26-Nov-06, at 8:11 AM, Steinar H. Gunderson wrote:

> On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote:
>> All its tables constantly manipulated (INSERT, UPDATE, DELETE)
>> they need a
>> VACUUM
>
> Just a minor clarification here: INSERT does not create dead rows,
> only
> UPDATE and DELETE do. Thus, if you only insert rows, you do not
> need to
> vacuum (although you probably need to analyze).

Not entirely true. An insert & rollback will create dead rows. If you
attempt and fail a large number of insert transactions then you will
still need to vacuum.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Sun, Nov 26, 2006 at 09:24:29AM -0500, Rod Taylor wrote:
> attempt and fail a large number of insert transactions then you will
> still need to vacuum.

And you still need to vacuum an insert-only table sometimes, because
of the system-wide vacuum requirement.

A


--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Rod Taylor wrote:
>> Just a minor clarification here: INSERT does not create dead rows, only
>> UPDATE and DELETE do. Thus, if you only insert rows, you do not need to
>> vacuum (although you probably need to analyze).

Is there no real-time garbage collection at all in Postgres? And if so, is this because nobody has had time to implement garbage collection, or for a more fundamental reason, or because VACUUM is seen as sufficient?

I'm just curious ... Vacuum has always seemed to me like an ugly wart on the pretty face of Postgres. (I say this even though I implemented an identical solution on a non-relational chemistry database system a long time ago. I didn't like it then, either.)

Craig

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Craig A. James" <cjames@modgraph-usa.com> writes:
> Is there no real-time garbage collection at all in Postgres?

No.

> And if so, is this because nobody has had time to implement garbage
> collection, or for a more fundamental reason, or because VACUUM is
> seen as sufficient?

If you really want to know, read the mountains of (mostly) junk that
have been written about replacing VACUUM in pgsql-hackers. The short
answer (with apologies to Winston Churchill) is that VACUUM is the worst
solution, except for all the others that have been suggested.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> If you really want to know, read the mountains of (mostly) junk that
> have been written about replacing VACUUM in pgsql-hackers. The short
> answer (with apologies to Winston Churchill) is that VACUUM is the worst
> solution, except for all the others that have been suggested.

The lesser of 50 evils?

Joshua D. Drake



>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Sun, Nov 26, 2006 at 12:24:17PM +0100, Joost Kraaijeveld wrote:
> Hi,
>
> Are there guidelines (or any empirical data) available how to determine
> how often a table should be vacuumed for optimum performance or is this
> an experience / trial-and-error thing?

Most of the time I just turn autovac on, set the scale factors to
0.2/0.1 and the thresholds to 300/200 and turn on vacuum_cost_delay
(usually set to 20). That's a pretty decent setup for most applications.
It also doesn't hurt to run a periodic vacuumdb -av and look at the tail
end of it's output to make sure you have adequate FSM settings.

The exception to that rule is for tables that are very small and have a
lot of churn; I'll vacuum those by hand very frequently (every 60
seconds or better).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match