Neues vom PostgreSQL Planet

PostgreSQL Person of the Week Interview with Keith Fiske: I’m originally from Pennsylvania but moved to Maryland around 2000 and have lived here ever since. I’ve been a Senior Database Engineer with CrunchyData for almost 4 years now.

TDE is an important feature missing from PostgreSQL, it is also a crucial tick in the box which people are deciding to move to PostgreSQL from other DBMS systems. The community has been trying to add this feature in PG however it has taken too long because of lack of attention from serious community members for reaching consensus on basic spec and design.

As part of my work on the open source PostgreSQL team at Microsoft, I recently committed a new feature into PostgreSQL 14 to track dependencies on collation versions, with help from co-author Julien Rouhaud and the many others who contributed ideas.

In our last blog post on using Postgres for statistics, I covered some of the decisions on how to handle calculated columns in PostgreSQL.

If you’ve seen the following error, you know that your CentOS6 has reached end of life (EOL).

Loaded plugins: fastestmirror, ovl Setting up Install Process YumRepo Error: All mirror URLs are not using ftp, http[s] or file. Eg. Invalid release/repo/arch combination/ removing mirrorlist with no valid mirrors: /var/cache/yum/x86_64/6/base/mirrorlist.txt Error: Cannot retrieve repository metadata (repomd.xml) for repository: base. Please verify its path and try again

I found this out a couple of days ago. So today I decided to fix it.

The osm2pgsql project has seen quite a bit of development over the past couple of years. This is a core piece of software used by a large number of people to load OpenStreetMap data into PostGIS / PostgreSQL databases, so it has been great to see the activity and improvements. Recently, I was contacted by Jochen Topf to see if I would give one of those (big!) improvements, osm2pgsql's new Flex output, a try.

Recently, PostgreSQL 13 was released. People are asking what are best ways upgrading and updating PostgreSQL 12 or some other version to PostgreSQL 13. This blog post covers how you can move to the latest release.

Before we get started, we have to make a distinction between two things:

• Updating PostgreSQL
• Upgrading PostgreSQL

Let’s take a look at both scenarios.

While we talk about "PostGIS" like it's one thing, it's actually the collection of a number of specialized geospatial libraries, along with a bunch of code of its own.

You have a file, possibly a huge CSV, and you want to import its content into your database. There are lots of options to do this but how would you decide which one to use. More often than not the question is how much time would the bulk load would take. I found my self doing the same few days back when I wanted to design a data ingestion process for PostgreSQL where we needed to bulk load around 250GB of data from CSV files every 24 hours.

When using logical replication with PostgreSQL, the wal level needs to be set to ‘logical’, so the logical level wal contains more data to support logical replication than the replicate wal level. I think it’s unclear to many users or DBAs about the difference between logical and replicate level. This blog is about the difference of logical level and replicate level, which the reader can use to evaluate or analyze the impact of changing to logical level on wal log bloat.

PostgreSQL Person of the Week Interview with Carole Arnaud: I was born in Nantes (~300k inhabitants, western France) 44 years ago already. I am the eldest of 3 siblings. I used to be a shy child and teenager: actually, I was more comfortable studying than socializing. Fortunately it’s much better now :-)

If you’ve built your application on Postgres, you already know why so many people love Postgres.

Introduction

On November 17-20, 2020, PostgresConf.CN & PGconf.Asia2020 (referred to as 2020 PG Asia Conference) was held online for the very first time! This conference was jointly organized by the PG China Open Source Software Promotion Alliance, PostgresConf International Conference Organization, and PGConf.Asia Asian Community. This conference was broadcast exclusively via the Modb Technology Community platform in China with a record-high number of viewers streaming the conference events.

After having discussed isolation problems and having made a digression regarding the low-level data structure, last time we explored row versions and observed how different operations changed tuple header fields.

Now we will look at how consistent data snapshots are obtained from tuples.

This post continues from my report on Random Numbers. I have begun working on a random data generator so I want to run some tests to see whether different random number generators actually impact the overall performance of a data generator. Let’s say we want to create random data for a table with 17 columns, […]

PostgreSQL offers a nice BLOB interface which is widely used. However, recently we came across problems faced by various customers, and it makes sense to reflect a bit and figure out how PostgreSQL handles BLOBs – and especially BLOB cleanup.

Using the PostgreSQL BLOB interface

In PostgreSQL, you can use various means to store binary data. The simplest form is definitely to make use of the “bytea” (= byte array) data type. In this case a binary field is basically seen as part of a row.
Here is how it works:

Greetings readers, today we're going to take a semi-break from my “doing data science in SQL” series to cover a really cool use case I just solved with regular expressions (regex)

Real time, fast update of materialized views

Big data has an unfortunate tendency to get messy. A few years in, a growing database that use to be small, lean, and well-designed, has better odds than not of becoming something large, bloated, and with best practices tossed aside and now considered unsalvageable.

There’s a few common reasons that this happens, some better than others:

AWS announces Babelfish: open source Postgres with SQL Server compatibility

At AWS re:Invent’s first keynote, Andy Jassy has announced a major breakthrough for Postgres: SQL Server compatibility for Postgres. Open Source.