Neues vom PostgreSQL Planet

PostgreSQL Person of the Week Interview with Adam Hendel: My name is Adam Hendel and I live in Minnesota (USA). I grew up playing ice hockey and it was a very important part of my life up until age 21. Then I went on to get bachelor degrees in finance and physics and amasters in data science. I served in the U.S. Army military intelligence corps after college. I’ve been working in data science, machine learning, and software engineering ever since.

PostgreSQL databases tend to grow silently. One day, you notice queries slowing down, backups taking forever, and no one remembering what is in that 500 GB log table. If this sounds familiar, you’re not alone.

Partitioning makes things manageable

If your database has one massive table that keeps growing, partitioning can be a lifesaver. PostgreSQL has supported native declarative partitioning since version 10, and recent versions (13 and up) have made it much easier to work with.

Last week I visited the Malmö PUG to talk about performance cliffs. It’s a really nice meetup - cozy environment, curious audience asking insightful questions. I highly recommend attending or even giving a talk there.

A very minimal edition this week, due to lack of time, and it's been relatively quiet anyway.

more...

Postgres is a powerful and feature-rich database, but like any system, it has certain limits that are good to be aware of. In this post, we'll take a look at a few interesting limits in Postgres.

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use.
This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies.

When I have set returning functions and even non-set returning functions, I love putting them in the FROM clause instead of the SELECT part. I often take it for granted that the results are usually the same, but in some important situations, they are different. It's not guaranteed to be the same when your function is not immutable.

Continue reading "FROM function or SELECT function"

Check TRAKTOR 2 - less complicated, more stable, cloud ready! Compatible with PostgreSQL 16 and 17.

Stacey Haysler created a document which lists all the available volunteer options for the PostgreSQL project.

Luigi Nardi organized the Malmö PostgreSQL Meetup. Tomas Vondra presented.

A small follow up on my previous post on various Postgres scale-up avenues in my preferred order. The post received quite a bit of interest - meaning people at least still “wish” performant databases :) And - the ones who are willing to put in a bit of effort with...

Working on TRAKTOR again. The good news is, it still works with PostgreSQL 17.x and I have completely replaced psycopg2 with pg8000 to remove all native dependencies. The bad news is, if you don't want to run the arbiter as superuser it's a complicated dance of permissions, some of which can only be GRANTed by a superuser, and I have to find a way to automate this as far as possible.

Today, I would like to discuss additional techniques to speed up query execution. Specifically, I will focus on rearranging conditions in filter expressions, JOINs, HAVING clauses, and similar constructs. The main idea is that if you encounter a negative result in one condition within a series of expressions connected by the AND operator, or a positive result in one of the conditions linked by the OR operator, you can avoid evaluating the remaining conditions. This can save computing resources.