Monday, November 12

Tag: PostgreSQL11

OmniDB debugger for PostgreSQL 11

OmniDB, William's PlanetPostgreSQL
PostgreSQL 11 was released recently, with exciting new features. One of them is the ability to write SQL procedures that can perform full transaction management, enabling developers to create more advanced server-side applications. SQL procedures can be created using the CREATE PROCEDURE command and executed using the CALL command. Since OmniDB 2.3.0 it is possible to debug PostgreSQL PL/pgSQL functions. Support to PostgreSQL 11 functions and procedures was added in OmniDB 2.11.0. Last week we released OmniDB 2.12.0 with nice new features and a new revamped visual, so I'm going to show you how OmniDB 2.12.0 can debug PostgreSQL 11 procedures. First of all, if you have not done that already, download and install a binary PostgreSQL library called omnidb_plugin and enable it in (more…)

PostgreSQL 11: Partitioning Evolution from Postgres 9.6 to 11

2ndQuadrant, David's PlanetPostgreSQL
During the PostgreSQL 11 development cycle an impressive amount of work was done to improve table partitioning.  Table partitioning is a feature that has existed in PostgreSQL for quite a long time, but it really wasn't until version 10 that it started to become a highly useful feature. We'd previously claimed that table inheritance was our implementation of partitioning, which was true.  It just left you to do much of the work yourself manually. For example, during INSERTs, if you wanted the tuples to make it to your partitions then you had to set up triggers to do that for you. Inheritance partitioning was also slow and hard to develop additional features on top of. In PostgreSQL 10 we saw the birth of "Declarative Partitioning", a feature which is designed to solve many of the (more…)

Adding new table columns with default values in PostgreSQL 11

2ndQuadrant, Andrew's PlanetPostgreSQL
In PostgreSQL version 10 or less, if you add a new column to a table without specifying a default value then no change is made to the actual values stored. Any existing row will just fill in a NULL for that column. But if you specify a default value, the entire table gets rewritten with the default value filled in on every row. That rewriting behavior changes in PostgreSQL 11. In a new feature I worked on and committed, the default value is just stored in the catalog and used where needed in rows existing at the time the change was made. New rows, and new versions of existing rows, are written with the default value in place, as happens now. Any row that doesn't have that column must have existed before the table change was made, and uses this value stored in the catalog when the row is (more…)

PG Phriday: Studying Stored Procedures in Postgres 11

2ndQuadrant, Shaun's PlanetPostgreSQL
Studying Stored Procs in Postgres 11 With Postgres 11 looming on the near horizon, it's only appropriate to check out a recent beta and kick the tires a few times. Whether it's improvements in parallelism, partitions, stored procedures, JIT functionality, or any number of elements in the release page, there's a lot to investigate. It just so happens that I ran across a fortuitous event on Twitter when deciding on an appropriate topic. Behold! Wait! No! That's not what stored procedures are for! I felt so good like anything was possible When confronted with such a blatant corruption of such a cool new feature, it's only natural to question the wisdom of doing so. It is, after all, not a great idea to programatically consume transaction IDs. I said as much and moved on with life (more…)

Webinar : New Features in PostgreSQL 11 [Follow Up]

Liaqat's PlanetPostgreSQL, PostgreSQL
PostgreSQL 11, the next major release of the world’s most advanced open source database, is just around the corner. The new release of PostgreSQL will include enhancements in partitioning, parallelism, SQL stored procedures and much more. To give PostgreSQL enthusiasts a deeper look into the upcoming release, 2ndQuadrant hosted a Webinar discussing the new features in PostgreSQL 11. The webinar was presented by Peter Eisentraut, Core Team Member and Major PostgreSQL Contributor. If you weren’t able to make it to the live session, you can now view the recording here. Questions that Peter couldn’t respond to during the live webinar have been answered below. Q: Could you use a custom hash function for partitioning? (or is this in future plans) A: This is currently not (more…)

PostgreSQL 11: Patch Reviewers for Partitioning Patches

Alvaro's PlanetPostgreSQL, PostgreSQL
We seldom credit patch reviewers. I decided to pay a little homage to those silent heroes for a few of them: here's the list of people who were credited as having reviewed the patches mentioned in my previous article for PostgreSQL 11. The number in front is the number of times they were credited as reviewers. 7 Amit Langote, Robert Haas 5 Dilip Kumar, Jesper Pedersen, Rajkumar Raghuwanshi 4 Peter Eisentraut 3 Amul Sul, David Rowley, Rafia Sabih, Simon Riggs, Thomas Munro 2 Antonin Houska, Ashutosh Bapat, Kyotaro Horiguchi 1 Álvaro Herrera, Amit Kapila, Amit Khandekar, Etsuro Fujita, Jaime Casanova, Keith Fiske, Konstantin Knizhnik, Pascal Legrand, Pavan Deolasee, Rajkumar Raghuanshi, Rushabh Lathia, Sven Kunze, Thom Brown, Yugo Nagata (more…)

Partitioning Improvements in PostgreSQL 11

Alvaro's PlanetPostgreSQL, PostgreSQL
A partitioning system in PostgreSQL was first added in PostgreSQL 8.1 by 2ndQuadrant founder Simon Riggs. It was based on relation inheritance and used a novel technique to exclude tables from being scanned by a query, called “constraint exclusion”. While it was a huge step forward at the time, it is nowadays seen as cumbersome to use as well as slow, and thus needing replacement. In version 10, it was replaced thanks to heroic efforts by Amit Langote with modern-style “declarative partitioning”. This new tech meant you no longer needed to write code manually to route tuples to their correct partitions, and no longer needed to manually declare correct constraints for each partition: the system did those things automatically for you. Sadly, in PostgreSQL 10 that's pretty much (more…)

Upgrading to PostgreSQL 11 with Logical Replication

2ndQuadrant, Eisentraut's PlanetPostgreSQL, PostgreSQL
It's time. About a year ago, we published PostgreSQL 10 with support for native logical replication. One of the uses of logical replication is to allow low- or no-downtime upgrading between PostgreSQL major versions. Until now, PostgreSQL 10 was the only PostgreSQL release with native logical replication, so there weren't many opportunities for upgrading in this way. (Logical replication can also be used for moving data between instances on different operating systems or CPU architectures or with different low-level configuration settings such as block size or locale -- sidegrading if you will.) Now that PostgreSQL 11 is near, there will be more reasons to make use of this functionality. Let's first compare the three main ways to upgrade a PostgreSQL installation: pg_dump and (more…)

Talk slides: Partitioning Improvements in PostgreSQL 11

Alvaro's PlanetPostgreSQL, PostgreSQL
I spent a couple of days in São Paulo, Brazil last week, for the top-notch PGConf.Brazil 2018 experience. This year I gave a talk about improvements in the declarative partitioning area in the upcoming PostgreSQL 11 release — a huge step forward from what PostgreSQL 10 offers. We have some new features, some DDL handling enhancements, and some performance improvements, all worth checking out. I'm told that the organization is going to publish video recordings at some point; for the time being, here's my talk slides. I'm very happy that they invited me to talk once again in Brazil. I had a great time there, even if they won't allow me to give my talk in Spanish! Like every time I go there, I regret it once it's time to come home, because it's so easy to feel at home with the (more…)

Partition Elimination in PostgreSQL 11

2ndQuadrant, David's PlanetPostgreSQL
The feature freeze for the PostgreSQL 11 release is now upon us. During the last few days my colleague Álvaro Herrera pushed two changes into the development branch of PostgreSQL: 1. Faster Partition Pruning 2. Partition Pruning at Execution Time These patches aim to improve the performance and usability of the declarative table partitioning feature (added in PostgreSQL 10). Amit Langote wrote the first of these two patches, with some assistance from me. I'm the author of the second patch. This one is based on an original patch by Beena Emerson. Background Internally in PostgreSQL, a partitioned table is made up from a series of individual tables. These tables are all grouped under one common parent partitioned table. Queries being run against the partitioned table need the (more…)