Neues vom PostgreSQL Planet

In April 2023, I submitted my proposal for a full-day pre-conference at PASS 2023. My chosen topic was focused on PostGIS, titled GIS Data, Queries, and Performance. A key part of my submission was that the session would be an interactive, follow-along type design. Julie and I believe that doing is key to learning so we wanted to enforce that as much as possible.

A new feature of pgagroal-cli that now makes another step towards the full automation.

pgagroal-cli gains JSON output

At last, I made it: a commit in pgagroal to support JSON output. It has been quite hard and long, not for the technological challenge, rather for all the little details like continuos integration, to get this work completed. As a rule of thumb, I stated this work last November (of course, slowly working in and out).

1. Overview

In my previous blog, I discussed how to quickly set up a TLS connection between a PostgreSQL server and a psql client. In this blog, I will guide you through the process of setting up a TLS connection using OCSP Stapling, which can help improve the security of PostgreSQL.

2. What is OCSP Stapling?

Before diving into how to enable OCSP Stapling with PostgreSQL, we need to understand some basic concepts:

Strangely quiet around here

My colleague Bob Pacheco asked me to help with a strange problem he was witnessing for a client. A new Postgres cluster was getting created on a Kubernetes node, but it refused to start.

Everyone knows FOSDEM, right?
The PostgreSQL Project has been present there with a booth and a Devroom since more than 15 years now!
And this year there was the 10th FOSDEM PGDay organized on the Friday before FOSDEM.

I had the chance to spend 3 days with awesome #PostgresFriends and even more free and open source software aficionados.
Here’s a short writing of how I lived those exhausting but very exciting days.

I wrote very simple extension pgmeminfo for PostgreSQL12+, that should to help with investigation of memory usage by Postgres. Function pgmeminfo returns glibc information about memory usage provided by function mallinfo(). It is much more precious than usage top or htop.

After the craziness of the FOSDEM weekend in Brussels (FOSDEM PGDay, and the PostgreSQL booth and Devroom at FOSDEM itself) I headed to Paris for

PostgreSQL 16 introduces quite a few improvements to the query planner and makes many SQL queries run faster than they did on previous versions of PostgreSQL.

If you look at the PG16 release notes, you’ll see some of these planner improvements. But with the volume of changes made in each PostgreSQL release, it’s not possible to provide enough detail about each and every change. So maybe you might need a bit more detail to know what the change is about—before you understand if it’s relevant to you.

On 16th of January 2024, Alexander Korotkov committed patch: Add new COPY option SAVE_ERROR_TO   Currently, when source data contains unexpected data regarding data type or range, the entire COPY fails. However, in some cases, such data can be ignored and just copying normal data is preferable.

On 16th of January 2024, Peter Eisentraut committed patch: Support identity columns in partitioned tables   Previously, identity columns were disallowed on partitioned tables. (The reason was mainly that no one had gotten around to working through all the details to make it work.) This makes it work now.   Some details on the behavior: … Continue reading "Waiting for PostgreSQL 17 – Support identity columns in partitioned tables"

On February 6th, 2024, we had the PostgreSQL February Meetup in Berlin.

FOSDEM 2024 and I was there!

The programme for pgconf.dev in Vancouver (May 28-31) has been selected, the speakers have been notified, and the whole thing should be posted on the web site relatively soon.

I have been on programme committees a number of times, but for regional and international FOSS4G events, never for a PostgreSQL event, and the parameters were notably different.

A new command in the pgenv script.

pgenv gains a new command (and contributor!)

[pgenv](https://github.com/theory/pgenv){:target="_blank"} , the PostgreSQL binary manager written as a Bourne Again Shell script, has gained a new command: status.

Once again, Postgres has been elected DBMS of the Year by DB-Engines. Although this is only a ranking, it gives a trend. It has also been recognized for several years in Stackoverflow surveys : Most popular Databases.

Three weeks ago, we wrote a blog comparing pg_vector and lantern on index creation and search speed, and also mentioned that this is a fast evolving space with great momentum.

Just last week, we saw the release of Pgvector v0.6.0, which contains improvements in HWSW build time thanks to the ability to build indexes in parallel.

A way to fix some oddity that comes from other databases.

Changing a Column from Integer to Boolean in One Transaction

I was migrating a database from SQLite3 to PostgreSQL, not because the former isn’t good, rather because the latter shines!

The presentation I gave for Chicago PUG in January was a reserved talk for FOSDEM, so I hold of with sharing until after the conference. Now that the conference is over, here is it!

This month’s PGSQL Phriday #015 topic is about UUIDs, hosted by Lætitia Avrot. Lætitia has called for a debate. No, no, no. I say let’s have an all-out war. A benchmark war.

I have decided to orchestrate a benchmark war between four different methods of storing a primary key: