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.

45 comments:

  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.

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

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

    ReplyDelete
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. BUY SPOTIFY PLAYS REAL PLAYS TO BOOST YOUR MUSIC.
    We will promote your tracks and give you very good promotion Buy spotify plays

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

    ReplyDelete

  11. 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
  12. 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
  13. 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

  14. 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
  15. 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
  16. 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.

    ReplyDelete
  17. canim sohbet Android Dear ChatCanım chat is growing day by day with the android chat service it has given to you and serves you, dear chat lovers, free of charge. You can connect and chat online at any time of the day with your smart devices on our chat site. Anonymous chat is a free chat site that allows you to chat randomly and does not charge any fee or money from our valued chat users.

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

    ReplyDelete
  19. buy saxenda online we focus on developing clients and improvement in healthcare. Our leading solutions include a predictive and action-oriented business intelligence platform, fully integrated retail, specialty and outpatient pharmacy solutions, and much more. Our products and services are designed to help drive operational results for our customers while improving outcomes for patients.

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

    ReplyDelete

  21. 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.

    https://carol-s-farm.company.com

    ReplyDelete
  22. polyuria As a worldwide leader in industrial coatings and spray-on truck bed liners, ArmorThane offers a full line of top-quality polyurethane and polyurea coatings and high-tech spraying equipment and supplies. Our professionally applied pure and hybrid polyurethane and polyurea formulas are extremely durable, flexible and safe.

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

    ReplyDelete
  24. polyurea As a worldwide leader in industrial coatings and spray-on truck bed liners, ArmorThane offers a full line of top-quality polyurethane and polyurea coatings and high-tech spraying equipment and supplies. Our professionally applied pure and hybrid polyurethane and polyurea formulas are extremely durable, flexible and safe.

    ReplyDelete
  25. Totally loved your article. Looking forward to see more more from you. Meanwhile feel free to surf through my website while i give your blog a read.
    welcome to Newsome frenchies
    where to buy french bulldog puppies
    frenchies bulldog puppies
    in Australia

    Nice & Informative Blog ! We offer welcome to Mila bulldogs. Check it out!...
    puppies for sale near me
    British bulldog puppies for sale
    where to buy

    ReplyDelete