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.
PostgreSQL is capable of handling complex objects and rules. Examples of the functionality that PostgreSQL supports are declarative queries in SQL, concurrency control, transactions, query optimization, and multi-user support.
PostgreSQL supports user-defined operators, functions, access methods and data types.
PostgreSQL supports the core SQL99 specification, and includes advanced features such as SQL99 joins, inheritance, and arrays.
PostgreSQL supports referential integrity which is used to insure the validity of a databases' data.
PostgreSQL includes an extensive API. The flexibility of the PostgreSQL API has allowed for vendors to easily provide development support for the PostgreSQL database. PostgreSQL arguably has the largest amount of interface capabilities of any database. These interfaces include Object Pascal, Python, Perl, PHP, ODBC, Java/JDBC, Ruby, TCL, C/C++, and Pike.
PostgreSQL has support for internal procedural languages, including a native language called PL/pgSQL . The language is comparable to the Oracle procedural language PL/SQL. Another real advantage to PostgreSQL is the ability for it to use Perl, Python or TCL as an embedded procedural language.
MVCC, or Multi-Version Concurrency Control, is the technology that PostgreSQL uses to manage database locking . If you have ever used another SQL capable DBMS, such as MySQL or Access, you will notice that there are times that a reader will have 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 being 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.
The PostgreSQL client-server model involves three main processes:
Postmaster, which is the supervisory daemon process. It allocates the shared buffer pool to other processes, manages communication between the frontend and backend processes, and initializes other processes during start up.
The PostgreSQL backend process, which executes queries and runs on the server machine. For each frontend application that is started, a backend process is also initialized.
The frontend application is commonly the command-line psql client, but could also be PHP, PgAccess, etc. It uses the postmaster to request a connection to the postgres backend.
The PostgreSQL feature known as Write Ahead Logging increases the reliability of the database by causing changes to be logged before they are written to the database. This insures that, in the unlikely occurrence of a database crash, there will be a record of transactions to restore from. 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.