Neues vom PostgreSQL Planet

Last week a user noted on the postgis-users list (paraphrase):

I upgraded from PostGIS 2.5 to 3.3 and now the results of my coordinate transforms are wrong. There is a vertical shift between the systems I’m using, but my vertical coordinates are unchanged.

Hmmm.

PostGIS gets all its coordinate reprojection smarts from the proj library. The user’s query looked like this:

PostgreSQL 15 builds upon the enhancements of earlier versions and takes them to the next level, with several improvements to logical replication, increased sorting  performance, and the introduction of the MERGE command. I would like to talk about these features as well as what is under discussion for PostgreSQL 16.

Custom data types is one of those many features that makes PostgreSQL flexible for development of a huge variety of business and application use cases. Data types will help you primarily for data integrity, ensuring your data is stored in the database as you want it to be. A common surprise gift of using strict data types is that you can end up reducing your long term data maintenance.

There’s two main ways to customize data types in Postgres:

PostgreSQL is a fantastic database, but if you’re storing images, video, audio files, or other large data objects, you need to “toast” them to get optimal performance. This post will look at using The Oversized-Attribute Storage Technique (TOAST) to improve performance and scalability. 

Database migrations from Oracle to PostgreSQL and SQL Server to PostgreSQL can get tricky when certain differences are not known. Unknown differences can lead to migration failures.

Last week-end, I found this tweet: PostgreSQL, why do you fail me 😭 (on this syntax that not a single jOOQ user ever requested in > 10 years) pic.twitter.com/Wxr2JkWQBF — Lukas Eder (@lukaseder) January 26, 2023 My take was that Postgres was right and conforming to the SQL Standard whereas the other RDBMS were wrong, but that’s faith, not proof. So, I gathered my courage and deep dived into the SQL Standard to find formal proof.

Register at the conference website, and take advantage of the Early Bird discount!

© Laurenz Albe 2022

PostgreSQL Person of the Week Interview with Luca Ferrari: I live in Italy, with my beautiful wife Emanuela, my great son Diego, our female cats, Sofia and Olivia, and our female dog Franzina. We live near the city of Modena, the land of sports cars like Ferrari and Maserati, and also the center of very good food and wine.

I’m very pleased to be talking about real-life logical replication at Nordic PgDay 2023, in beautiful Stockholm.

If you are an application developer and you build on PostgreSQL, then maybe you have looked into where PostgreSQL comes from, who develops it, and where you can get professional help if needed.

Now, if you are a PostgreSQL developer (hi!), do you know what you are building on, where those things come from, who develops them, and where you get get professional help if needed?

Consider the dependency diagram of the internet:

Ryan Lambert asks in this month's PGSQL Phriday:

PGDay/MED is pleased to announce that the call for papers is open for our inaugural conference.

We are looking for talks on any PostgreSQL-related subject for the one-day conference. Got a big idea you want to share? A problem you have an innovative solution to? An advancement you want to put forward? We want to hear from you!

All talks should last about 40-45 minutes, with an additional 5-10 minutes for questions. Due to the international nature of our audience, we are requesting that talks be made in English for this conference.

1. Overview

Sometimes, you may need to manage large objects, i.e. CLOB, BLOB and BFILE, using PostgreSQL. There are two ways to deal with large objects in PostgreSQL: one is to use existing data type, i.e. bytea for binary large object, and text for character-based large object; another is to use pg_largeobject; This blog will explain how to use pg_largeobject.

Invitation from Ryan Lambert

In my career, I have frequently worked for companies with large amounts of time-partitioned data, where I was a software engineer focusing on our PostgreSQL databases. So it was my job to deal with outages in the database. One source of repeated outages has been partition management to handle data retention.

You might have already seen our previous articles supporting Oracle to PostgreSQL and SQL server to PostgreSQL migrations. Our previous articles have provided solutions to some of the complexities during Oracle to PostgreSQL migrations. In this article, we are going to discuss about migration of synonyms from Oracle to PostgreSQL.

As I've written about before, a PostgreSQL superuser always has the ability to take over the operating system account in which PostgreSQL is running, but sometimes you'd like to have a role that can administer the database but not break out of it.

Attempts to compress PostgreSQL WAL at different levels have always been around since the beginning. Some of the built-in features (wal_compression) have been there since 2016, and almost all backup tools do the WAL compression before taking it to the backup repository. But it is time to take another look at the built-in wal_compression because PostgreSQL 15 has much more to offer.

Disclaimer

This article will contain spoilers both on how I solved 2022 Day 14's challenge "Regolith Reservoir" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up.