PostgresqlDb

If you want to do complex searches on large text fields in Postgresql? then you need to use http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html?. The current install comes with tsearch2 installed by default, but you need to run the included tsearch2.sql file first before you can create any tables with tsvector fields.

The instructions to install tsearch2 on linux are different: http://www.devx.com/opensource/Article/21674/0/page/2
On linux it's also necessary to install the dictionary myself: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
INSERT INTO pg_ts_dict (dict_name,dict_init,dict_initoption,dict_lexize)
SELECT 'en_ispell',
dict_init,
'DictFile="/usr/local/pgsql/lib/english.med",'
'AffFile="/usr/local/pgsql/lib/english.aff",'
'StopFile="/usr/local/pgsql/share/contrib/english.stop"',
dict_lexize
FROM pg_ts_dict
WHERE dict_name = 'ispell_template';
To make it ready for pg_backup and pg_restore, do:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_update.sql


How to creat triggers: http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html


When installing on linux the following location has a good instruction on how to install it:
http://www.galatea.com/flashguides/start-postgresql-linux
But, the gmake step looped infinitely for me. To fix it I needed to do:
cd /<your psql source dir>
find . -exec touch {} ;

To quit out of the psql command line client, do "q".

On the dev machine, enable stats collection by editing the postgresql.conf file and setting the following to true:
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true
These stats can be reset by doing:
select pg_stat_reset();
The stats can be viewed in pgAdmin on the "Statistics" tab of many of the objects (most importantly on the tables).