you need to do vacuum full on a regular basis, like at about the time that the select/update times increase by 10-25%.
Youcould put that on a chron job.
marco wrote:
> Hello all,
>
> I've a little performance problem with psql 7.2.3.
>
> I have a little database with approx. eight tables. They have two or six
> columns and less than 2000 rows.
>
> Three of them are regularly updated by a small C++-tool. It inserts or
> updates entries of these three tables. All entries of one of these
> tables are deleted with "Delete from..." everytime the tools runs (e.g.
> every 20 minutes).
>
> I'm using indices on all tables.
>
> Now I can observe the following phenomenon:
>
> The total runtime of the tool is increasing up to a certain peak and
> then it drops to the half of the peak.
> The time interval from one peak to the next is also increasing as well
> as the total value of the peak.
>
> But the total time for database access is not only increasing for write
> access but also for read access.
>
> The time it takes to Select things from the database is increasing too.
>
> I unfortunately don't understand the whole thing totally, but if I dump
> the database (with pg_dump), delete it and restore it, the time values
> for reading and writing have decreased to a normal level and begin to
> increase again.
>
> It seems to me, that I do sth. fundamentally wrong :( But even after
> searching google and the postgresql archives I don't see the light at
> all...
>
> Please help me out here.
>
> Thanks and best regards
>
> Marco Wedekind
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>
