Book Review: “Practical PostgreSQL”

PostgreSQL is considered by many to be the most advanced open source database in the world (sorry mySQL), providing a wealth of features (sub-select, transactions, write-ahead logging etc) that are usually only found in commercial databases. PostgreSQL is an object-relational database management system (ORDBMS), originally developed in 1977 by the University of Berkeley and it is included by default as the main database on most *BSD systems, like FreeBSD. But in spite of its long history and extensive user base (however, it is still not as popular as the Linux-centric mySQL though), PostgreSQL suffered from a lack of comprehensive and easy-to-use documentation. “Practical PostgreSQL” (along with 2 more PostgreSQL books, all released the last 2-3 months) fills that information void with a fast-paced guide to installation, configuration, and usage.The book is written by John Worsely and Joshua Drake, co-owners of Command Prompt Inc. (a company which specializes in advanced web-based application development and the development of LXP, an application server), and published by O’Reilly.

The book weighs in at more than 600 pages and it begins with the history of PostgreSQL and a listing of its feature set. I did not know that Ingres (which was one of the databases I was instructed in during college in Greece) is nothing but an offshoot of PostgreSQL back in the ’80s. That caught me by surprise. The installation chapter is very detailed but someone could easily say that it is too detailed. For example, the authors nit-pick a bit on the versions of gzip or tar that may be installed on certain systems, but overall they give some good guidelines on how to install and pre-configure PostgreSQL successfully on pretty much any UNIX system.

The second part is a primer of SQL. It will teach you not only how to use PostgreSQL, but also it is a detailed instructional text on how to design simple databases and how to query them with the use of SQL. Around 200 pages of the book are about how to use SQL, how to do queries, learning how transactions work, creating views or functions, dealing with cursors, arrays, constraints and more. If you are just a bit familiar with databases and SQL, you will find the reading of that part of the book easy going and help you dust off some of your knowledge, while if you are a rookie, you probably would like to take it a bit easy and read the book in a more slow pace and learn by example.

The book also shows how to use several PostgreSQL clients, from psql to some graphical utilities like pgAccess. Personally, I would additionally recommend phpPgAdmin, a PHP-based client which works well for me on both my Linux & FreeBSD boxes (online demo here).

In the advanced section you will learn how to manage databases, your privileged users, how to do backups and how to make better use of authentication & encryption with the built-in SSL module in PostgreSQL.

In the fourth part, the authors discuss how to include PostgreSQL support in your programs with the use of PL/pgSQL and JDBC. The information given in these chapters is, at the most, sketchy (especially the JDBC chapter), and there is no mention on how to use C or C++ with PostgreSQL – which I found the second worst critical oversight of the book.

The last 40 pages (just before the extensive PostgreSQL-supported SQL reference at the end of the book) are dedicated to LXP, the application server – it works as an Apache module – that Command Prompt Inc. is developing, which can connect to a PostgreSQL database, pull data and serve this data as HTML pages. LXP is XML-based and it seems pretty powerful as it does not require any “logic” programming (as you would in Perl or PHP) to do something a bit more advanced than a simple SELECT. Well, LXP is certainly neat, but I have a problem with the authors not including any info (not even a basic introduction) on how to use PHP and/or Perl with PostgreSQL. To me, this is the biggest weakness of the book. I do not pretend to know that it had something to do with the fact that they are the people behind the rival LXP, but certainly the failure to include the basics of using PHP/Perl with PostgreSQL is not welcome.

The other thing that may bother some people that read the book is that it reads like a manual or a “cold” reference, rather than a more friendly textbook with real life examples. That is not to say that the book is not comprehensive, on the contrary. But the book does not leave a sweet aftertaste, it feels like a manual that you would most likely refer to when you are stuck somewhere in your PostgreSQL project and you require assistance. Actually, some readers might find this a plus, some might not though.

On the bright side though, the book is up to date: it covers the brand new and current PostgreSQL 7.1.3 and it includes a CD-ROM with a PostgreSQL package and LXP 0.8. The book is generally recommended and certainly it will teach you all you need to know for your migration to PostgreSQL or to learn SQL in gereral. However, you may want to give a look to the also recent “PostgreSQL: Developer’s Handbook” or “Beginning Databases with PostgreSQL” which have garnered rave reviews thus far. Whichever is your choice, one thing is absolutely recommended without a second thought: PostgreSQL itself.

Overall: 8 / 10