University of North Texas: Computing and Information Technology Center

By Shannon Eric Peevey(speevey@unt.edu)
The PostgreSQL client/server database, (pronounced post-gres-cue-el), is the most advanced open-source database available today. It is a database system that has come from the shrouded depths of a University of California at Berkeley basement, and which has now moved to the forefront among a plethora of open-source and commercial relational database management systems. It was chosen as the "Best Database Management System of 1999" by LinuxWorld, and was also chosen as the "best Database of 2000" by the Linux Journal. It is used for mission critical applications by such noted companies as Volvo, Reuters, MCI WorldCom, and the National Center for Supercomputing Applications (http://imagelib.ncsa.uiuc.edu), and is the only open-source database system to support Atomicity, Consistency of Preservation, Isolation, and Durability, or ACID, which we will discuss these more in a minute. But for now, we are still plagued with this question... What is PostgreSQL...? Let me tell you.

PostgreSQL is a relational database management system that allows us to store and retrieve data according to various parameters given in what we call a query. A query is a statement written in the Structured Query Language, or SQL, which issues commands to which a database reacts and responds to. SQL queries can retrieve information from a database, (SELECT ), insert information into the database, (INSERT), or update information in the database, (UPDATE). (There are many more actions that SQL can be used for, but they are beyond the scope of this article for now.) When a query is executed, the PostgreSQL database receives these commands and then returns the values that we want, and can even manipulate that information into a form that we would prefer. (For example, we can sum the total of the returned values or even change the returned string values as all uppercase letters, or lowercase letters .) This is all because it has lined up all of our information in a way that follows the laws of relational algebra, using a system of rows and columns to index and store data. "What is PostgreSQL?", we have asked. PostgreSQL is a relational database management system that is on par with, and has even surpassed, many of its relational counterparts.

The first step in understanding how PostgreSQL surpasses many of it's competitors, is by understanding what it means to support Atomicity, Consistency of Preservation, Isolation, Durability, or ACID. Basically, these daunting words state that PostgreSQL supports "all or nothing" transactions. "All or nothing" transactions are transactions that either work all of the way through, or do not change anything at all. This is important in highly complex database transactions such as those that take place at the ATM machine every day. Suppose you withdraw money from an ATM and the transaction fails, but the ATM issues you the $200 anyway. You wouldn't know that anything foul had taken place, and the bank wouldn't know that $200 had been issued because your account hasn't been updated. Therefore, there would be a descrepancy between your records and the banks, (which could be beneficial in this case ;-) ), but would be detrimental anyway. If this happened once, twice, or even 100 times a day, the banking system as we know it would be a thing of the past, but because the bank's databases support ACID, the botched transaction would show up as an error on the ATM display, costing you a few minutes and some grumbling, but nonetheless harmless, because the database remains unchanged.

PostgreSQL also surpasses it's counterparts, with a high compliance with the SQL92 standards. These are the standards that have been set forth by the International Standards Organization (ISO), and are the standards that all database systems try to comply with. (The use of standards allows for a consistency in database interaction, and makes it easier to port knowledge and applications from platform to platform.) PostgreSQL recognizes almost all SQL constructs, such as subselects, transactions, user-defined types, and functions and even extends the SQL language with the addition of non-imbedded cursors, EXPLAIN, which shows the statement execution plan, and many other constructs that are not part of the SQL92 standard. As you can see, PostgreSQL leads the competition in SQL compliance and usability.

Third, continuing with the thread on SQL, we see that PostgreSQL has added powerful tools to help us customize our queries. One of these tools is regular expression support. Regular expressions are basically just patterns of characters that describe a string, and are extremely useful in server management, file management, and any kind of search query. When I am searching for a file, or for a particular word in a file, I use regular expressions to narrow the search sufficiently enough to allow me to find what I am looking for with ease. For example, if I am looking for every file containing a single digit and nothing else, I would us the regular expression [0123456789] which matches any single digit. If I were to look for all of the text files on our server, I would use a regular expression such as, locate *.txt. (The * being the regular expression that stands for any occurrence of a file with ".txt" at the end of the filename.) As you can see, these are very powerful, and can be used in conjunction with any query on the PostgreSQL system.

In this article, we have looked at the PostgreSQL database and how it is truly an innovative relational database management system. It is rising above the competition, first, by supporting ACID, and using "all or nothing" transactions, it also supports SQL92 and almost all of it's constructs, and also allows us to customize our queries by using regular expressions. I have also pointed out that many large companies are using the PostgreSQL database in mission critical situations, and have been very pleased with the results. Even Tim Perdue, developer of Geocrawler.com and Sourceforge.net, states that he was "pleasantly surprised" at the robust nature of PostgreSQL v7.1 in his article, "Open Source Databases: As The Tables Turn", for phpbuilder.com, (the link can be found at the bottom of this article), and says that he will be looking into PostgreSQL for other websites that are going to be receiving huge amounts of traffic.

What does this mean for us here at UNT? I think it means that we have a very good contender for an open-source back-end to the ColdFusion 5 server that we are going to updgrade to next year. I have ported many of the ColdFusion applications from our NT box to a server running PostgreSQL, and have found few problems that appear as a result of the porting process. It also means that we will have a much more stable and efficient back-end for you to manipulate and monitor data from. (One of the bonuses of using PostgreSQL, is the fact that you can use Microsoft Access as a front-end to the PostgreSQL back-end, meaning a relative easy adjustment for you. (I will write more about this in future articles.)) And finally, it means that we have the opportunity to use a powerful database server to provide your ColdFusion applications with the optimum performance and functionality possible.

If you have any more questions about PostgreSQL, or any other facet concerning the UNT ColdFusion server, you can contact me at: speevey@cc.admin.unt.edu

Last updated: November 6, 2002 Questions or Comments: cws@unt.edu