tuning options for 8.4

[raster846]

I recall seeing a topic where one of you had some tuning options for PostgreSQL 8.4. Something about the config files. I can't seem to find it on the forum.
Can you point me to it?

Thanks

[WhiteRider]

v3.07.2 includes this fix so that you don't need to make the changes manually, but if you don't want to udpate I'll post them here.

Download page
PT3 Build History / Release Notes

We've found a setting in PostgreSQL 8.4 which should speed up the slow queries.
Please try this:

Edit the postgresql.conf file (Start > Programs > PostgreSQL 8.4 > Configuration Files > postgresql.conf) - if you're using Vista or Win 7 then right click > Run As Administrator in order to be able to save the file.

On Mac the files are in /Library/PostgreSQL/8.4/data (note: not ~/library) and you can edit them with any text editor. If you don't have permission see this page.
Mac Files and Folders

Find "from_collapse_limit" and "join_collapse_limit" and set them both to 12. You must REMOVE the # from the start of the lines, like this:
from_collapse_limit = 12
join_collapse_limit = 12

Save the file and then Start > Programs > PostgreSQL > Reload Configuration (on Mac: Applications > PostgreSQL 8.4 > Restart Server).

That should help performance.

[Brainish]

Is 8.3 still the preferred version for PT3?

[WhiteRider]

I suspect we'll start including 8.4 with PT3 before too long, although I don't know for sure. Personally I still use 8.3 - the latest updates are pretty good. Either is fine, though - we have a lot of users using 8.4 as well.

[pasita]

Any of this valid for 9.0? Both options seem to be commented out in my conf file.

[WhiteRider]

This shouldn't be necessary at all any more, because the changes were made in PT3, but if you're having problems you could try making the changes. Be sure to make a copy of the file before you make any changes so that you are able to revert if you need to.

You could also check out the tuning threads linked from the Database / PostgreSQL FAQ.

[tarix]

This is completely unnecessary and you should not make these changes to your config file.

[Brainish]

This is completely unnecessary and you should not make these changes to your config file.

Any recommended tuning for 9.0? What about for SSDs?

[kraada]

The tuning recommended in the Advanced Method (here) is still valid for 9.0 and holds true whether you have an SSD or not. The biggest difference as an SSD user in terms of general maintenance is that you'll get basically no benefit from ever clustering so we do not recommend you do it unless you have very specific errors (in which case we'll suggest it).

[tarix]

Any recommended tuning for 9.0? What about for SSDs?

For SSD make sure you random_page_cost is 1.0 or 1.5. Sequential reads and random reads are generally the same speed on SSD. (1.5 is more conservative in case your SSD has a small penalty for random data.)