PostgreSQL 9.3 - A few interesting features

PostgreSQL 9.3 was released yesterday, and as a big fan of the DBMS, I thought it apt to highlight some of its more interesting new features. Here's a link to its manual.

My favourite feature is better support for the JSON data type. With the rapid adoption of node.js, the newfound "MEAN" acronym (mongoDB, express, angular.js, node.js), it seems like mongoDB is at the forefront of the "hip" startup. They should probably remove the "M" though. Combining postgres hstore with better JSON support, async drivers, PL/V8 (not part of core or contrib modules; needs to be installed separately; enables you to write SQL functions in javascript, which you can later call using SQL), node ORM libraries (e.g., Sequelize) and wrappers, I would much rather utilize postgres over mongo for most use cases, even with node.js (granted, I like the sharding capabilities of mongo). Here's one benchmark, and a pretty informative presentation, that favours postgres over mongo.

"Prevent non-key-field row updates from blocking foreign key checks" is probably the next best feature, in terms of concurrent performance since it "reduces the probability of deadlocks when updating tables involved in a foreign-key constraint".

Event triggers look pretty cool, and clearly something I might think of using. "Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events." I wonder if perhaps it might be better capturing such logic in the application code.

SP-GiST indexes interest me, mainly for theoretical reasons. The fact that there is yet another implementation pathway to construct indexes is pretty cool, though I'll probably stick with the existing GIN and GiST indexes for most things.

Lateral joins (I doubt I'll use this; I'd rather handle such logic in the application) and updatable views are "meh". The updatable view logic seems highly restrictive. If you were seriously using a view, it would likely be a fairly complex query, spanning multiple tables. I would just create a new one and have the application switch or something.

Here's what the folks at hacker news have to say about postgres 9.3.