Esger Abbink wrote:
> 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).
No wonder data is growing real fast. If it is updated in real time it is going
to generate dead tuples like crazy..
> 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.
Hmm.. Vacuum full should have done the trick..
> the bulk of the space is occupied by a dozen huge files in base/16559,
> all sufficiently meaninglessly named: 72646, 72653.1 etc.
You can find the corresponding table names by querying pg_class..
> 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?
If you can take down the database for some time, you can dump the entire
database using pg_dump, drop the database and recreate from the dump. Surely it
would take lot less than few hours.
And I think you despartely need pg_autovacuum.. A daemon that vacuums in real
time according to requirement. It's in CVS head and works with 7.3/7.4. Up your
max fsm setting appropriately too.. That should help keeping your database size
in check..
HTH
Shridhar