Neues vom PostgreSQL Planet

Using encrypted network connections is not really a choice in a modern environment. But: using an additional encryption phase, and using an encryption handshake will add latency and overhead to a network connection.

This writeup is more a scratchpad for myself, because there are other blogposts that guide you with enabling SSL. However, these did not exactly what I wanted to do.

My small contributions to pgagroal.

pgagroal log rotation and formatting

A few weeks ago I implemented a small contribution to [pgagroal](https://agroal.github.io/pgagroal/){:target="_blank"}, the high-performance PostgreSQL connection pooler, in order to implement log rotation and log formatting.
At last, my contribution was accepted and merged, but I did not get enough time to write on this until now.

Imagine you have something like this in Django:

class MyModel(models.Models): last_name = models.CharField(max_length=255, blank=True) ...

The most basic sorting is either: queryset.order_by('last_name') or queryset.order_by('-last_name'). But what if you want entries with a blank string last? And, you want it to be case insensitive. Here's how you do it:

Partial map of the voyage of the Endurance, from “South”, by Ernest Shackleton

The shapefile format is commonly used in geospatial vector data interchange, but as it’s managed by a commercial entity, Esri, and as GIS is a fairly specialized field, and perhaps because the format specification is only “mostly open”, these files can sometimes be confusing to the newcomer. Perhaps these notes can help clarify things.

Hot on the heels of our discussion on preventing Postgres XID wraparound using basic monitoring, let’s talk about arresting the risk almost entirely through Autovacuum. With a few relatively minor tweaks to our configuration and focusing on the occasional problematic table, we can both increase maintenance throughput, and also reduce impact on client queries. Let’s demystify the art of VACUUM and keep our cluster self-maintaining in the bargain. [Continue reading...]

On 29th of March 2022, Andrew Dunstan committed patch: SQL/JSON query functions   This introduces the SQL/JSON functions for querying JSON data using jsonpath expressions. The functions are:   JSON_EXISTS() JSON_QUERY() JSON_VALUE()   All of these functions only operate on jsonb. The workaround for now is to cast the argument to jsonb.   JSON_EXISTS() tests … Continue reading "Waiting for PostgreSQL 15 – SQL/JSON query functions"

Using Postgres sometimes feels like magic. But sometimes the magic is too much, such as when you are trying to understand the reason behind a seemingly bad Postgres query plan. I've often times found myself in a situations where I asked myself: "Postgres, what are you thinking?". Staring at an EXPLAIN plan, seeing a , and being puzzled as to why Postgres isn't doing what I am expecting. This has lead me down the path of reading the Postgres source, in search for answers. Why is Postgres choosing…

On 28th of March 2022, Andrew Dunstan

1. Overview

PostgreSQL is one of the greatest open source databases, not only because of the extensibility and SQL compliance but also the evolution of new features. For example, in postgres_fdw, there is a new feature parallel commit has been added into the main branch and will be released in PG15. This blog is for a quick taste of this new feature.

On 27th of March 2022, Andrew Dunstan committed patch: SQL/JSON constructors   This patch introduces the SQL/JSON standard constructors for JSON:   JSON() JSON_ARRAY() JSON_ARRAYAGG() JSON_OBJECT() JSON_OBJECTAGG()   For the most part these functions provide facilities that mimic existing json/jsonb functions. However, they also offer some useful additional functionality.

On 28th of March 2022, Alvaro Herrera committed patch: Add support for MERGE SQL command   MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows -- a task that would otherwise require multiple PL statements. For example, … Continue reading "Waiting for PostgreSQL 15 – Add support for MERGE SQL command"

In previous articles we discussed how the system plans a query execution and how it collects statistics to select the best plan. The following articles, starting with this one, will focus on what a plan actually is, what it consists of and how it is executed.

When you want to include a running sum for each line of data in your query, you generally use a window clause with SUM. What if you don't want to list all line items of your data. You want a report that gives you a weekly sum and another that gives you the running sum for the whole year. What do you do then? We'll demonstrate how to do that.

Even though there is a wide range of RPMs for PostgreSQL and related projects in the repositories, some people don't want to use prebuilt options, or may apply some extra specific patches. Also, some companies prefer to build their packages from srpms, per their IT policy -- so they maintain their own packages.

We continue our #AlwaysBeLaunching Cloud Week with MOAR features! 🐯☁️ Today, we’re introducing you to pg_stat_statements, a PostgreSQL extension now enabled by default in all Timescale Cloud services. pg_stat_statements allows you to quickly identify problematic queries, providing instant visibility into your database performance.

One of the good things with a virtual event like Citus Con is that you have a lot of flexibility about where and when to watch the talks. From your home office, or a café, or the beach—or even the car, while you wait to pick up your kids. As long as you have an internet connection, you’re in.

In my previous post I described how to deploy Crunchy Postgres Operator v4 on Kubernetes and use it to achieve disaster recovery and high availability. The new major version, v5 was release last year and the installation methods have significantly changed. 

Slide from my talk Understanding Jsonb performance at PGCONF.NYC, Dec 2, 2021, with some corrections.

© Laurenz Albe 2022

You might, at first glance, believe that DROP ROLE (or DROP USER, which is the same) is a simple matter. However, that’s not always the case. So I thought it might be a good idea to show you the problems involved and how to solve them.

PostgreSQL Person of the Week Interview with Claire Giordano: I have lived in northern California in the San Francisco Bay Area for my entire adult career, from the time I was a junior software engineer in the developer tools group at Sun. But before that, I moved around — a lot. I was born in Taiwan, and then lived in Rhode Island, California, New Jersey, Athens Greece, Mississippi, Rhode Island again, and New Hampshire.