As many of us have anticipated, Josh Berkus has, with Shridhar Daithankar's help put together two marvelous documents to help everyone sort out the details of the postgresql.conf file and the GUC (Global User Configuration) options.

The first document, Tuning PostgreSQL for Performance is a set of guidelines and questions to ask when preparing to tune an installation.

In the second document, The Annotated postgresql.conf, the elements of the postgresql.conf file are nicely organized into functional groups. Each item is documented with its range, default value, corresponding variable and -o option and is described in detail. Comments on most items also give you a clearer understanding of how and when to set these variables and what impact should be expected.

Both of these documents are required reading for anyone who plans to set up a production installation of PostgreSQL.

• Tuning PostgreSQL for Performance
  
• The Annotated postgresql.conf (html)
  
• The Annotated postgresql.conf (sxw OpenOffice Format)
  

Contributors: Josh Berkus at agliodbs.com, Shridhar Daithankar at persistent.co.in

In order to search data ignoring accents, you must convert the data to ASCII.

A table containing the following data and selected with the usual LIKE case, then you would only get "Polo".

	--------
	Colón
	Polo
	--------
	SELECT * 
	FROM testtable 
	WHERE testfield like '%olo%';

	SELECT * 
	FROM testtable 
	WHERE to_ascii(testfield,'LATIN1') LIKE '%olo%'

Contributors: Alejandro Javier Pomeraniec apomeraniec at buenosaires.gov.ar, Ian Barwick barwick at gmx.net, Alvaro Herrera alvherre at dcc.uchile.cl

For a table with a three part key, the question was raised whether a functional index on the concatenated fields or a multi-part index might be faster. The indexes would be:

	create index threepart on table foo (col_one, col_two, col_three);
	create index catthree on table foo ( cattext3(col_one, col_two, col_three));

It is agreed that the three part index is better than the functional index for several reasons. Particularly if the "least non-unique" value is the leftmost in the index definition, the three part index will be faster.

The multi-part indexes in general are much more flexible as well. Partial index searches can use multi-part indexes. For example, if you were to search only on col_one the first part of the multi-part index would be used. The multi-part index would not be used for col_two without being preceded with col_one, but that would not work in the functional index either.

In a related question, if the table had the threepart index and an index on col_one alone, the index on col_one alone would be redundant. There is no need to create or store the second index when the first will do the work.

Contributors: Ernest E Vogelsinger ernest at vogelsinger.at, Tom Lane tgl at sss.pgh.pa.us, Vivek Khera khera at kcilink.com, Bruno Wolff III bruno at wolff.to, Manfred Koizar mkoi-pg at aon.at, scott.marlowe scott.marlowe at ihs.com, Reece Hart rkh at gene.COM

When installing languages, like plpgsql in a database, the system checks whether the directory $libdir exists. $libdir is a PostgreSQL representation of the directory specified as the path to the shared objects library when PostgreSQL was configured.

If you configured with --prefix=/local/pgsql73, for example, the string $libdir is expanded to /local/pgsql73/lib. $libdir's value is usually the result of the cascading defaults of --prefix=PREFIX --exec-prefix=EPREFIX and --libdir=dir.
PREFIX defaults to /usr/local/pgsql.
EPREFIX defaults to PREFIX.
$libdir defaults to EPREFIX/lib

The location of $libdir is also pertinent when writing C functions. When you define a C function in SQL, you must specify the location of the shared object (.so) of your function. You can use $libdir literally in the SQL definition to signify that your shared object is in your installation's definition of $libdir

	CREATE FUNCTION hello_cstr()
	RETURNS cstring
	AS '$libdir/hello_cstr.so'
	LANGUAGE 'c';

It is not required that $libdir be used for C function shared objects. It is also possible to specify a full path name for development or for storing shared objects in an alternate directory.

Contributors: Rich Cullingford rculling at sysd.com, Tom Lane tgl@sss.pgh.pa.us, elein at varlena.com

Having two separate installations on the same machines is fairly straightforward. With the beta of 7.4 coming up, many people will want to keep their existing installations and still be able to test a 7.4 installation.

There are three key elements in creating separate installations, location of the installation, location of the data directory and client access. The location of the installation is determined at configuration time by setting the prefix of the build to be in a specified directory instead of the default directory. The location of the data directory is determined with initdb. The client access is determined by setting the appropriate PG variables and ensuring the appropriate installation's bin is earlier in your PATH than the other one.

To build a 7.4 installation into /local/pgsql74 one would use:

	./configure --prefix=/local/pgsql74 ... 

Next you need to determine where the data directory for the installation will reside. My own preference is the directory PREFIX/data so that it is not in the database superuser's home directory and so that it will not be confused with any other installation. To set up the data directory, then, use:

	initdb -D /local/pgsql74/data 

In Issue 12 a convention for a user environment set up was described. It consisted of an environment file called pgenv which is sourced in the client environment to set the default PG environment variables. PG_INST is not a regular PG variable; it was created for convenience.

	PG_INST=/local/pgsql74
 	PGDATA=$PG_INST/data
 	PGPORT=5433
 	PGLIB=$PG_INST/lib
 	PATH=$PG_INST/bin:$PATH
 	PGHOST=cookie
 	PGDATABASE=production
 	export PG_INST PGDATA PGHOST PGPORT PATH PGLIB PGDATABASE

As described previously these variables can be set in a user's .profile or in /etc/profile to set the default installation on a system. Alternatively, two versions of the file can be made available, perhaps called pgenv73 and pgenv74 so that when switching from one to the other is only a matter of sourcing the correct file.

Note that this pgenv file changes your PATH. This is important, particularly if one of your installations is in /usr/local/bin. You must be sure that the executables for the installation of choice come before others on your path.

Contributors: elein at varlena.com Madhavi Daroor madhavi at zoniac.com, Arguile arguile at lucentstudios.com, scott.marlowe scott.marlowe at ihs.com