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.

13 comments:

  1. 먹튀This web site is very popular, educated and intended to benefit.

    ReplyDelete
  2. 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.

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

    ReplyDelete
  4. great work man i would like to congratulate you on this effort Toto Desk

    ReplyDelete
  5. Minecraft Premium HesapMinecrafthesabim.com sizlere çok daha uygun fiyatlı bir şekilde minecraft premium hesap satmak amacıyla kurulmuş bir şirkettir. Şirketimizde çalışan bütün kişiler konular hakkında yeterli bilgiye sahiptir ve Minecraft Premium hesabınızla ilgili bir sorun olduğu zaman 7/24 ulaşabilmeniz için hazır beklemektedirler.

    ReplyDelete
  6. hepsibahis Yüksek bahis oranları sunan en güncel canlı bahis şirketi olan hepsibahis firmasına buradan direk giriş yapın. Youwin adı ile tanınan ve Türkiye deki ismi hepsi bahis olarak kullanılan iddaa firması ile ilgili detaylı bilgileri öğrenin.

    ReplyDelete
  7. bahis siteleri Avrupa da yasal olarak faaliyet gösteren bir çok güvenilir bahis sitesi için güncel giriş adresleri Bahis5XL sitemizde. Bahis5XL.com ile bir çok yabancı bahis şirketi hakkında tanıtım yazılarını inceleyerek, size uygun en iyi iddaa sitesini seçebilirsiniz. Canlı bahis siteleri için en çok bedava deneme bonusu veren Kıbrıs ve Malta lisanslı Türkçe bahis sitelerini Bahis5XL ile inceleyin. Türkiye nin en güncel 2020 bahis siteleri listesi burada.

    ReplyDelete
  8. 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.

    ReplyDelete
  9. meal kits Making cooking a little easier. It's hard, it takes time and who's got the time after a long day of work, looking after kids and being on your feet all day ? I bet you don't and neither do I.. making cheap and tasty modern day meal kits the new normal among all the changes going on in our world today.

    ReplyDelete
  10. 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.

    ReplyDelete
  11. BUY SPOTIFY PLAYS REAL PLAYS TO BOOST YOUR MUSIC.
    We will promote your tracks and give you very good promotion Buy spotify plays

    ReplyDelete
  12. investment strategy GET UNIQUE INVESTMENT IDEAS FROM AN EXPERIENCED FUND MANAGER TARGETING 5X - 10X+ RETURNS.

    ReplyDelete