Neues vom PostgreSQL Planet

Encrypting data at rest is often an important compliance task when working on securing your database system.

Version control of any code is essential for every organization. All organizations use their own preferred tools like git, svn, perforce etc. As I was working on a new requirement, which is about porting the current CI/CD pipeline to PostgreSQL, I needed to look for a tool which will take care of the schema changes. In this use case, the customer is maintaining all the schema in a separate directory, and creating the objects/schema during the application bootstrap process.

PostgreSQL Person of the Week Interview with Alexander Kukushkin: My name is Alexander. Originally I am from Russia. Since 2013 I live in Berlin and work as a Database Engineer at Zalando.

The PostGIS Team is pleased to release the first beta of the upcoming PostGIS 3.2.0 release.

Every now and then someone asks me to add index suggestions to explain.depesz.com. I always respond with polite decline. This is complicated thing to do, and I just have that time. Lately I was asked, on Slack to add (to explain.depesz.com) link to pganalyze Index Advisor for Postgres. So I checked it out. And results … Continue reading "Why is it hard to automatically suggest what index to create?"

While migrating to PostgreSQL, have you ever wondered whether you could make changes to SQL-only code without triggering a build, re-deployment, or even a restart? Have you ever attempted packaging software as a binary and allowing clients to execute their own compatible queries on any DB engine at no additional cost? In such cases, a better approach to organizing SQL queries in Golang is required. We recently developed an in-house micro-service using golang and PostgreSQL by leveraging the go's template language feature.

Managing time series data at scale can be a challenge. PostgreSQL offers many powerful data processing features such as indexes, COPY and SQL—but the high data volumes and ever-growing nature of time series data can cause your database to slow down over time.

Fortunately, Postgres has a built-in solution to this problem: Partitioning tables by time range.

The Free and Open Source for GIS conference : FOSS4G 2021 Bueno Aires Online took place September 27th to October 2nd, 2021 online. Many of the videos from the conference (including mine on PostGIS) are at FOSS4G 2021 Playlist viewable online.

Over the past year, one database we keep hearing about is ClickHouse, a column-oriented OLAP database initially built and open-sourced by Yandex.

In this detailed post, which is the culmination of 3 months of research and analysis, we answer the most common questions we hear, including:

StackGres 1.0.0: Open Source Postgres-aaS with 120+ Extensions TL;DR

We’re announcing StackGres 1.0.0 GA. StackGres is an Open Source Postgres-as-a-Service that runs on any Kubernetes environment. StackGres, today, is the Postgres platform with the most Postgres extensions available: 120 as of today. Many more to come in the future.

The new column compression option LZ4 introduced in PostgreSQL 14 provides faster compression. How can I use it, and how much faster is it? 

Originally published at https://blog.yugabyte.com with YugabyteDB in mind, because the benefit of Index Only Scan is huge in a distributed database. It also applies to PostgreSQL, knowing that the hops to the table pages are saved only for the vacuumed part of the table.

Running databases in the cloud offers the promise of more efficient operations and greater agility. However, there are important architectural and management decisions that often people don’t consider. [Continue reading...]

© Laurenz Albe 2021

Both cursors and transactions are basic ingredients for developing a database application. This article describes how cursors and transactions interact and how WITH HOLD can work around their limitations. We will also see the dangers involved and how to properly use WITH HOLD cursors in a PL/pgSQL procedure.

Today, we are announcing function pipelines, a new capability that introduces functional programming concepts inside PostgreSQL (and SQL) using custom operators.

Performing a major upgrade of Postgres cluster poses a very little risk for the database itself. The binary data structure is quite stable (and because of that, a very quick upgrade using the pg_upgrade’s –link option is possible) and it’s highly unlikely that the data can be damaged or lost during the process.

Monthly recurring revenue (MRR) and annual recurring revenue (ARR) are important metrics for a subscription-based business model. It allows for the business to project its available capital to make important decisions around expansion, hiring and more.

For OLTP workloads (such as web and mobile applications), it is important to understand object-level and row-level locks in PostgreSQL. There are several good materials that I can recommend reading:

PostgreSQL Person of the Week Interview with Ivan Panchenko: My name is Ivan Panchenko, I live in Moscow, Russia. I am 49 now. Married, 5 children (already grown up).

In Part 6 and Part 7 of the pipeline series we took a different path in the pipe/tunnel and explored PostgreSQL and Apache Superset, mainly from a functional perspective—how can you get JSON data into PostgreSQL from Kafka Connect,