Table size growing for no reason

I am running PostgreSQL 8.1.4 on windows.
I have a table with the following structure:

CREATE TABLE "public"."tblksaura" (
"ksaurasysid" SERIAL,
"testtime" TIMESTAMP WITHOUT TIME ZONE,
"lasthouralive" DOUBLE PRECISION[],
"last24hrsalive" DOUBLE PRECISION[],
"last7daysalive" DOUBLE PRECISION[],
"last30daysalive" DOUBLE PRECISION[],
"sumalivelasthour" DOUBLE PRECISION,
"sumreplylasthour" DOUBLE PRECISION,
"sumalivelast24hrs" DOUBLE PRECISION,
"sumalivelast7days" DOUBLE PRECISION,
"sumalivelast30days" DOUBLE PRECISION,
"sumreplylast24hrs" DOUBLE PRECISION,
"sumreplylast7days" DOUBLE PRECISION,
"sumreplylast30days" DOUBLE PRECISION,
"lasthourreply" DOUBLE PRECISION[],
"last24hrsreply" DOUBLE PRECISION[],
"last7daysreply" DOUBLE PRECISION[],
"last30daysreply" DOUBLE PRECISION[],
"lasthourstatus" INTEGER[],
"totaltestslasthour" BIGINT[],
"totaltestslast24hrs" BIGINT[],
"totaltestslast7days" BIGINT[],
"totaltestslast30days" BIGINT[],
"kstestssysid" INTEGER,
CONSTRAINT "tblksaura_kstestssysid_key" UNIQUE("kstestssysid"),
CONSTRAINT "tblksaura_pkey" PRIMARY KEY("ksaurasysid"),
CONSTRAINT "tblksaura_fk_tblkstests" FOREIGN KEY ("kstestssysid")
REFERENCES "public"."tblkstests"("kstestssysid")
MATCH FULL
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;

CREATE UNIQUE INDEX "tblksaura_idx_kstestssysid" ON "public"."tblksaura"
USING btree ("kstestssysid", "testtime");

Running explain select * from tblkaura indicates that the width of the
row is 1859.
The table has 3297 rows.

Initially, it was 6.8MB. All of a sudden it has started growing in size
- both on the main table size, the indices, and toast.

I truncated the table and reloaded the data - it went back to 6.8 Mb.

This table is updated at a rate of about 10-12 updates statements per
second, by a single connection (a monitoring agent).

What can be causing this strange behavior? Is there anything which can
be done? The update is being performed by a pl/pgsql stored procedure.

The SQL query which is updating it uses the primary key for retrieveing
the field.

The query looks as follows:

update tblksaura
set
testtime = t_mytesttime,
totaltestslasthour = ia_totaltestslasthour,
totaltestslast24hrs = ia_totaltestslast24hrs,
totaltestslast7days = ia_totaltestslast7days,
totaltestslast30days = ia_totaltestslast30days,
lasthourstatus = ia_lasthourstatus,
lasthourreply = fa_lasthourreply,
last24hrsreply = fa_last24hrsreply,
last7daysreply = fa_last7daysreply,
last30daysreply = fa_last30daysreply,
lasthouralive = fa_lasthouralive,
last24hrsalive = fa_last24hrsalive,
last7daysalive = fa_last7daysalive,
last30daysalive = fa_last30daysalive,
sumalivelasthour = r_aurarecord.sumalivelasthour,
sumreplylasthour = r_aurarecord.sumreplylasthour,
sumalivelast24hrs = r_aurarecord.sumalivelast24hrs,
sumreplylast24hrs = r_aurarecord.sumreplylast24hrs,
sumalivelast7days = r_aurarecord.sumalivelast7days,
sumreplylast7days = r_aurarecord.sumreplylast7days,
sumalivelast30days = r_aurarecord.sumalivelast30days,
sumreplylast30days = r_aurarecord.sumreplylast30days
where ksaurasysid = r_aurarecord.ksaurasysid;

Any assistance in getting this tracked down will be deeply appreciated.

Looking at the current running processes in pgadmin I cannot see
anything which could be causing this.

After about 1 hour of running, the table is now over 450MB!!!!!

I am out of ideas.

On 7/13/06, Benjamin Krajmalnik <kraj(at)illumen(dot)com> wrote:
>
>
> I am running PostgreSQL 8.1.4 on windows.
> I have a table with the following structure:
>
> CREATE TABLE "public"."tblksaura" (
> "ksaurasysid" SERIAL,
> "testtime" TIMESTAMP WITHOUT TIME ZONE,
> "lasthouralive" DOUBLE PRECISION[],
> "last24hrsalive" DOUBLE PRECISION[],
> "last7daysalive" DOUBLE PRECISION[],
> "last30daysalive" DOUBLE PRECISION[],
> "sumalivelasthour" DOUBLE PRECISION,
> "sumreplylasthour" DOUBLE PRECISION,
> "sumalivelast24hrs" DOUBLE PRECISION,
> "sumalivelast7days" DOUBLE PRECISION,
> "sumalivelast30days" DOUBLE PRECISION,
> "sumreplylast24hrs" DOUBLE PRECISION,
> "sumreplylast7days" DOUBLE PRECISION,
> "sumreplylast30days" DOUBLE PRECISION,
> "lasthourreply" DOUBLE PRECISION[],
> "last24hrsreply" DOUBLE PRECISION[],
> "last7daysreply" DOUBLE PRECISION[],
> "last30daysreply" DOUBLE PRECISION[],
> "lasthourstatus" INTEGER[],
> "totaltestslasthour" BIGINT[],
> "totaltestslast24hrs" BIGINT[],
> "totaltestslast7days" BIGINT[],
> "totaltestslast30days" BIGINT[],
> "kstestssysid" INTEGER,
> CONSTRAINT "tblksaura_kstestssysid_key" UNIQUE("kstestssysid"),
> CONSTRAINT "tblksaura_pkey" PRIMARY KEY("ksaurasysid"),
> CONSTRAINT "tblksaura_fk_tblkstests" FOREIGN KEY ("kstestssysid")
> REFERENCES "public"."tblkstests"("kstestssysid")
> MATCH FULL
> ON DELETE CASCADE
> ON UPDATE NO ACTION
> NOT DEFERRABLE
> ) WITHOUT OIDS;
>
> CREATE UNIQUE INDEX "tblksaura_idx_kstestssysid" ON "public"."tblksaura"
> USING btree ("kstestssysid", "testtime");
>
> Running explain select * from tblkaura indicates that the width of the row
> is 1859.
> The table has 3297 rows.
>
> Initially, it was 6.8MB. All of a sudden it has started growing in size -
> both on the main table size, the indices, and toast.
>
> I truncated the table and reloaded the data - it went back to 6.8 Mb.
>
> This table is updated at a rate of about 10-12 updates statements per
> second, by a single connection (a monitoring agent).
>
> What can be causing this strange behavior? Is there anything which can be
> done? The update is being performed by a pl/pgsql stored procedure.
>
> The SQL query which is updating it uses the primary key for retrieveing the
> field.
>
> The query looks as follows:
>
> update tblksaura
> set
> testtime = t_mytesttime,
> totaltestslasthour = ia_totaltestslasthour,
> totaltestslast24hrs = ia_totaltestslast24hrs,
> totaltestslast7days = ia_totaltestslast7days,
> totaltestslast30days = ia_totaltestslast30days,
> lasthourstatus = ia_lasthourstatus,
> lasthourreply = fa_lasthourreply,
> last24hrsreply = fa_last24hrsreply,
> last7daysreply = fa_last7daysreply,
> last30daysreply = fa_last30daysreply,
> lasthouralive = fa_lasthouralive,
> last24hrsalive = fa_last24hrsalive,
> last7daysalive = fa_last7daysalive,
> last30daysalive = fa_last30daysalive,
> sumalivelasthour = r_aurarecord.sumalivelasthour,
> sumreplylasthour = r_aurarecord.sumreplylasthour,
> sumalivelast24hrs = r_aurarecord.sumalivelast24hrs,
> sumreplylast24hrs = r_aurarecord.sumreplylast24hrs,
> sumalivelast7days = r_aurarecord.sumalivelast7days,
> sumreplylast7days = r_aurarecord.sumreplylast7days,
> sumalivelast30days =
> r_aurarecord.sumalivelast30days,
> sumreplylast30days = r_aurarecord.sumreplylast30days
> where ksaurasysid = r_aurarecord.ksaurasysid;
>
>
> Any assistance in getting this tracked down will be deeply appreciated.
>
> Looking at the current running processes in pgadmin I cannot see anything
> which could be causing this.
>
> After about 1 hour of running, the table is now over 450MB!!!!!
>
> I am out of ideas.

VACUUM?

http://www.postgresql.org/docs/8.1/static/maintenance.html#ROUTINE-VACUUMING