Neues vom PostgreSQL Planet

The PostgreSQL code base has been around for almost 3 decades, and as recent commit 710e6c43 shows, there is still some legacy cruft from the initial import/conversion in need of cleanup:

Remove unneeded casts of BufferGetPage() result BufferGetPage() already returns type Page, so casting it to Page doesn't achieve anything. A sizable number of call sites does this casting; remove that. This was already done inconsistently in the code in the first import in 1996 (but didn't exist in the pre-1995 code), and it was then apparently just copied around.

Unsung Heros of Postgres : Episode I

Not all heroes wear capes. In PostgreSQL, some don’t even write code.

At PGDay Austria, Floor Drees highlighted this truth: countless contributions to PostgreSQL happen outside of code commits and patches.

How many people/services have superuser access to your PostgreSQL cluster(s)? Did you ever ask why your software engineers might need it? Or your BI team? Why those use cases require same privileges as someone who can drop your databases?

The answer isn't because these operations are inherently dangerous - it's because PostgreSQL historically offered limited options for operational access or simply because not enough people are aware of the options. So the common practice is to either got basic permissions or handover the keys to the kingdom.

Next month, I'll be hosting 2 or 3 discussions of Thomas Munro's talk, Investigating Multithreaded PostgreSQL, given at 2025.pgconf.dev (talk description here).

Postgres 18 will be released in just a couple weeks! Here’s some details on the most important and exciting features.

Asynchronous i/o

Postgres 18 is adding asynchronous i/o. This means faster reads for many use cases. This is also part of a bigger series of performance improvements planned for future Postgres, part of which may be multi-threading. Expect to see more on this in coming versions.

What is async I/O?

It's been a while since the last performance check of Transparent Data Encryption (TDE) in Cybertec's PGEE distribution - that was in 2016. Of course, the question is still interesting, so I did some benchmarks.

My trip to pgday.at started Wednesday at the airport in Düsseldorf. I was there on time, and the plane started with an estimated flight time of about 90 minutes. About half an hour into the flight, the captain announced that we would be landing in 30 minutes - in Düsseldorf, because of some unspecified technical problems. Three hours after the original departure time, the plane made another attempt, and we made it to Vienna.

Learn how pgstream v0.8.1 transforms hstore data and improves snapshot experience with roles snapshotting and excludedtables option

This article has been published with explicit permission from Jesús Espino. It's a copy of a post originally published on his LinkedIn account on September 16, 2024.

It is that time of the year again. The first release candidate of PostgreSQL 18 is out, and things look promising. We should expect General Availability in the next 2-4 weeks. 

Exciting times! 

Over the past many years and as many releases, the PostgreSQL community has done a phenomenal job of being disciplined about the annual release process. And we have done so averaging 150+ new features with each release!

For the upcoming v18, here are the top three features I am most excited about:

Introduction

Recently, I read Laurenz Albe's blog about case insensitive string search. He recommended case insensitive collations saying, "it won't be any more expensive (and maybe more intuitive) than the current techniques of using upper() or the citext extension are today". In other words, a custom collation can be used instead of citext to achieve case insensivity with a lower performance penalty.

In November last year after nearly two decades at my previous gig, I came to the conclusion that I didn’t want to work at what seemed to be rapidly becoming an AI-focused company and moved to pgEdge where the focus is well and truly on distributed PostgreSQL and Postgres generally. Distributed databases (and particularly Postgres of course) have always been a passion of mine – even being a key topic of my master’s dissertation many years ago.Moving to pgEdge was a breath of fresh air.

Case-insensitive search is one of the most common issues I encounter when helping customers migrate from Oracle or SQL Server to PostgreSQL. Unlike Oracle (with NLS_SORT) or SQL Server (with case-insensitive collations), PostgreSQL does not natively support case-insensitive search. Developers typically rely on workarounds such as:

Developers typically rely on workarounds such as:

PGDay Austria took place on September 4 in Vienna.

Organised by

• Patricia Horvath
• Ilya Kosmodemiansky
• Hans-Jürgen Schönig
• Cornelia Biascics

CfP Committee

• Rafia Sabih
• Oleksii Vasiliev
• Ryan Booz
• Pavlo Golub (non voting member)

Speakers

TPC-C is supposedly the most objective performance measurement of OLTP database systems… and I’ve used it quite a fair bit over the years as well…yet, running it on Postgres is sadly not exactly as smooth as things typically are with Postgres 😔 If to compare at least with the wonderful...