Section 14.5. Oracle, PostgreSQL, MySQL

14.5. Oracle, PostgreSQL, MySQL

One of the first major decisions you must make is what to use as a database server. On Linux systems, you have both Free Software and commercial software options. As we have said before, we will tend to focus on Free Software in this text, but we cannot ignore the most popular database software package out there, which is, of course, Oracle.

Let's take a quick look at each of the "big 3" database choices on Linux platforms.

14.5.1. MySQL

MySQL is a very fast but somewhat limited SQL database system. It is wildly popular mainly because it is simple, fast, and Free. It is everything that you would need to back a dynamic Web site. As of this writing, the stable production version of MySQL is 4.0.16, but most Linux distributions are still shipping something from the 3.23.x series.

At this point, MySQL lacks some key features:

• Stored procedures

• Sub-SELECTs

• Triggers

Version 4.0.x does support some long-awaited features (if you use the InnoDB table type instead of the default MyISAM tables), such as row-level locking, foreign keys, and transactions. But InnoDB tables are not directly available in the 3.23.x versions still shipping with many distributions.

MySQL is an excellent choice for designs that do not require stored procedures, triggers, or transactions. It is widely used as a back end for dynamic Web sitesapplications with many reading users and few writing users.

For more information on MySQL, see Core MySQL by Leon Atkinson (ISBN 0-13-066190-2).

14.5.2. PostgreSQL

PostgreSQL is a surprisingly complete and full-featured database offering.

Not only does it fully support stored procedures, triggers, views, foreign keys, and transactions, but it also implements an innovative "record versioning" system for multiuser integrity. Unlike many other databases, readers may continue to read consistent data during writing activity (nonblocking revisions), and backups may be taken while the database is still available for queries. This is a serious database.

This database has excellent public documentation. Take a look at the PostgreSQL Documentation.^[1] Another useful book is PostgreSQL by Korry and Susan Douglas (ISBN 0-7357-1257-3).

^[1] http://www.postgresql.org/docs/current/static/index.html

14.5.3. Oracle

What can we say? In the world of databases, this is the top dog. This is the standard against which other database systems are measured. Oracle is the DB that runs the "big applications" out there in the enterprise world. It is also a costly piece of software when it is used for production applications. It is, however, available for free download to use for development. It is emphatically not Free Software. You don't have the source code. You don't have the right to modify it, and you don't have the right to pass it on to third parties. That may or may not be an issue for you.

If you need advanced features like high-availability clustering, it is certain that Oracle will work for you. You may download it for evaluation and development from Oracle's OTN (Oracle Technology Network)^[2] Web site.

^[2] http://otn.oracle.com/software/products/oracle9i/htdocs/othersoft.html

14.5.4. Selection Criteria

For the simple application we are developing, all of these databases are sufficient. But in the real world, there could be many factors that might come to bear upon your selection of a database server product. These factors might include:

• Price

• License terms

• Transaction capacity (speed)

• Integration (does it work with or depend on other software you use?)

• Human resources (do you have operators and developers familiar with the product on your staff, or are they available for hire?)

• Presence (does your organization already use this product?)

• Features (do you have future plans that might require a particular product?)