Neues vom PostgreSQL Planet

As IT budgets tighten and workloads increase, 2025 is the year to focus on maximizing PostgreSQL efficiency, security, and reliability. Whether you are running fully-managed or self-managed PostgreSQL databases, these three priorities

- Reducing cloud costs
- Increasing data security, and
- Enhancing availability

will be key to staying competitive.

Here is a deep dive into each priority and actionable steps to make them a reality.

1. Reduce Cloud Costs Without Compromising Performance

A couple days ago I wrote about performance improvements on OLTP workloads since Postgres 8.0, released 20 years ago. And I promised to share a similar analysis about analytical workloads in a follow-up post. So here we go ;-) Let me show you some numbers from a TPC-H benchmark, with some basic commentary and thoughts about the future.

I compiled and uploaded zip files with latest orafce and plpgsql_check for PostgreSQL 16 and PostgreSQL 17 - I used Microsoft Visual C 2022.

Setup:

When your function argument types are loosely changed

This article results from a code review I did for a customer.

Our customer created a pg_dump --schema-only of the target database to provide
me with the plpgsql code and database object structures to review. So far
so good.

I started to read the code and then became puzzled. The code looks like this:

PostGIS Day yearly conference sponsored by Crunchy Data is my favorite conference of the year because it's the only conference I get to pig out on PostGIS content and meet fellow passionate PostGIS users pushing the envelop of what is possible with PostGIS and by extension PostgreSQL. Sure FOSS4G conferences do have a lot of PostGIS content, but that content is never quite so front and center as it is on PostGIS day conferences.

I was reviewing some old code when I stumbled across something I must have completely forgotten or someone else some time ago knew. That is that it is possible to use the function substring for regular expression work.

Continue reading "Substring function Regex style"

Sadly, the home of the PGXN Mastodon bot for the last two years, botsin.space is shutting down. I’ve created a new account, @pgxn@mastodon.social and moved all the followers. Please give it a follow if you didn’t follow the old account, and stay up-to-date on the latest PGXN releases!

Every now and then I see something like this: SELECT u.* FROM users u WHERE 0 = (SELECT COUNT(*) FROM addresses a WHERE a.user_id = u.id); and it kinda pains me. So figured, I'll write about what is the problem with it, and how to avoid such constructs. Lets consider what went through someones mind … Continue reading "SQL best practices – don’t compare count(*) with 0"

While working on SQL/PGQ patch I wanted to find an equality operator for given left and right argument types to construct a condition to match an edge with its adjacent vertexes. It would look as simple as calling C function oper() with operator as "=" and required left and right data types. But soon it turned out to be a walk in PostgreSQL's operator labyrinth, which held my equality operator at the center instead of Minotaur.

vip-manager has long been a crucial tool for managing virtual IPs in PostgreSQL HA setups, providing reliable leader routing. With the release of version 2.8.0, we're taking things to the next level by introducing native integration with the Patroni REST API.

pgBackRest is a popular backup and restore tool for PostgreSQL, known for easily handling even the largest databases and workloads. It’s packed with powerful features, but all that flexibility can sometimes feel a bit overwhelming.

In late November, on the day after GIS Day, we hosted the annual PostGIS day online event. 22 speakers from around the world, in an agenda that ran from mid-afternoon in Europe to mid-afternoon on the Pacific coast.

Rails developers know the joy of working with ActiveRecord. DHH didn’t just give us a framework; he gave us a philosophy, an intuitive way to manage data that feels delightful. But when it comes to time-series data, think metrics, logs, or events, ActiveRecord can start to feel a little stretched. Handling huge volumes of time-stamped data efficiently for analytics? That’s a challenge it wasn’t designed to solve (and neither was PostgreSQL).

Rails developers know the joy of working with ActiveRecord. DHH didn’t just give us a framework; he gave us a philosophy, an intuitive way to manage data that feels delightful. But when it comes to time-series data, think metrics, logs, or events, ActiveRecord can start to feel a little stretched. Handling huge volumes of time-stamped data efficiently for analytics? That’s a challenge it wasn’t designed to solve (and neither was PostgreSQL).

Rails developers know the joy of working with ActiveRecord. DHH didn’t just give us a framework; he gave us a philosophy, an intuitive way to manage data that feels delightful. But when it comes to time-series data, think metrics, logs, or events, ActiveRecord can start to feel a little stretched. Handling huge volumes of time-stamped data efficiently for analytics? That’s a challenge it wasn’t designed to solve (and neither was PostgreSQL).

I was invited to the Maintainable Podcast hosted by Robby Russell. We talked about what makes software maintainable and naturally we also talked about Postgres.

Efficient data retrieval is crucial in any production environment, especially for databases handling heavy traffic and large datasets. PostgreSQL’s operator classes are a powerful but often overlooked tool for fine-tuning index performance. They allow you to control how PostgreSQL compares data within an index, helping to streamline searches and improve query efficiency in ways that default settings simply can’t match.

• Rushabh Lathia was interviewed as “PostgreSQL Person of the Week”
• Markus Winand updated the modern-sql.com website, it now includes all the PostgreSQL v17 features.
• Gulcin Yildirim Jelinek organized the Prague November Meetup. Tudor Golubenco and Noémi Ványi* spoke.

In a previous article in this series, I explored the magic of INSERT...UNNEST for improving PostgreSQL batch INSERT performance. While it’s a fantastic technique, I know it’s not the fastest option available (although it is very flexible). Originally, I hadn't intended to loop back and benchmark all the batch ingest methods, but I saw a lot of confusion out there, so I'm back, and this time I'm looking at COPY too.

The Postgres open source project is nearly 30 years old, I personally started using it about 20 years ago. And I’ve been contributing code for at least 10 years. But even with all that experience I find it really difficult to make judgments about how the performance changed over the years. Did it improve? And by how much? I decided to do some benchmarks to answer this question.