Neues vom PostgreSQL Planet

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

If you've been working with PostgreSQL, you've probably seen memes advocating for denormalized counters instead of counting related records on demand. The debate usually looks like this:

The issue of table and index bloat due to failed inserts on unique constraints is well known and has been discussed in various articles across the internet. However, these discussions sometimes lack a clear, practical example with measurements to illustrate the impact.

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"