Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 3 Stunden 15 Minuten

Adrian Klaver: Postgres and JSON

7. Dezember 2021 - 19:43
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 →

Hans-Juergen Schoenig: pg_rewrite: PostgreSQL Table Partitioning

7. Dezember 2021 - 10:00

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.

Michael Christofides: Some indexing best practices

6. Dezember 2021 - 18:36

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. 

Andreas 'ads' Scherbaum: Emre Hasegeli

6. Dezember 2021 - 15:00
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.

Luca Ferrari: kill that backend!

6. Dezember 2021 - 1:00

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:

Luca Ferrari: pgdump, text and xz

6. Dezember 2021 - 1:00

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

Regina Obe: PostGIS 3.2.0beta3 Released

4. Dezember 2021 - 1:00

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

Paul Ramsey: Tricks for Faster Spatial Indexes

3. Dezember 2021 - 18:07

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.

Frits Hoogland: podman machine on mac OSX 12.0.1 (Monterey)

3. Dezember 2021 - 16:50

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).


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:

Jimmy Angelakos: Slow things down to make them go faster [Postgres Build 2021]

3. Dezember 2021 - 16:30

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.

Lukas Fittl: Understanding Postgres GIN Indexes: The Good and the Bad

2. Dezember 2021 - 13:00
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…

Amit Kapila: PostgreSQL 14 and beyond

2. Dezember 2021 - 0:00

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.

Elizabeth Garrett Christensen: PostGIS Day 2021

1. Dezember 2021 - 21:42

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.

Miranda Auhl: PostgreSQL vs Python for data cleaning: A guide

1. Dezember 2021 - 15:59

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?

Ryan Lambert: Permissions required for PostGIS

1. Dezember 2021 - 6:01

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.

Hans-Juergen Schoenig: Primary Keys vs. UNIQUE Constraints in PostgreSQL

30. November 2021 - 10:00

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:

Robert Haas: Collation Stability

29. November 2021 - 21:39
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.

Franck Pachot: Text Search example with the OMDB sample database

29. November 2021 - 18:42

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.

Ibrar Ahmed: PostgreSQL 14 Database Monitoring and Logging Enhancements

29. November 2021 - 15:29

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.

Vincenzo Romano: A case involving Postgres and some carelessness

29. November 2021 - 15:17
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.