Neues vom PostgreSQL Planet

In the last blog (When PostgreSQL Parameter Tuning is not the Answer), we compared several execution plans for a SQL statement as we made changes to parameters and indexes. Still, there was no mention of what an execution plan is, how one can obtain an execution plan for a query, and how to interpret the result. In this blog, we’ll take a deep dive into this topic.

I almost missed this month’s PGSQL Phriday, but I hope I still have time to make a small contribution. 

Partitioning in PostgreSQL is a topic that recently became painfully familiar to me. Like other people who already contributed to PGSQL Phriday #011, I will talk only about my industrial experience rather than “how things should be.”  

You want to upgrade your PostgreSQL cluster with no cutover time for your applications—or you want to import your database in Kubernetes but you don't know how—read this article and find out what is already possible with CloudNativePG! [Continue reading...]

The PostGIS Team is pleased to release PostGIS 3.4.0rc1! Best Served with PostgreSQL 16 Beta2 and GEOS 3.12.0.

This version requires PostgreSQL 12 or higher, GEOS 3.6 or higher, and Proj 6.1+. To take advantage of all features, GEOS 3.12+ is needed. To take advantage of all SFCGAL features, SFCGAL 1.4.1+ is needed.

For this month’s PGSQL Phriday #011, Tomasz asked us to think about PostgreSQL partitioning vs. sharding. This is a topic near and dear to me and I’m excited to think about it some this month. For 20+ years of database and application development, time-series data has always been at the heart of the products I ... Read more

The topic of this month's PGSQL Phriday #011 community blogging event is partitioning vs. sharding in PostgreSQL. It seemed right to share a perspective on the question of "partitioning vs. sharding" from someone in the Citus open source team, since we eat, sleep, and breathe sharding for Postgres.

Postgres built-in "native" partitioning—and sharding via PG extensions like Citus—are both tools to grow your Postgres database, scale your application, and improve your application's performance.

This month’s PGSQL Phriday invitation from Tomasz Gintowt is on the topic of “Partitioning vs sharding in PostgreSQL“.

I have pushed Release 17 of the PostgreSQL Buildfarm client.

Release 17 has two main features:

For this month’s PGSQL Phriday blogging challenge, Tomasz Gintowt asks if people rather use partitioning or sharding to solve business problems. Big Data: Partitioning vs Sharding Adjust Here at Adjust we use both. We also have quite a few databases of all sizes. Let’s look at some examples. We leverage four primary database systems, termed as “Backends”, “Shards”, “Bagger” and “Tracker”. The Backend systems function as intermediate storage of data, anything between hours and weeks.

This month’s PGSQL Phriday #011 prompts bloggers to write about Sharding and Partitioning in PostgreSQL.

Posts should help clarify what these terms mean, why these capabilities are useful, and how to use them.

Let’s dive in! 🤿

We are pleased to announce that the schedule for PGDay UK 2023 has now been published. You can see the fantastic selection of talks we have planned at:

https://2023.pgday.uk/schedule/

The team would like to thank all those who submitted talks, as well as the program committee who had a long and difficult job selecting the talks!

We look forward to seeing you in London in September!

PostgreSQL® offers two types of data types to handle JSON data:

• JSON stores the JSON as text, performing a validation on the correctness of the JSON syntax
• JSONB optimizes the JSON storage in a custom binary format. Therefore, on top of validating the correctness of the JSON format, time is spent to properly parse and store the content.

NOTE: more info is available in the PostgreSQL JSON types documentation page

We’ve released PGMQ, a packaged extension for message queues on Postgres.

People have been implementing queues on Postgres in many different ways and we’re excited about combining lessons learned from those projects into a simple, feature-rich extension.

Some exciting features of the project include:

Creating a shift-like function for manipulating arrays in PL/PgSQL.

Increase performance in pgvector by using embedding vectors with fewer dimensions

Everyone has a cache for database results, right? Something like redis or memcached to prevent unnecessary and comparatively slow calls to the database. No? Worry not, for with Postgres and some trigger magic, we can produce a cache where there was none before. Don’t let complicated and expensive queries limit your application throughput when you really need to put the pedal to the metal. Let us show you how! [Continue reading...]

In my previous blog, we started discussing this topic: https://www.pgedge.com/blog/postgresql-replication-and-upcoming-logical-replication-improvements-in-postgresql-16I briefly discussed replication methods in PostgreSQL, and provided a summary of some of the key features of logical replication that made it in PostgreSQL 16.

Invitation from Tomasz Gintowt

We’re a little behind on the invitation this month, so there will certainly be some wiggle room for posting responses over the weekend into Monday. Remember, this is all volunteer work.

© Laurenz Albe 2023

There are several techniques to bulk load data into PostgreSQL. I decided to compare their performance in a simple test case. I’ll add some recommendations for parameter settings to improve the performance even more.

An example table to bulk load data

The table is simple enough:

Creating a shift-like function for manipulating arrays in PL/PgSQL.

A Possible Way to Implement a Shift Function in PL/PgSql

PostgreSQL does support arrays in a very excellent way, but it does not provide a shift like function. A shift function takes an array as input and removes the first (left-most) element from the array. This is quite simple to do in PostgreSQL, since array slices are easy to implement. However, a slice returns the modified (shifted) array, not the shifted element.