Neues vom PostgreSQL Planet

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.

One of the guiding Postgres design principles is heavy reliance on features provided by the environment (particularly operating system) and file systems are a prime example of this. Unlike other databases Postgres never supported raw devices that would require implementing a “custom” file system.

PostgreSQL Person of the Week Interview with Christoph Berg: In Debian, I’ve been working on Quality Assurance, in the New Members team, on various random packages, and lastly of course on the PostgreSQL packages. At work, I’m a senior consultant with credativ’s PostgreSQL team. I’m a PostgreSQL Major Contributor, working on packaging apt.postgresql.org for Debian and Ubuntu.

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:

I released new version of pspg . This release can be interesting for users that uses some BSD platforms (like FreeBSD). Unfortunately, these platforms doesn't support alternate screen, and then can users can see some unwanted visual effects, when pspg is closed. Most unwanted are chars of bottom menu in command line.

Uh, what is this #PGSQLPhriday thing about? Everyone is writing blog posts about this today ... For details, please read the introduction from Ryan Booz.

This month's topic: pick your three favorite or most important best pratices for #PostgreSQL. Tell them in a form that two are true, one is a lie. See if you can spot which one is true and which one is false. The resolution is at the end of this blog posting.

Postgres shared_buffers remains one of the most difficult parameters to configure since its inception. Beyond an initial estimate, query patterns, relative storage performance, available resources, and realistic limits can all influence the final value entered here. This is the first in a two-part series discussing the role of shared buffers in the past, and on more modern systems. [Continue reading...]

As a part of my own journey of learning within PostgreSQL, I’ve decided that I’m going to take part in PGSQL Phriday as often as I can, just as a way to continue to stretch my knowledge of this platform. Along the way, hopefully, I can help you learn a little too. The topic of […]

The post PGSQL Phriday #001: Two Truths and a Lie appeared first on Grant Fritchey.

OK, I got your attention. You’re probably thinking I must actually be referring to other tools like pg_repack. No I am not. I really am referring to running the VACUUM FULL command in non-blocking mode. WHAT!?#$%@!
Here we go:

Ryan Booz asked the community on Twitter to contribute to a challenge called “PGSQL Phriday” where we publish posts according to a theme. The theme for PGSQL Phriday #001 is: “Two truths and a lie”. Of course, it’s difficult for me to call something a “truth”… So I was granted the right to use a “2 it depends and one absolutely always do this” instead!" My blog will be about psql command line options.

After four beta releases and one Release Candidate, the RC2 for PostgreSQL 15 was announced earlier today. If all goes well – fingers crossed! – this release will be promoted to General Availability (or GA) on October 13.

PostgreSQL Global Development Group (PGDG) has been really good about releasing one major version and four minor versions each year. As always, this year’s major version, i.e. v15, has loads of new features and enhancements to offer. I describe below some of the features that I find especially exciting.

In the previous articles, covered query execution stages, statistics, sequential and index scan, and two of the three join methods: nested loop and hash join.

PgconfEu 2022 in Berlin is coming very fast and one of my colleagues, who’s not very used to other pGConf Eu events asked what session we would recommend and why. First, let’s state the obvious, there will be concurring sessions and you will miss some that you want to see but will be at the same time than another one you absolutely want. Of course, as I’m volunteering and speaking, this is not my real schedule.