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.”
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.
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:
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:
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.
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.