|Back to Papers and Articles|
PostgreSQL: An Open-Source Object Relational Database Management System (ORDBMS)
Copyright 2002 Paragon Corporation ( June 04, 2002)
What is PostGreSQL?PostGreSQL is an open-source object relational DBMS (ORDBMS) that traces its roots back to Academia. It traces its roots back to a database called Postgres (developed at UC Berkley in the early 80's). It was officially known as PostGreSQL around 1996 mostly to reflect the added on ANSI SQL compliant translator. It is perhaps the most feature-rich robust open-source database around and perhaps the most feature rich even among non-open source databases. In this article, we'll focus on the features that make Postgres a rare gem. Our focus is on the most recent versions of PostgreSQL (7.1.3 and 7.2).
What makes PostGreSQL Different from other Relational Databases
PostGreSQL in addition to being a relational database, is object-relational as well. What this means is that it has some object-oriented features such as concept of inheritance and ability to define complex datatypes with special functions to deal with these datatypes, but is for the most part relational in nature. In fact, most uses of Postgres do not take advantage of it's extensive object-oriented functionality. It has features that you may not find in even expensive well-known commercial relational database management systems (RDBMS) and ORDBMS systems. Below are a couple of neat features that make it stand out from the pack.
ANSI-SQL 92/99 CompliancePostGreSQL supports the standard ANSI SQL operators and predicates such as SELECT, DISTINCT, SELECT INTO, INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, FULL, HAVING, GROUP BY, common aggregrate functions such as SUM, AVG, MAX, MIN, and subselects. In addition to those - it supports some non-common ones such as INTERSECT, EXCEPT, and DISTINCT ON. For more information on how to use these and other SQL SELECT predicates - check out the postgres manual sql-select page.
Below is an example of how one would use INTERSECT. Note that these predicates can be mimicked in systems that do not support them using other join constructs, but these provide a cleaner and more logical way of expressing these.
Support for Numerous languagesAs of this writing there are 4 (Procedural Languages (PLs) that come with the Postgres source (PL/pgsql, PL/Python, PL/Tcl, and PL/Perl - others are in the works such as a PL/Java that will be similar to Oracle's PL Java). (PL languages allow one to write Postgres functions and stored procedures natively in the language as opposed to simply binding to a library of already compiled functions.. The Postgres architecture allows to define new PL languages by providng PL handlers. This feature makes Postgres very extensible - for example one can imagine creating a specialized rules language and then providing this as a PL extension to Postgres. This is extremely rare among databases in which introducing a new language option requires reachitecting the system.
Pretty much any language you can name, you can use to define functions that can then be bound to a postgres database to be used in SQL queries or to create new specialty operators (e.g. +, = etc.) that work on particular kinds of data. Note function binding is a little different from creating functions with a PL'd language. When binding, you have a compiled set of functions, classes etc. that you then map to skeleton Postgres stored procedure/function names. The skeleton merely defines the function signature (inputs , outputs and name of the function in a compiled library) as opposed to a PL'd stored procedure in which the stored procedure or function code is actually stored in the database, but interpretated by a PL language handler.
Inheritance of Table StructuresTable inheritance is a feature that is not found in a mere RDBMS, but is one of the hallmarks of an ORDBMS. This feature provides a compromise for those looking for an object orientated database, but who wish to also have the simplicity and speed that a relational database system provides.
Here is an example of such a rare commodity.
Suppose you are put to the task of developing a database that inventories various types of computer equipment. You want to be able to keep track of various kinds of attributes of an item, but don't want the design to get unwieldy and you also want to make it easy report on. You have several kinds of equipment - computers, printers, routers, switches etc. There are several properties that each shares in common, but also properties that are specific to say only a computer.
There are several ways of doing this - here are a couple of options that come to mind
In fact Postgres allows you to inherit from more than one table or inherit from another inhertied table thus achieving single inheritance as well as multiple inheritance
Built-in complex data types and ability to define new onesThe above example demonstrates a complex data type called inet which can be used for storing ip addresses, subnet masks etc. Although PostgreSQL comes with quite a few complex datatypes, one can define new ones or get extensions to PostgreSQL that extend these.
One such product we've found is a product called PostGIS which is an opensource spatial engine that spatially enables PostgreSQL. Although PostgreSQL comes with some simple geometry data types, PostGIS defines new ones and provides an OpenGIS SFSQL interface to these.
Makes a Great Web DatabasePostgreSQL is a fairly fast database with ample support in web languages such as PHP, Perl and the ODBC and JDBC drivers make it easily usuable in other languages such as ASP, ASP.Net , ColdFusion and Java. It is often compared with MySQL - one of the fastest databases on the web (opensource or non). It's querying speed is in line with MySQL and in fact studies have shown that it scales better with more users than MySQL. In terms of features, it is leagues above MySQL - but the new version of MySQL coming out which will provide transactional support and trigger support, and subselects will change some of that . MySQL is in general an easier install for those who don't need all the extended functionality provided by PostGreSQL. In terms of features though - if you want functionality as extensive as Oracle or SQL Server and don't want to spend an arm and a leg, then PostGreSQL is definitely a database to take a second look at.
Links of Interest
Back to Papers and Articles