January 31st 2013

By Ines Sombra

TAGS:
Databases

Why we upgraded our PostgreSQL default to 9.2 and why you should, too

Hello again! Last week we launched the Early Access release of our very first NoSQL Database, Riak. This release is very important to us as Riak uses a new way to organize and manage instances that have a specific function. Exciting things are spawning from the flexibility that this model provides so expect our database stack to significantly evolve, improve, and grow this year!

But we won’t start paving a new road without making sure that our existing databases are in top shape. Which is why this week we are changing the default of our relational database stack from PostgreSQL 9.1 to PostgreSQL 9.2.

Benefits of Upgrading to 9.2

Much has been written about the features and enhancements that this release of PostgreSQL brings. We’ll briefly highlight a few of the benefits of upgrading to 9.2 but please check What’s new in PostgreSQL 9.2 and the references listed below for more information.

Additionally we recommend watching Josh Berkus’s “PostgreSQL 9.2: Full Throttle Database” presentation for a fun introduction to the many benefits and improvements of 9.2. We’d like to thank Josh for his great slides (some of which are shamelessly included in this post).

</b>

Native JSON support

The JSON data type allows you to store and validate JSON data. Note that under certain circumstances you may still want to use hstore extension but there is great potential and power in supporting this very pervasive data type at the database level. The ability to export rows or arrays to json with ‘row_to_json’ and ‘array_to_json’ is very useful.

Range Types

Range types are used to store ranges of data of a given type (like integers, numeric, timestamps and dates). They support many operators and ranges can be continuous, discrete, open or closed. Range types are extremely useful with temporal data and they simplify what were often complex calculations, say for example to find available dates of a given resource. For more examples check Jonathan Katz’s presentation on range types given at PostgreSQL Open.

Index-only Scans

In PostgreSQL, indexes have no “visibility” information. This means that when you access a record by its index, PostgreSQL has to visit the real tuple to ensure that it’s visible to your transaction. In 9.2 PostgreSQL can use the visibility map to infer if the content of a page is visible to a transaction or not.

When the index record points to a tuple contained in a page known to have no dead tuples, PostgreSQL won’t have to access the actual tuple and avoid an I/O operation. This translates into even faster lookup times. Here is a visual example of the performance implications of this feature:

Slide stolen from Josh’s 9.2 presentation

Replication Enhancements

PostgreSQL 9.2 allows for cascading replication. This means replicas are no longer required to connect to the same (and unique) master. Note that we will continue to connect your database replicas to the environment’s db_master while the number of replicas is low. This improvement will allow us to make cascading replication possible for customers with large environments who want to lighten the database master’s replication load.

And So Much More!

And there are many more features like improved write performance and group commits that make upgrading to PostgreSQL 9.2 something you certainly want to consider. Additionally all of our previous extensions have been tested and verified against PostgreSQL 9.2.

We support PostGIS 2.0

We have also incorporated PostGIS 2.0 into the list of supported extensions. PostGIS adds spatial functions such as distance, area, union, intersection, and specialty geometry data types to the database. PostGIS 2.0 brings a significant list of new features and it also introduces new 3D Types (Triangle, Tin, and Polyhedralsurface).

Note that some deprecated functions have been removed from the PostGIS 2.0 codebase. If your application was using any of the deprecated functions in PostGIS 1.5 you will have to update your code to use functions included in the new version.

A new type of Index, Space-Partitioned GiST (SP-GiST)

Many of you know that PostgreSQL supports versatile index types (like GiST, and GIN) that enhance your ability to query specific data. PostgreSQL 9.2 and PostGIS 2.0 introduced a brand new index type, Space-partitioned GiST (SP-GiST).

SP-GiST supports partitioned search trees, which facilitates the development of a wide range of non-balanced data structures, such as quad-trees, k-d trees, and suffix trees. These structures repeatedly divide the search space into partitions that need not be of equal size.

The SP-GiST index has similar applications than GiST but may be faster in certain kinds of data distributions. It’s also faster to read and update than GiST. In a geolocation-heavy application this type of index may allow you to traverse space in a highly efficient manner.