Note: this page is for tuning PostgreSQL on Linux systems. For the default PostgreSQL included in Desknow for Windows , see /kb/idx/0/145/article/

PostgreSQL can be a very fast database, but its default settings are very conservative. Very simple adjustments can improve its performance dramatically.

General considerations

In almost every interactive web application, the bottleneck is the database, not the application. This is the case of DeskNow as well. As with every database, the more RAM you can give to the database, the better.

Very important are also SCSI or SATA NCQ drives, as they free CPU for other tasks while data is being loaded.

Finally, partitioning the database over several disks can improve considerably performance. Even the ability of writing transaction log files to a separate disk improves performance a lot (this last is a very advanced performance improvement, which will not be discussed here).

Simple configuration changes.

Most of the config changes are done in the postgresql.conf file (typically in /var/lib/pgsql/data on Unix systems).

Important: before starting, backup the file postgresql.conf

shared_buffers this is the most important parameter. Multiply this value by 8192 to understand how much memory postgres will take.
Ex 20000=160Mb ram
Keep in mind that DeskNow uses 260 Mb at max.
So 20000 is good for a server with 512Mb RAM.
If you have more memory, increase this number. Make sure to not use too much, otherwise the system will start swapping to disk.

After changing this parameter, probably postgres will not start.
do this:
su - postgres
pg_ctl -D /var/lib/pgsql/data start

If the message says that you need to increase the kernel's SHMMAX value to a certain number, do this command (you must be root):
echo <shared_buffers * 8192> > /proc/sys/kernel/shmmax (this is for Linux)
<shared_buffers * 8192> is also the number reported by pg_ctl in the error message.

After setting the proper number, make sure it is used at the next reboot, by adding/setting the following in /etc/sysctl.conf
kernel.shmmax = <shared_buffers * 8192>

Example: for shared_buffers=20000 it is 167763968 (160 Mb), so you would type:
echo 167763968 > /proc/sys/kernel/shmmax (this is for Linux)

and then add this line to /etc/sysctl.conf
kernel.shmmax = 167763968

Tip: on some Linux flavours /etc/sysctl.conf does not exist. You can create this file. Also you may need to type this command to make it effective: 

sysctl -p

You may also add the same command to your boot sequence. In Suse Linux, for example, you may add it to  
/etc/init.d/boot.local

sort_mem a good value for this parameter is 4096

max_fsm_pages and max_fsm_relations

In the PostgreSQL console, type the command:

vacuum verbose;

and look at the bottom:

INFO:  free space map: 199 relations, 17028 pages stored; 33200 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.

Increase max_fsm_pages and max_fsm_relations to be more than the values needed as specified in INFO.

For instance, in this example INFO reported 33200 pages needed. The PostgreSQL default is 20000, so it's good to set in max_fsm_pages = 40000 in postgresql.conf
Not too much, otherwise it slows down.

Repeat this test regularly.

Vacuum regularly

This section can be skipped if you are using the autovacuum feature available in PostgreSQL 8.1

It is very important to give a 'vacuum analyze;' command periodically to the database. This will clean up deleted rows, and improve query statistics.

Tip: it is possible to vacuum the database without entring the PostgreSQL console. Type 'vacuumdb --help' in console for more information.