Neues vom PostgreSQL Planet

Another calendar year draws to an end, so this will be the last PgPedia Week of 2024.

PostgreSQL 18 changes

As always, the end-of-year holidays mean commit log activity is lower than usual, but we did see one new potential performance improvement, with commit 58a359e5 (" Speedup tuple deformation with additional function inlining ") promising query performance increases of around 5-20% in deform-heavy OLAP-type workloads.

more...

2024 was a transformative year for CloudNativePG, marked by significant contributions to the Kubernetes ecosystem, increased adoption, and a growing community. This article reflects on key milestones, including the integration of advanced Kubernetes features, conference highlights, and personal insights, while looking ahead to the opportunities awaiting PostgreSQL in the cloud-native era.

I can’t get through a zoom call, a conference talk, or an afternoon scroll through LinkedIn without hearing about vectors. Do you feel like the term vector is everywhere this year? It is. Vector actually means several different things and it's confusing. Vector means AI data, GIS locations, digital graphics, and a type of query optimization, and more. The terms and uses are related, sure. They all stem from the same original concept. However their practical applications are quite different. So “Vector” is my choice for this year’s name collision of the year.

CloudNativePG 1.25 enhances control of PostgreSQL synchronous replication with a new dataDurability option, allowing you to choose between prioritising data consistency or self-healing capabilities. This article explains the feature, contrasts it with previous approaches, and provides guidance on migrating to the new API format.

Dear Postgres Community! Happy Holidays! Whatever you celebrate, there are high chances you have several additional days off at the end of the year. That being said, please consider using some of this time to submit a proposal or two or three to PG Day Chicago 2025!

The CfP is open until January 20, but why leave it to the very last minute?! Our amazing CfP committee will work very hard to evaluate all proposals within a very short period of time, but you can make their work easier by not leaving it to the very last minute.

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 17 Windows 64-bit.

To use these, copy the files into your PostgreSQL 17 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

These were compiled against PostgreSQL 17.2 using msys2 / mingw64 and tested against PostgreSQL 17.2 EDB windows distribution.

I am unsure whether others have this problem, but I find myself explaining the benefits9and what’s not)of partitioning tables in Postgres over and over again.

The PostGIS development team is pleased to provide bug fix releases for 3.5.1, 3.4.4, 3.3.8, 3

The year may be winding down, but PostgreSQL development is still ploughing ahead unabated. The pgPedia elves have also been busy and added a bunch of entries for the various advisory locking system functions.

PostgreSQL 18 changes this week libpq PQservice() function added psql variable SERVICE added prompt configuration option %s added various tab completion improvements pg_stat_reset_backend_stats() pg_stat_get_backend_io()

more...

Postgres Conference Seattle 2024 partnered up with PASS this year to present a united database front. They accepted my “Kubernetes Killed the High Availability Star” talk, which I graciously gave on the last day of the conference.

The call for proposals for 2025.pgconf.dev has been extended to January 6, 2025, otherwise known as "very soon". I'm writing this post to encourage you to submit, if you haven't done so yet, regardless of whether you have submitted to 2024.pgconf.dev or its predecessor, PGCon, in the past.

I have been playing around with the powercap framework. I wrote a postgres extension that shows the energy usage of a query. Postgres has a hook mechanism that allows an extension to override the default executor. This implementation is very simple: the extension records the current energy reading when a query starts and then calls the actual executor that runs the query. When the query finishes, a second hook records the current energy reading.

Recently I had a customer come to me with a poorly performing query that had a perfectly fine execution time on Oracle, but once migrated to Postgres the query was slow. Upon investigation, it was found that even though the “WHERE” and “JOIN” clauses had explicitly specified partition keys and were joined on the partition key, “pruning” was only happening on one part of the query.

The setup for the test is at the bottom of the blog post: Jump to Test Case Setup

Federico Campoli and Vik Fearing organised PGDay/MED 2025 in Naples, Italy. Speakers presented Gabriele Quaresima, Ryan Booz, Gianluca Padovani, Patrick Lauer, Stefanie Janine Stölting, Fabiana Farias, Pavlo Golub, Devrim Gündüz.

Prepare for the next great event by PgTraining!

OpenDay 2025 in Bolzano (Italy)

PgTraining is organizing the next year event, namely OpenDay 2025 that will be held on April 11th in Bolzano, Italy.

The event will be totally free but registration is required because the room assigned has a fixed number of seats.

Please note that all the speaks will be in italian.

The event will be held at the NOI Techpark.

Discover how pgroll helps teams manage schema changes with ease in our new Postgres Cafe blog and video series.

In the spirit of the holiday season, I thought I would write a quick post regarding some of my favorite queries that I use on a day to day basis working on Postgres. Some of these queries I have developed and others were found on the internet (hat tip to those who have previously posted) and further refined.

Many more are found on my github site:

https://github.com/shane-borden/sqlScripts/tree/master/postgres

Today I’m excited to introduce pg_incremental, a new open source PostgreSQL extension for automated, incremental, reliable batch processing. This extension helps you create processing pipelines for append-only streams of data, such as IoT / time series / event data workloads.

Notable pg_incremental use cases include:

PostgreSQL comes with a variety of functions that allow you to group rows into a “window” and perform calculations on that window. By using these functions, you can create more advanced and efficient queries for analyzing your database.

Version 17 of PostgreSQL has been released for a while. One of the many features is a change by Tom Lane called “Rearrange pg_dump’s handling of large objects for better efficiency”. In the past, we have seen our customers have several problems with a large number of large objects being a performance issue for dump/restore.