Neues vom PostgreSQL Planet

Backward compatibility is straightforward. You have full control over new code and you have full knowledge of past data and APIs. Forward compatibility is more challenging. You have full control over new code, but you don't know how data is going to change in the future, and what types of API you're going to have to support.

We, at Fundación, believe that the current Postgres trademarks disagreement should not require a years-long litigation process. Instead, we publicly propose here a solution.

Watch below our President’s message, in a short video (4:44) addressed to the whole Postgres Community.

Did you ever wonder why VACUUM does not make your PostgreSQL tables smaller? Did you ever wonder why VACUUM does not shrink data files? Well, maybe this is the article you have been looking for. The point is: usually, space is not returned to the operating system after a cleanup and it is important to understand why. Often people make wrong assumptions about the inner workings of VACUUM. It makes sense to dig deeper and understand what’s really going on.

The following post reveals the most useful secrets about VACUUM to users.

In late 2020 when osm2pgsql released the flex output I eagerly jumped on that bandwagon. The osm2pgsql flex output enabled the type of data structure and cleanup abilities I had always wanted from osm2pgsql. By January 2021 the PgOSM Flex project was up and running and I was phasing out my legacy OpenStreetMap processes.

Connecting to PostgreSQL and to YugabyteDB YSQL will have a latency/delay. The reason is the database client must setup a network connection, and let the database create a server side facility to respond to the requests and keep the state of the connection.

PostgreSQL Person of the Week Interview with Joseph Sciarrino: I’m the co-founder and CEO of Hydra - the open source data warehouse that’s built on Postgres. I grew up in Connecticut, but have spent my adult life all across the US; North Carolina, Utah, Virginia, New York, and at long last, California. I live by Duboce park in San Francisco which is great for our tiny dog, Titus.

Welcome to the inaugural invite of #PGSQLPhriday, a new, monthly blogging event for the larger PostgreSQL community! Each month, a different member of the PostgreSQL community will act as the host, choose the topic, and write an invite post similar to this. Once the blog posts are contributed, the host will write a wrap-up of all the contributions. What a great way to share your knowledge with other PostgreSQL users!!

(The topic for this first event is explained a bit further , after a brief detour to discuss the last-minute name change.)

I had coffee with an IT colleague here in Victoria last week, and he was interested in getting into core PostgreSQL programming. “What resources would you recommend I look at?”

That’s… a hard question!

PostgreSQL is a huge code base with a multi-decade history. I’ve been poking around the edges for almost 10 years and feel comfortable with the extension APIs, foreign data wrappers, access methods APIs, some system catalogue stuff… maybe 5% of the surface area of the beast?

I'm excited to announce my book, Mastering PostGIS and OpenStreetMap, is available to purchase as of October 1, 2022! This book provides a practical guide to introduce readers to PostGIS, OpenStreetMap data, and spatial querying. Queries used for examples are written against real OpenStreetMap data (included) to help you learn how to navigate and explore complex spatial data.

1.0 Introduction

Recently I have been involved in creating a solution that guarantees cross-partition uniqueness within a partitioned table consisting multiple child tables. Some people refer to this feature as a global index and there has been some discussion about it in the email thread here. Though the idea is good, the approach sparks a lot of controversies as the approach changes how partitioned indexes are stored.

Almost everyone using PostgreSQL, as well as the users of YugabyteDB, are using pg_stat_statements for looking at historical execution. However, there are some things that lots of people keep on wondering about, which is what this blogpost is about.

By combining triggers, the PostgreSQL LISTEN/NOTIFY system, and the pg_eventserv service, you can build a real-time application that keeps your web application state perfectly in sync with your database state.

pg_eventserv converts events from the PostgreSQL event bus to standard WebSockets messages that any web client can handle.

Oh ****, everyone knows that bad words shouldn't be used!

This post is about a lesser known 'feature' of postgres: the loading or creation of the pg_internal.init files (plural) that happens when logging on to a postgres database. Because YugabyteDB reuses the postgres source, YugabyteDB performs dealing with the pg_internal.init files identical to postgres.

What is LISTEN and NOTIFY?

LISTEN / NOTIFY is a feature that enables users to listen to what goes on in the database. It is one of the oldest functionalities in PostgreSQL and is still widely used. The main question is: What is the purpose of the asynchronous query interface (LISTEN / NOTIFY), and what is it good for? The basic idea is to avoid polling.

Often, code works like this:

Pgpool-II Exporter is a tool that periodically collects Pgpool-II and PostgreSQL Cluster's metrics and exports the results as Prometheus metrics. 

Pgpool-II Exporter uses SHOW command to collect Pgpool-II and PostgreSQL Cluster's metrics, including:

PostgreSQL Person of the Week Interview with Nicolas Thauvin: I am a PostgreSQL DBA from France. I live near the sea in Normandy.

 Query cache in Pgpool-II

Pgpool-II has built-in in memory query result cache which may dramatically enhance performance in certain use cases. If your workload is mostly read only SELECT and certain query is frequently used, then you might be able to enjoy the feature. When a SELECT comes to Pgpool-II, it saves the result of the query in shared memory or memcached (you can configure which one to be used).

One of the new features in PostgreSQL 15 is that you can use ICU collations on a database and instance level.

In PostgreSQL 10, we first added ICU support to PostgreSQL. That allowed you to define collation objects that use ICU as the backing locale library (called collation provider or locale provider in PostgreSQL), as an alternative to the existing “libc” provider. For example,

I was browsing the Postgres slack channel when I ran into this intriguing question from a member: How come my query SELECT TO_CHAR(POW(2,54), ‘9999999999999999999’) AS V0, TO_CHAR(POW(2,54)-1, ‘9999999999999999999’) AS V1; gives that result? v0 | v1 ------------------------------+---------------------- 18014398509481984 | 18014398509481984 Of course, I did some IT support, so my first reaction was to test by myself: laetitia=# select laetitia-# power(2,54) as v1, laetitia-# power(2,54)-1 as v2; v1 | v2 ------------------------+------------------------ 1.