Neues vom PostgreSQL Planet

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.

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).