Обсуждение: Postgres Table grows very quickly?

I have a postgres database that I use to keep stats on my routers. I have a
separate table for each router and another called routerinfo where I keep
model information and the time and date each of the seperate router tables
have been updated.

Routerinfo has 16 rows (for 16 routers) it gets updated every five minutes
with the last date and time each of the other tables have been updated. I
use a standard sql update statement. eg:   

UPDATE routerinfo SET lastdate = '09-14-1999', lasttime = '09:00' WHERE
linkid = 'Sydney';

Routerinfo is 8k after the initial update, from then on it grows 8k for
every update. I never do an insert and it never grows beyond 16 rows. It
seems as if the table is keeping a history of all updates, but timetravel is
disabled in the current release of postgres, so that can't be it. If anybody
has any idea of how I can stop this, please let me know

Thank you.


Michael Green
Senior Systems Engineer
Communications Systems
Global Banking & Securities Transactions
http://www.gbst.com.au
GBST is a trademark of Star Systems Pty Ltd

On Tue, 14 Sep 1999, Michael Green wrote:

> UPDATE routerinfo SET lastdate = '09-14-1999', lasttime = '09:00' WHERE
> linkid = 'Sydney';
> 
> Routerinfo is 8k after the initial update, from then on it grows 8k for
> every update. I never do an insert and it never grows beyond 16 rows. It

Check out mvcc. Basically running an update does duplicate the tuple. A
vacuum will reclaim the extra space. 8k (an entire page) seems a little
excessive for each updated tuple, but I don't really know anything about
how data is physically stored.

I generally run vacuum; once a night and vacuum analyze; once a week, but
that's more or less dependant on tyour database.

-Michael

I also had this problem - I use pastgres as a backend for counting
of Web hits - not insert but update as in your case.
Postgres historicallydoing update as insert new row and marking old one
to be deleted in next vacuum, Tha's why  database grows while only update.
But also, index file grow infinitely. I do vacuum of my table in cron job -
this truncate table size but stock 6.5.1 doens't truncates index file !
Vidim Mikheev fixed this problem by implementing re-use of index file in
vacuum analyze so index file still grows but not so quickly. I think it's
a hack but this works.  Full solution would be implemeting update as
an update but I don't know if this in postgres TODO list.
Wait for 6.5.2 (it will be available rather soon)
Regards,    Oleg


On Tue, 14 Sep 1999, Michael Green wrote:

> Date: Tue, 14 Sep 1999 09:35:41 +1000
> From: Michael Green <michael.green@gbst.com>
> To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgreSQL.org>
> Subject: [SQL] Postgres Table grows very quickly?
> 
> I have a postgres database that I use to keep stats on my routers. I have a
> separate table for each router and another called routerinfo where I keep
> model information and the time and date each of the seperate router tables
> have been updated.
> 
> Routerinfo has 16 rows (for 16 routers) it gets updated every five minutes
> with the last date and time each of the other tables have been updated. I
> use a standard sql update statement. eg:   
> 
> UPDATE routerinfo SET lastdate = '09-14-1999', lasttime = '09:00' WHERE
> linkid = 'Sydney';
> 
> Routerinfo is 8k after the initial update, from then on it grows 8k for
> every update. I never do an insert and it never grows beyond 16 rows. It
> seems as if the table is keeping a history of all updates, but timetravel is
> disabled in the current release of postgres, so that can't be it. If anybody
> has any idea of how I can stop this, please let me know
> 
> Thank you.
> 
> 
> Michael Green
> Senior Systems Engineer
> Communications Systems
> Global Banking & Securities Transactions
> http://www.gbst.com.au
> GBST is a trademark of Star Systems Pty Ltd
> 
> 
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83