Click to see

Home Tutorials SQL Links Enterprise DB PostgreSQL
Keep an Open Eye ThePhotoFinishes Takethe5th PixofCanada Bookraft
©Imagenation 2001-2009 - If you found this page or site useful, please let others know


Sidebar 1
Open Innovation

One of database's pioneer's, Michael Stonebraker, is the inspiration behind PostgreSql. Lots of nifty development originated with the University of California group under Stonebraker's lead first as Ingres and the mid 1980's as Postgres.

Postgres added a several innovations including rule based triggers and stored procedures, multiple storage engines and improved query ops. In 1994 2 grad students added SQL and the name of the database became PostgreSQL.

During the late 1990s and early 2000s 2-3 commercial DB start-ups added new store procedure routine and other DB enhancements only to run afoul of the economic downturn. All of those ran afoul of the DOT.com bust. So PostgreSQL had many top notch DB features ahead of MySQL but lacked major presence in Open Source and a top notch set of GUI tools to support use of PostgreSQL. In the past 5 years that has changed as we show here.

Major Improvements to PostgreSQL

PostgreSQL has always been one of the pioneering relational databases. Professor Michael Stonebraker and his cohorts working out of the University of California managed to produce some very clever innovations in PostgreSQL - see the sidebar for the details. PostgreSQL has had the well-deserved reputation of being an enterprise caliber database able to take on large scale database applications while providing top notch security and reliability. But PostgreSQL's developers made two critical mistakes.

First, PostgreSQL delayed its Open Source database strategy that would allow it to protect itself and mature in a market already dominated by big vendors like IBM, Oracle, Microsoft, and Sybase. In contrast, MySQL got into the market by being a free database and part of the LAMP free Open Source movement; yet for many years lagged behind the PostgreSQl feature set. Second, PostgreSQL was late to adopt SQL instead of its own proprietary Quel query language. Finally, PostgreSQL was also late to offer a full set of GUI based development and administrative tools; so development shops, happy with the MySQL and other commercial databases development tools, passed up on PostgreSQL .

Well that has changed in the past 4-6 years. There are a number of Open Source PostreSQL providers lead by Enterprise DB which have helped to develop not only enterprise performance features for PostgreSQL but also have added GUI-based DBA and developer tools that now make PostgreSQL a very attractive option for database development - particularly on the Web. By making entry level Web database tasks very easy to develop, PostgreSQL gains users and credibility among developers that translates well to the enterprise arena that they perform well in.

PostgreSQL Strategy

PostgreSQL, taking advantage of its performance pedigree, is targeting organizations that are looking for cost effective enterprise capabilities. PostgreSQL underlines this by having versions available for BSD, Linux, Mac, Solaris, most versions of Windows and a whole line up of Unix servers. Currently PostgreSQL vendors are targeting the relatively high cost of running Oracle databases by making itself the plug-in substitute for Oracle. But it is also courting Web developers with a number of improvements including:
1)PHP support including updated access methods;
2)Direct support of popular free CMS systems like Drupal and Mediawiki;
3)Much improved GUI based DBA tools;
4)Improved documentation and helps systems;
5)Good tools for backup, synchronization and server configuration.
In effect, wherever users currently employ Firebird, MySQL or SQLite they can consider using PostgreSQL. And given the improvements in the support tools that is more feasible than ever before.

Support Tool Features

Like Oracle and MySQL, PostgreSQL has a very fast and capable command line support tool. In Windows, PostgreSQL has tied it into the Notepad editor - and as you can see from the screenshot it is easy to not only do SQL queries and updates; but also general back-up and other database administrative tasks.

But a key addition to PostgreSQL tools has been the GUI based PGAdmin III program.

This program has three major information panels [see the screenshot above]- the Object Browser stretching down the left side with an expanding tree view of all the objects associated with a PostgreSQL installation. To the upper right is the Info Panel that has four tabs including Properties, Statistics, Dependencies, Dependents. And below the Info panel is the SQL Pane. The three panels work in conjunction. Point to an object in the browser - say a Drupal table - and immediately in the Info panel each of the four tabs gets updated. Likewise the SQL Pane is updated with the SQL used to create the object - a Create Table SQL command as in the screenshot.

For DBA administrators and developers this is extremely helpful - I know I can get all the details on the structure and history of the database in one place and very quickly. But I also know I have available a command file and SQL editor to use that info in making any necessary changes. What's even better is that PGAdmin III provides a powerful and easy to use SQL Editor for Query, Update and Insert commands that makes developing code or maintaining a database that much easier.

The following is a screenshot of how to invoke the SQL Editor:
Because PostgreSQL follows the RDBMS guidelines, almost all aspects of database configuration are controlled by SQL commands - truly a declarative language. So getting at and using a SQL editor is a vital part of working with PostgreSQL. Many starting SQL scripts are auto-generated by PGAdmin III depending on what database object you are using or pointing to in the OBject Browser when you invoke the SQL Editor. This is a great time saver.

But even better, the SQL Editor has a Graphical Query Builder as shown below:

This is a drag, drop and click tool similar to the ones developed in dBase, Paradox and Access to easy creation of SQL commands. I find that the Query Builder gets me close and then I switch to the SQL Text Editor to test and polish off the SQL command. In sum, PGAdmin III has really beefed up the administration and development of PostgreSQL databases. This then makes support of Web 2.0 applications much easier. Another factor is the much improved PHP support in PostgreSQL.

PostgreSQL Support in PHP

PostgreSQL has actually had good support in PHP for quite awhile. For example, PHP supports four direct access methods for PostgreSQL:
1)the PostgreSQL specific direct database command set - pg_connect() etc;
2)the more general MODx commands;
3)the ADOdb database framework which mimics Microsoft's ADO database connections;
4)the PHP PDO commands which connect and control many databases.
The following is a sample of PDO coding for PostgreSQL:

The code above which uses PDO commands, produces the report seen in the screenshot below. However, by changing the $dbtable = PDO(...) command with the correct parameters for connecting to say MySQL or SQLite, all the rest of the code stays unchanged. And in fact that is what was done to get here from a MySQL code snippet.


In addition to several access methods, PostgreSQL also has a browser based development tool which is a chip off phpAdmin, available to MySQL developers. It is named phpPGAdmin and provide developers with a Web based DBA tool:

As a regular user of MySQL's phpAdmin, I was surprised to find how polished and complete phpPGAdmin is in delivering DBA caliber database administration and development support online. With database support like this in PHP a lot of developers are using PostgreSQL in a variety of newly supported Open Source CMS systems like Drupal, Mediawiki, PHPBB, PHPwiki among others. The screenshot below shows Drupal in action:

This means users have available an enterprise capable database for their CMS applications. And there are a variety of other PostgreSQL tools for use.

Other PostgreSQL Tools and Features

PostgreSQL is perversely lucky - it has profited from so many commercialization attempts that went awry from the original Ingres through Illustra to Pervasive. All of these ventures released to Open Source key technologies that furthered PostreSQL capabilities. In the next tutorial, PostgreSQL Query Tools, there is a description not just of the powerful native query capabilities that come with the free download of PostgreSQL,; but also some of the DBA features available [and sources of 3rd party DBA tools]. But the bottom line is that PostgreSQL has top notch Query and DBA Tools.

Not only are PostgreSQL's tools equal to many of the other commercial databases but its basic database features match well with DB2, Oracle and SQL Server. The following table shows some of the key features of the PostgreSQL:

PostgreSQL Features
Feature Description
Philosophy Supports most of the major features of SQL:2008 standard
Supported OS BSD, Linux, Mac, Windows, Solaris + many Unix flavors
Object Rules Allows defining new datatypes, domains, functions, etc. Also tables and views can inherit from parent tables.
Datatypes Special datatypes including geographics and reg. expressions
Triggers Complete statement, row, and statement support
Stored Procedures Choice of PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, PL/Lua,PL/Java, PL/PHP, PL/Ruby, PL/sh and C
PL/pgSQL Oracle PL/SQL clone database programming language
Indexes 4 basic types plus partial, expression and user defined
MVCC Multi-version Concurrency Control adopted by SQL Server 2005
Views Updateable Views
Referential integrity Many types of constraints including foreign key, column , and row-check constraints
SELECT Joins Inner, outer (full, left and right), and cross joins
Large DB objects Binary and textual large-object storage including TOAST
Transactions Transactions with savepoints 2-phase commits with point recovery
Storage management Domains and tablespaces with rules control

These are top-tier database features and as indicated at the outset of the table- PostgreSQL has implemented most of the SQL 2008 standard unlike the other major database vendors. This gives PostGreSQL a leg up on all of the other databases. And then one has to consider the PostgreSQL add-ons:
GIST - Geographical database extensions
pgRouting - for complex shortest path routing
Tsearch and OpenFTS - for extended full text search
Slony 1 and Mammoth -for asynchronous master-slave replication
PGPool and Sequoia - for balancing, failover and other load management tasks
Because of its object base, extendability and Open Source roots, PostgreSQL has profited from many university lead research projects and extensions. So the feature set is more than competitive in the marketplace. And this is reflected in its usage. Yahoo has a multi-petabyte database for patterns analysis.Sony Online uses PostgreSQL for its roster of Web-based games while SKYPE uses PostgreSQL for its VOIP and backend database. And benchmarks show that has one of the lowest cost per measured benchmark test results.

Summary

PostgreSQL has an enviable position in the Open Source database market. It delivers an Enterprise level performance and feature set. Now it has added a complete set of Query and Database Admin tools plus database-power to popular Source Applications like Drupal, PHPbb, Mediawiki, and others. As a result PostgreSQL plays well in the PHP and AJAX markets. So when you have a tough and/or large scale database problem - yet want to avoid the premium database prices - definitely consider PostgreSQL.