Sammlung von Newsfeeds

Hubert 'depesz' Lubaczewski: What tables were touched within given range of wal LSN?

Neues vom PostgreSQL Planet - vor 2 Stunden 25 Minuten
We had a case recently where one of our DBs failed over to a new primary. To this server (old primary, and then new primary) we had connection from some kind of CDC tool (Debezium, I think). The thing is that while there was failover, this tool read (using logical decoding) changes on old primary … Continue reading "What tables were touched within given range of wal LSN?"

Elizabeth Garrett Christensen: Magic Tricks for Postgres psql: Settings, Presets, Echo, and Saved Queries

Neues vom PostgreSQL Planet - vor 5 Stunden 25 Minuten

As I’ve been working with Postgres psql cli, I’ve picked up a few good habits from my Crunchy Data co-workers that make my terminal database environment easier to work with. I wanted to share a couple of my favorite things I’ve found that make getting around Postgres better. If you’re just getting started with psql, or haven’t ventured too far out of the defaults, this is the post for you. I’ll walk you through some of the friendliest psql settings and how to create your own preset settings file.

Tatsuo Ishii: Row pattern recognition feature for PostgreSQL

Neues vom PostgreSQL Planet - vor 11 Stunden 11 Minuten
What is row pattern recognition feature? Row pattern recognition (RPR) is a feature defined in the SQL standard. It allows to search for a sequence of rows by pattern.  Since I am working on this feature for PostgreSQL, I would like to give a brief introduction to RPR.
  Consider a table holding date and daily stock price of a company.   company  |   tdate    | price
 company1 | 2024-07-01 |   100
 company1 | 2024-07-02 |   200
 company1 | 2024-07-03 |   150

Andrew Farries: pgroll 0.6.0 update

Neues vom PostgreSQL Planet - vor 17 Stunden 25 Minuten
Learn about the latest changes in pgroll in the 0.6.0 release as we continue to build and turn it into a first-class open-source schema migration tool for Postgres.

Mark Wong: PostgreSQL Performance Farm 2024 Progress Update

Neues vom PostgreSQL Planet - 18. Juli 2024 - 2:26

It feels like it was time to revisit the PostgreSQL Performance Farm that Tomas Vondra envisioned back in 2010.  Between that and several Google Summer of Code iterations starting in 2018, the project just didn't seem to gain enough traction.  See Ilaria Battiston's presentation in 2022 for a demo.

Carlos Pérez-Aradros Herce: Postgres major version upgrades with minimal downtime

Neues vom PostgreSQL Planet - 18. Juli 2024 - 2:00
With the beta release of dedicated clusters, we've added the ability to move branches between clusters. Xata customers can now perform Postgres major version upgrades with minimal downtime.

Andreas Scherbaum: PostgreSQL Berlin July 2024 Meetup

Neues vom PostgreSQL Planet - 17. Juli 2024 - 17:09

On July 16th, 2024, we had the PostgreSQL July Meetup in Berlin. Adjust hosted and Neon sponsored the Meetup in their Berlin Headquarter at Prenzlauer Berg, near the TV Tower.

Deepak Mahto: Exploring PostgreSQL 17: A Developer’s Guide to New Features – Part 3: The COPY Command Gets More User-Friendly

Neues vom PostgreSQL Planet - 17. Juli 2024 - 16:12

PostgreSQL 17 Beta was released on May 23, 2024, introducing a host of exciting new features anticipated to be part of the official PostgreSQL 17 release. In this blog series, we’ll delve into these features and explore how they can benefit database developers and migration engineers transitioning to the latest PostgreSQL version.

Dave Page: PGDay UK 2024 - Schedule published

Neues vom PostgreSQL Planet - 16. Juli 2024 - 12:35

Join us on 11th September 2024 in London, for a day of talks on the World's Most Advanced Open Source Database coupled with the usual valuable hallway track. This event is aimed at all users and developers of PostgreSQL and is your chance to meet and exchange ideas and knowledge with like-minded database fanatics in London.


We are pleased to announce that the schedule for PGDay UK 2024 has now been published. You can see the fantastic selection of talks we have planned at:

Laurenz Albe: Keyset pagination with descending order

Neues vom PostgreSQL Planet - 16. Juli 2024 - 7:49
© Markus Winand 2014

Keyset pagination is the most performant way to retrieve a large result set page by page. However, the neat trick with composite type comparison doesn't always work. This article explains why and how you can work around that shortcoming.

An example table for paginated queries

We create a table with a million rows:

Andrew Farries: Introducing multi-version schema migrations

Neues vom PostgreSQL Planet - 16. Juli 2024 - 2:00
Today's release of multi-version schema migrations addresses one of the most common pain points of application deployment - keeping your application code and database schema in sync. You can now present two versions of your schema, both old and new, to client applications.

David Wheeler: RFC: PGXN Meta Spec v2

Neues vom PostgreSQL Planet - 15. Juli 2024 - 21:15

Two bits of news on the “PGXN v2” project.

Daniel Vérité: Implementing UUIDs v7 in pure SQL

Neues vom PostgreSQL Planet - 15. Juli 2024 - 20:14
In May 2024, the IETF standard on UUIDs (Universally Unique IDentifiers) has been updated with RFC 9562, finally officializing the UUID Version 7. This version is known to be a much better choice for database indexes than previous ones, since it has values generated consecutively already sorted. PostgreSQL does not yet have a built-in function to generate UUIDs v7, but of course several extensions do exist. The ones I found tend to require a compilation step and superuser privileges to install, as they’re written in “untrusted languages” like C or Rust.

Tomas Vondra: Autovacuum Tuning Basics

Neues vom PostgreSQL Planet - 15. Juli 2024 - 19:16

A few weeks ago I covered the basics of tuning checkpoints, and in that post I also mentioned autovacuum as the second common source of performance issues (based on what we see on the mailing list and at our customers). Let me follow-up on that with this post about how to tune autovacuum, to minimize the risk of performance issues. In this post I'll briefly explain why we even need autovacuum (dead rows, bloat and how autovacuum deals with it), and then move to the main focus of this blog post - tuning.

Andreas 'ads' Scherbaum: Ozgun Erdogan

Neues vom PostgreSQL Planet - 15. Juli 2024 - 15:15
PostgreSQL Person of the Week Interview with Ozgun Erdogan: I’m originally from Istanbul. After college, I moved to the Bay Area for grad school and then up to Seattle for my first programming gig at Amazon. After about four years, I wanted to learn more and co-founded Citus Data with two of the smartest guys I know.

Andrei Lepikhov: How expensive is it to maintain extended statistics?

Neues vom PostgreSQL Planet - 15. Juli 2024 - 1:24

In the previous post, I passionately advocated for integrating extended statistics and, moreover, creating them automatically. But what if it is too computationally demanding to keep statistics fresh?

This time, I will roll up my sleeves, get into the nitty-gritty and shed light on the burden extended statistics put on the digital shoulders of the database instance. Let's set aside the cost of using this type of statistics during planning and focus on one aspect - how much time we will spend in an ANALYZE command execution.

Shayon Mukherjee: Use pg_easy_replicate for setting up Logical Replication and Switchover in PostgreSQL

Neues vom PostgreSQL Planet - 13. Juli 2024 - 15:11
Logical replication is a powerful feature in PostgreSQL that allows for real-time data replication between databases. It can be used for performing major version upgrades using a blue/green setup where you have two databases, allowing you to test and switch over to a new version with minimal downtime. Logical replication can also be use to facilitate database migrations between different environments, using the same technique and tooling. In this post, I will describe the process of setting up simple replication and switchover between two databases using pg_easy_replicate.

Andrew Atkinson: SaaS on Rails on PostgreSQL — POSETTE 2024

Neues vom PostgreSQL Planet - 13. Juli 2024 - 2:00

In this talk attendees will learn how Ruby on Rails and PostgreSQL can be used to create scalable SaaS applications, focusing on schema and query design, and leveraging database capabilities.

We’ll define SaaS concepts, B2B, B2C, and multi-tenancy. Although Rails doesn’t natively support SaaS or multi-tenancy, solutions like Bullet Train and Jumpstart Rails can be used for common SaaS needs.

Christophe Pettus: Checking Your Privileges, 2

Neues vom PostgreSQL Planet - 10. Juli 2024 - 19:48

I turned the last blog post into a talk; you can get the slides here.

Umair Shahid: Guide to Auditing and Monitoring Access in PostgreSQL

Neues vom PostgreSQL Planet - 10. Juli 2024 - 10:52

In the data-driven world of today, maintaining the security and integrity of your database is paramount. Auditing and monitoring access to your database are critical components of an effective security strategy. These processes help ensure that only authorized users are accessing sensitive information and that any unauthorized access attempts are detected and addressed promptly.