Tuesday, October 24

Tag: PostgreSQL10

v10, The Best PostgreSQL Yet?

v10, The Best PostgreSQL Yet?

Umair's PlanetPostgreSQL
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 easy installation with 2UDA

Haroon's PlanetPostgreSQL
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 with OmniDB

OmniDB, William's PlanetPostgreSQL
1. Introduction 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 cd OmniDB git checkout dev 2.2. Create 2 virtual machines with BDR On your host machine, you need to have installed: VirtualBox Vagrant Vagrant plugin (more…)

Transaction traceability in PostgreSQL 10 with txid_status(…)

Craig's PlanetPostgreSQL
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. Recovery 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…)

PG Phriday: pglogical and Postgres 10 Partitions

pglogical, Shaun's PlanetPostgreSQL
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. The Problem 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…)

PostgreSQL 10 Logical Replication with OmniDB

OmniDB, William's PlanetPostgreSQL
1. Introduction 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…)

PG Phriday: Crazy Correlated Column Crusade

Shaun's PlanetPostgreSQL
For a long time, the Postgres query planner has sported a huge blinking neon blind-spot that frustrated and enraged DBAs throughout the universe to a level just shy of murderous frenzy. How is this even possible? What terrible lurking horror could elicit such a visceral response from probably the most boring and straight-laced people ever to draw breath? What else? Correlated statistics. The Adventure Begins! The Postgres query planner is a cost-estimation engine. Postgres gathers statistics on table contents such as most and least frequent values, value cardinality, rudimentary histograms, and so on. Along with multiple metrics related to hardware responsiveness, it generates multiple viable execution strategies, and chooses the one that probably costs the least resources. So far, this (more…)

News and Roadmap from Postgres-XL 10

2ndQuadrant, Pavan's PlanetPostgreSQL
We expect Postgres-XL 10 to be released in the next few months, with the new features of partitioning and logical replication. You’ll be able to load Postgres-XL directly from PostgreSQL. For the first time, you’ll be able to run massively parallel queries both across the datanodes and within the datanodes to give huge performance gains. Earlier today, we updated the master branch of the Postgres-XL repository to include all commits from PostgreSQL's master branch up to June 26. That means the XL project is now fully to date with the PostgreSQL source code, meaning there is now only minimal lag between PostgreSQL and Postgres-XL. At this point the code is only in Development/Alpha, though we expect to produce Beta1 soon after PostgreSQL Beta 3 in August. Support from the (more…)

More robust collations with ICU support in PostgreSQL 10

Eisentraut's PlanetPostgreSQL, PostgreSQL
In this article, I want to introduce the ICU support in PostgreSQL, which I have worked on for PostgreSQL version 10, to appear later this year. Sorting Sorting is an important functionality of a database system. First, users generally want to see data sorted. Any query result that contains more than one row and is destined for end-user consumption will probably want to be sorted, just for a better user experience. Second, a lot of the internal functionality of a database system depends on sorting data or having sorted data available. B-tree indexes are an obvious example. BRIN indexes have knowledge of order. Range partitioning has to compare values. Merge joins depend on sorted input. The idea that is common to these different techniques is that, roughly speaking, if you have sorted (more…)