As stated previously in this chapter, PostgreSQL is widely considered the most advanced open source database in the world. PostgreSQL provides a wealth of features that are usually only found in commercial databases such as DB2 or Oracle. The following is a brief listing of some of these core features, as of PostgreSQL 8.x.
PostgreSQL approaches data with an object-relational model, and is capable of handling complex routines and rules. Examples of its advanced functionality are declarative SQL queries, multi-version concurrency control, multi-user support, transactions, query optimization, inheritance, and arrays.
PostgreSQL supports user-defined operators, functions, access methods, and data types.
PostgreSQL supports the core SQL99 specification and includes advanced features such as SQL92 joins.
PostgreSQL supports referential integrity, which is used to insure the validity of a database's data.
The flexibility of the PostgreSQL API has allowed vendors to provide development support easily for the PostgreSQL RDBMS. These interfaces include Object Pascal, Python, Perl, PHP, ODBC, Java/JDBC, Ruby, TCL, C/C++, and Pike.
PostgreSQL has support for many internal procedural languages, including a native language called PL/pgSQL. This language is comparable to the Oracle procedural language, PL/SQL. PostgreSQL can also use use Perl, Python, PHP, Ruby, or Java as an embedded procedural language.
MVCC, or Multi-Version Concurrency Control, is the technology that PostgreSQL uses to avoid unnecessary locking. If you have ever used another SQL capable DBMS, such as MySQL or Access, you have probably noticed that there are times when a reader has to wait for access to information in the database. The waiting is caused by people who are writing to the database. In short, the reader is blocked by writers who are updating records.
By using MVCC, PostgreSQL avoids this problem entirely. MVCC is considered better than row-level locking because a reader is never blocked by a writer. Instead, PostgreSQL keeps track of all transactions performed by the database users. PostgreSQL is then able to manage the records without causing people to wait for records to become available.
PostgreSQL uses a process-per-user client/server architecture. This is similar to the Apache 1.3.x method of handling processes. There is a master process that forks to provide additional connections for each client attempting to connect to PostgreSQL.
PostgreSQL version 8.x now supports the ability to restore a database from its Write Ahead Log. Using this method of recovery one can take a standard file system backup and then use the transaction logs to restore to a specfici period of time.
PostgreSQL version 8.x has the ability to configure which volume a particular table will reside on. This allows a user of PostgreSQL to move large or frequently accessed tables to faster hard drives systems for better performance and efficiency.
PostgreSQL like other Enterprise databases has the ability to replicate itself from one database machine to another. The most popular replication engines for PostgreSQL are Slony-I and Mammoth Replicator.
The PostgreSQL feature known as Write Ahead Logging increases the reliability of the database by logging changes before they are written to the database. This ensures that, in the unlikely occurrence of a database crash, there will be a record of transactions from which to restore. This can be greatly beneficial in the event of a crash, as any changes that were not written to the database can be recovered by using the data that was previously logged. Once the system is restored, a user can then continue to work from the point that they were at before the crash occurred.