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 7.1.x.
Object-Relational DBMS
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.
Highly extensible
PostgreSQL supports user-defined operators, functions, access methods, and data types.
Comprehensive SQL support
PostgreSQL supports the core SQL99 specification and includes advanced features
such as SQL92 joins.
Referential integrity
PostgreSQL supports referential integrity, which is used to insure the validity of
a database's data.
Flexible API
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.
Procedural languages
PostgreSQL has support for internal procedural languages, including a native
language called PL/pgSQL. This language is comparable to the Oracle
procedural language, PL/SQL. Another advantage to PostgreSQL is its ability to
use Perl, Python, or TCL as an embedded procedural language.
MVCC
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.
Client/server
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.
Write Ahead Logging (WAL)
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.
