Neues vom PostgreSQL Planet

In my previous post, I described how to use multi-cluster architecture provided by Crunchy PostgreSQL Operator to achieve disaster recovery and high availability on Kubernetes.

If you have a large PostgreSQL database that runs on a single node, eventually the single node’s resources—such as memory, CPU, and disk—may deliver query responses that are too slow. That is when you may want to use the Citus extension to Postgres to distribute your tables across a cluster of Postgres nodes.

PostgreSQL includes robust security standards around all aspects such as authentication, authorization, accounting and auditing. User management and role based segregation that satisfy enterprise standards can be implemented at ease. Similarly, organizations may be willing to enforce certain compliance around the password standards such as the minimum upper case characters or minimum number of numbers and special characters and the length of the password. Such username password checks are important in an environment where strong security policies are a must.

The PostGIS Team is pleased to release PostGIS 3.1.3! Best Served with PostgreSQL 14 beta2.

This release is a bug fix release, addressing issues found in the previous 3.1 release.

• #4929, Fix missing error from GetRingEdges when invoked with unexistent topology or edge (Sandro Santilli)

It has been 20 Years since i have been maintaining the Ora2Pg project, an Open Source software for Oracle to PostgreSQL migrations. The first version of Ora2Pg released on 9th May, 2001. Since then, there have been several features related to schema conversions and data migrations. Over a period of time, i have witnessed several tens of thousands of migrations using Ora2Pg that also increased the need of several optimizations.

Last time, we discussed object-level locks and in particular relation-level locks. In this article, we will see how row-level locks are organized in PostgreSQL and how they are used together with object-level locks. We will also talk of wait queues and of those who jumps the queue.

Row-level locks Organization

Let's recall a few weighty conclusions of the previous article.

PostGIS setup with Ubuntu 20.04.2

In one of his last blogposts, our CEO Hans-Jürgen Schönig explained how to set up PostgreSQL on Ubuntu. He consciously avoided any discussion of how to deal with our beloved PostGIS extension. Let’s fix that up right now by following these steps to implement PostGIS setup:

Introduction

In the previous post, I showed some performance evaluation of Incremental View Maintenance (IVM) feature that is under development. In this article, I will explain the implementation of IVM and how it works.

Simple Example of Incremental View Maintenance

First, I will explain the process of IVM using a simple example.

PostgreSQL Person of the Week Interview with Stefan Keller: I’m a Professor for Data Engineering since about 20 years and director of Institute for Software at Eastern University of Switzerland. I’m a spatialist; my focus is on spatial data engineering, spatial data analytics and open source, open data and educational resources.

When you’re trying to work out why a Postgres query is slow, EXPLAIN ANALYZE can be a huge help, but calculating the per-operation timings can get tricky. This post will start off fairly gentle, but it gets complex pretty quickly, so strap in!

The basics

The first thing to know is that the reported time of a parent operation is inclusive of its children. So if we want to know the contribution of the parent operation, we need to subtract the timings reported by its children.

I have spent a few days trolling through the features added to PostgreSQL 14 and in this blog, I want to share some statistics about PG-14 contributions, hopefully, you will find these statistics interesting. Please note that this data is based on my research on PG 14 contributions by going through the GIT LOG, commitfest entires, hackers emails threads, PG-14 release notes on the PostgreSQL website, etc. So please do give me the margin of human error in case the numbers aren’t 100% accurate.

The release notes for PG-14 are here

Back in August 2019, I wrote about the Horizontal scaling / Sharding in PostgreSQL and where it is going https://www.highgo.ca/2019/08/08/horizontal-scalability-with-sharding-in-postgresql-where-it-is-going-part-3-of-3/. One of the key feature that i talked about in this blog is parallel foreign scan i.e. asynchronous append of FDW nodes.

pgAdmin has long had a container distribution; however the development team rarely used it, except when testing releases. So virtually all of our experience has been using Docker. Recently, a user ran into an issue when running under Kubernetes that I was unable to reproduce in Docker, so I spent some time learning how a pgAdmin deployment would work in that environment—and ironically it worked just fine; I couldn't reproduce the bug!

Glad to present new updates of PostgreSQL Observability diagram and tell about further improvements.

Too much stats in Postgres

New releases of Postgres bring more and more stats. Upcoming Postgres 14 has new stats features and improvements on existing views.
I’ve reorganized items on the diagram and made a second side columns. It looks a bit clunky, but I think after two major releases all space will be occupied by new stats views and functions.

Glad to present new updates of PostgreSQL Observability diagram and tell about further improvements.

Too much stats in Postgres

New releases of Postgres bring more and more stats. Upcoming Postgres 14 has new stats features and improvements on existing views.
I’ve reorganized items on the diagram and made a second side columns. It looks a bit clunky, but I think after two major releases all space will be occupied by new stats views and functions.

The one from important Postgres's stats view is pg_stat_database. We can see in this view lot of important cumullative metrics. But sometimes, we can prefer to see these metric related to last seconds. It is not hard to calculate it:
CREATE OR REPLACE FUNCTION public.tps(text)
RETURNS
TABLE(xact_commit integer,
xact_rollback integer,
tup_returned integer,
tup_fetched integer,
tup_inserted integer,
tup_updated integer,
tup_deleted integer)
AS $function$
DECLARE

Has your table become too large to handle? Have you thought about chopping it up into smaller pieces that are easier to query and maintain? What if it's in constant use?

An introduction to the problems that can arise and how PostgreSQL's partitioning features can help, followed by a real-world scenario of partitioning an existing huge table on a live system.

Video from my talk at this year's Postgres Vision 👇

PostgreSQL 14 beta 2 is released. As written in the announcement, "In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 14 in your systems to help us eliminate bugs or other issues that may exist."

pg_repack is one of the oldest, widely used, extension projects for PostgreSQL. It is so much popular that even DBaaS service providers couldn’t avoid it. It is a “power tool” in the hands of a DBA to deal with bloated/fragmented tables. I can’t imagine a serious production deployment without it these days. It magically replaces the bloated, fragmented tables with a fresh fully packed table without holding an exclusive lock on the table during its processing**.

In PostgreSQL and many other relational databases, constraints are an integral part of the feature set. Many people are aware of primary keys, foreign keys, CHECK-constraints, table constraints and so on. However, from time to time, the situation is way more complicated. That’s when some more advanced techniques are needed to enforce integrity in the way it is desired by end users.