Neues vom PostgreSQL Planet

There is something new you may not have seen in the release notes for Postgres 17. No, not a new feature - I mean inside the actual release notes themselves! The Postgres project uses the git program to track commits to the project, and now each item in the release notes has a link to the actual commit (or multiple commits) that enabled it.

Speaking of PGXN news, I neglected to link to this post I wrote for the Tembo Blog last month, a fairly detailed accounting of what’s been happening on the PGXN v2 project:

A couple weeks ago, I drafted PGXN RFC–5 — Release Certification, which proposes to replace the simple inclusion of a SHA-1 hash digests in PGXN release META.json files with a JWS-signed release payload. From the introduction:

Given the success of the Extension Ecosystem Summit at PGConf.dev back in May, my colleague Floor Drees has organized a sequel, the Extension Ecosystem Summit EU on Tuesday, October 22, at the Divani Caravel Hotel in Athens.

Build a RAG app using Postgres and pgvector to enhance AI applications with improved data management, privacy, and efficient local LLM integration.

I travel quite a lot for work. Most of it is in the US and Europe, but I get around to other places as well. Most of the time, connectivity, while not the greatest, isn’t that bad, or hard. I run a VPN on my phone and laptop. I can set up my RDS connectivity […]

The post RDS Connectivity appeared first on Grant Fritchey.

PostgreSQL Person of the Week Interview with Yurii Rashkovskii: I was born and raised in Ukraine, and moved to Canada in 2008 without knowing much about it. I wanted to go as far west as I could to explore. That move changed my life a lot, and I now live on beautiful Vancouver Island in British Columbia.

• Melanie Plageman ran a mentorship feedback session and was the guest on episode 19 of Talking Postgres with Claire Giordano.

In March of ‘23, I took over as the Lead Architect of my employer’s Ansible-based automation for creating highly-available PostgreSQL clusters.

Tablespaces play an important role in database management systems, as they determine where and how database objects like tables and indexes are stored. Both Oracle and PostgreSQL have the concept of tablespaces, but they implement them differently based on the overall architecture of each database.

The post Transitioning from Oracle to PostgreSQL: Tablespaces appeared first on Stormatics.

With the introduction of failover slots in PostgreSQL-17, logical replication can now be effectively utilized in high-availability environments. This feature ensures that logical replication continues seamlessly even if the publisher node fails and its physical standby is promoted as the new publisher.

To maintain continuous replication after a failover, you need to configure the following parameters and options for both logical and physical replication setups:

1. Enable Failover Property on Slots:

My notes from the Open Source Summit Europe in Vienna, featuring fresh updates from open source communities, insights from the Xata booth, and highlights from our Postgres Happy Hour

Introduction

As usual, this project was prompted by multiple user reports with typical complaints, like 'SQL server executes the query times faster' or 'Postgres doesn't pick up my index'. The underlying issue that united these reports was frequently used VALUES sequences, typically transformed in the query tree into an SEMI JOIN.

In one of the recent PostgresFM episodes, Michael Christofides and Nikolay discussed planning time and what can affect it. One of the obvious negative factors we've discussed is the large number of partitions a partition table might have.

In this post, we're using our Postgres.AI assistant to see how planning time depends on the number of partitions.

On 17th of September 2024, Peter Eisentraut committed patch: Add temporal FOREIGN KEY contraints   Add PERIOD clause to foreign key constraint definitions. This is supported for range and multirange types. Temporal foreign keys check for range containment instead of equality.   This feature matches the behavior of the SQL standard temporal foreign keys, but … Continue reading "Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints"

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

Is it now possible to catch a login event.

PostgreSQL adds the login type for event triggers

PostgreSQL 17 adds a new firing event for event triggers: login. Therefore it is now possible to catch a login attempt on a database.

Caution: this is not the same as Oracle logon triggers, even if it resembles the same functionality to me.

However, thanks to this, is is now possible to get some more information when a login attempt succeeds.

PostgreSQL 17 includes a new (among others) tunable to control the ALTER SYSTEM command.

PostgreSQL 17 allow_alter_system tunable

Among the new excellent features of PostgreSQL 17, one captured my attention: the capability to disable the ALTER SYSTEM command via the tunable [allow_alter_system](https://www.postgresql.org/docs/current/runtime-config-compatible.html#GUC-ALLOW-ALTER-SYSTEM){:target="_blank"}.

There are multiple tools to run benchmarks on Postgres, but pgbench is probably the most widely used one. The workload is very simple and perhaps a bit synthetic, but almost everyone is familiar with it and it’s a very convenient way to do quick tests and assessments. It was improved in various ways (e.g. to do partitioning), but the initial data load is still serial - only a single process does the COPY. Which annoys me - it may take a lot of time before I can start with the benchmarks itself.

© Laurenz Albe 2024

Every year in fall, there is a new PostgreSQL release. After looking at the highlights of PostgreSQL v17, you may think, “what's the big deal?” Quite a few people might even be unhappy about the reminder that they should really upgrade some time soon. Time to explain how wonderful PostgreSQL v17 is!