Thursday, August 17

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 - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc | sudo apt-key add - sudo apt-key finger AA7A6805 | grep -A2 -B3 BDR Now 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 (more…)

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"," (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 Query Version 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 (more…)

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 (more…)
Schedule is live for pgDay Paris 2017 on March 23!

Schedule is live for pgDay Paris 2017 on March 23!

Vik's PlanetPostgreSQL
pgDay Paris 2017 is the third annual one-day, one-track PostgreSQL conference, held in Paris, France. The schedule is live! This year, all presentations are in English in order to bring the very best of the international community to Paris and to show how PostgreSQL is used around the globe. Here is what you'll see at pgDay Paris 2017: A PostgreSQL DBAs Toolbelt for 2017 by Kaarel Moppel, a consultant from Austrian PostgreSQL company Cybertec, takes us on a tour of handy tools and scripts that every DBA should know about. Corruption War Stories by Christophe Pettus, CEO of American company PostgreSQL Experts, shares with us his experiences with data corruption and how he was able to overcome it. Can't imagine what a newbie can do with PostgreSQL on Linux in a month by (more…)

Corruption in CREATE INDEX CONCURRENTLY

Pavan's PlanetPostgreSQL
Two weeks back we discovered an ancient bug in PostgreSQL which may cause index corruption when index is built via CREATE INDEX CONCURRENTLY (aka CIC). All supported releases of PostgreSQL are affected by this bug. The bug could be a source of index corruption reports that we receive from the field once in awhile. Now before we discuss the problem, let me make it absolutely clear that PostgreSQL is known for its gold standards as far as code quality and product stability is concerned. But just like any other software, the database engine can also have unnoticed bugs. But PostgreSQL community takes every known bug very seriously and try to fix it on priority. No wonder that the bug fix went into the scheduled 9.6.2 release (and all other stable releases) at a very short notice, but not (more…)

Problem with Buildfarm registrations fixed

Andrew's PlanetPostgreSQL
Recently we switched the PostgreSQL Buildfarm to use https for its front-facing web transactions. That's worked pretty well, but one thing got broken in the change. The registration page uses a Captcha to help avoid web spam, which has been a problem in the past. However, the code to fetch the relevant html was still getting plain http, and thus browsers were refusing to display the Captcha because it came from an untrusted source. Without the Captcha the registration was rejected. This was discovered yesterday, and has now been fixed. The Captcha code now uses https. If you have recently found a buildfarm registration rejected like this, please resubmit it.