Neues vom PostgreSQL Planet
Joshua Tolley: Formatting SQL code with pgFormatter within Vim
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
Ryan Lambert: Using Uber's H3 hex grid in PostGIS
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
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
Akshay Joshi: Use of the Master Password in pgAdmin 4
Shaun M. Thomas: Steady Storage Stampede
Regina Obe: PostGIS 2.5.6
Bruce Momjian: Postgres in the Microservices World
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)
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
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
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:
And perform the connection in main.rs:
Hans-Juergen Schoenig: Find and fix 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
Pavel Stehule: Orafce 3.21.0
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
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
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
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
Editing SQL and PostgreSQL related code within Emacs, in a beautiful war!