PostgreSQL 9.1 is now in Beta

Hello again from the Data team! We are excited to start 2012 with a new Beta release: PostgreSQL 9.1 is now in Beta and available in every new environment.

###Features Here is a short summary of the exciting features of PostgreSQL 9.1; for more information, please refer to the official PostgreSQL documentation.

• Synchronous Replication: Synchronous replication ensures that transactions have been confirmed by a standby server in addition to the master, greatly preventing the possibility of data loss. PostgreSQL also supports transaction-level synchronous replication, which allows users to choose between response time and data safety on a per transaction basis.
• Common Table Expressions (CTE): Ensures the relational integrity of your data by allowing you to update multiple related records in a single statement. By using the results of one query to execute another query, you can update records recursively, hierarchically, or across foreign keys.
• Per-Column Collations: Adds support for linguistically-correct sorting per database, table, or column. This permits true multilingual databases where text columns can have different languages and the database correctly indexes and sorts values
• K-Nearest Neighbor Indexes: Supports incredibly fast search for things that are “near” a particular object. KNN enhances PostgreSQL’s query capabilities by using mathematical “distance” for indexing and searching. These indexes can be used to enhance common text searches, similarity searches, geospatial location comparisons, and other queries.
• Foreign Data Wrappers (FDW): FDWs allow users to create “drivers” and attach external data sources, which can be used as read-only tables. FDWs are a powerful tool for enterprise-data integration and they make data migration easier.

###Extensions PostgreSQL 9.1 has simplified the installation and management of contribs (modules that allow you to incorporate additional features to your database server). Extensions replace the old contrib system and allow you to expand the functionality of your database in a much more straightforward way. Each extension has a companion sql file and PostgreSQL has a catalog of what’s installed and available right from the database console.

You can list your available extensions by executing the following command from your psql prompt:

myapp_production=#  select * from pg_available_extensions;

In order to use an extension module in a database you only need to run the CREATE EXTENSION command to install the module’s features into that database. (Note: this command must be run by a database superuser). For example, if you want to support the multidimensional-cube datatype, you can extend your database enabling the cube module:

(myapp_production=#  CREATE EXTENSION cube;)

###Using Extensions on Engine Yard Cloud We are updating all PostgreSQL 9.0 contribs to the new extension format and making both versions available via the postgres9_extension custom recipe. This recipe allows you to enable PostgreSQL extensions packages on Engine Yard Cloud. Just edit the main/recipes/default.rb file with your database name and comment out the line that contains the extension you want.

Please make sure to let us know if you want us to add a specific extension. We’re actively working on this recipe so expect it to change often. Also, make sure that you review the instructions, because extensions may be available for different versions of PostgreSQL.

We hope you are as excited as we are about this release.

Here are more resources for PostgreSQL extensions:

• pgFoundry
• PostgreSQL Extension Network (PGXN)