Neues vom PostgreSQL Planet

I recently needed to create a GiST index on an integer array column for performance comparison purposes. I expected it to be easy: — If you’re here, you’re probably wondering “What in Sam Hill is an ‘operator class'”? An operator manipulates data in some way. You’re likely already familiar with arithmetic operators, e.g. ‘+’, ‘-‘, […]

This week the PostgreSQL pets are two lovely dogs, Gustaw, Stefan that live with a mean parrot. They are presented by Alicja Kucharczyk.

The PostgreSQL Global Development group has released the latest update to the community, crushing 40 bugs that had been previously disclosed. Critical security vulnerability CVE-2022-2625 has been announced and an associated remediation has been included in this quarter’s release.

Checking how well a Postgres cluster running in Kubernetes tolerates failures

So far we have covered query execution stages, statistics, sequential and index scan, and have moved on to joins.

The previous article focused on the nested loop join, and in this one I will explain the hash join. I will also briefly mention group-bys and distincs.

In a world where everything is stored in git following IaC (infrastructure as code) you may want the same from your database. For many following this style of engineering modernization we see a focus on IaC and K8s. We have many users standardizing on our PGO Kubernetes Operator to help. But following an IaC approach doesn’t mean you always want to manage your database and be in Kubernetes.

PostgreSQL Person of the Week Interview with Adam Wright: I grew up as an Army brat - someone who moves from base to base. I am settled with my family now and live in a coastal town between Boston and Cape Cod.

The PostGIS Team is pleased to release PostGIS 3.3.0rc1! Best Served with PostgreSQL 15 beta2 ,GEOS 3.11.0 , and SFCGAL 1.4.1

Lower versions of the aforementioned dependencies will not have all new features.

This release supports PostgreSQL 11-15.

3.3.0rc1

Introduction

Network File System (NFS) is a distributed file system protocol that allows a user on a client node to access files residing on a server node over network much like local storage is accessed. Today in this blog, I will share how to set up both NFSv4 server and client on CentOS7 and run PG on it.

NFS Server

First, install the NFS server components by

$ yum install nfs-utils

This will install nfs process on the server machine in which we can go ahead to enable and start the NFS server

This week the PostgreSQL pet is Milo the ginger cat adopted by Dave Page.

On Monday 11th July the pgAdmin Development Team opened the first pgAdmin user survey which we then ran for three weeks, closing it on Monday 1st August. The aim of the survey was to help us understand how users are using pgAdmin to help us shape and focus our future development efforts.

We had a fantastic response with 278 people taking the time to complete the survey - far exceeding our expectations. Responses were generally positive as well, with a number of people expressing their appreciation for the work of the development team, which is always nice to hear.

PostgreSQL Person of the Week Interview with Beena Emerson: I am an Indian who grew up in Mumbai and now settled near Chennai.

The PostgreSQL Performance Puzzle was, perhaps, too easy – it didn’t take long for someone to guess the correct answer!

But I didn’t see much discussion about why the difference or what was happening. My emphasis on the magnitude of the difference was a tip-off that there’s much more than meets the eye with this puzzle challenge – and one reason I published it is that I’d looked and I thought there were some very interesting things happening beneath the surface.

The PostgreSQL pets of the week are Comet and Shadow, two lovely dogs adopted by Karen Jex.

If you’ve been running PostgreSQL for a while, you’ve heard about autovacuum. Yes, autovacuum, the thing which everybody asks you not to turn off, which is supposed to keep your database clean and reduce bloat automatically.

I just released Pagila 3.0.0, with some new shiny features: Continue reading "Pagila 3.0.0 is out with shiny new features and bugfixes"

Rocky Linux 9 is finally out. Some repo names changed, so here is a short guide about installing PostgreSQL and PostGIS on Rocky Linux 9: Continue reading "How to install PostgreSQL and PostGIS on Rocky Linux 9"

The State of PostgreSQL 2022 survey closed a few weeks ago, and we're hard at work cleaning and analyzing the data to provide the best insights we can for the PostgreSQL community.

In the database community, however, there are usually two things that drive lots of discussion year after year: performance and tooling. During this year's survey, we modified the questions slightly so that we could focus on three specific use cases and the PostgreSQL tools that the community finds most helpful for each: querying and administration, development, and data visualization.

We all know and value SQL functions as a handy shortcut. PostgreSQL v14 has introduced a new, better way to write SQL functions. This article will show the advantages of the new syntax.

An example of an SQL function

Let’s create a simple example of an SQL function with the “classical” syntax so that we have some material for demonstrations:

CREATE EXTENSION unaccent; CREATE FUNCTION mangle(t text) RETURNS text LANGUAGE sql AS 'SELECT lower(unaccent(t))';

You can use the new function like other database functions:

Around this time of year, I am reading through the upcoming PostgreSQL release notes (hello PostgreSQL 15), reading mailing lists, and talking to PostgreSQL users to understand what are the impactful features.