Neues vom PostgreSQL Planet

Time for a renovation on an old post that dealt with Postgres and hstore. This post will move from the hstore data type to json(b). At the time the original post was written introduction of the json type was about … Continue reading →

PostgreSQL table partitioning is by far one of the most popular new PostgreSQL features. Many people look to partitioned tables as a way to improve performance and broaden scalability. However, partitioning comes with a little problem: How can you partition an existing table without locking up the database? The answer is: pg_rewrite can help you with PostgreSQL table partitioning. Here you will learn how to implement pg_rewrite to help you solve partitioning problems in the most elegant way possible.

Cover photo: Clark Young

We all know that indexing is important, but it can be difficult to know where to start. In this post, my aim is to collate some of the best advice I’ve seen for indexing Postgres, and simplify it for folks just getting started. 

PostgreSQL Person of the Week Interview with Emre Hasegeli: I was born and grew up in İzmir, Turkey, studied in İstanbul, lived and worked in Germany and in the UK for a while, and moved back to my hometown this year. I am currently working remotely for End Point, a US based software consultancy company which develops Bucardo.

How to kill a backend process, the right way!

kill that backend!

Sometimes it happens: you need, as a DBA, to be harsh and terminate a backend, that is a user connection.
There are two main ways to do that:

A not-scientific look at how to compress a set of SQL dumps.

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

One of the curious aspects of spatial indexes is that the nodes of the tree can overlap, because the objects being indexed themselves also overlap.

Podman is a drop in replacement for Docker, and can handle containers daemonless and rootless ("ruthless"?). Containers work based on cgroups, namespaces and IPC, which is existing in Linux, and therefore requires a linux system to support it (which is based on Fedora CoreOS, and runs in QEMU).

Setup

Much of the configuration depends on the existence of 'brew' on OSX. If you haven't got brew (homebrew) installed, you can do so using:

It's easy to get misled into overconfidence based on the performance of powerful servers, given today's monster core counts and RAM sizes.
However, the reality of high concurrency usage is often disappointing, with less throughput than one would expect.
Because of its internals and its multi-process architecture, PostgreSQL is very particular about how it likes to deal with high concurrency and in some cases it can slow down to the point where it looks like it's not performing as it should.

Adding, tuning and removing indexes is an essential part of maintaining an application that uses a database. Oftentimes, our applications rely on sophisticated database features and data types, such as JSONB, array types or full text search in Postgres. A simple B-tree index does not work in such situations, for example to index a JSONB column. Instead, we need to look beyond, to GIN indexes. Almost 15 years ago to the dot, GIN indexes were added in Postgres 8.2, and they have since become an…

I would like to talk about the key features in PostgreSQL 14, and what is being discussed in the community for PostgreSQL 15 and beyond.

Crunchy Data hosted the third annual PostGIS Day on November 18th.This was our second year with a virtual format and another year of record attendance! We had attendees from more than 99 countries.

Introduction

During analysis, you rarely - if ever - get to go directly from evaluating data to transforming and analyzing it. Sometimes to properly evaluate your data, you may need to do some pre-cleaning before you get to the main data cleaning, and that’s a lot of cleaning! In order to accomplish all this work, you may use Excel, R, or Python, but are these the best tools for data cleaning tasks?

PostGIS is a widely popular spatial database extension for Postgres. It's also one of my favorite tools! A recent discussion on the People, Postgres, Data Discord server highlighted that the permissions required for various PostGIS operations were not clearly explained in the PostGIS documentation. As it turned out, I didn't know exactly what was required either.

Most of my readers will know about primary keys and all kinds of table constraints. However, only a few of you may have ever thought about the difference between a primary key and a UNIQUE constraint. Isn’t it all just the same? In both cases, PostgreSQL will create an index that avoids duplicate entries. So what is the difference? Let’s dig in and find out…

What primary keys and UNIQUE constraints do

The following example shows both a primary key and a unique constraint:

When PostgreSQL needs to sort strings, it relies on either the operating system (by default) or the ICU collation library (if your PostgreSQL has been built with support for ICU and you have chosen to use an ICU-based collation) to tell it in what order the strings ought to be sorted. Unfortunately, operating system behaviors are confusing and inconsistent, and they change relatively frequently for reasons that most people can't understand. That's a problem for PostgreSQL users, especially PostgreSQL users who create indexes on text columns.

This post exposes some basic examples for one of the greatest feature of PostgreSQL: text search. In standard SQL, we can use LIKE, SIMILAR and REGEXP. But general text search cannot be optimized with simple B-Tree indexes on the column value. Text contain words, and indexing the text as a whole is not sufficient. Fortunately, PostgreSQL provides many index types, and one of them is GIN - Generalized Inverted Index. We can index the words, automatically, with functions to extract them as "tsvector" - Text Search vectors.

PostgreSQL-14 was released in September 2021, and it contained many performance improvements and feature enhancements, including some features from a monitoring perspective. As we know, monitoring is the key element of any database management system, and PostgreSQL keeps updating and enhancing the monitoring capabilities. Here are some key ones in PostgreSQL-14.

The case

I have been involved into a case where an important application has been behaving erratically in regards of execution time. This is the background.