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 (more…)
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 (more…)
The short answer … Hell Yeah!
The long answer lies in extensive improvements and the impressive new feature list that makes up this major release - which, by the way, changes the version scheme of PostgreSQL as well (more details on that here). This wiki page lists out, in detail, all the new features in PostgreSQL 10, but for the purpose of this blog, I will focus on some of the exciting features contributed by 2ndQuadrant. (more…)
PostgreSQL 10 offers an exciting new set of features in addition to making further improvements to many of the already existing features including Big Data, Replication and Scaling, Administration, SQL, XML and JSON, Security, Performance and a lot more.
If you are planning to try your hands at PostgreSQL 10 and wondering on how you can easily get it on your machine, 2ndQuadrant’s GUI installers 2UDA can help you with an easier installation of PostgreSQL 10 for Windows, OS X and Linux platforms.
2ndQuadrant is a Platinum Sponsor of the PostgreSQL project and is committed to following community timelines for all releases; major & minor. This ensures that 2UDA releases are always up to date and available in a timely manner. With 2UDA's builtin upgrade feature for minor releases, it (more…)
Postgres-BDR (or just BDR, for short) is an open source project from 2ndQuadrant that provides multi-master features for PostgreSQL.
Here we will show how to build a test environment to play with BDR and how to configure it using the OmniDB 2.1 web interface.
2. Building test environment
Let's build a 2-node test environment to illustrate how to configure BDR within OmniDB.
2.1. Pull OmniDB repo
The first thing you need to do is to download OmniDB repo from GitHub and make sure you are in the development branch. Run the following:
git clone https://github.com/OmniDB/OmniDB
git checkout dev
2.2. Create 2 virtual machines with BDR
On your host machine, you need to have installed:
Vagrant plugin (more…)
One feature quietly added to PostgreSQL 10 is the ability to determine the commit status of any transaction by transaction-id.
It's reasonable to wonder why you'd want this, since you know if you committed the transaction, it's still in progress, or if you or rolled it back. And you can check for in-progress transactions in pg_stat_activity.
It exists to help the application recover to a known state after a failure without having to use heavyweight two-phase commit. It's also useful for querying standbys.
Imagine that your application has just sent the COMMIT for a transaction that's part of a queue processing system. Before the application receives a reply to its commit request, the database connection breaks due to network issues, a database crash, etc. It's possible (more…)
During the Postgres Open 2017 conference in San Francisco, someone came to the 2ndQuadrant booth and struck up a conversation with me. During our shameless geeking out over database mechanics, he asked me if pglogical supported the new Postgres 10 partitions. Given my noted expertise in all things Postgres, I answered in the appropriate manner:
"I have no idea. I'll have to look into that."
Well, after a bit of experimentation, I have a more concrete answer, and it's reassuringly positive.
Given a table on a provider node, is it possible to capture only INSERT traffic such that it accumulates on a subscribed system for archival purposes? It's a fairly common tactic, and allows an active OLTP system to regularly purge old data, while a reporting OLAP system keeps it (more…)
Logical replication uses a publish/subscribe model and so we create publications on the upstream (or publisher) and subscriptions on downstream (or subscriber). For more details about it, please refer to this blog post from my colleague Petr Jelinek, and also to the PostgreSQL documentation.
Here we will show how to build a test environment to play with this new feature from PostgreSQL 10, and how to configure it using OmniDB 2.1.
2. Building test environment
Let's build a 2-node test environment to illustrate how to configure PG10 logical replication feature within OmniDB.
2.1. Pull OmniDB repo
The first thing you need to do is to download OmniDB in the repo from GitHub and make sure you are in the development branch. Run the following:
2.2. Create 2 virtual (more…)