Sammlung von Newsfeeds

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.

Our waitlist has now been activated, as the conference is close to selling out.

We have seats left, and may get a couple of extra ones if outstanding invoices are not paid on time. However, we have no ability to further extend the total number of seats (as they are limited by the venue). So if you want a chance to get one of these seats, make sure you sign up as soon as possible! The waitlist ensures fair allocation of the remaining seats.

A PostGIS cluster is essentially a PostgreSQL cluster with a primary and an arbitrary number of standby instances where the running operand image contains all the required PostGIS related extensions. [Continue reading...]

Compared to PostgreSQL alone, TimescaleDB can dramatically improve query performance by 1000x or more, reduce storage utilization by 90%, and provide features essential for time-series and analytical applications. Some of these features even benefit non-time-series data–increasing query performance just by loading the extension.

PostgreSQL 15 introduces a new feature that allows specifying column lists in publications, to restrict the amount of data replicated. Let's see what are the advantages of this feature and how to use it.

I don’t know if you’re aware that PostgreSQL Conference Europe (in Berlin this year) will have a daycare option for children. It’s been published here. I’m really happy with this and I hope this will help more parents to attend the event. Please follow the instructions if you need to add this option to your registration for the event. What will children do? The professionals who will take care of the children suggested the following activities:

Postgres is an awesome database for a lot of reasons, put simply it's a really feature rich, powerful, and reliable database. One of those rich areas is the many built-in functions in Postgres.

Let's take a look at a few of my favorites.

Date functions

First, let's start with one of the most simple built-in ones but when doing things with time is quite handy.

Want to know what time it is according to your database?

Around a month ago I wrote about new pretty-printer for SQL queries that I created. Today, figured I'll add command line tool for putting queries through it, to make my life a bit easier. Tool is named pg_prettify, and you can find in in my shell_utils repo. Example usage: =$ cat bad.sql SELECT n.nspname as … Continue reading "Prettify SQL queries from command line"

With pgBackRest 2.41 just released, a new feature called backup annotations is now available. Let’s see in this blog post what this is about.

Backup annotations

This new feature adds an --annotation option to the backup command. We can now attach informative key/value pairs to the backup and the option may be used multiple times to attach multiple annotations.

Example:

This topic describes how you can detect and solve PostgreSQL checkpoints ratio issues

What happened?

To detect checkpoints ratio issues, and have an ability to look in some historical metrics of this – you must have some kind of monitoring solution. Today, there is a rich selection of monitoring solutions – you can use any you already have, or install another one. Here we will discuss samples based on Awide management and monitoring solution.

In this blog we look at how server definitions can be imported and exported from pgAdmin using the command line or the graphical user interface. This allows server definitions to be migrated between pgAdmin installations and shared with colleagues.

Sequences are a core feature of SQL. However, some users are tempted to implement sequences to generate invoices. That’s dangerous and should be avoided. The core question is: Why? What’s the problem with using database-side sequences to put unique invoice numbers to send to clients? Let’s dive in and find out.

I’m excited to announce that the translation of Part III of the “PostgreSQL 14 Internals” book is finished. This part is about a diverse world of locks, which includes a variety of heavyweight locks used for all kinds of purposes, several types of locks on memory structures, row locks which are not exactly locks, and even predicate locks which are not locks at all.

A new release of the PostgreSQL virtual environment manager.

pgenv 1.3.2 is out!

Today we released version 1.3.2 of pgenv, the binary manager for PostgreSQL.
This release fixes a quite subtle bug in the handling of the configuration that prevented custom settings to be correctly loaded back into the running system. Users are encouraged to upgrade as soon as possible.