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@xxxxxxxxxxxxxxx www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general