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.

30 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

  13. burner weight loss pills Authentic Option LLC offers collection of natural herbal products for skincare, hair care, and weight loss. We specialize in thermal weight loss, electrostimulation for muscle toning, cellulite removal, under-eye bags treatment and skincare products. We have an amazing range of beard growing products.

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

    ReplyDelete
  15. Online Reputation Repair Reputation Repair Service removes criminal history information and deletes criminal records. Creates positive content that replaces negative search results.

    ReplyDelete
  16. 500 1 million best downloads 1millionbestdownloads.com offers a variety of downloads in several different industries. Our downloads range from general, to software, services and to even making money. These downloads come in many formats from ebooks, to signups, traffic generation, hosting, marketing, finance, arts and entertainment, cooking, investing, employment, games,

    ReplyDelete
  17. Xhamster DownloaderDownload Apk For Android with ApkHonor Download Apk Free online Downloader, Fast Premium Apk Free Download Apk Update Version?

    ReplyDelete

  18. Facts about addiction Addiction stands as the top public health problem in America outstanding all other major diseases including stroke, heart diseases and even cancer. The population of addicts is far larger than the patients suffering from the three mentioned diseases in America with sufferers ranging in millions.

    ReplyDelete
  19. Crunchyroll Crunchyroll is a dedicated anime streaming service that uploads over 30,000 episodes of top-rated anime episodes to subscribe members to it. Crunchyroll offers a comprehensive selection of high-quality dubbed anime series available for viewing no matter what time of day or night. All users can enjoy streaming live anime trailers, latest episodes, and popular anime show in the Crunchyroll catalog.

    ReplyDelete