Unless you were in Sydney for linux.conf.au 2018 last week you probably didn't see my talk Geographically distributed multi-master replication with PostgreSQL and BDR. Luckily for you it's on archive.org and YouTube.
If you're interested in multi-master replication of any sort, even non-PostgreSQL-based multi-master, it should be worth taking a look. The talk could've been better titled "Understand Multi-Master and if it's right for you" or "Physics is Mean".
Comments and questions are welcome - @craig2ndq.
You can also just
My colleague Alvaro Herrera has been working on a series of connected features for PostgreSQL 11. It's worth explaining what these are rather than trying to piece together what is happening from reading commit messages.
The overall idea is to allow Partitioned tables to have Referential Integrity, by way of Primary Keys and Foreign Keys, as well as some additional tweaks.
To achieve that, we need to understand the structure of features in PostgreSQL.
Foreign Keys (FKs) are implemented using row Triggers, so we must allow Triggers to be executed on Partitioned Tables. FKs also require Primary Keys (PKs), so we must add those also.
Primary Keys are implemented using Unique Indexes, so we need to add indexes and allow them to be unique. I'll write about partitioned indexes
Backups are a critical component to a fully covered Postgres database infrastructure. In some ways, it's fair to say a database without a backup is no database at all---sometimes literally. 2ndQuadrant's Barman tool is aptly named as a Backup And Recovery Manager for Postgres, and it exists primarily for encouraging a stable and robust backup process.
Backing up a database and then restoring from that backup often has an army of associated scripts and utilities. Some of these components are probably the Postgres default tools of pg_dump, pg_restore, and pg_basebackup. The rest are often shell scripts, homegrown or otherwise copied from blogs or git repositories. Let's just assume the best case scenario and assume all of these work just fine; I used to think the same thing myself.
Spectre and Meltdown have caused severe alarm in recent days. You may have read about up to 30% impact on PostgreSQL databases, which I believe to be overstated because of misunderstandings in the media. Let's dig into this in more detail.
TL;DR Summary: no PostgreSQL patch required, -7% performance hit
In response to these new security threats various OS patches have been released. Various authors have published benchmarks around these and they have, in some cases, stated worst-case measurements as impact measurements. For example: stating a 30% hit when, in fact, we are seeing a 7% hit on a busy server. Regrettably, it looks to me like some people outside the PostgreSQL community have spread this news as a problem for PostgreSQL, without clearly stating the workload measured, or
The annual DBEngines ranking have just been updated, with PostgreSQL winning the "DBMS of the Year 2017" award.
PostgreSQL was a runner up last year.
So across the year, we have made more gains in popularity than all other databases. Looking at the detail we see that all of the other 4 databases that make up the Top 5 have reduced in popularity from last year. PostgreSQL has gone up, others have gone down.
What's even more interesting, is that PostgreSQL is the only database in the top 5 systems that has increased steadily over the last 4 years.
Slow, steady progress. The word is out!
Why is that? PostgreSQL is multi-talented, offering relational features when needed, as well as JSON features for
I have released version 6.1 of the PostgreSQL Buildfarm client. It is available at https://buildfarm.postgresql.org/downloads/releases/build-farm-6_1.tgz
This release fixes a couple of bugs that became apparent in yesterday's release. The first was a relatively minor one where the verbosity was always set if using the run_branches.pl script. The second was a portability issue where some versions of Perl and its Time::HiRes module behaved unexpectedly and as a result log files were not sorted in correct order, leading to very strange timing results. The second one is sufficiently bad to warrant this point release.
Thanks to Tom Lane for identifying and helping to diagnose and patch these bugs.
Also, one other small bug is fixed in some utility scripts, and the BlackholeFDW module
PostgreSQL Buildfarm client Release 6 is now available and can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-6.tgz
use a persistent cache for configure, leading to a substantial performance improvement for that step, based on an idea from Tom Lane
improve installcheck security, mirroring earlier changes on this in Postgres itself
allow skipping individual TAP tests, e,g, "--skip_steps=pg_dump-check"
a use_installcheck_parallel setting that uses the parallel schedule rather than the serial schedule for the installcheck step (not for MSVC)
use_valgrind and valgrind_options settings that will cause the installed postmaster to be run under valgrind and report any output. This doesn't apply to the check step, TAP tests, or any
ZFS is a filesystem originally created by Sun Microsystems, and has been available for BSD over a decade. While Postgres will run just fine on BSD, most Postgres installations are historically Linux-based systems. ZFS on Linux has had much more of a rocky road to integration due to perceived license incompatibilities.
As a consequence, administrators were reluctant or outright refused to run ZFS on their Linux clusters. It wasn't until OpenZFS was introduced in 2013 that this slowly began to change. These days, ZFS and Linux are starting to become more integrated, and Canonical of Ubuntu fame even announced direct support for ZFS in their 16.04 LTS release.
So how can a relatively obscure filesystem designed by a now-defunct hardware and software company help Postgres? Let's find out!
On 12-Dec-2017, 2ndQuadrant held the first in a series of PostgreSQL webinars. The session was conducted by Andrew Dunstan, Principal Contributor of JSON functionality to PostgreSQL and Senior Developer & PostgreSQL Committer at 2ndQuadrant.
As promised, the recording of the Webinar is now available. Those who couldn't make it to the live session, can now view Introduction to JSON data types in PostgreSQL here.
While Andrew was able to address many questions live, there were several queries that couldn’t be answered due to time restrictions. Andrew Dunstan has taken time to answer those questions below.
Q1: Since PostgreSQL 9.4 JSONB supports GIN index, does that means that GIN index has a better performance on JSONB? What about JSON?
A: There is no direct indexing
One of the headline features of the brand new PostgreSQL release out 2 months ago is Logical Replication. Logical replication allows more flexibility than physical replication, including replication between different major versions of PostgreSQL and selective-table replication. You can get more details on the feature here.
So, now that we have this, I've been asked on occasion if we are still going to continue develop pglogical and if it's even needed. I was also asked a couple of times why we put the native logical replication into PostgreSQL when we already have pglogical. I'd like to answer those questions in this blog post.
Why Logical Replication in PostgreSQL 10?
Let's start with the simpler, or rather shorter, topic of why we added logical replication into PostgreSQL 10.