« Zak Greant: Copyright, Contracts and Licenses (and more) | Main | PHP Debugging with XDebug »

March 30, 2006

Introduction to PostgreSQL

First afternoon session for PHP Quebec is An Introduction to PostgreSQL by Robert Bernier.

"PostgreSQL isn't hard, it's just loaded with details." Robert says the goal of the presentation is to make the audience curious enough to go play around with PostgreSQL. PostgreSQL documentation is superb.

History

Relational databases started with Ted Codd breaking data into concepts using language for breakpoints. IBM, Oracle (or what was to become Oracle) and Cal Berkeley took the idea and started development. Postgres was an idea developed at Berkeley by lots of students. This was in the late 60s & 70s. Informix is based on Postgresql, bought by IBM now. Oracle and SQL Server has PostgreSQL code in it too. Pretty much every database except MySQL has some PostgreSQL in it.

Open sourced in 1996, Berkeley terminated the project. Core team of 4 took the code and for 2 years cleaned it up.

In 1996 Robert started working with PostgreSQL and MySQL. MySQL worked, PostgreSQL had better documentation.

MySQL is more popular, why isn't PostgreSQL? Two reasons:

Who uses Postgres? Every router by Cisco has a postgres database in it. Big companies are using Postgres, Wall Street included. Weather service adopted it. Robert gives numerous examples of folks using Postgres. Some pretty big uses.

Technical Details

Robert uses Debian with KDE. In Japan PostgreSQL is the number two database, Oracle is number one. MySQL is not used because of language issues.

After installing use the initdb to create a data cluster. A data directory is created with a set of folders and configuration files. New cluster is 30MB (Oracle is 1G empty database).

A recent project Robert was working on where a financial company was moving to PostgreSQL. They were getting 400 transactions/second with Oracle. On an untuned PostgreSQL were getting 740 transactions/second.

PostgreSQL won't run as root and automatically disables connections from outside the server it's installed on. Some permissions are stored in the a configuration file. The default network access is not secure, passwords can be encrypted using another encryption option.

Robert gives an explanation of domain sockets and how they work. PostgreSQL uses sockets on local machines (like MySQL).

In PostgreSQL you can create template databases to be used when creating a new database. That's a pretty cool feature. I guess in MySQL it's a dump piped to another database.

Robert recommends going to the Client Applications and Server Applications in the documentation to start.

Example

Robert shows an example of a PDF upload tool. His table has a field that is cdir datatype, that is cool.

The last slide is a picture of a dolphin being roasted on a skewer over a fire. Yikes . . . sorry Sakila.

Posted by mike at March 30, 2006 2:03 PM