Neues vom PostgreSQL Planet

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

Haki Benita: Future Proofing SQL with Carefully Placed Errors

5. Oktober 2022 - 23:00

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.

Álvaro Hernández: PostgreSQL Trademarks Disagreement: Proposing a Solution

5. Oktober 2022 - 13:39

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.

Hans-Juergen Schoenig: VACUUM does not shrink my PostgreSQL table

4. Oktober 2022 - 11:43

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.

Ryan Lambert: Better OpenStreetMap data using PgOSM Flex 0.6.0

4. Oktober 2022 - 7:01

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.

Frits Hoogland: Postgres and Yugabyte connection latency

3. Oktober 2022 - 17:03

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.

Andreas 'ads' Scherbaum: Joseph Sciarrino

3. Oktober 2022 - 16:00
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.

Ryan Booz: PGSQL Phriday #001 – Two truths and a lie about PostgreSQL

3. Oktober 2022 - 15:38

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.)

Paul Ramsey: Learning PostgreSQL Internals

1. Oktober 2022 - 10:00

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?

Ryan Lambert: Book Release! Mastering PostGIS and OpenStreetMap

1. Oktober 2022 - 4:15

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.

cary huang: How Unique Index Works in PG

30. September 2022 - 20:23
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.

Frits Hoogland: All about pg_stat_statements

30. September 2022 - 16:40

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.

Paul Ramsey: Real-time Database Events with pg_eventserv

30. September 2022 - 16:15

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.

Francesco Tisiot: Remove naughty words from your data using DataCater

30. September 2022 - 2:00

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

Frits Hoogland: Postgres first database connection latency

28. September 2022 - 17:20

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.

Hans-Juergen Schoenig: LISTEN / NOTIFY: Automatic client notification in PostgreSQL

27. September 2022 - 11:00
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:

Bo Peng: Grafana Dashboard for Pgpool-II Exporter

26. September 2022 - 19:56

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:

Andreas 'ads' Scherbaum: Nicolas Thauvin

26. September 2022 - 16:00
PostgreSQL Person of the Week Interview with Nicolas Thauvin: I am a PostgreSQL DBA from France. I live near the sea in Normandy.

Tatsuo Ishii: Query cache improvement in Pgpool-II 4.4

26. September 2022 - 11:02
 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).

Peter Eisentraut: ICU features in PostgreSQL 15

26. September 2022 - 6:00

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,

Lætitia AVROT: Preferred types

24. September 2022 - 2:00
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.