Neues vom PostgreSQL Planet

Just like any advanced relational database, PostgreSQL uses a cost-based query optimizer that tries to turn your SQL queries into something efficient that executes in as little time as possible. For many people, the workings of the optimizer itself remain a mystery, so we have decided to give users some insight into what is really going on behind the scenes.

On 19th of March 2021, Robert Haas committed patch: Allow configurable LZ4 TOAST compression.   There is now a per-column COMPRESSION option which can be set to pglz (the default, and the only option in up until now) or lz4. Or, if you like, you can set the new default_toast_compression GUC to lz4, and then … Continue reading "Waiting for PostgreSQL 14 – Allow configurable LZ4 TOAST compression."

In a previous post, I talked about pg_stat_statements as a tool for helping direct your query optimization efforts. Now let's say you've identified some queries you want to look into. The EXPLAIN command helps you look even closer into an individual query.

PostgreSQL Person of the Week Interview with Julien Riou: My name is Julien Riou. I currently work at OVHcloud, a major Cloud Computing provider in Europe. As a student, I discovered GNU/Linux. I started my career as a system administrator, then I became an open-source database specialist. I live in Belgium, a small country with cold and wet Winters but also with warm welcoming people.

FreeBSD service(8) is a fully featured system to manage services, and allows multiple instances of PostgreSQL.

This was the most common cause of calls at might: your cluster ran out of space. Nowadays, we can add storage on the fly, storage is cheap and (normally) we do monitor disk space so that this should not happen. Anyhow, how can we deal with such a problem? To simulate such a problem, I created a virtual host and I created a bug empty file to almost fill the file system.

One of the big new things in Citus 10 is that you can now shard Postgres on a single Citus node. So in addition to using the Citus extension to Postgres to scale out Postgres across a distributed cluster, you can now also:

Bloating in database is created when tables or indexes are updated, an update is essentially a delete and insert operation. The diskspace used by the delete is available for reuse but it is not reclaimed hence creating the bloat. Same is the case with PostgreSQL database, frequent UPDATE and DELETE operations can leave a lot of unused space in table or index relation files on disk. Over the time this space can build up and cause the performance degradation for both tables and indexes. This buildup is referred to as bloated tables or indexes.

We started with problems related to isolation, made a digression about low-level data structure, discussed row versions in detail and observed how data snapshots are obtained from row versions.

An online event organized by PgTraining.

Welcome to Episode 2 of the "Musings of a PostgreSQL Data Pontiff" series! In this installment I’m aiming to achieve three objectives.

Introduction to a PostgreSQL "Data Science" Blog Series

This is the first in a series of blogs on the topic of using PostgreSQL for "data science". I put that in quotes because I would not consider myself to be a practicing data scientist, per se. Of course I'm not sure there is a universally accepted definition of data scientist.

The query parser is a core component of Postgres: the database needs to understand what data you're asking for in order to return the right results. But this functionality is also useful for all sorts of other tools that work with Postgres queries. A few years ago, we released pg_query to support this functionality in a standalone C library. pganalyze uses pg_query to parse and analyze every SQL query that runs on your Postgres database. Our initial motivation was to create pg_query for checking…

PostgreSQL is a secure database and we want to keep it that way. It makes sense, then, to consider SSL to encrypt the connection between client and server. This posting will help you to set up SSL authentication for PostgreSQL properly, and hopefully also to understand some background information to make your database more secure.

At the end of this post, you should be able to configure PostgreSQL and handle secure client server connections in the easiest way possible.

Months ago, I woke up with a fantastic idea: adding an option in pg_dump to export stored functions and procedures only. It happened that, as a consultant, I sometimes need weird tools like that to better understand what my customers did. So I wrote it down in my todo list (46 items, 7 checked) and I left it there waiting for a time when I’ll be free enough to take care of it.

Introduction

auto_explain is a very cool PostgreSQL module that can be used to log the execution plans of your slowest queries automatically. It does this by tracking every query (or a sample of them), only logging the output for those slower than a threshold you set.

The Crunchy Data team announced the latest release of our open source PostgreSQL Operator for Kubernetes 4.6 a few weeks back. So let's take a whirlwind tour of how we make it easy to run production-quality Postgres on Kubernetes.

Last time, we experimented with lesser known PostGIS functions to extract areas of interest for sales. Now, let’s extend our example regarding catchment areas by optimizing trips within the area of interest we generated in our previous example, which is around Hamburg. Let’s ask the following question:

Crunchy Data has recently announced an update to the CIS PostgreSQL Benchmark by the Center for Internet Security, a nonprofit organization that provides publications around standards and best practices for securing technologies systems.

PostgreSQL Person of the Week Interview with Anastasia Lubennikova: Hi, I am Anastasia, and I live in Moscow, Russia. I am a core developer working at Postgres Professional and all my career so far was devoted to the PostgreSQL project. In my spare time I like reading science fiction, jogging on the embankments of the Moscow river and practicing yoga. Sometimes I sign up to do something that excites and scares me at the same time, and it usually turns out to be an amazing adventure.