Neues vom PostgreSQL Planet

If you are using PostgreSQL for analytics or large-scale aggregations, you might occasionally notice the planner making false assumptions regarding the number of rows. While this isn't a problem for small aggregates, it is indeed an issue for large-scale aggregations featuring many different dimensions.

In short: The more columns your GROUP BY statement contains, the more likely it is that optimizer overestimates the row count.

This blog explains how this can be handled in PostgreSQL.

One of my favorite uses of JSONB functionality is to unpivot data.

Continue reading "Unpivoting data using JSONB"

The schedule of the free event is available!

Open Day 2025 in Bolzano (Italy): schedule available

The schedule of the next free event organized by PgTraining is available.

The event, that will be held in the great NOI Techpark in Bolzano (Italy) will be organized in two parts:

Not all operations require the same level of locking, and PostgreSQL offers tools and techniques to minimize locking impact.

The PostgreSQL Project is present with a booth at FOSDEM ever since 2007. And ever since 2008 we organize a Devroom, starting 2013 we also have our own PGDay on the Friday before FOSDEM. This year it’s the 11th FOSDEM PGDay, skipping 2021 and 2022 for obvious reasons. This blog post provides useful information for visitors of the PGDay, the booth and the PostgreSQL Devroom at FOSDEM. Welcome to FOSDEM PGDay 2025 When does the PGDay take place, and can I still attend?

That happened one long and warm Thai evening when I read another paper about the re-optimisation technique in which the authors used Postgres as a base for implementation. Since I had nearly finished with the WIP patch aiming to do the same stuff in the Postgres fork, I immediately began comparing our algorithms using the paper's experimental data as a reference. However, I quickly realised that neither my code nor even the standard Postgres instance bore any resemblance to the paper's figures.

PostgreSQL 18 changes pg_stat_io count IOs as bytes instead of blocks for some operations postgres_fdw : SCRAM authentication can be passed through using new option use_scram_passthrough psql x can be appended to various list commands (e.g.

The PostGIS Team is pleased to release PostGIS 3.5.2.

This version requires PostgreSQL 12 - 17, GEOS 3.8 or higher, and Proj 6.1+. To take advantage of all features, GEOS 3.12+ is needed. SFCGAL 1.4+ is needed to enable postgis_sfcgal support. To take advantage of all SFCGAL features, SFCGAL 1.5+ is needed.

Please considering joining us next month (February 2025) for a discussion of Heikki Linnakangas's talk on The Wire Protocol, from PGCONF.EU 2024. For those not familiar with the concept, this hacking workshop is basically a virtual meetup: you watch the talk, and then you sign up to participate in one of two or three Zoom meetings where we discuss the talk. Usually, we're able to get the original author of the talk to join us; thanks to Heikki for agreeing to join us this month.

How we discovered a trivial bug in pgagroal

The importance of testing with not-so-usual setups

This week we found a trivial and silly bug in [pgagroal](https://github.com/agroal/pgagroal){:target="_blank"}.

On 11st of December 2024, David Rowley committed patch: Enable BUFFERS with EXPLAIN ANALYZE by default   The topic of turning EXPLAIN's BUFFERS option on with the ANALYZE option has come up a few times over the past few years. In many ways, doing this seems like a good idea as it may be more … Continue reading "Waiting for PostgreSQL 18 – Enable BUFFERS with EXPLAIN ANALYZE by default"

Cloud costs can quickly spiral out of control if resources are not optimized. One of the most significant contributors to these costs is CPU core allocation, which forms the basis of the instance size with every major cloud provider. Many organizations over-provision cores for their PostgreSQL databases, paying for unused capacity, or under-provision them, leading to poor performance and missed SLAs.

This blog will explore strategies to allocate CPU cores effectively for PostgreSQL databases, ensuring optimal performance while keeping cloud expenses in check.

As I have been on several CfP committees (CfPC) for PostgreSQL conferences, I like to share my experiences.

Submissions

When you submit more than one talk, think about the additional work of the CfPC. Every member has to read almost all submissions and to vote for each one of it. The exceptions are the ones they submitted themselves or the ones submitted by coworkers.

All the members of CfP committees of PostgreSQL community conferences are doing their duty mostly without compensation, sometimes they are offered a free ticket to the conference.

PostgreSQL is one of the most powerful database systems in the world. I have always been passionate about its great power, especially its modern SQL language features. However, that doesn’t mean everything is great. There are areas where it hurts. Novice users unaware of the problem might get into trouble, and I see such things […]

© Laurenz Albe 2024

Catching up with the last weeks of 2024, and entering into 2025, these are the last contributions we were able to know about:

This blog explains locking mechanisms in PostgreSQL, focusing on table-level locks that are required by Data Definition Language (DDL) operations.

2025 is picking up speed, and with CommitFest 2025-01 underway we're seeing some more PostgreSQL 18 changes again. In news closer to home, the PgPedia Week format has been expanded to include links to recent articles on PostgreSQL 18 , other PostgreSQL-related newsletters published in the preceding week, as well as to general PostgreSQL announcements. Oh, and there's also a new-ish section listing commits of interest from 25 years ago , in case anyone wants an occasional trip down memory lane.

On 27th of November 2024, Peter Eisentraut committed patch: Support LIKE with nondeterministic collations   This allows for example using LIKE with case-insensitive collations. There was previously no internal implementation of this, so it was met with a not-supported error. This adds the internal implementation and removes the error.

Your database is configured for the needs of your day-to-day OLTP (online transaction processing) application workload, but what if you need to run analytics queries against your application data? How can you do that without compromising the performance of your application?