5 Cool Things You Can Do With PostgreSQL

By Karen Tegan Padir
LinuxInsider
Part of the ECT News Network
10/30/12 5:00 AM PT

One could write thousands of pages about all the features PostgreSQL offers. Instead, let's take a look at five features that are particularly interesting and find out where PostgreSQL sits in relation to other open source and proprietary database systems. PostgreSQL has a lot more to offer than might be immediately obvious.

Considering Live Chat For Your Company?
Which live chat solution is right for you? Find out now with the new Live Chat Comparison 2012 Report. Download the free report.

This story was originally published on July 17, 2012, and is brought to you today as part of our Best of ECT News series.

PostgreSQL is one of the most versatile, powerful and reliable object-relational database systems available. The enterprise-class open source database is in use by some of the largest companies in the world, handling exceptionally large workloads. But you knew all of that already, so let's talk about a few features in PostgreSQL you probably didn't know about already.
The fact is that PostgreSQL is a sophisticated creature. You have to dive beneath the surface to really understand just what PostgreSQL is actually capable of. We could write a War and Peace-sized compendium of all of PostgreSQL's features, but the comprehensive documentation for PostgreSQL takes care of that to a large extent. Instead, we want to call out five features today that are particularly interesting and show where PostgreSQL sits in relation to other open source and proprietary database systems.

Customize Your Data Types

With many database systems, you're more or less stuck with the data types that the creators of the database envisioned when the database system was created. Not so with PostgreSQL, which features Generalized Search Tree (GiST) indexing. One of the features of GiST is that PostgreSQL users can create custom data types that allow them to decide what to store and how to store it.
This has enabled some substantial projects based on PostgreSQL, such as the OpenFTS full-text search engine and PostGIS. PostGIS provides data types that are used by geographic information systems (GIS).
If your organization needs support for data types that aren't offered "out of the box," then PostgreSQL may provide the solution.

Easy Error Recovery

Mistakes happen, hardware fails, and Murphy's Law sometimes prevails. You want to be able to recover from transactions that were made by mistake or for any other reason. PostgreSQL has the ability to do this through what's called "transactional DDL" (Data Definition Language). DDL is the language that defines, essentially, how you can define and work with your database -- such as creating and dropping tables, altering objects, and so on.
With one exception (adding and dropping databases), all operations are transactional. Using the logs, you can easily recover from even very large changes. You might think that this is true for all databases, but it really isn't.

Querying XML Data

Many organizations are working with a lot of XML data these days. If you are in this position, you should take a look at PostgreSQL's XML features, which allow users to query XML data.
Using PostgreSQL, you can directly query XML data stored in the database and extract elements from the data stored in your database. For example, it's possible to store an XML file created in another application in PostgreSQL, and query just one element from the file rather than pulling the entire file and having to query it outside the database.

Baked-In Streaming Replication

Another compelling feature in PostgreSQL is streaming replication. You might not know this, but PostgreSQL 9.0 and later can continually update standby servers so that they're ready at a moment's notice.
Note that this isn't an extra feature; it's baked into the database as part of PostgreSQL's standard feature set.

12 Authentication Systems Supported

One of the challenges that teams face when deploying any service is making sure that the service supports the type of authentication that's in use. While PostgreSQL may not support every type of authentication imaginable, it certainly comes close. Current PostgreSQL releases support 12 authentication systems.
Whether you're using an all-UNIX environment and LDAP, or putting PostgreSQL in an environment that's primarily Windows with Active Directory, PostgreSQL comes prepared. In addition to LDAP and Kerberos, PostgreSQL also supports GSSAPI, password-based authentication, RADIUS, or even "trust-based" authentication for situations when anyone that can connect to the server is assumed to be allowed to connect (we don't recommend that last one for most usage, though).
As you can see, PostgreSQL has a lot more to offer than might be immediately obvious. Already familiar with these features? Tell us -- what features do you find most interesting in PostgreSQL?