PHPBuilder - Optimizing Postgresql

RSS Twitter

Optimizing Postgresql

by: PHP Builder Staff
August 21, 2001

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 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.

Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.




(Maximum characters: 1200). You have characters left.