Neues vom PostgreSQL Planet
Andreas 'ads' Scherbaum: Pavel Stehule
Marco Slot: Making Postgres stored procedures 9X faster in Citus
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.
Stefan Fercot: Combining pgBackRest dedicated repository host and Streaming Replication
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.
Greg Smith: PostgreSQL Benchmarks: Apple ARM M1 MacBook Pro 2020
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!
Regina Obe: PostGIS 3.0.3
The PostGIS Team is pleased to release PostGIS 3.0.3.
Best served with PostgreSQL 13.1, and GEOS 3.8.1
Continue Reading by clicking title hyperlink ..Egor Rogov: MVCC in PostgreSQL — 3. Row Versions
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).
Paul Ramsey: Waiting for Postgis 3.1: Vector tile improvements
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
Michał Mackiewicz: Solving the “3rd arg isn’t within [0,1]” problem in pgRouting
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!)
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.
Regina Obe: PostGIS 3.1.0alpha3
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.
Joshua Drake: IS OF
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?
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 14 – Support negative indexes in split_part().
Don Seiler: Unattended Upgrades, Ubuntu 18.04, and PostgreSQL 10: The Perfect Storm
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!
Paul Ramsey: Waiting for PostGIS 3.1: Performance
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.
Pavlo Golub: pg_timetable v3 is out!
As you probably know, pg_timetable is the advanced PostgreSQL cron compatible scheduler already released!
Julien Rouhaud: Queryid reporting in plpgsql_check
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 morePostgreSQL supports procedural code for many languages, the most popular one probably being plpgsql.
Andreas 'ads' Scherbaum: Markus Wanner
Robert Treat: Do This 10 Times And Stop... In Postgres
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
now2020-11-14 17:37:50.694618-05 (1 row)
pagila=# select now();
now2020-11-14 17:37:56.045518-05 (1 row)
pagila=# \g
now2020-11-14 17:37:58.133626-05 (1 row)