Neues vom PostgreSQL Planet

Since very few people read the subsequent comments to the posts, I wanted to publish Dian Fay’s comments as a separate entity. Like pretty much everything Dian does, this comment is perfect!!!

Enjoy!

Citus, a database scaling extension for PostgreSQL, is known for its ability to shard data tables and efficiently distribute workloads across multiple nodes. With Citus 12.0, Citus introduces a very exciting feature called schema-based sharding.

This month’s PGSQL Phriday topic prompts bloggers to write about Sharding and Partitioning. Posts should help clarify what these terms mean, and how these capabilities can be put to use.

Let’s dive in! 🤿

This month’s PGSQL Phriday topic prompts bloggers to write about Sharding and Partitioning, covering what they mean and how they are similar and different concepts.

Let’s dive in! 🤿

Benedikt Deicke (co-founder of UserList) recently hosted me on the Slow & Steady podcast (co-hosted by Benedicte Raae). Our main topic was Database Constraints, why they’re useful and how to use them.

Benedikt and I had a mutual connection in Michael C. (founder of PgMustard) and had been following each on social media for a long time, so it was fun to meet on Zoom!

An addition to the pg_stat_xxx_tables and pg_stat_xxx_indexes that can help a lot in finding out seldomly used stuff.

Did anybody ever ask you: what are Postgres’ best practices regarding this? ” Even if you are not a consultant (because if you are, it’s your job to answer questions like this) but a humble human working with Postgres, chances are you’ve been asked this question more than once.

This question may cover various topics, from high availability and backup tools to the optimal number of indexes on a table or correct schema usage.

Introduction

The purpose of this blog post is twofold:

• Firstly, to introduce a new Postgres C extension to record subtransactions counters
• Secondly, to share its Rust version

Introduce the extension

The new extension, namely pg_subxact_counters can be found in this repo.

Subtransactions can lead to performance issue, as mentioned in some posts, see for example:

The PostGIS Team is pleased to release PostGIS 3.4.0beta2! 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.

1. Overview

PostgreSQL is a powerful open-source relational database management system used in various applications. During development, debugging PostgreSQL can be essential for identifying and resolving issues. In this blog, we’ll walk through setting up a remote PostgreSQL development debugging environment using Visual Studio Code (VSCode) on a client machine and PostgreSQL running on a Linux machine.

Ever been in situation where you had to sort data that is partially text, and partially numerical? Like invoice numbers: prefix-9, prefix-10, prefix-11, other-5, other-20 ? Normally you can't do order by as you will get them in wrong order: other-20 ⇒ other-5 ⇒ prefix-10 ⇒ prefix-11 ⇒ prefix-9. Can something be done with it? … Continue reading "Human/version sort in PostgreSQL"

In Part 2 of this 2 part PostgreSQL 🐘 Table Partitioning series, we’ll focus on how we modified the Primary Key online for a large partitioned table. This is a disruptive operation so we had to use some tricks to pull this off.

Read on to learn more.

I joined Jason Swett recently on his podcast and had a great time. The podcast topic was PostgreSQL Table Partitioning and how developers can use it. We also discussed the 2024 edition Sin City Ruby, a conference Jason organizes and is running for the second time next year.

The conference will be held in March of 2024 in Las Vegas.

I attended in 2022, but this time around I’m honored to announce that I’ll be speaking at Sin City Ruby 2024. I’m looking forward to the opportunity to create a great presentation involving PostgreSQL and of course Ruby.

In Part 2 of this 2 part PostgreSQL 🐘 Table Partitioning series, we’ll focus on how we modified the Primary Key online for a large partitioned table. This is a disruptive operation so we had to use some tricks to pull this off.

Read on to learn more.

Postgres community released a new feature, in Postgres 15.0, that performs actions to modify rows in the target table, using the data from a source. MERGE provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements, using INSERT with ON CONFLICT clause etc.

A brief look to understand how the main cluster connection limits work.

PostgreSQL Cluster Connection Limits

PostgreSQL has two main connection limit tunables that allow the system administrator to decide what is the maximum number of connections the cluster will support and, in case an emergency activity has to be performed, what part of such connections is reserved to superusers.

PostgreSQL 16 is going to introduce a new parameter named reserved_connections among the other two max_connections and superuser_reserved_connections:

We recently faced a challenge working with a large table where query performance had worsened. This table is high growth tracking activity as applicants move through a hiring process.

Read on to find out how we improved performance and growth management.

In this post, we’ll look at how PostgreSQL table partitioning helped us solve our operational challenges from rapid data growth.

We recently faced a challenge working with a large table where query performance had worsened. This table is high growth tracking activity as applicants move through a hiring process.

Read on to find out how we improved performance and growth management.

In this post, we’ll look at how PostgreSQL table partitioning helped us solve our operational challenges from rapid data growth.

Choosing to use a block range index (BRIN) to query a field with high correlation is a no-brainer for the optimizer. The small size of the index and the field's correlation makes BRIN an ideal choice. However, a recent event tought us that correlation can be misleading. Under some easily reproducible circumstances, a BRIN index can result in significantly slower execution even when the indexed field has very high correlation.

A big thank you to all 32 speakers whom submitted 72 talk proposals to PGDay UK 2023.

The vast majority of talk proposals were of high quality and has made choosing which talks to accept difficult. As is the nature of a single track one day conference the CfP committee wants to select talks to appeal to the widest audience possible.

We'd really encourage speakers whom were not accepted to PGDay UK 2023 to submit talks to other PostgreSQL events. Especially if this was your first time submitting a talk to a PostgreSQL conference.