 PostgreSQL Notes 0.4

- if possible use PostgreSQL 7.3.2 (or higher). Lots of 
BUGFIXES and PERFORMANCE fixes went into recent versions of PostgreSQL. According to 
real word experience even 7.2.3 is noticeably faster than 7.1.3  somebody claimed 40% speed
 increase just by upgrading from 7.1.3 to 7.2.3 and I would agree. Also future versions
 of Postgresql will help a lot with phorum queries (IN vs EXISTS and such)

- if you need to use locale (which you do if US ASCII does not cover all your
characters) try to initdb with locale C for LC_COLLATE. Something like this
will do the job nicely:
(example for sh/sh/zsh/bash shells and Slovenia  modify it accordingly)

LC_COLLATE=C
LC_MESSAGES=sl_SI.ISO_8859-2
LC_NUMERIC=sl_SI.ISO_8859-2
LC_CTYPE=sl_SI.ISO_8859-2
LC_MONETARY=sl_SI.ISO_8859-2
LC_TIME=sl_SI.ISO_8859-2
export LC_COLLATE LC_MESSAGES LC_NUMERIC
export LC_CTYPE LC_MONETARY LC_TIME

and finally initdb for this:

initdb -E LATIN2 lc-collate=C

This way we get the best of both world  PostgreSQL will use optimization for
indexes while still having proper data presentation in accordance to set locale.

- PostgreSQL defaults are EXTREMELY conservative - only 32 concurrent connections
and hardly any usage of shared memory and temporary buffers. Check your operating
system docs about shared memory and increase it both in operating system and
postgresql.conf as this will greatly speed up things. But do not raise shm too
much as this can cause too much scanning of shm - practice shows few thousand
buffers (usually less than 10 000) is enough for most cases.

- create a user for phorum and give him temporary ability to create new databases
(example: createuser phorumuser), create new database with this user (example:
createdb -U phorumuser phorumdb), remove its ability to create databases, now proceed
with installation of phorum as per other docs


Regular maintainance

- run VACUUM FULL ANALYZE nightly by putting vacuumdb -a -f -z into cron as
this will greatly help PostgreSQL perform better
- run pg_dump/pg_dumpall regularly to perform database backups as this will
help you A LOT in case of problems
- run reindexdb (from contrib) every now and then to recreate indexes which can
have MAJOR speed impact for very busy sites as vacuuming does not yet (not before
pgsql 7.4) clean up things for indexes. You know you need this badly when things
simply take much longer than before.

Feel free to improve this document by vising Documentation Forum at phorum.org
(http://phorum.org/support/list.php?f=5).



