You are correct, in that these tables are not large (50,000 records), but
their effect on performance is noticeable. Plain VACUUM (no freeze, full,
etc)
does the trick well, but I am unable to figure a way to call the 'plain
vanilla
version' of VACUUM via a PostgreSQL trigger function (does not allow it).
Using the Windows scheduler (schtask, somewhat like Unix cron) is an option,
but not a good one, as it takes too much out of the platform to run. My
client
does not use strong platforms, so I have to be concerned about that. VACUUM
is
a minimum impact on performance when running. I believe it would be much
better
to be able to call VACUUM out of a function, the same way in which other SQL
commands are used.
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Friday, October 07, 2005 3:53 AM
To: Lane Van Ingen
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Need Some Suggestions
Lane Van Ingen wrote:
> I have an application that is prone to sudden, unscheduled high bursts of
> activity, and I am finding that the application design permits me to
detect
> the activity bursts within an existing function. The bursts only affect 3
> tables, but degradation becomes apparent after 2,000 updates, and quite
> significant after 8,000 updates.
Hmm - assuming your free-space settings are large enough, it might be
adequate to just run a vacuum on the 3 tables every 5 minutes or so. It
sounds like these are quite small tables with a lot of activity, so if
there's not much for vacuum to do it won't place too much load on your
system.
--
Richard Huxton
Archonet Ltd
