Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 1 Stunde 24 Minuten

Hans-Juergen Schoenig: How the PostgreSQL query optimizer works

24. März 2021 - 9:30

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.

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 14 – Allow configurable LZ4 TOAST compression.

22. März 2021 - 21:18
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."

Kat Batuigas: Getting Started with EXPLAIN (ANALYZE)

22. März 2021 - 20:42

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.

Andreas 'ads' Scherbaum: Julien Riou

22. März 2021 - 15:00
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.

Luca Ferrari: Managing Multiple PostgreSQL Instances on FreeBSD

22. März 2021 - 1:00

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

Lætitia AVROT: No space left on device

21. März 2021 - 14:53
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.

Onder Kalacı: Sharding Postgres on a single Citus node, how why & when

20. März 2021 - 17:25

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:

Asif Rehman: How to check and resolve Bloat in PostgreSQL

20. März 2021 - 16:32

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.

Egor Rogov: MVCC in PostgreSQL — 8. Freezing

19. März 2021 - 1:00

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.

Luca Ferrari: PgTraining online webinar 2021-03-12 (Italian): video available

19. März 2021 - 1:00

An online event organized by PgTraining.

Joe Conway: Episode 2 - Hot Magick; Analytics and Graphical Output Using PL/R with Magick

18. März 2021 - 19:26

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

Joe Conway: Musings of a PostgreSQL Data Pontiff Episode 1

18. März 2021 - 19:00
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.

Lukas Fittl: Introducing pg_query 2.0: The easiest way to parse Postgres queries

18. März 2021 - 13:00
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…

Hans-Juergen Schoenig: Setting up SSL authentication for PostgreSQL

18. März 2021 - 10:30

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.

Lætitia AVROT: Why I did not write that patch

16. März 2021 - 21:37
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.

Michael Christofides: Can auto_explain (with timing) have low overhead?

16. März 2021 - 16:35

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.

Jonathan Katz: Crunchy Postgres Operator 4.6.0

16. März 2021 - 16:15

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.

Florian Nadler: Traveling Salesman problem with PostGIS and pgRouting

16. März 2021 - 10:00

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:

Doug Hunley: Enhancing PostgreSQL 13 Security with the CIS Benchmark

15. März 2021 - 21:05

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.

Andreas 'ads' Scherbaum: Anastasia Lubennikova

15. März 2021 - 15:00
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.