abnormal data grow

[Reynard Hilman]

Hi,

I have been having this problem where the database size suddenly grows
from the normal size of about 300Mb to 12Gb in one night.
When I look up the table size, the biggest one is only 41Mb and the
total of all table size is only 223Mb.
But in the filesystem data directory the total size is 12Gb. I noticed
there are 10 files with 1Gb size each:
1.1G 25677563
1.1G 25677563.1
1.1G 25677563.2
1.1G 25677563.3
1.1G 25677563.4
1.1G 25677563.5
1.1G 25677563.6
1.1G 25677563.7
1.1G 25677563.8
1.1G 25677563.9

So I tried to find out what table that is, but this query return nothing:
SELECT relname, relpages
FROM pg_class
WHERE relname = 'pg_toast_25677 563' OR relname =
'pg_toast_25677 563_index'
ORDER BY relname;

and if I run this query:
SELECT relname, relpages
FROM pg_class ORDER BY relpages desc

the top ones are these tables (which is not the id of the 1.1 Gb files)
pg_toast_256775 61 1382845
pg_toast_256775 61_index 22116

I'm just wondering is there a way to know what that 25677563 file is?
Why does postgres create a copy of that file with .1, .2, .3, etc. Is
that file still in used (because I can't find it in the pg_class table)?
Will postgres clean the database up or should I do a manual clean?

- reynard
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ed L.]

On Tuesday September 21 2004 12:40, Reynard Hilman wrote:

Okay, so you have a lot of wide (toasted) fields in whatever table that
toast table belongs to ...

Just curious though, why the toast file for this transaction table takes
the most space (the transaction table itself was only about 10Mb),
considering there are a few other tables with bigger size (40Mb) than
this transaction table but do not have toast file that's comparable in
size to this one.

Not sure this is your problem, but when I have tables whose on-disk size is
significantly larger than the size reported from contrib's dbsize.sql
queries (if that's what you're using), I begin to suspect diskspace
"leakage" due to insufficient postgresql.conf settings for max_fsm_pages
and max_fsm_relatio ns. Those settings need to be bigger than the max
amount of diskspace and number of relations that you'll ever use, or you
may experience this leakage, requiring a "vacuum full" or dump/reload
(alias downtime) to repair.

Ed
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match