Friday, November 24

Tag: PostgreSQL 10

Here’s How You Can Run OmniDB On Postgres10 [VIDEO]

Liaqat's PlanetPostgreSQL, OmniDB, PostgreSQL
OmniDB is an open source web tool designed specifically for managing relational databases with focus on simplicity and user interaction, but at the same time be fast, powerful and light on memory. OmniDB, at the moment, supports several different relational database systems such as PostgreSQL. The latest release of OmniDB, version 2.3.0, has introduced ‘Debugger for PL/pgSQL functions’ - a much requested feature for OmniDB users. If you’re a PostgreSQL user and want to extract the power of its functionalities through a simple GUI tool, here is Simon Riggs from 2ndQuadrant demonstrating how to run OmniDB on top of PostgreSQL 10. Transcript: My name is Simon Riggs from 2ndQuadrant, and I’m going to give you a demo of OmniDB running on top of Postgres 10. What is OmniDB (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…)

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…)

Logical Replication in PostgreSQL 10

Petr's PlanetPostgreSQL
PostgreSQL 10 is getting close to its first beta release and it will include the initial support for logical replication, which is was written primarily by me and committed by my colleague Peter Eisentraut, and is internally based on the work 2ndQuadrant did on pglogical (even though the user interface is somewhat different). I'd like to share some overview of basics in this blog post. What's logical replication? Let me start with briefly mentioning what logical replication is and what's it good for. I expect that most people know the PostgreSQL streaming master-standby replication that has been part of PostgreSQL for years and is commonly used both for high availability and read scaling. So why add another replication mechanism and why call it logical? Well, the traditional (more…)

PostgreSQL 10 identity columns explained

Eisentraut's PlanetPostgreSQL, PostgreSQL
For PostgreSQL 10, I have worked on a feature called "identity columns". Depesz already wrote a blog post about it and showed that it works pretty much like serial columns: CREATE TABLE test_old ( id serial PRIMARY KEY, payload text ); INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *; and CREATE TABLE test_new ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, payload text ); INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *; do pretty much the same thing, except that the new way is more verbose. ;-) So why bother? Compatibility The new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some have lately (more…)