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.
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.
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:
And perform the connection in main.rs:
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.
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.
As I introduced in the past posts (, , ), 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!