 PostgreSQL Notes
 - v0.5 (tomaz@phorum.org)

- if possible use PostgreSQL 7.3.3 (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 or create db as pgsql superuser and specify
during create time (like: createdb -O phorumuser phorumdb) if you run pgsql 7.3 or higher
(which you should!).


Regular maintainance

- run VACUUM FULL ANALYZE nightly/weekly by putting vacuumdb -a -f -z into cron as
this will greatly help PostgreSQL perform better. Schedule this when usage is almost
non-existent as it affects users by locking tables.
- run VACUUM ANALYZE at least once per day or more. Can be as much as few times per
hour if database has a lot of new/changed data. This will EXTREMELY help indexes
perform better resulting in better overall experience.
- 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.



