> A strange behaviour is observerd in the physical files with respect to
> this table. The size of the file is growing abnormally in GBs. Suppose
> the file name (oid of relation )with respect to the table is "18924" I
> could find entries of 1 GB files like 18924, 18924.1, 18924.2 ,
> 18924.3......18924.40 in the data directory, though there are on 10k
> records in the table.
Hello,
your application is similar to mine and has similar problems.
In mine I have average 10 Updates per second with peaks of 100 updates
per second...
this kind of applications is known to cause un undesireable behaviour in
PostgresSQL called (I hope I'm not confusing things here) table bloat
and if you have indexes/primary keys, also index bloat;
the solution is somewhere in the correct balance of: autovacuum,
periodic vacuuming, vacuum full once in a while, and re-indexing; of
course upgrading to 8.3.x or higher is very very much recommended. but
with this kind of application (very frequent updates)
vacuum/autovacuum/reindex/cluster will always be good friends to have
around.
My best advice is to quickly read the documentation regarding those
commands and start applying them to your test database; when you get the
feeling of it configure autovacuum and plan weekly (to start with)
vacuum/reindex operations; vacuum full is typically not needed but let
the experience tell you how it goes in your case.
I'm not a postgres expert but my application, similar in behaviour to
yours, teached me these things. So I'm sorry if I'm not being totally
cientific regarding my suggestions.
cheers
Joao
>