Neues vom PostgreSQL Planet

Today we released Citus 11.0 beta, which is our first ever beta release of the Citus open source extension to Postgres. The reason we are releasing a beta version of 11.0 is that we are introducing a few fundamentally new capabilities, and we would like to get feedback from those of you who use Citus before we release Citus 11.0 to the world.

Working with databases one can’t help but wonder what happens with data stored on the disk.
For example you need to know this to be able to save at least some of the information on damaged data blocks with help of hexedit.
Today we’ll try to find out how data types such as integer are kept on disk. To do that we will create a testing table, review its contents using hexedit and change data on disk. For the sake of this exercise I will be using PostgreSQL 14.

Let’s create a testing table:

The pgAdmin 4 browser tree is a crucial component from the UI perspective as it lists all the database server objects and all other functionality relies on it. As it lists thousands of objects, the performance of the tree was a concern for the team. In 2020, we decided to move from Backbone to React, so after doing some R & D work, we came to the conclusion to implement the browser tree from scratch using the React Aspen Tree package.

On 22nd of March 2022, Dean Rasheed committed patch: Add support for security invoker views.   A security invoker view checks permissions for accessing its underlying base relations using the privileges of the user of the view, rather than the privileges of the view owner. Additionally, if any of the base relations are tables with … Continue reading "Waiting for PostgreSQL 15 – Add support for security invoker views."

© Laurenz Albe 2022

Recently, I could help solve a “mysterious” performance problem for a customer. The problem turned out to be a badly chosen query parameter type, which led to poor query performance. After working on this customer’s case, I realized that the meaning of the data type of a query parameter is not universally understood, which is why I decided to write this article.

What are query parameters?

In SQL, we often encounter queries that only differ in constant values, for example

This blogpost is about a linux feature to introduce delays in sending packets on the network.

A first question obviously is: why would you want this? Well, for several reasons actually.

PostgreSQL Person of the Week Interview with Michael Christofides: Hi, I’m Michael Christofides. I have lived in the UK my whole life, in Essex, Nottingham, Cambridge, London, and now, with my partner Lauren, in Guildford. My surname is Cypriot, but I’m also part German, French, and English. I consider myself European, even if my country doesn’t (sorry). Work-wise, my background is mostly in product management.

Tuning of the max_wal_size configuration parameter in PostgreSQL can have a profound effect on performance, but it's often overlooked or not given the priority it deserves in favour of other parameters, such as those that are easier to understand. In this blog we'll discuss what the parameter does, how to tune it, and how to monitor the system to ensure the tuning is effective. [Continue reading...]

First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu.

This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG.

Prerequisites

This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers. 

1. Introduction

If you are into distributed database research, especially one that is setup using Foreign Data Wrapper (FDW) + partitioned foreign tables, you probably have heard that there are many potential issues associated with this setup. Atomic commit, atomic visibility and global deadlock detection are one of the most popular issues that one can encounter in a distributed database environment.

Since transactions and replication within Postgres are essential to all types of availability, that means keeping the transaction ID state healthy. This Phriday we’ll cover the best configuration settings for avoiding this and take a look at what kind of monitoring we can employ as an early warning system. Don’t let the prospect of Postgres XID wraparound make you dizzy! [Continue reading...]

Just got information that PGDay.PL is postponed till 4th quarter of 2022. This is due to situation in Ukraine. Figured I'll let you know, given that I previously wrote about it.

Recently I added consul and haproxy packages to PostgreSQL RPM repository to support Patroni installations. The main motivation is to provide latest and greatest versions of these packages. However, these packages may break existing installations, like HAProxy 1.8.4 RPM on RHEL 8, where we provide 2.5.4 as of now.

So, in order not to break some users' environment, I added new repos:

Photo from PxHere

Recently I started learning to tune pianos. There are many techniques and variations, but the traditional method, and the one apparently most accepted by ardent piano tuning purists, involves tuning one note to a reference, tuning several other notes in relation to the first, and testing the results by listening closely to different combinations of notes.

pg_featureserv provides access to the powerful spatial database capabilities of PostGIS and PostgreSQL via a lightweight web service. To do this, it implements the OGC API for Features (OAPIF) RESTful protocol.

Avoid vendor lock-in, provide a private Database-as-a-Service for internal teams, quickly deploy-test-destroy databases with CI/CD pipeline – these are some of the most common use cases for running databases on Kubernetes with operators. Percona Distribution for PostgreSQL Operator enables users to do exactly that and more.

© Laurenz Albe 2022

It is known that high network latency is bad for database performance. PostgreSQL v14 has introduced “pipeline mode” for the libpq C API, which is particularly useful to get decent performance over high-latency network connections. If you are using a hosted database in “the cloud”, then this article might be interesting for you.

PostgreSQL Person of the Week Interview with Jaime Casanova: My name is Jaime Casanova and I’m starting my 40’s. I’m from Ecuador, a little country in South America, a nice place to visit with beaches and highlands, jungles and big cities too… especially I will recommend anyone to visit the Galapagos Islands. I’m also a Chilean citizen because of my dad and have a little percentage of Italian blood because of my mom’s grandfather.

We have submitted TOAST API for PG15.

Below is a performance analysis of JSONB, which uses JSONB TOASTER implemented using TOAST API.

Each jsonb looks like: key1, looong key2[], key3, loong key4[]. Check slide #10 of our talk at pgvision 2021 for description of data and queries used in benchmarks.

Autovacuum was added LONG time ago (in 7.4, as pg_autovacuum). Since then, there were many changes related to it. These days, hopefully, we no longer see someone saying that they have to disable autovacuum due to performance issues. But I still see people that say that they have to run daily/weekly vacuum because “autovacuum is … Continue reading "Is my autovacuum configured properly?"