Tuesday 10 September 2013

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.


  1. javascript experts We are professionals in web development for established businesses and start-ups using own team or by building and managing a dedicated development team exactly for your needs.

  2. buy telegram members We provide all the Telegram services (increase real & fake members - views posts)

  3. 1xbet türkiye 1xbet Türkiye, online bahis şirketi hakkında giriş ve inceleme bilgileri. Onebahis firması için 2020 güncel yeni giriş adresi, ödemeler ve üyelik işlemleri için detaylı bilgiler, 1xbahis sayfamızda yer almakta.

  4. less than one dollar web hosting Domain hosting can be hit and miss when it comes to finding it cheap. Don’t get me wrong there are cheap web hosting options, but how about “super cheap and affordable” web hosting. Less than a dollar is “truly” affordable web hosting and this is a chance to get access to web hosting that is consistent and reliable when it comes to affordable web hosting.

    We will promote your tracks and give you very good promotion Buy spotify plays

  6. xanax bar mg This dosage provides relief of anxiety for up to 6 hours after it has been ingested, which can help you focus on what needs to be done without being disrupted.

  7. British blue cat Kittens for sale near me English shorthiarWe have been branded as the best cattery for breeding healthy kittens and puppies ready for a home.

  8. legitimate online casinos Compare the best legit online casinos that pay real money including the best crypto casinos, best mobile casinos and the best online casinos in 2021

  9. motherhood CO.MOM is a fun new online magazine and social network community created by moms for moms. Read interesting articles, get advice from our agony aunt, aunt\y anne, learn how to earn an income online with our guru debbie or you can even seek medical advice from our resident physician dr samantha and relax while checking out the latest horoscopes from our trained mystic. Theres even a thriving forum community where moms of the world can get together and enjoy good conversation along with lots of other fun ways to kill some time with a cuppa when taking a break from being super mom at co.mom.


  10. maine coon kittens for sale
    We raise exceptional Maine coon kittens for sale in a loving and caring environment. Dedicated to following a responsible breeding program, our focus is on keeping our animals healthy and happy for when they find their new homes.
    We are well known for having the best and healthiest maine coon kittens for sale nationwide. Browse wide variety of kitttens on our Website.


  11. polyurea reviews
    Ever since the construction of the first concrete floor, humanity has searched for a way to protect and beautify the surface. Polyurea coatings have been around for some time and polyurea coatings have quickly become the darlings of those who want a fast dry time. Let’s examine each of these contenders and see how they stack up against each other.