Neues vom PostgreSQL Planet

What is the purpose of safety systems? To make things safer? To make them appear safer? To satisfy some external requirement? The purpose of safety systems is not always clear, but even for safety systems whose sole purpose is to increase safety — do they always succeed in increasing safety? The simple answer is "no". Here are three examples:

PostgreSQL Person of the Week Interview with Oleksii Kliukin: Hi, my name is Oleksii (people also call me Alex). I work as a Database Engineer at Adjust.com, improving their PostgreSQL infrastructure. Before that, I worked on Patroni and the Postgres Operator at Zalando. I’m originally from Simferopol, Ukraine. I moved to Germany around 7 years ago. I live in Berlin with my wife and two sons, three years old and 7 months old. When I have time, I enjoy spending it outdoors, hiking, cycling, or running.

And now we arrive at the second article in our migration from Oracle to PostgreSQL series. This time we’ll be taking a look at the START WITH/CONNECT BY construct. In Oracle, START WITH/CONNECT BY is used to create a singly linked list structure starting at a given sentinel row. The linked list may take the form of a tree, and has no balancing […]

Just pushed some new changes to expain.depesz.com and it's underlying parsing library. The changes are: If you upload plan in JSON/YAML/XML format, and then you'll look at “TEXT" tab, you will see explain in text format, generated using data from JSON. This explain had slight error in number formatting (3 decimal digits for estimated costs, … Continue reading "Changes on explain.depesz.com"

How to find the PostgreSQL configuration file on an unknown system?

Locating the PostgreSQL configuration file

Sometimes you get to manage a PostgreSQL instance on an unknown system, and this means you don’t know how to locate the PostgreSQL configuration file.
An example could be when you are running PostgreSQL on a Docker container:

Sometimes PostgreSQL users get errors and warnings and they are unable to understand why. To cater to these situations, this blog will cover some common errors and warnings in PostgreSQL. In some cases, it is a user setting problem or query error, but in other cases, it can be a PostgreSQL bug. But, it is quite rare to be a PostgreSQL bug, and therefore it is really important to differentiate between user error and PostgreSQL bug. Here is a list of some common PostgreSQL errors, with symptoms and solutions.

In our last installment, we covered the use of a constraint trigger to enforce data quality by looking at geometry spatial relationships.

© Laurenz Albe 2020

A while ago, I wrote about B-tree improvements in v12. PostgreSQL v13, which will come out later this year, will feature index entry deduplication as an even more impressive improvement. So I thought it was time for a follow-up.

Most people who deal with relational databases think of transaction commits as binary operations — the query is running and not yet committed, then it is completed and committed. However, internally, there are many stages to a commit:

If constraints in general have caught your interest, our interactive learning portal has a whole section on the use of non-spatial constraints, even a video walkthrough!

A hierarchical query is an SQL query that handles hierarchical model data such as the structure of organizations, living species, and a lot more. All important database engines including PostgreSQL, Oracle, DB2 and MS SQL offer support for this type of query.

I ran into the challenge to determine the best partitioning key on an Amazon RDS Postgres database. Normal approach would be to start a project trying to implement this on a development environment. But in most cases, like during a QuickScan, the customer expects an educated guess in as little time as possible. It concerns running production environments where you would rather not release all kinds of scripts and analysis tooling to avoid disrupting the primary process. Which then also takes up unnecessary extra time.

Having presented at two online conferences in the past two weeks, presenting at two this coming week, and presenting at many EDB-sponsored webinars, I have learned a few things about online presentations that might be helpful for Postgres organizers, speakers, and attendees:

PostgreSQL Person of the Week Interview with Markus Winand: I’m living in Vienna, Austria together with my wife and my 8-year-old son. Vienna is also the base of my one-man business for SQL consulting, training and publishing.

The term “Window Functions” never really give much away in terms of the capability and various options they provide. So, it made sense to explore these and while doing so, I thought it’s worth sharing. Turns out, Window functions are quite useful and can simplify writing complicated SQL queries.

The PostGIS Team is pleased to release PostGIS 2.3.11. This is the last bug fix release of the PostGIS 2.3 series. Please upgrade to 2.4 or higher if you want to continue receiving bug fixes.

HighGo Software Inc. (Canada) is pleased to announce the first GitHub community release of MongoDB Logical Decoding plugin v1.0.6 (wal2mongo), which can be used to replicate PostgreSQL database changes to an output format that can be directly fed into the mongo client tool to achieve logical replication between PostgreSQla and MongoDB. Wal2mongo plugin is useful for a case where PostgreSQL is used as the main raw data source to collect data from outside world but MongoDB is used internally for data analytics purposes.

At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive.

There is still significant confusion about characters sets, encodings, and collations. This is because in the real, non-digital world, we usually treat languages, their characters, and ordering as unified, but in the digital world, they are treated separately, and their distinction can be hard to visualize.

Generating simple data sets

Before we get started I want to introduce my favorite set-returning functions which can help you to generate sample data: