Optimizing Postgresql

Ericson Smith

Following Tim
Perdue’s excellent article on the comparison between MySQL
and Postgresql, I decided to take a shot at installing and using this
database. For most of our work I use MySQL and will continue to do so,
because of its ease of use and unrivaled select query speed, and also
because there is no point in trying to mess around with production
systems that already work fine.

But some new projects suffered greatly from MySQL’s table locking
feature when I needed to update data (which I do a lot). Here are my
adventures in setting up a Postgresql database server.

Our configuration for a dedicated Postgresql server was:

• Redhat 7.1
• Dual PIII 650Mhz System
• 512MB RAM
• 18Gig SCSI drive for the postgresql data partition

Downloading and Installing

I downloaded and installed the 7.1.2 RPM’s from http://postgres.org
without any trouble. For a server installation, I only installed:
postgresql-server and postgresql-7.1.2 (base).

I then started the server up and running by executing:

/etc/init.d/postgresql start

A small sized database was ported from MySQL (three tables totaling about
5000 records). I created sufficient indexes for postgresql’s optimizer
to use, and modified our C application to use the postgresql C client
interface for a small CGI program that would brutally query this table.
This small CGI program receives thousands of queries per minute.