Neues vom PostgreSQL Planet

I have written extensively about the performance benefits of allowing caches to be in write-back mode, rather than write-through mode, and for databases this is only safe by preventing cache data loss during power failure.

We’re excited to unveil pg_timetable v5.6, a significant release that takes your PostgreSQL job scheduling to a new level. In this release, we’ve introduced several key enhancements and features that empower you to manage and automate your database tasks precisely and flexibly.

A recap on how to comile pgBackRest on Amazon Linux.

Installing pgBackRest on Amazon Linux (by sources)

I had the need to install pgBackRest on Amazon Linux machines.

Unluckily, even if Amazon Linux 2023 is a Red-Hat like operating system, the official PGDG repository did not install in any version. Therefore, I decided to install from sources, compiling the latest 2.48 version.

A little trick to monitor your own running transaction in term of time and data size.

Using psql Variables to Introspect Your Script

psql is by far my favourite SQL text client, it has features that even the most expensive database tools provide. One very interesting property of psql is to support internal variables, pretty much like the variables you can find in a shell.

Most databases have users and groups to handle access rights. PostgreSQL is different as there are only roles.

The Application User Story

A new app has to be developed. The choosen RDBMS is PostgreSQL.

The application does need different roles:

1. Overview

pg_basebackup is a powerful tool for creating physical backups of PostgreSQL database clusters. Unlike pg_dump, which generates logical backups, pg_basebackup captures the entire cluster state. These backups are crucial for point-in-time recovery or for setting up a standby server.

2. Backup Compression

Please RSVP in MeetUp if you can: https://www.meetup.com/pdxpug/events/296865128/

2023 Thursday November 2nd Meeting 6:30pm:8:30pm (Note non-typical week.)

Location (different than last time):
American Red Cross
3131 N Vancouver Ave, Portland, OR 97227

What is a Table Access Method?

Table access method is the interface between the PostgreSQL core and data storage management. Since PostgreSQL 12, it is possible to define your own custom table access method that stores data in custom forms by implementing over 45 interface API callback functions. Generally, implementing all of the interface API callback functions is a difficult task as you are essentially defining your own custom storage engine that has to cooperate with PostgreSQL core to achieve:

A versioning bug in the ICU 73 release might affect people that use ICU collations with Postgres. Let's explain the issue and see what to do about it.

Enhance PostgreSQL query performance of your database with our guide on optimizing work_mem.

The post PostgreSQL performance tuning using work_mem appeared first on Stormatics.

Want to dive right in? Check out this brand new Citus Technical README on our GitHub repo for the open source Citus database extension.

This README is a bit of a deep dive into the Citus concepts, the use cases and design principles, how Citus uses the PostgreSQL extension hooks to distribute Postgres, and more.

Transparent Data Encryption (TDE) at the cluster level has been supported by the Oracle database for many years and has become a requirement of many security specifications and best practices. The Oracle documentation lists two practical advantages of the feature:

Version 1.21 of PgBouncer, the Postgres connection pooler, has added a long awaited feature: support for prepared statements inside of transaction mode. Prior to this, one had to choose between using prepared statements (a performance win), and using PgBouncer's transaction mode (also a large performance win). Now, we can have our cake and eat it too 🎂 🎉.

I am excited to announce the update of pgxmock v3.1! This update enables pgx.QueryRewriter interface support for mocking named parameters in your unit tests.

Named parameters? In Postgres?

The actual PostgreSQL $1 style placeholders are not that bad when only a few exist. But it rapidly gets challenging to manage when there are many of them, especially when specific arguments are used multiple times.

You can now restore a multi-terabyte Postgres database in minutes in Kubernetes with CloudNativePG, thanks to its declarative support for the standard Kubernetes Volume Snapshot API for backup and recovery. Discover how! [Continue reading...]

Language models are like the wizards of the digital world, conjuring up text that sounds eerily human. These marvels of artificial intelligence, such as GPT-3.5, are sophisticated algorithms that have been trained on vast swathes of text from the internet. They can understand context, generate coherent paragraphs, translate languages, and even assist in tasks like writing, chatbots, and more. Think of them as your trusty digital scribe, ready to assist with their textual sorcery whenever you summon them.

Postgres's non-relational data type support has greatly increased its adoption in recent years. The use cases I have heard is that Postgres supports relational data, and it supports non-relational data like JSON. This ability is often called multi-model.

How to terminate long running queries in PostgreSQL:

People might be fans of SQL, people might like PostgreSQL on Kubernetes or people might even love database ORMs. But have you ever heard of a single person who loves slow queries? In 20+ years of professional database engineering, I have not seen a single fan club paying respect to slow queries, bad SQL and a horrible user experience. So let’s terminate long running queries and simply remove them from our system.

Toulouse, October 17th, 2023

Today, we are releasing pglift 1.0, a new framework for deploying and operating PostgreSQL at scale. The project ships with both a command-line interface to manage the life-cycle of your databases and a collection of Ansible modules to drive your infrastructure as code in a production context.

By this 1.0 milestone, the user interface of pglift is now stable and the product considered ready for general use.

I just spent last week at Rails World in Amsterdam and had a blast digging back into the Rails and Active Record world. In conversations with developers over the week, I had some notable takeaways from the newest version of Ruby on Rails that I just had to get written up.

A quick summary before we dig in:

• async queries: send long-running queries the background while the code runs along, great for pages with multiple long-running queries that can be run in parallel