Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 2 Stunden 5 Minuten

Frits Hoogland: How to enable SSL for postgres connections

4. April 2022 - 15:14

Using encrypted network connections is not really a choice in a modern environment. But: using an additional encryption phase, and using an encryption handshake will add latency and overhead to a network connection.

This writeup is more a scratchpad for myself, because there are other blogposts that guide you with enabling SSL. However, these did not exactly what I wanted to do.

Luca Ferrari: pgagroal log rotation and formatting

4. April 2022 - 2:00

My small contributions to pgagroal.

pgagroal log rotation and formatting

A few weeks ago I implemented a small contribution to [pgagroal]({:target="_blank"}, the high-performance PostgreSQL connection pooler, in order to implement log rotation and log formatting.
At last, my contribution was accepted and merged, but I did not get enough time to write on this until now.

Peter Bengtsson: How to sort case insensitively with empty strings last in Django

3. April 2022 - 21:34

Imagine you have something like this in Django:

class MyModel(models.Models): last_name = models.CharField(max_length=255, blank=True) ...

The most basic sorting is either: queryset.order_by('last_name') or queryset.order_by('-last_name'). But what if you want entries with a blank string last? And, you want it to be case insensitive. Here's how you do it:

Joshua Tolley: On Shapefiles and PostGIS

2. April 2022 - 2:00

Partial map of the voyage of the Endurance, from “South”, by Ernest Shackleton

The shapefile format is commonly used in geospatial vector data interchange, but as it’s managed by a commercial entity, Esri, and as GIS is a fairly specialized field, and perhaps because the format specification is only “mostly open”, these files can sometimes be confusing to the newcomer. Perhaps these notes can help clarify things.

Shaun M. Thomas: PG Phriday: Tidying Up With VACUUM

1. April 2022 - 17:15
Hot on the heels of our discussion on preventing Postgres XID wraparound using basic monitoring, let’s talk about arresting the risk almost entirely through Autovacuum. With a few relatively minor tweaks to our configuration and focusing on the occasional problematic table, we can both increase maintenance throughput, and also reduce impact on client queries. Let’s demystify the art of VACUUM and keep our cluster self-maintaining in the bargain. [Continue reading...]

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – SQL/JSON query functions

1. April 2022 - 12:13
On 29th of March 2022, Andrew Dunstan committed patch: SQL/JSON query functions   This introduces the SQL/JSON functions for querying JSON data using jsonpath expressions. The functions are:   JSON_EXISTS() JSON_QUERY() JSON_VALUE()   All of these functions only operate on jsonb. The workaround for now is to cast the argument to jsonb.   JSON_EXISTS() tests … Continue reading "Waiting for PostgreSQL 15 – SQL/JSON query functions"

Lukas Fittl: How Postgres Chooses Which Index To Use For A Query

1. April 2022 - 10:15
Using Postgres sometimes feels like magic. But sometimes the magic is too much, such as when you are trying to understand the reason behind a seemingly bad Postgres query plan. I've often times found myself in a situations where I asked myself: "Postgres, what are you thinking?". Staring at an EXPLAIN plan, seeing a , and being puzzled as to why Postgres isn't doing what I am expecting. This has lead me down the path of reading the Postgres source, in search for answers. Why is Postgres choosing…

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – IS JSON predicate

1. April 2022 - 3:13
On 28th of March 2022, Andrew Dunstan

David Z: parallel commit in postgres fdw

31. März 2022 - 23:46
1. Overview

PostgreSQL is one of the greatest open source databases, not only because of the extensibility and SQL compliance but also the evolution of new features. For example, in postgres_fdw, there is a new feature parallel commit has been added into the main branch and will be released in PG15. This blog is for a quick taste of this new feature.

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – SQL/JSON constructors

31. März 2022 - 21:32
On 27th of March 2022, Andrew Dunstan committed patch: SQL/JSON constructors   This patch introduces the SQL/JSON standard constructors for JSON:   JSON() JSON_ARRAY() JSON_ARRAYAGG() JSON_OBJECT() JSON_OBJECTAGG()   For the most part these functions provide facilities that mimic existing json/jsonb functions. However, they also offer some useful additional functionality.

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – Add support for MERGE SQL command

31. März 2022 - 14:44
On 28th of March 2022, Alvaro Herrera committed patch: Add support for MERGE SQL command   MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows -- a task that would otherwise require multiple PL statements. For example, … Continue reading "Waiting for PostgreSQL 15 – Add support for MERGE SQL command"

Egor Rogov: Queries in PostgreSQL: 3. Sequential scan

31. März 2022 - 2:00

In previous articles we discussed how the system plans a query execution and how it collects statistics to select the best plan. The following articles, starting with this one, will focus on what a plan actually is, what it consists of and how it is executed.

Regina Obe: Creating cumulative sums by combining aggregation with windowing

30. März 2022 - 21:14

When you want to include a running sum for each line of data in your query, you generally use a window clause with SUM. What if you don't want to list all line items of your data. You want a report that gives you a weekly sum and another that gives you the running sum for the whole year. What do you do then? We'll demonstrate how to do that.

Devrim GÜNDÜZ: How To Build Your Own PostgreSQL (and related software) RPMs on RHEL/Rocky/Fedora

30. März 2022 - 17:48
Even though there is a wide range of RPMs for PostgreSQL and related projects in the repositories, some people don't want to use prebuilt options, or may apply some extra specific patches. Also, some companies prefer to build their packages from srpms, per their IT policy -- so they maintain their own packages.

Ryan Booz: Identify PostgreSQL Performance Bottlenecks With pg_stat_statements

30. März 2022 - 15:15

We continue our #AlwaysBeLaunching Cloud Week with MOAR features! 🐯☁️ Today, we’re introducing you to pg_stat_statements, a PostgreSQL extension now enabled by default in all Timescale Cloud services. pg_stat_statements allows you to quickly identify problematic queries, providing instant visibility into your database performance.

Claire Giordano: Ultimate Guide to Citus Con: An Event for Postgres

29. März 2022 - 21:33

One of the good things with a virtual event like Citus Con is that you have a lot of flexibility about where and when to watch the talks. From your home office, or a café, or the beach—or even the car, while you wait to pick up your kids. As long as you have an internet connection, you’re in.

Bo Peng: Installing Crunchy Postgres Operator v5 on EKS

29. März 2022 - 17:44

In my previous post I described how to deploy Crunchy Postgres Operator v4 on Kubernetes and use it to achieve disaster recovery and high availability. The new major version, v5 was release last year and the installation methods have significantly changed. 

Oleg Bartunov: SQL/JSON in PG15 !

29. März 2022 - 14:30

Slide from my talk Understanding Jsonb performance at PGCONF.NYC, Dec 2, 2021, with some corrections.

Laurenz Albe: How to DROP ROLE or DROP USER in PostgreSQL

29. März 2022 - 10:00

© Laurenz Albe 2022

You might, at first glance, believe that DROP ROLE (or DROP USER, which is the same) is a simple matter. However, that’s not always the case. So I thought it might be a good idea to show you the problems involved and how to solve them.

Andreas 'ads' Scherbaum: Claire Giordano

28. März 2022 - 16:00
PostgreSQL Person of the Week Interview with Claire Giordano: I have lived in northern California in the San Francisco Bay Area for my entire adult career, from the time I was a junior software engineer in the developer tools group at Sun. But before that, I moved around — a lot. I was born in Taiwan, and then lived in Rhode Island, California, New Jersey, Athens Greece, Mississippi, Rhode Island again, and New Hampshire.