When you use a PostgreSQL database, it gets vacuumed every night (by default on most systems, verify on yours, it may not be the case...) This is a quite simple process to make sure that the database remains fast. And it works great, also. You can have millions of entries and it remains very fast (they have real good algorithms to auto-generate index files as required by your repetitive queries.)

The problem is that tables are being grown for 24h and then they get cleaned up. They can then grow again for 24h. If you don't clean up, the tables continue to grow, fast. Up to the point where looking for something in your tables takes a long time because they have to seek very large files (multiple giga bytes.)

I use Drupal for many of my sites and I reached the default limit of 1,000 FSM relations quickly. However, when the VACUUM command reaches the limit, instead of cleaning up in a cycling way, it just breaks and many tables just never got vacuumed.

I put the limit to 2000 and it works fine. I now have:

   max_fsm_pages = 45000
   max_fsm_relations = 2000

Note that you have to setup the pages too because the default is 20,000 and it needs to be at least the number of relations times 16 (i.e. ~32,000—although I tried with 32,000 and it failed! I could not start the server.)

Now it will work a lot better.

By the way, you will NOT see anything about VACUUM failing. You will most certainly notice a slow down on your site, though. To discover whether you need to increase your FSM values, go to your database and run VACUUM as follow:

  alexis% psql mydb
  pgsql> VACUUM;
  NOTICE:  max_fsm_relations(1000) equals the number of relations checked
  HINT:  You have at least 1000 relations.  Consider increasing the configuration parameter "max_fsm_relations".

As you can see, they give you the solution right there. But this is a NOTICE and thus you are not likely to see it in your logs and your database will get slow...

After your change, your will have to restart your database server. I suggest a stop followed by a start so you can see any errors in your setup, if any. (i.e. too small value in max_fsm_pages, etc.)

I'm still experiencing some problems, it could be because wikishop.com is scanning this very website. I just tried a VACUUM FULL command to see whether that helps...

One reference on Postgresql.org: http://archives.postgresql.org/pgsql-patches/2005-03/msg00110.php

See also: Auto-Vacuum not working?