Benjamin Krajmalnik on PostgreSQL

Published 2006-06-08    Printer-friendly version

Benjamin Krajmalnik has kindly given me permission to repost his newsgroup message on PostgreSQL. I've had a long-standing interest in PostgreSQL, and if I had ClarionMag to do over, I'd look hard at this database over MySQL.

One thing to keep in mind - Benjamin's post is from the perspective of using PostgreSQL with mainly non-Clarion tools.

I will chime in with my comments based on my experience, since I use all three. Right now, I am really liking PostgreSQL over all of the others. Feature-wise, it is superior to all the others (at least in my opinion). Their richness in data structures is amazing. One nice feature (and, please guys, do not yell at me!), is the support for arrays. While in most applications I would not use these, there are some where it comes in quite handy.

For example, in our network monitoring back end, I have array structures which hold data in buckets. These buckets are dynamically aggregated. To create the structures which I am using in the other SQL flavors would have been, well, painful. Using PostgreSQL made it a breeze.

Performance-wise, I am not having problems (and I insert over a million rows per day). It is very efficient in its data storage. It supports table partitionning, which is another really cool feature.

PostgreSQL is an object relational database. This means you can derive objects inside the datanbase. So, in my case, I created a table structure to hold raw numeric data. I then derived 12 tables from it (one for each month). All access to the "collection" of tables is done via the parent table. A trigger routes the record insertion to the correct partition. If I want to query data, I query the parent table, and it cascades into the child tables. When the data retention is over, I simply truncate the partition. This is instantaneous - no need to manage huge indices, since the indices are actually declared on the child tables. Imagine deleting 30-50 million rows per month in the other SQL flavors. I had to do it with SQL srerver, and had to write a stored procedure which broke the deletion set up into manageable sets so as to not overflow the transaction log.

Another nice thing is that it is very good with resources. Forget the MS SQL Server paradign where it gbbles up all of your memory.

Their licensing is the best - a BSD license - which means you can do anything you want with it (unlike GPL). Support in the mailing lists is amazing, and their are various vendors providing commercial support (Command Prompt, EnterpriseDB, Pervasive, GreenPlum).

Cons - a little bit more difficult to back up the database over, say, SQL Server. There are various options available. pg_dump, which creates a consistent backup of the database, but requires performing some additional tasks with sequences after a restore, since the sequences may not reflect the maximum valued in the database.

MySQL - well, I have played with it mostly i the 3.x series and some on the 4.x series. Getting better, but my biggest concern lies with their transactional database engine, InnoDB, which now belongs to Oracle. So, in short, Oracle has MySQL by the you know what. While I found it to be great for simple websites, etc., I was still a bit wary to use it in an Enterprise environment.

SQL Server - not a bad product overall, but very expensive licensing wise (unless you use MSDE or SQL Express, in which case you have database size limitations). My biggest beef is the fact that it eats up resources like a bat out of hell. All 3 are good options.

My commercial applicatios right now run under SQL Server (since they are 3rd party add-ons to a package runing on SQL Server). My new projects are PostgreSQL based. I have not done too much with Clarion (the current project is PHP absed), but the next step in this project will also require a Windows client for some of the added features for which a Web UI is not the best.

Printer-friendly version

Copyright © 1999-2009 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942