Neues vom PostgreSQL Planet

Someone recently asked on the Postgres mailing lists about how to remove unwanted duplicate rows from their table. They are “unwanted” in that sense that the same value appears more than once in a column designated as a primary key. We’ve seen an uptick in this problem since glibc was kind enough to change the way they sorted things. This can lead to invalid indexes when one upgrades their OS and modifies the underlying glibc library.

PostgreSQL is equivalent to a Swiss Army Knife in the database world. There are things in PostgreSQL that are very simple to use, while in another database, they take many more steps to accomplish. But sometimes, the knife has too many blades, which can cause confusion. This is one of those cases. COPY and \COPY I needed a quick dump of a table as part of an ETL (Extract, Transform, and Load - official definition, Exorcism, Trauma, and Lost-sleep - unofficially) to port some data.

Introduction

regexp_matches() and regexp_match() are two similar string functions that support regular expression matching directly in the PostgreSQL database.

We're happy to announce that the Call for Papers and Call for Sponsors is open for PGDay UK 2025!

Call for Papers is open from today until May 12, 2025 at 23:59:59 in London, UK. Selected speakers will be notified before May 26, 2025. We accept proposals from both established and new speakers on a range of PostgreSQL related topics. You don't need to have the talk written beforehand, we just need an abstract of your talk, so don't hold back and tell your friends. Find out more: https://2025.pgday.uk/call-for-papers/ .

Citus is in a small class of the most advanced Postgres extensions that exist. While there are many Postgres extensions out there, few have as many hooks into Postgres or change the storage and query behavior in such a dramatic way. Most that come to Citus have very wrong assumptions. Citus turns Postgres into a sharded, distributed, horizontally scalable database (that's a mouthful), but it does so for very specific purposes.

Citus, in general, is fit for these type of applications and only these type:

PostgreSQL is the foundation for thousands and thousands of applications. The system has long proven its worth and is operating reliably. However, the question people often ask is: What is the best way to actually debug a database application? 

For a couple of years now, I have been using a simple method that greatly speeds up the debugging process of typical applications.

Fujitsu has participated in the PostgreSQL community for over 20 years and has contributed to PostgreSQL development by developing new features, as well as creating and reviewing correction patches. And last year we took our team contributions to a whole new level.

PostgreSQL Person of the Week Interview with Emma Saroyan: I’m Emma Saroyan, a Developer Advocate from Yerevan, Armenia. Since graduating from university, I’ve been working with tech startups and actively contributing to open-source communities by mentoring, giving talks, and organising meetups.

CommitFest 52 (2025-03), the final CommitFest in the PostgreSQL 18 development cycle, is past the halfway stage, and this week has seen a number of new features.

I was recently looking into some workloads that generate a lot of I/O and CPU contention on some very high-write code paths and came across synchronous_commit (https://www.postgresql.org/docs/current/wal-async-commit.html). It can be very tempting to turn this off globally because the performance gains in terms of I/O, CPU, and TPS (transactions per second) are very hard to overlook. I noticed I/O completely gone, CPU down 20% (at peak), and a 30% increase in TPS.

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated. But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive?

On March 6 and 7, PostgreSQL@SCaLE22x took place in Pasadena, California. The PostgreSQL team organized and volunteered at the event, with PGUS hosting the booth. The list of involved volunteers:

Now – to the afternoon sessions (see Part I for morning sessions)

What's your favourite infinite sequence of non-repeating digits? There are some people who make a case for e, but to my mind nothing beats the transcendental and curvy utility of π, the ratio of a circle's circumference to its diameter.

Drawing circles is a simple thing to do in PostGIS -- take a point, and buffer it. The result is circular, and we can calculate an estimate of pi just by measuring the perimeter of the unit circle.

SELECT ST_Buffer('POINT(0 0)', 1.0);

This option didn’t seem super widely documented from my initial searches online; it should be able to basically enforce layer 4 ingress/firewall rules at the individual service level. This is a quick test to check if it works.

"PostgreSQL scales" - we have all heard this phrase over and over again. However, the question is: What does this actually mean? Does it mean 1 million rows? Or maybe even 1 billion rows? So, on a rainy weekend, I decided to do a little experiment to figure out if it is possible to squeeze 1 trillion rows (= 1000 billion rows) into my local personal computer.

For those of you who don't believe this to be possible - here is proof:

On 5th of March 2025, Andrew Dunstan committed patch: Allow json{b}_strip_nulls to remove null array elements   An additional paramater ("strip_in_arrays") is added to these functions. It defaults to false. If true, then null array elements are removed as well as null valued object fields. JSON that just consists of a single null is not … Continue reading "Waiting for PostgreSQL 18 – Allow json{b}_strip_nulls to remove null array elements"

Hi Postgres community, apologies for the delay one more time – here is the recording of our February meetup! If you didn’t have a chance to attend in person or virtual, please take a moment to watch! You won’t be disappointed!

Also, one more reminder that our March meetup will take place later in the month than usual: on March 25. That will be the last meetup before PG Day Chicago, and I hope you will be able to attend!

On 3rd of March 2025, Tomas Vondra committed patch: Allow parallel CREATE INDEX for GIN indexes   Allow using parallel workers to build a GIN index, similarly to BTREE and BRIN. For large tables this may result in significant speedup when the build is CPU-bound.   The work is divided so that each worker builds … Continue reading "Waiting for PostgreSQL 18 – Allow parallel CREATE INDEX for GIN indexes"

© Laurenz Albe 2025

I have worked with PostgreSQL since 2006. Still, I keep learning about new aspects of PostgreSQL regularly. Sometimes it feels like the more I know, the better I see that I have yet a lot to learn. Often, beginners' questions in a training session make me learn something new! Today, my co-worker Pavlo told be about the PostgreSQL parameter stats_fetch_consistency. Probably not a parameter that you want to tune every day, but you never know.