Neues vom PostgreSQL Planet
© Laurenz Albe 2022
Recently, a customer asked me why a bulk load into a PostgreSQL table kept slowing down as time went by. They saw that it was a trigger on the table that took longer and longer, and in that trigger, the updates were getting slower all the time. Now slow updates are a frequent problem, so I thought it would be interesting to explain what’s going on here in detail.
PostgreSQL 15 came out just last week. To celebrate the arrival of PostgreSQL 15, I will revisit the number one problem people have with PostGIS, how to upgrade your PostGIS enabled cluster, without installing an old PostGIS version.
A few years ago around the time PostgreSQL 12 was released, I created a tool to help identify the changes to postgresql.conf. The pgConfig tool has helped me become (and stay) aware of important changes to Postgres configuration as I work with various major version upgrades.
Jon Jensen: Upgrading PostgreSQL 14 to 15 on Fedora, RHEL, CentOS, Rocky, Alma Linux with PGDG RPM packages
PostgreSQL 15 changes
Yesterday PostgreSQL 15 was released! It includes a number of headline features since version 14 that make it worth upgrading your databases running earlier versions of PostgreSQL:
There is an elegant mathematical theory of binary relations. Homogeneous relations are an important subclass of binary relations in which both domains are the same. A homogeneous relation R is a subset of all ordered pairs (x,y) with x and y elements of the domain. This can be thought of as a boolean-valued function R(x,y), which is true if the pair has the relationship and false if not.
PostgreSQL’s initial release was in 1996 when cloud-native was not even a term. Right now it is the second most popular relational open source database according to DB-engines. With its popularity growth and the rising trend of Kubernetes, it is not a surprise that there are multiple solutions to run PostgreSQL on K8s.
In this blog post, we are going to compare these solutions and review the pros and cons of each of them. The solutions under our microscope are:
The end is nigh! PostgreSQL has substantially tightened restrictions on the use of the "public" schema.
Here, a standard login user (not superuser) tries to make a table, as one does:user=> CREATE TABLE mydata (id integer); ERROR: permission denied for schema public LINE 1: CREATE TABLE mydata (id integer);
NoooO! Why can I not write a table into public?
Recently I blogged about a significant improvement in PostgreSQL 15: PostgreSQL 15: Stats Collector Gone? What’s New? While there is great cheer for this upcoming improvement, we could see a few comments about “inefficiencies” in previous versions.
pgbouncer is one of the most widely used tool for connection pooling. At CYBERTEC, we’ve successfully deployed it in many different situations. It has proven to be reliable as well as useful.
Before we dive into different pooling modes and their implications, why do we need a connection pooler in the first place? The reason is that we want to reduce the overhead of new connections. That is right. Creating new connections is not free of charge.
Some time ago, someone asked on a PostgreSQL mailing list whether there was a diagram of the PostgreSQL system catalogs. There wasn’t at the time. Something like that used to be included in the PostgreSQL documentation, but it was never updated, and so it was eventually removed.
I was fond of a tool called postgresql_autodoc that could create schema diagrams automatically by querying catalog information. I was thinking why we couldn’t use that on the system catalogs, too.
As I sit here on October 7, 2022 watching PostgreSQL friends from all around the globe post contributions to the first ever PGSQL Phriday blogging event, I’m honestly pretty shocked… and very (very, very, very) grateful! While I have lots of ideas and love connecting with people to hear their stories, I wasn’t sure what to expect because, let’s face it, there are so many demands for our time and attention.
PostgreSQL has some special ways to provide numeric opeators by means of ASCII chars.PostgreSQL ASCII numeric operators
PostgreSQL has some ASCII numeric representations of commonly used numeric operators. It could be not well know, since I suspect pretty much everyone is using the function operators, and moreover it is not so simple to find them in the documentation by means of a searching for.
In any case, here they are: