Neues vom PostgreSQL Planet

Photo by Garrett Skinner

Sometimes a little, seemingly simple tip can make a world of difference. I’ve got enough gray hair these days that it would be pretty easy for me to start thinking I’d seen an awful lot, yet quite frequently when I watch a colleague working in a meeting or a tmux session or somewhere, I learn some new and simple thing that makes my life demonstrably easier.

PostgreSQL Person of the Week Interview with Philippe Beaudoin: I live in France, more precisely in Rouen, in the Normandy. I’m working at Dalibo for about 6 years. In my previous job at Bull, I have had the great opportunity to manage the migration to PostgreSQL for a large customer in France: CNAF. That was 14 years ago.

This post explores using the H3 hex grid system within PostGIS. H3 was developed by Uber and has some cool benefits over the PostGIS native ST_HexagonGrid() function used in my post Find missing crossings in OpenStreetMap with PostGIS. The hex grid built-in to PostGIS is great for one-off projects covering a specific region, though it has shortcomings for larger scale consistency.

The PostGIS Team is pleased to release PostGIS 2.4.10! This is the end-of-life release of the 2.4 branch.

2.4.10

pgAdmin 4 introduced the Master Password in order to secure and later unlock saved server passwords. Any password that is saved in the SQLite DB file - used for storing user preferences and other data - through the save password or save SSH tunnel password option will be encrypted and decrypted using the master password.

A colleague of mine recently tweeted the following, highlighting the challenges often associated with database upgrades:

High Availability is more than just choosing the right architecture, replication tools, and failover systems. Storage can play a surprisingly important role in server responsiveness, as can somewhat obscure operating system tuning parameters. This week in PG Phriday, we’re going to examine just how important storage behavior can be to Postgres High Availability. [Continue reading...]

The upcoming PostgreSQL 15 introduces a new feature added by the Fujitsu OSS team in collaboration with the PostgreSQL open source community that allows supporting of two-phase commits in logical replication using publications/subscriptions. Let's take a look at how to use it.

The PostGIS Team is pleased to release PostGIS 2.5.6! 2.5.6 source download md5 NEWS PDF docs en This release is a bug fix release, addressing issues found in the previous 2.5 releases.

I try to keep current on database industry needs, and how Postgres meets or can be enhanced to meet those needs. One of my recent areas of study was microservices — the result is 111-slides on the topic.

Yet another online event organized by PgTraining!

Don’t forget the PgTraining online webinar on 2022-04-29 (Italian)

There are still some seats available for another great online event provided you by PgTraining!

Concurrent access to data within PostgreSQL is managed with the Multiversion Concurrency Control (MVCC) model. Data snapshots are maintained for each SQL statement so that they always get consistent data, even if other transactions are modifying it concurrently. This leads to managing multiple versions of the same row when the row has been modified by one or more transactions. From a user perspective, there might only be a single row of data, but internally PostgreSQL may be maintaining one or more versions of that row.

This blogpost is about using the rust language to create a connection to PostgreSQL and YugabyteDB, which is wire-compatible with postgres so therefore applies too. This is actually extremely simple:

unencrypted simple postgres connection

Add the necessary crate to Cargo.toml:

postgres = "0.19.2"

And perform the connection in main.rs:

Spot a missing PostgreSQL index

Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible.   However, if you want to ensure that your database performs well and if you are generally not in favor of user complaints – better watch out for missing indexes and make sure that all relevant tables are properly taken care of. One PostgreSQL index can make all the difference in performance.

PostgreSQL Person of the Week Interview with Bharath Rupireddy: My name is Bharath Rupireddy. I’m working at Microsoft on Postgres internals. I live with my wife, Mrs. Brundhavani, in Hyderabad, Telangana, India.

I released Orafce 3.21.0. In this release, the Oracle regexp functions regexp_instr and regexp_replace was backported (and little bit modified) from PostgreSQL 15. The C implementation is more faster, and much more robust than PLpgSQL implementation. The problem was not in just PL/pgSQL, but in some missing possibilities of PostgreSQL regexp API available from SQL or PL/pgSQL.

Since April 2021 and the 2.33 release, pgBackRest allows using multiple repositories at the same time. This brings a lot of benefits like, for example, redundancy and the ability to define various retention policies.

I had the chance to talk about this feature recently at pgDay Paris to highlight the impact of this new feature on the existing pgBackRest commands.

Introduction

As I introduced in the past posts ([1], [2], [3]), we have proposed to implement Incremental View Maintenance (IVM) support in PostgreSQL core. This project is aiming to add the new feature into PostgreSQL in future, but not to make a tool that enables the current PostgreSQL to use IVM.

Transaction ID Wraparound occurs when the VACUUM process cannot keep up with database activity and the PostgreSQL service is forced to shut down.

In more technical parlance: Transaction ID Wraparound occurs when the semantics of Multi-Version Concurrency Control (MVCC) fail and when the number of unique transaction ids reaches its maximum which numbers about two billion.

What leads up to this situation is when the VACUUM process managed by either the autovacuum workers or user-interaction (manual) does not keep up with the DML operations.

Editing SQL and PostgreSQL related code within Emacs, in a beautiful war!