Neues vom PostgreSQL Planet

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.

Today we released Citus 11.0 beta, which is our first ever beta release of the Citus open source extension to Postgres. The reason we are releasing a beta version of 11.0 is that we are introducing a few fundamentally new capabilities, and we would like to get feedback from those of you who use Citus before we release Citus 11.0 to the world.

Working with databases one can’t help but wonder what happens with data stored on the disk.
For example you need to know this to be able to save at least some of the information on damaged data blocks with help of hexedit.
Today we’ll try to find out how data types such as integer are kept on disk. To do that we will create a testing table, review its contents using hexedit and change data on disk. For the sake of this exercise I will be using PostgreSQL 14.

Let’s create a testing table:

The pgAdmin 4 browser tree is a crucial component from the UI perspective as it lists all the database server objects and all other functionality relies on it. As it lists thousands of objects, the performance of the tree was a concern for the team. In 2020, we decided to move from Backbone to React, so after doing some R & D work, we came to the conclusion to implement the browser tree from scratch using the React Aspen Tree package.