Neues vom PostgreSQL Planet

Welcome to Part 7 of our series exploring the exciting new features anticipated in the official PostgreSQL 17 release. In this series, we delve into newly added features and discuss how they can benefit database developers and migration engineers transitioning to PostgreSQL 17 in future.

In case you missed the earlier parts:

As Joe said a few days ago: so many Postgres providers. Aiven, AWS, Azure, Crunchy, DigitalOcean, EDB, GCP, Heroku, Neon, Nile, Oracle, Supabase, Tembo, Timescale, Xata, Yugabyte… I’m sure there’s more I missed. And that’s not even the providers using Postgres underneath services they offer with a different focus than Postgres compatibility.

Similarly to PostgreSQL, also SQLite3 needs some care…

SQLite3 Vacuum and Autovacuum

Today I discovered, by accident I need to confess, that PostgreSQL is not the only database requiring VACUUM: also SQLite3 does.

Solid Cache is a relatively new caching framework that’s available now as a Ruby gem. In the next major version of Ruby on Rails, version 8, it’s becoming the default cache backend.

Solid Cache has a noteworthy difference from alternatives in that it stores cache entries in a relational database and not a memory-based data store like Redis.

In this post, we’ll set up Solid Cache, explore the schema, operations, and discuss some Postgres optimizations to consider.

Welcome CNPG Playground, a local learning environment for exploring CloudNativePG and PostgreSQL in Kubernetes. Using Docker and Kind, it simulates real-world scenarios, enabling developers and DBAs to experiment with PostgreSQL replication across two clusters. Designed for hands-on learning, CNPG Playground provides an accessible entry point for testing configurations and features, with plans for future enhancements and community collaboration.

As businesses increasingly move toward open-source technologies, many Oracle Database professionals find themselves needing to work with PostgreSQL, one of the most popular open-source relational database management systems (RDBMS). Although both Oracle and PostgreSQL share many similar concepts, there are fundamental differences in how these systems handle certain database structures, one of which is the schema.

The SQL standard defines a set of rules so that database systems can be interchangeable, but there are small singularities in the wild. In this regard, the hierarchyid data type provided by SQL Server is a striking example. If you are switching to PostgreSQL, two solutions are available to you.

A first and simpler solution consists in linking each node to its parent using a new parentid column and applying a foreign key constraint. Another, more complete approach consists in using the ltree extension. This article deals with the latter case.

When we introduced columnar compression for Postgres in 2019, our goal was to help developers scale Postgres and efficiently manage growing datasets, such as IoT sensors, financial ticks, product metrics, and even vector data. Compression quickly became a game-changer, saving users significant storage costs and boosting query performance—all while keeping their data in Postgres. With many seeing over 95 % compression rates, the impact was immediate.

This post contains the slides and transcript from the talk that I gave at PyCon Italia 2024, and at EuRuKo 2024.

You can also watch the video from PyCon Italia here.

"Elevator Pitch"

Why Solid Queue?

Background jobs are used commonly in Ruby on Rails apps to perform any work possible outside of a user request. A classic example is sending an email to a new user, where that doesn’t need to happen synchronously within a request. Thus, a background job framework of some kind helps to keep things consistent.

In the 2010s, Sidekiq seemed to become the most popular choice, usable as a free open source version, or a commercial Pro version. Sidekiq uses Redis to persist the job data.

SQL makes sense when it's working on a single row, or even when it's aggregating across multiple rows. But what happens when you want to compare between rows of something you've already calculated? Or make groups of data and query those? Enter window functions.

Window functions tend to confuse people - but they’re a pretty awesome tool in SQL for data analytics. The best part is that you don’t need charts, fancy BI tools or AI to get some actionable and useful data for your stakeholders. Window functions let you quickly:

Time for yet another “first patch” idea post ;-) This time it’s about BRIN indexes. Postgres has a contrib module called amcheck, meant to check logical consistency of objects (tables and indexes). At the moment the module supports heap relations (i.e. tables) and B-Tree indexes (by far the most commonly used index type).

To recognize over 20 years of growth, the CYBERTEC team welcomed dozens of partners at the headquarters in Wöllersdorf this month. It was a great opportunity to share knowledge and sync about planning for continued success together.

PostgreSQL Person of the Week Interview with Andrew Atkinson: I’m a programmer, dad, and husband, and live with my family in Minneapolis, Minnesota, a northern state in the USA. I’ve worked remotely as a software engineer for more than 15 years at big companies like Microsoft and Groupon, and many small and mid-sized startups, scaling up their platform.

Last Wednesday (September 11, 2024), PGDay UK took place in London. Some of my colleagues live in the area (or not too far away), so it was a great occasion to spend time together! Since London is also a lovely city to visit, we made sure to leave some time for sightseeing

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

The PostGIS Team is pleased to release PostGIS 3.5.0beta1! 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 key metric when evaluating vector similarity search algorithms is “recall” - which measures the relevancy of the returned search results. Typically better recall means better quality search results, but this is often at the cost of another key metric, such as index size or query latency. This has led to different techniques to “boost” recall while trying to limited any adverse impact to other metrics.

Tansu is an Apache Kafka API compatible broker with a PostgreSQL storage engine. Acting as a drop in replacement, existing clients connect to Tansu, producing and fetching messages stored in PostgreSQL. Tansu is in early development, licensed under the GNU AGPL. Written in async 🚀 Rust 🦀.

• Tomas Vondra held his first "PostgreSQL Office Hour" meeting.