PostgreSQL

PostgreSQL is a free object-relational database server, released under a BSD-style license. It is an alternative to other free-software database systems (such as MySQL, Firebird, and SapDB[?]), as well as commercial systems like Oracle and IBM's DB2.

The official pronunciation of "PostgreSQL" is "Post-Gress-Q-L".

History

PostgreSQL is the ultimate result of a long evolution starting with the Ingres project at UC Berkeley. The project lead, Michael Stonebraker had left Berkeley to commercialize Ingres in 1982, but eventually returned to academia. After returning to Berkeley in 1985, Stonebraker started a post-Ingres project to address the problems with the relational database model that had become increasing clear during the early 1980s. Primary among these was the relational model's inability to understand "types", combination of simpler data that make up a single unit. Today we typically refer to these as objects.

The resulting project, named Postgres, aimed at introducing the minimum number of features need to add complete types support. These included the ability to define types, but also the ability to fully describe relationships – which up until this time had been widely used but maintained entirely by the user. In Postgres the database "understood" relationships, and could retrieve information in related tables in a natural way using rules.

Starting in 1986 the team released a number of papers describing the basis of the system, and by 1988 had a prototype version up and running. Version 1 was released to a small number of users in June 1989, followed by Version 2 with a re-written rules system in June 1990. 1991's Version 3 re-wrote the rules system again, but also added support for multiple storage managers, and an improved query engine. By 1993 there were a huge number of users and the project was being overwhelmed with requests for support and features. After releasing a Version 4 primarily as a cleanup, the project ended.

Although the Postgres project had officially ended, in 1994, Andrew Yu and Jolly Chen added a SQL language interpreter to replace the earlier QUEL system Ingres had been based on, creating Postgres95. The code was subsequently released to the web to find its own way in the world. Postgres95 was a public-domain, open source descendant of this original Berkeley code. In 1996 it was decided to change the name to reflect the use of SQL, becoming PostgreSQL, and the version numbering was pushed forward to Version 6, thereby making Postgres95 become Version 5.

The license under which Postgres was developed enabled Open Source developers to obtain a copy and develop it further. In 1994, Andrew Yu and Jolly Chen removed the PostQuel query language originally used by Postgres, replacing it with SQL. The new version of the code was called Postgres95; it was later renamed to PostgreSQL. Since this time, the software has been maintained by a group of database developers from around the world, coordinated via the Internet.

Although the license allowed for the commercialization of Postgres, unlike Ingres the Postgres code was not developed commercially with the same rapidity as Ingres, which is somewhat surprising considering the advantages the product offered. The main offshoot was created when Michael Stonebraker and Paula Hawthorn, an original Ingres team member who moved from Ingres, formed Illustra Information Technologies[?] to commercialize Postgres.

Illustra's product was first introduced in 1991, where it was used in the Sequoia 2000 project late that year. By 1995 the product had added an ability to write plug-in modules they referred to as DataBlades. Unlike other plug-in technologies, with DataBlades external authors could write code to create new low-level datatypes, and tell the database how to store, index and manage it. For instance, one of the most popular DataBlades was used to create a time-series, a list of one particular variable over time, often with gaps. For instance, the price of a stock over time changes, but there are times, like weekends, where the data does not change and there is no entry. Traditional databases have difficultly handling this sort of task; while they can find a record for a particular date, finding the one that is "active" in one of the gaps is time consuming. With the Time Series DataBlade, this was fast and easy.

DataBlades were increadibly successful and started to generate considerable industry "buzz", eventually leading Informix to purchase the company outright in 1996. Industry insiders claimed that it would not be possible to merge the two products, but in fact this was fairly easy because both were based on the original Ingres code and concepts. Infomix released their Illustra-based Universal Server in 1997, leaving them in an unchallenged position in terms of technical merit.

Description

A cursory examination of Postgres will result in a description of a system that is very similar to other relational database systems: PostgreSQL uses the SQL language to run queries on data that is organized as a series of tables with foriegn keys linking related data together. The primary advantages of Postgres over these systems are best described as programmability: Postgres makes it much easier to build real-world applications using data taken from the database.

The relational model stores simple data types in "flat" tables, requiring the user to gather up related information using the SQL language. This contrasts with the way the data itself ends up being used, typically in a high-level language with rich data types where all of the related data is considered to be a complete unit of it's own, typically referred to as a record or object depending on the language.

Converting information from the SQL world into the programming world is difficult because the two simply have very different models of the way data is organized. This problem is widely known as impedance mismatch in the industry, and mapping from one model to the other typically takes up about 40% of a project's time. A number of mapping solutions, typically referred to as object-relational mapping, are on the market, but they tend to be expensive and have problems of their own, notably performance.

In Postgres many of these issues can be solved directly in the database. Postgres allows the user to define new types based out of the normal SQL types, allowing the database itself to understand complex data. For intance, you can define an address to consist of several strings for things like street number, city and country. From that point on one can easily create tables containing all the fields needed to hold an address with a single line.

Postgres also allows types to include inheritance, one of the major concepts in object-oriented programming. For instance, one could define a post_code type, and then create us_zip_code and canadian_postal_code based on them. Addresses could then be speciallized for us_address and canadian_address, including speciallized rules to validate the data in each case.

Another very useful feature is that Postgres directly understand relationships between tables. Real-world people typically have several addresses, which in the relational model is stored by placing the addresses in one table and the rest of the user information in another. The addresses are "related" to a particular user by storing some unique information, say the user's name, in the address table itself. In order to find all the addresses for "Bob Smith", the user writes a query that "joins" the data back together, by selecting a particular name from the users table and then searching for that name in the address table. Doing a search for all the users in New York is somewhat complex, requiring the database to find all the user names in the address table, then search the user table for those users. A typical search might look like this:

SELECT u.* FROM user u, address a WHERE a.city="New York" and a.user_name=u.user_name

In Postgres the relationship between users and addresses can be explicity defined. Once defined the address becomes a property of the user, so the search above can be simplified greatly to:

SELECT * FROM user WHERE address.city="New York"

No "join" is required, the database itself understands the user.address relationship. A related example shows the power of types, if one uses Postgres to do:

SELECT address FROM user

The results will be broken out automatically, returning only those addresses for users, not those for companies or other objects that might be using the address table.

Finally the programming of the database itself is greatly enhanced due to functions. Most SQL systems allow you to write a stored proceedure, a block of SQL code that can be called in other SQL statements. However SQL itself is a poor programming language, and complex logic is very difficult to create. Worse, many of the most basic operations in a programming language, like branching and looping, are not supported in SQL itself. Instead each vendor has written their own extensions to the SQL language to add these features, which are not cross platform.

In Postgres you can write the logic in most any language you choose, and the list of supported languages is growing with every release. The code is then inserted into the server as a function, a small wrapper that makes the code appear as if it were a stored proceedure. In this way SQL code can call (for instance) C code and vice-versa, dramatically increasing simplicity and performance.

These advantages add up to making Postgres easily the most advanced database system from a programming perspective, which is one reason for the success of Illustra. Using Postgres can dramatically reduce overall programming time on many projects, with the advantages growing with project complexity.

Features

Some features of PostgreSQL rarely found in other relational databases include:

• defining new basic types
• defining new operators

• defining new functions, using C, SQL, Perl, Python, Tcl, Ruby, sh, or PL/PgSQL
• support for geographic objects via PostGIS
• concurrency is managed via a Multi-Version Concurrency Control (MVCC) design, which ensures excellent performance even under heavy concurrent access
• inheritance
• rules (a way of implementing server-side logic that allows the application developer to modify the "query tree" of an incoming query)
• functional indexes, partial indexes

In addition, PostgreSQL supports almost all the constructs expected from an enterprise-level database, including:

• foreign keys
• triggers
• views
• outer-joins
• sub-selects
• transactions
• good compliance with the SQL standard (SQL92, SQL1999)
• connections via SSL
• large objects (both textual and binary)
• online backup

External links:

PostgreSQL (http://www.postgresql.org/)