Neues vom PostgreSQL Planet
Hans-Juergen Schoenig: How the PostgreSQL query optimizer works
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.
Kat Batuigas: Getting Started with EXPLAIN (ANALYZE)
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
Luca Ferrari: Managing Multiple PostgreSQL Instances on FreeBSD
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
Onder Kalacı: Sharding Postgres on a single Citus node, how why & when
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
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
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
An online event organized by PgTraining.
Joe Conway: Musings of a PostgreSQL Data Pontiff Episode 1
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
Hans-Juergen Schoenig: Setting up SSL authentication for PostgreSQL
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
Michael Christofides: Can auto_explain (with timing) have low overhead?
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
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
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
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.