Neues vom PostgreSQL Planet

We recently announced the release of version 5.0 of  PGO, the open source Postgres Operator from Crunchy Data.

LATERAL joins are one of the lesser-known features of PostgreSQL and other relational databases such as Oracle, DB2 and MS SQL. However, LATERAL joins are a really useful feature, and it makes sense to take a look at what you can accomplish with them.

Inspecting FROM more closely

Before we dive into LATERAL, it makes sense to sit back and think about SELECT and FROM clauses in SQL on a more philosophical level. Here is an example:

pgpool-II is a well-known tool to pool PostgreSQL connections and load-balance work loads. In this blog, we will verify whether pgpool works well on ARM64 architecture.

ARM64 packages

On Ubuntu, pgpool2 ARM64 debian package is made available by the Debian PostgreSQL Maintainers :

$ dpkg -s pgpool2
Maintainer: Debian PostgreSQL Maintainers
Architecture: arm64

We're excited to announce the release of PGO 5.0, the open source Postgres Operator from Crunchy Data.

Intro to Memory Management Most software can not predict the exact memory needs during compile time, so memory is dynamically allocated during run time. When a programm needs more memory it requests a new chunk of memory from the operating system. The OS can either grant or deny the request. Most modern software does not always care very much about the amount that is actually needed and tries to get a lot more than is currently necessary - just to have some spare for bad times.

Suppose that you want to be completely over-the-top paranoid about making sure that when you execute some particular SQL statement on your Postgres database, you’re doing it in the safest and least risky way?

For example, suppose it’s the production database behind your successful startup’s main commercial website. If anything even causes queries to block/pause for a few minutes then people will quickly be tweeting about how they can’t place orders and it hurt both your company’s revenue and reputation.

On some rare occasions, I’ve been asked if Postgres supports system-versioned temporal tables – in other words, time travel on table data for your queries. As quite often with Postgres, thanks to its extensibility, I can answer: well, officially, it does not…but, you can make it work, nevertheless

Just a quick note to say that PGXN Manager has been updated with more secure password storage. Existing passwords are unmodified, but the next time you change your password, it will be upgraded to a more robust password hashing algorithm that’s more resisant to attacks. I recommend everyone update their passwords. If you have a PGXN Manager account, just hit password reset, enter your username or email address, and check your mail for a reset link.

PostgreSQL Person of the Week Interview with Daniel Westermann: I grew up in Germany exactly at the border to Switzerland and I am still living in this area. For my entire life I have been enjoying the region where France, Switzerland and Germany come together. Today I live very close to the city of Basel, but still in Germany.

Materialized views in Postgres are a handy way to persist the result of a query to disk. This is helpful when the underlying query is expensive and slow yet high performance SELECT queries are required. When using materialized views they need to be explicitly refreshed to show changes to the underlying table. This is done through the REFRESH MATERIALIZED VIEW <name>; syntax.

I just released first version of change to explain.depesz.com that displays buffer I/O information, as described recently. You can see it in here. There are two new columns in there, showing how much data given node read from disk, and how much it wrote. It's definitely not 100% OK now, as you can see, speed … Continue reading "Buffers I/O information on explain.depesz.com"

In my previous post, I described how to use multi-cluster architecture provided by Crunchy PostgreSQL Operator to achieve disaster recovery and high availability on Kubernetes.

If you have a large PostgreSQL database that runs on a single node, eventually the single node’s resources—such as memory, CPU, and disk—may deliver query responses that are too slow. That is when you may want to use the Citus extension to Postgres to distribute your tables across a cluster of Postgres nodes.

PostgreSQL includes robust security standards around all aspects such as authentication, authorization, accounting and auditing. User management and role based segregation that satisfy enterprise standards can be implemented at ease. Similarly, organizations may be willing to enforce certain compliance around the password standards such as the minimum upper case characters or minimum number of numbers and special characters and the length of the password. Such username password checks are important in an environment where strong security policies are a must.

The PostGIS Team is pleased to release PostGIS 3.1.3! Best Served with PostgreSQL 14 beta2.

This release is a bug fix release, addressing issues found in the previous 3.1 release.

• #4929, Fix missing error from GetRingEdges when invoked with unexistent topology or edge (Sandro Santilli)

It has been 20 Years since i have been maintaining the Ora2Pg project, an Open Source software for Oracle to PostgreSQL migrations. The first version of Ora2Pg released on 9th May, 2001. Since then, there have been several features related to schema conversions and data migrations. Over a period of time, i have witnessed several tens of thousands of migrations using Ora2Pg that also increased the need of several optimizations.

Last time, we discussed object-level locks and in particular relation-level locks. In this article, we will see how row-level locks are organized in PostgreSQL and how they are used together with object-level locks. We will also talk of wait queues and of those who jumps the queue.

Row-level locks Organization

Let's recall a few weighty conclusions of the previous article.

PostGIS setup with Ubuntu 20.04.2

In one of his last blogposts, our CEO Hans-Jürgen Schönig explained how to set up PostgreSQL on Ubuntu. He consciously avoided any discussion of how to deal with our beloved PostGIS extension. Let’s fix that up right now by following these steps to implement PostGIS setup:

Introduction

In the previous post, I showed some performance evaluation of Incremental View Maintenance (IVM) feature that is under development. In this article, I will explain the implementation of IVM and how it works.

Simple Example of Incremental View Maintenance

First, I will explain the process of IVM using a simple example.

PostgreSQL Person of the Week Interview with Stefan Keller: I’m a Professor for Data Engineering since about 20 years and director of Institute for Software at Eastern University of Switzerland. I’m a spatialist; my focus is on spatial data engineering, spatial data analytics and open source, open data and educational resources.