Neues vom PostgreSQL Planet

On 17th of September 2024, Peter Eisentraut committed patch: Add temporal PRIMARY KEY and UNIQUE constraints   Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints. These are backed by GiST indexes instead of B-tree indexes, since they are essentially exclusion constraints with = for the scalar parts of the key and && for … Continue reading "Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE constraints"

This month, I'll be hosting a discussion of Thomas Munro's 2024.pgconf.dev talk, Streaming I/O and vectored I/O. As usual, there will be three sessions, and you can use this form to sign up for the session you prefer.

Introduction

In previous blogs, we talked about an overview of PostgreSQL’s table access method API here , how sequential scan is handled within this API here, and how data insertion is handled here.

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 has released a new version, with a host of updates and improvement to the world’s most advanced open-source database. In this blog post, I want to take you through the key new features implemented in PostgreSQL 17.

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.

Upgrading your PostgreSQL cluster is an important task to keep your database running smoothly and securely. With each new release, PostgreSQL introduces performance improvements, security patches, and new features that can benefit your system. However, upgrading can be a bit tricky, especially if you're working in a Windows environment, where certain challenges like permissions, service management, and file handling may differ from Linux setups.

In my last blog post, Evolution of Logical Replication, I mentioned the future development of a feature to allow "upgrades of logical replication nodes." The upcoming release of PostgreSQL 17 includes this feature. Previously, after major version upgrades (via pg_upgrade), users couldn't immediately connect and write data to logical replication nodes. This was because the slots were lost during upgrades, preventing replication from continuing.

The PostGIS Team is pleased to release PostGIS 3.5.0! Best Served with PostgreSQL 17 RC1 and GEOS 3.13.0.

This version requires PostgreSQL 12 - 17, GEOS 3.8 or higher, and Proj 6.1+. To take advantage of all features, GEOS 3.12+ is needed. SFCGAL 1.4+ is needed to enable postgis_sfcgal support. To take advantage of all SFCGAL features, SFCGAL 1.5 is needed.

A couple days ago I had a bit of free time in the evening, and I was bored, so I decided to play with BOLT a little bit. No, not the dog from a Disney movie, the BOLT tool from LLVM project, aimed at optimizing binaries. It took me a while to get it working, but the results are unexpectedly good, in some cases up to 40%. So let me share my notes and benchmark results, and maybe there’s something we can learn from it.

This post contained content that could not be rendered in the Atom feed. Please use the official post link: https://tembo.io/blog/self-hosted-ga

As I mentioned in my last blog post, as your cluster grows with multiple standby servers and potentially automated failover (using tools like Patroni), it becomes more practical to set up a dedicated repository host, also known as a dedicated backup server. This backup server can then trigger backups and automatically select the appropriate node in case of failover, eliminating the need for manual intervention.

The fifth Prague PostgreSQL Meetup of 2024 will take place on September 30th!

This post contained content that could not be rendered in the Atom feed. Please use the official post link: https://tembo.io/blog/image-search

Introduction

As a senior database consultant and developer at CYBERTEC PostgreSQL International, I enjoyed speaking at PGDay Hyderabad 2024. This event marked two firsts for me: my first time visiting India and the first-ever PostgreSQL conference held in Hyderabad. I was filled with excitement and curiosity as I started this journey. I didn't know what to expect, but I was eager to experience it all.

Since the PostgreSQL 17 RC1 on a home run towards the official PostgreSQL release, scheduled for September 26, 2024.

With RC1 freshly cut, the release of Postgres 17 is right on the horizon, giving us a host of features, improvements, and optimizations to look forward to.

As a backend developer, one in particular pops off the page, distinguishing itself amongst the dozens of new release items:

Allow btree indexes to more efficiently find a set of values, such as those supplied by IN clauses using constants (Peter Geoghegan, Matthias van de Meent)

PostgreSQL Person of the Week Interview with Nicolas Payart: I live in Brittany, France and work as a Database System Engineer for OVHcloud. My wife, daughters and I live in a house in the countryside with our cats (official and otherwise).

• Floor Drees wrote an article, how the Postgres community migrated to Mastodon, and which tools can be used.

• Elodie Jex (Instagram: elodie.s_art_) designed the three proposals for the PostgreSQL Europe Diversity Task Force, from which one was selected as the final logo.

Recently, we’ve received many questions about how to take backups from a standby server using pgBackRest. In this post, I’d like to clarify one of the most frequently asked questions and address a common misconception for new users.