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.
Create Your Own 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.
All DDL Is Transactional
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.
You Can Query 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.
Streaming Replication Is Baked In
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.
Supports 12 Authentication Systems
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?