Neues vom PostgreSQL Planet

PostgreSQL Person of the Week Interview with Pavel Stehule: I am from the Czech Republic, and live in my home village in Central Czech. I am an independent developer, consultant and lecturer. My work is 100% related to Postgres - training, solving customer performance issues, writing patches, bug fixing.

Stored procedures are widely used in commercial relational databases. You write most of your application logic in PL/SQL and achieve notable performance gains by pushing this logic into the database. As a result, customers who are looking to migrate from other databases to PostgreSQL usually make heavy use of stored procedures.

Summarizing data against a fixed grid is a common way of preparing data for analysis. Fixed grids have some advantages over natural and administrative boundaries:

pgBackRest is a well-known powerful backup and restore tool. It offers a lot of possibilities.

In this post, we’ll see how to setup a dedicated repository host to backup a PostgreSQL 3-nodes cluster.

The repository host will be called backup-srv and the 3 PostgreSQL nodes in Streaming Replication: pg1-srv, pg2-srv, pg3-srv. All the nodes will be running on CentOS 7.

This week Apple started delivering Macs using their own Apple Silicon chips, starting with a Mac SOC named the M1. M1 uses the ARM instruction set and claims some amazing acceleration for media workloads. I wanted to know how it would do running PostgreSQL, an app that's been running on various ARM systems for years. The results are great!

The PostGIS Team is pleased to release PostGIS 3.0.3.

Best served with PostgreSQL 13.1, and GEOS 3.8.1

pgRouting 3.1.1

Continue Reading by clicking title hyperlink ..

Well, we've already discussed isolation and made a digression regarding the low-level data structure. And we've finally reached the most fascinating thing, that is, row versions (tuples).

This is a guest post from Raúl Marín, a core PostGIS contributor and a former colleague of mine at Carto. Raúl is an amazing systems engineer and has been cruising through the PostGIS code base making things faster and more efficient. You can find the original of this post at his new personal tech blog. – Paul

Happy PostGIS Day everyone!

(for those who don’t know: the third Wednesday of November each year is celebrated as the “GIS Day“, and the-day-after-GIS Day, is, well, the PostGIS Day!)

On 14th of November 2020, Tom Lane committed patch: Provide the OR REPLACE option for CREATE TRIGGER.   This is mostly straightforward. However, we disallow replacing constraint triggers or changing the is-constraint property; perhaps that can be added later, but the complexity versus benefit tradeoff doesn't look very good.   Also, no special thought is … Continue reading "Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER."

The PostGIS Team is pleased to release the third alpha of upcoming PostGIS 3.1.0 release. This version is exposes some of the new performance and feature enhancements in not yet relesed GEOS 3.9 as well as numerous speed enhancements not requiring newer GEOS. Requires GEOS 3.6+ and PostgreSQL 9.6+. To use MVT you will need proto-buf 1.2.1 or higher.

Best served with

PostgreSQL 13.1, GEOS 3.7 or higher is recommended.

I am on the phone with Eric Ridge of ZomboDB and PGX fame. We chat often on the People, Postgres, Data Discord server (yes you should join) and we have unofficial “we are human so we get on the phone” calls about twice a month. The calls are generally about PostgreSQL and the awesome Open Source projects he is building around our famed database. However, on this call I got a question I don’t normally get: how good is your SQL?

On 13rd of November 2020, Tom Lane committed patch: Support negative indexes in split_part().   This provides a handy way to get, say, the last field of the string. Use of a negative index in this way has precedent in the nearby left() and right() functions.   The implementation scans the string twice when N … Continue reading "Waiting for PostgreSQL 14 – Support negative indexes in split_part()."

We recently enabled unattended-upgrades on our Ubuntu 18.04 LTS (Bionic) servers, including our PostgreSQL hosts. By default, unattended-upgrades will ignore PGDG packages (where all of our PostgreSQL packages come from), so I had assumed we wouldn’t have any interruptions due to apt installing updates and triggering a database restart. One evening, however, I received an alert that the nightly backup job had failed. Upon investigation I saw that the PostgreSQL 10 database had been restarted (not ideal in production), and then noticed that it had been upgraded!

Open source developers sometimes have a hard time figuring out what feature to focus on to generate the greatest value for end users. As a result, they will often default to performance.

As you probably know, pg_timetable is the advanced PostgreSQL cron compatible scheduler already released!

plpgsql_check version 1.14.0 was just released and brings some improvement for performance diagnostic.

Thanks a lot to Pavel Stěhule for the awesome plpgsql_check extension and the help for implementing the queryid reporting in v1.14!

plpgsql_check: static code analysis and more

PostgreSQL supports procedural code for many languages, the most popular one probably being plpgsql.

Some of the readers of my blog may be aware that I'm responsible for the PostgreSQL Installers produced by EDB for the PostgreSQL Community. This includes both the Windows and macOS installers, and the now-deprecated Linux installers (use RPMs/DEBs instead)!

PostgreSQL Person of the Week Interview with Markus Wanner: I’m Swiss, living in the German speaking part of Switzerland, together with my wife and our two kids. I turned 40 this year, which just makes me realize that it’s now half my life that I’m somehow involved in the PostgreSQL project.

Say you want to run a query a specific number of times and then stop... trivial right? Not as much as I first thought. Below is a recreation of how I thought this was supposed to work (I've subbed in select now(); for my actual query, as it's a bit more illustrative.) ``` pagila=# select now(); \g 10

now

2020-11-14 17:37:50.694618-05 (1 row)

pagila=# select now();

now

2020-11-14 17:37:56.045518-05 (1 row)

pagila=# \g

now

2020-11-14 17:37:58.133626-05 (1 row)