Since I manage multiple computers and each run a PostgreSQL database system, I can see discrepancies between versions. (i.e. newer versions fix problems in older versions.)

One is in regard to the VACUUM FULL function. A properly installed PostgreSQL system is expected to run a VACUUM command every now and then on each of your database system. That function is a defragmenter, if you wish. It goes through your clusters and remove empty space (i.e. deleted nodes, logs, aggregator items, etc.) and moves data that is accessed more to the start of your file which can be accessed faster than the end of the file.

Somehow, older versions of PostgreSQL did not properly run VACUUM and I wondered why that would be. Today I noticed that the configuration was using "auto_vacuum = yes" instead of the expected "auto_vacuum = on". (i.e. all the other variables use the keyword ON (or OFF) and not YES and NO, and newer system have that variable fixed.)

This being said, there is nothing I could find about that mistake. It sounds to me as if YES or ON should work alike. It is clearly defined that way in SQLite.

Although, notice that I have no clue whether that made any difference, looking at the output of ps -ef I still could not see anything such as the expected postgres VACUUM process:

   postgres: autovacuum launcher process

So I guess I will still have to search for the real cause of the non-starting process...

Solution

Okay, my computer got bugged down again so I looked further into this one.

First of all, if you use a Drupal website (or similar) and have an Aggregator turn on (i.e. a tool to get RSS feed data to your site) it generates a lot of database accesses without anyone touching your site. Those numerous accesses will generate a lot of dirt that definitively need to be vacuumed.

The solution is to make sure that at least ALL of the following variables are turned ON. Note that applies to version 8.1 and 8.2 of PostgreSQL. Since version 8.3, it is better handled and works by default (at least under Ubuntu it does.)

superuser_reserved_connections = 4
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 300
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = 0.4

The values can be whatever you think is appropriate on your system. The defaults are not bad but some values may either be too large or too small on your system.

FYI, I found this website http://bricolagecms.org/docs/1.10/api/Bric/DBA.html with information on how to optimize your PostgreSQL database for Bricolage CMS, useful tool to organize your enterprise documents.

See also: PostgreSQL VACUUM not working...