Monday, June 26

2ndQuadrant – Not the Gartner Magic Quadrant

It’s still appropriate though. Because that is what we are - thought leaders in open source PostgreSQL. But that's not what I am here to talk about.The name "2ndQuadrant" comes from "The Seven Habits of Highly Effective People" by Stephen Covey, specifically Habit 3 "Put First Things First", p.151. It refers to the classification of tasks in terms of 2 axes: Importance and Urgency. The second quadrant is the Important, Not Urgent quadrant. According to Covey, if you concentrate on doing work in that space, it leads to "vision, perspective, balance, discipline, control and few crises" - i.e. you focus on your long-term vision and your ability to execute short to medium term goals is also impeccable.These are all qualities highly valued by every individual at 2ndQuadrant; so th

PgDay Paris 2017

2ndQuadrant, Simon's PlanetPostgreSQL
Great conference! Paris is a great venue for travellers across Europe and worldwide. PgDay Paris 2017 was held in English and attracted a wide audience from many other countries: UK, NL, CH, BE, US, SE - and that was just the people I spoke to.Je suis desolee ne parler ou ecrit pas en francais. Je suis un developpeur seulement.I spoke in English about the new features in PostgreSQL 10 regarding Replication & Recovery. All very well received by a large technical audience. Logical replication, physical replication improvements, quorum commit, replication lag measurement and a ton of fine detailed improvements.No slides, sorry. Come to the conferences! Meet people, hear their stories and share yours.I travelled to Paris through London on a day of public murders that made news ...

PGConf India 2017 – An Event to Remember

2ndQuadrant, Pavan's PlanetPostgreSQL
Yet another edition of PGConf India came to conclusion in early March. You may have noticed the change from PGDay to PGConf, which signals a much larger gathering of PostgreSQL enthusiasts, now and in future. What started as a small meet-up of like minded people 4 years back, has now grown into a 2-day conference with a dedicated training day and a multi-track event at the main conference.The keynote this year was delivered by Simon Riggs, a major developer and committer at PostgreSQL. He presented his thoughts on why Persistence is key to PostgreSQL's success. Persistence is why users trust PostgreSQL to manage their critical data and persistence is why PostgreSQL community is able to deliver a solid product, release after release.This year's conference was attended by more th

Re-import repository keys for BDR and pglogical apt repositories

Craig's PlanetPostgreSQL
The BDR and pglogical apt repository GnuPG signing keys have been renewed.Users should re-import the existing keys. You can verify that it's still the same key as referenced in the documentation, just with a later expiry date.Simply run:wget --quiet -O - | sudo apt-key add - sudo apt-key finger AA7A6805 | grep -A2 -B3 BDRNow check the fingerprint printed by the second command to verify it's the same as this output:pub 2048R/AA7A6805 2015-03-24 [expires: 2019-03-23] Key fingerprint = 855A F5C7 B897 6564 17FA 73D6 5D94 1908 AA7A 6805 uid BDR Apt Signing Key for 2ndQuadrant sub 2048R/739C93DD 2015-03-24 [expires: 2019-03-23]and if it is, run:sudo apt-get update...

Support for enums in btree_gin and btree_gist

Andrew's PlanetPostgreSQL
I have just committed a series of patches that provide support for enum types in the btree_gin and btree_gist standard extensions. This is something I first started work on about a year ago. It turned out to be more involved that I had first thought it would be, as it requires some additional core code due to the way that enum comparisons work, which is a bit more complex than for most data types, and involves use of PostgresSQL's internal caching mechanism.The practical upshot of this, however, is that starting with PostgreSQL 10 you will be able to use enum columns in exclusion constraints. That's something that could be very useful - I started this work when I found, somewhat to my surprise, that it wasn't possible.  

JSON version of XMLTABLE example

Andrew's PlanetPostgreSQL
My colleague Álvaro Herrera gave a terrific explanation of the new XMLTABLE feature from Pavel Stěhule that he's put a huge amount of effort into, and finally recently committed. I thought it would be fun to see how the example he gave might work today with JSON.First, I came up with a JSON equivalent version of his piece of XML. Notice that in JSON, unlike in XML, containers are nameless, so we have no "room"  tags, for example, a room is just an object in the relevant array. This is what I came up with:CREATE TABLE IF NOT EXISTS hoteldata AS SELECT json $$[ {"id": "mancha", "name": "La Mancha", "rooms": [ {"id": "201", "capacity": 3, "comment": "Great view of the Channel"}, { "id": "202", "capacity": 5 } ], "personnel": [ {"id": "1025","nam


Alvaro's PlanetPostgreSQL, PostgreSQL
I just committed a patch by Pavel Stěhule that adds the XMLTABLE functionality to PostgreSQL 10.  XMLTABLE is a very useful feature dictated by the SQL/XML standard, that lets you turn your XML data into relational form, so that you can mix it with the rest of your relational data. This feature has many uses; keep reading for some details on it.(more…)

Autovacuum Tuning Basics

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
A few weeks ago I covered the basics of tuning checkpoints, and in that post I also mentioned that the second common source of performance issues is autovacuum (based on what we see on the mailing list and at our customers under support). So let me follow-up on that with this post about the basics of autovacuum tuning. I'll very briefly explain the necessary theory (dead tuples, bloat and how autovacuum deals with it), but the main focus of this blog post is tuning - what configuration options are there, rules of thumb, etc.(more…)

Benchmark on a Parallel Processing Monster!

David's PlanetPostgreSQL
Last year I wrote about a benchmark which I performed on the Parallel Aggregate feature that I worked on for PostgreSQL 9.6.  I was pretty excited to see this code finally ship in September last year, however something stood out on the release announcement that I didn’t quite understand: Scale Up with Parallel QueryVersion 9.6 adds support for parallelizing some query operations, enabling utilization of several or all of the cores on a server to return query results faster. This release includes parallel sequential (table) scan, aggregation, and joins. Depending on details and available cores, parallelism can speed up big data queries by as much as 32 times faster. It was the “as much as 32 times faster” that I was confused at. I saw no reason for this limit. Sure, if you have 32 CPU

Looking forward to PGConf India 2017

Pavan's PlanetPostgreSQL, PostgreSQL
It has taken a lot of planning and efforts, but I'm happy to see that PGConf India 2017 is coming along very well and promises to be a great event. This is our third year in a row in Bengaluru, but there are many distinguishing factors that make this year stand out:For the first time, we're having a dedicated training day ahead of the main conference. All seats for the trainings are sold out and we had to turn away many interested folks. For the first time, we're having a multi-track conference. And for the first time, we've crossed 225 conference delegate registrations, and there are still a few days left.When we started planning for the conference, we were a bit nervous about whether we could get a good response to the training programme, but we’re almost stumped by the p