Neues vom PostgreSQL Planet

In Part 1, we examined how Postgres behaves on older hardware with various allocations of Shared Buffers. This time we repeat those benchmarks on more contemporary hardware equipped with modern SSD storage. The difference this makes is dramatic and has far reaching implications. Join us as we discuss the continuing role of Shared Buffers in modern server deployments! [Continue reading...]

Announcing Postgres monitoring capabilities provided by Coroot Community Edition

Lukas Fittl recently posted one of his 5 minutes of Postgres videos about his experimentation with different Kubernetes Postgres Operators: Postgres on Kubernetes, choosing the right operator, and handling major version upgrades. One passage about version updates caught my interest:

Many blog posts deal with installing or upgrading PostGIS. Today we’ll talk about upgrading related libraries such as GEOS or GDAL. Since PostGIS’ functionality stack relies heavily on these libraries, downloading current library versions is unavoidable; you want to use the latest stunning spatial features. Unfortunately, not all repositories serve PostGIS with the latest libraries. In our example, we’ll upgrade GEOS with Ubuntu.

I have been gathering some statistics about each major PostgreSQL release. Here is the update for this year:

Creating a connection and execute SQL is required in PostgreSQL and YugabyteDB to perform work, and any request for work will have a certain amount of latency, no matter how small the unit of work is. For optimal performance, it is important to choose a strategy that fits the performance needed.

In a recent post, we introduced pg_eventserv and the real-time web notifications from database actions.

In this post, we will dive into a practical use case: displaying state, calculating events, and tracking historical location for a set of moving objects.

PostgreSQL Person of the Week Interview with Étienne Bersac: My name is Étienne BERSAC, I’m a 37 years old French man living in a small village of Périgord, France. I’m married and raising 3 young children.

One of my favorite layers to route with pgRouting is the water layer. I am interested in where water comes from, where it goes, where runoff happens, and how urban development interacts with this powerful force of nature. The OpenStreetMap water layer, however, presents a challenge when routing with PostGIS and pgRouting: Polygons.

Why are polygons a challenge? A routing network using pgRouting is built from lines (edges). Now, to state the obvious: polygons are not lines.

PostgreSQL 15 has now the capability to output logs in JSON format!

PostgreSQL 15: logging in JSON

The freshly released PostgreSQL 15 introduces a lot of new features and improvements, but one, according to me, is going to change the way our favourite database is monitored: the capability to log daemon status in JSON.

The Django 4.1 release notes feature this short, innocent-looking note:

On PostgreSQL, AutoField, BigAutoField, and SmallAutoField are now created as identity columns rather than serial columns with sequences.

In this post, we’ll expand on what this means, why you might want to update existing columns from serial types to identity columns, and a management command to update them.

This year at PASS Data Community Summit, I'll be presenting a full-day precon about PostgreSQL. Why is there a PostgreSQL precon at an event primarily focused SQL Server & the Microsoft Data Platform? Read along to find out more!

© 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.

pgsql_tweaks is a bundle of functions and views for PostgreSQL

The soucre code is available on GitLab, a mirror is hosted on GitHub.
One could install the whole package, or just copy what is needed from the source code.

The extension is also available on PGXN.

PostgreSQL Person of the Week Interview with Cédric Duprez: I am Cédric Duprez and I live near Montargis, in the center of France. I work as a database expert in the National Institute of Geographic and Forest Information (IGN), which is the reference public operator for geographic and forest information in France.

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.

1. Overview

A few years ago, there was a proposal about adding the global index support to PostgreSQL for partitioned table. Following that proposal, there were many discussions and also an initial version POC to demonstrate the possibility, the technical challenges and the potential benefits, etc.