Hello,
it is very possible that this is a well described problem, but I have
not been able to find the solution.
On two production server (7.2rc2) of ours the data directory is growing
to very large sizes while the data that is actually in the db's isnt 1.
that large and 2. growing.
The databases see a fairly limited/constant use at the moment. The data
they contain is close to real-time information and as such they are
continuously updated with new data. The amount of queries executed is
low or very low (this will change in the future).
To prevent the database from growing to unmanageable size it's being
emptied and vacuumed twice a day. This is only a "vacuum" btw, not a
"vacuum analyze". I did an analyze by hand to get the planner to use a
plan suited for 100k+ tables as before that performance was horrid.
The problem is that the data directories have reached sizes of 4.1 and
4.5 Gigabyte respectively. A recreation of the 4.1Gb db after a pg_dump
on a different system yields a total of about 460Mb in ALL databases on
that system, the dump file itself is 10Mb. Clearly the 4Gb is a bit
wastefull...
Next to the daily automatic vacuum's I have done a "vacuum full" by hand
(this took a few hours) but this has not resulted in any reduction in
file sizes.
the bulk of the space is occupied by a dozen huge files in base/16559,
all sufficiently meaninglessly named: 72646, 72653.1 etc.
As vacuuming doesnt help, whats up? Could it be that there are lots of
stuck transactions keeping huge amounts of old&removed rows on disk
through MVCC or something?
thanks for any help,
Esger
--
NeoMail - Webmail that doesn't suck... as much.
http://neomail.sourceforge.net