Database Management Systems | 97PostgreSQL
I list PostgreSQL*first because it is my platform of choice. It is one of the most
advanced open source databases available today. It has a long history, dating back to
the University of California at Berkeley’s Ingres project from the early 1980s. In con-
trast to MySQL, Postgres has supported advanced features such as triggers, stored
procedures, custom data types, and transactions for much longer.
PostgreSQL’s support for concurrency is more mature than MySQL’s. Postgres sup-
ports multiversion concurrency control (MVCC), which is even more advanced than
row-level locking. MVCC can isolate transactions, using timestamps to give each
concurrent transaction its own snapshot of the data set. Under theSerializableisola-
tion level, this prevents such problems as dirty reads, nonrepeatable reads, and phan-
tom reads.†See the upcoming sidebar, “Multiversion Concurrency Control,” for
more information about MVCC.
One advantage that PostgreSQL may have in the enterprise is its similarity to com-
mercial enterprise databases such as Oracle, MS SQL Server, or DB2. Although Post-
gres is not by any means a clone or emulation of any commercial database, it will
nevertheless be familiar to programmers and DBAs who have experience with one of
the commercial databases. It will also likely be easier to migrate an application from
Postgres to (say) Oracle than from MySQL to Oracle.
PostgreSQL has an unfortunate reputation for being slow. It got this reputation
because the default configuration is optimized for performance on a tiny machine.
Therefore, it will perform fairly consistently out of the box on a server with as little
as 64 MB of RAM or as much as 64 GB. Like any database, Postgres must be tuned
for any serious use. The official documentation athttp://www.postgresql.org/docs/has
lots of great information on performance tuning.
One disadvantage of using PostgreSQL is that it has a smaller community around it.
There are more developers, especially in the Rails world, working with MySQL.
There are more tested solutions built around MySQL than PostgreSQL. The com-
pany behind MySQL, MySQL AB, provides commercial support for its product.
There is no such centralized support structure for Postgres, as there is no single com-
pany behind PostgreSQL; however, there are several companies that specialize in
Postgres consulting and provide support contracts.
- Technically pronounced “post-gres-Q-L,” and usually just called “Postgres.” This is a contender for the least
intuitive name in computing today. It has its roots in PostgreSQL’s long-ago predecessor, Postgres, which
did not support SQL.
† For a detailed look at how Postgres handles concurrency, including a summary of the potential problems and
how Postgres handles them, see the documentation athttp://www.postgresql.org/docs/8.2/interactive/
transaction-iso.html.