Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 2 Stunden 16 Minuten

Joshua Tolley: Formatting SQL code with pgFormatter within Vim

26. April 2022 - 2:00

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.

Andreas 'ads' Scherbaum: Philippe Beaudoin

25. April 2022 - 16:00
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.

Ryan Lambert: Using Uber's H3 hex grid in PostGIS

24. April 2022 - 7:01

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.

Regina Obe: PostGIS 2.4.10

24. April 2022 - 2:00

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


Akshay Joshi: Use of the Master Password in pgAdmin 4

22. April 2022 - 16: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.

Andrew L'Ecuyer: PGO Version 5.1 Release Comes With Powerful New Capabilities

22. April 2022 - 15:09

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

Shaun M. Thomas: Steady Storage Stampede

21. April 2022 - 17:55
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...]

Ajin Cherian: Two-phase commits for logical replication publications/subscriptions

21. April 2022 - 2:57

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.

Regina Obe: PostGIS 2.5.6

21. April 2022 - 2:00
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.

Bruce Momjian: Postgres in the Microservices World

20. April 2022 - 23:30

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.

Luca Ferrari: Don't forget the PgTraining online webinar on 2022-04-29 (Italian)

20. April 2022 - 2:00

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!

Hamid Akhtar: PostgreSQL 14 B-Tree Index: Reduced Bloat with Bottom-Up Deletion

19. April 2022 - 14:01

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.

Frits Hoogland: Use PostgreSQL SSL connection in rust with self-signed certificates

19. April 2022 - 11:27

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

Hans-Juergen Schoenig: Find and fix a missing PostgreSQL Index

19. April 2022 - 10:00
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.

Andreas 'ads' Scherbaum: Bharath Rupireddy

18. April 2022 - 16:00
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.

Pavel Stehule: Orafce 3.21.0

16. April 2022 - 8:04

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.

Stefan Fercot: pgBackRest multi-repositories tips and tricks

15. April 2022 - 9:00

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.

Yugo Nagata: pg_ivm: a PostgreSQL extension providing Incremental View Maintenance feature

15. April 2022 - 2:07

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.

Robert Bernier: Overcoming VACUUM WRAPAROUND

13. April 2022 - 13:38

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.

Luca Ferrari: Formatting SQL code with pgFormatter within Emacs

13. April 2022 - 2:00

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