Neues vom PostgreSQL Planet

© Laurenz Albe 2024

Enough has been written about the connection between VACUUM, the visibility map and index-only scans in PostgreSQL. But recently I demonstrated index-only scans to a class I was teaching and made some observations that surprised me at first. I could eventually explain what I saw, but I may have given the class more to chew on than I intended.

I have tested several times which compiler builds PostgreSQL the fastest. Now let’s look at with which compiler PostgreSQL runs the fastest.

PostgreSQL Person of the Week Interview with Derk van Veen: I was born in Zoeterwoude (Netherlands), a small village close to Leiden, where Floor Drees was born. Leiden is where I went to secondary school.

We have been promoting and using Patroni as the best high availability framework for PostgreSQL, and Etcd was the preferred/recommended DCS for the Patroni cluster. Both Patroni and Etcd have been part of PostgreSQL distribution from Percona for years now. But one area where we were stuck was the Etcd version, and we continued to […]

This post contained content that could not be rendered in the Atom feed. Please use the official post link: https://tembo.io/blog/jwt-authentication

Instead of just sharing the slides from the keynote talk that I gave at DjangoCon Europe 2024 in Vigo this month, I'm trying a new approach, inspired by Simon Willison's annotated presentations. This post contains the slides together with the transcript, links etc. so it should be much more useful.

I'll add a link to the video as soon as it's available.

A couple months ago, Álvaro Hernández suggested that Postgres extensions should be distributed as OCI (née Docker) images:

Yesterday, I announced a call for applications for a new mentoring program for PostgreSQL code contributors that I'm trying to start. I'm posting this on my blog as well for better visibility, and also to offer a few more comments and thoughts on this general topic of mentoring.

Read more »

Learn how to use PostgreSQL's SELECT FOR UPDATE clause to ensure safe data modifications and prevent data corruption in critical environments.

The post Ensuring Safe Data Modifications in PostgreSQL with SELECT FOR UPDATE appeared first on Stormatics.

Learn how custom `ENUM` types in PostgreSQL can affect `SELECT` queries and `ORDER BY` clauses. Be mindful of indexing and typecasting.

In an effort to make discussion of PGXN and related improvements as accessible as possible, I’ve set up PGXN Discussions on GitHub. Of course GitHub created default categories for all sorts of conversation, and all is welcome, in accordance with the PostgresSQL Code of Conduct.

The sixteenth year of the Prague PostgreSQL Developer Day (P2D2) conference, organized by the local PUG, happened on June 4-5. This year EDB was one of the main sponsors of the event, so let us share a brief summary from the conference overall, and a bit more details about contributions by EDB speakers.

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent access to the database, and store each version of a tuple in a table. To keep the database efficient, PostgreSQL employs a cleanup process called VACUUM, which removes unnecessary tuples. This not only saves disk space and keeps the database size under control, it also prevents the dreaded “Transaction ID Wraparound” issue. Regularly running VACUUM is crucial, including on the template0 database, which is normally handled by the Autovacuum daemon.

I’m always working with customers migrating from Oracle to PostgreSQL. One of the things in Oracle that didn’t necessarily have any additional impact other than I/O against an index was if the application executed insert statements which violated a PK constraint. Typically an exception handler was added to the code and while you could argue that the application shouldn’t do that, it typically was not something that had to be dealt with too often. However in PostgreSQL it IS something you need to be aware of.

A quick post on the need to use a compatible Clang compiler when building Postgres with LLVM support. TL;DR: always point the CLANG variable to the correct Clang binary when compiling Postgres --with-llvm.

The PostgreSQL Extension Ecosystem Summit took place at PGConf.dev in Vancouver on May 28, 2024 and it was great!

PostGIS is getting more and more popular and for good reasons. It benefits from a great community and offers a huge set of cool and stable features. Nowadays, a lot of customers want to migrate their spatial datasets from various data sources to PostGIS, where they can easily work with them.
Today I want to take a closer look at how this migration could look like for those transitioning from Microsoft SQL server to PostGIS.  

My article is structured as follows: