PostgreSQL

• 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.

• 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.