Re: db size and VACUUM ANALYZE

Marcin Krol wrote:
> Result before (1.6G db):
>
>
> size_in_bytes | relname
> ---------------+----------------------
> 806387712 | cs_ver_digests_pkey
> 103530496 | oai_edi_atts_pkey

There's your problem. This is called "index bloat"; these are the two
biggest relations in the large and slow database, but don't even show up
in the top 10 on the smaller one. It usually happens when your VACUUM
strategy is bad and you delete/update things all the time. Notes on
this topic start at
http://www.postgresql.org/docs/8.3/static/sql-reindex.html

You can clean it up with REINDEX or CLUSTER, but not VACUUM FULL, which
actually makes the problem worse. No need to rebuild the whole DB.

> max_fsm_pages = 153600

It's quite possible that's way too low for your workload. I already
suggested VACUUM VERBOSE would dump info into the logs suggesting as
much if that's the case; try that out next time you see the database get
too big.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com