Neues vom PostgreSQL Planet

In recent years, PostgreSQL has seen several improvements which make sorting faster. In the PostgreSQL 15 development cycle—which ended in April 2022—Ronan Dunklau, Thomas Munro, Heikki Linnakangas, and I contributed some changes to PostgreSQL to make sorts go even faster.

Each of the improvements to sort should be available when PostgreSQL 15 is out in late 2022.

Instant Heatmap with pg_featureserv

The pg_featureserv micro-service is a thin middleware that binds tables and functions in a PostgreSQL database to a JSON collections API, accessible over HTTP. Using the Crunchy Bridge container apps, I'm going to give a quick overview of how to set up a web based spatial heatmap from Postgres.

Red Hat Enterprise Linux 9 is out. RPMs are already out even since 9.0 beta was announced.
Continue reading "How to install PostgreSQL 14, 13, 12, 11 and 10 to Red Hat Enterprise Linux 9"

A statistic corruption on TOAST chunk case Introduction

PostgreSQL stores data on a page that usually has 8KB of size, this means that when there isn’t enough space to store the data (for example, text or varchar data type), PostgreSQL uses the TOAST technique which allows us to store this extra data like chunks using other tables, each chunk is stored as a separate row in the TOAST table belonging to the main table. The users don’t need to know if the information is stored on TOAST.

7 years ago I left Heroku. Heroku has had a lot of discussion over the past weeks about its demise, whether it was a success or failure, and the current state. Much of this was prompted by the recent, and on-going security incident, but as others have pointed out the product has been frozen in time for some years now.

 

    Happy 20th Anniversary, Npgsql!

 

   It seems like yesterday when I decided to start Npgsql. And today, Npgsql completes 20 years since the first public beta release!

    On May 18th, 2002, this is what was written in the first release notes of Npgsql  version 0.1:

 

For many years the documentation has contained the following statement :

If the file includes a function named _PG_fini, that function will be called immediately before unloading the file. Likewise, the function receives no parameters and should return void. Note that _PG_fini will only be called during an unload of the file, not during process termination. ( Presently, unloads are disabled and will never occur, but this may change in the future . )

For many years the documentation has contained the following statement :

If the file includes a function named _PG_fini, that function will be called immediately before unloading the file. Likewise, the function receives no parameters and should return void. Note that _PG_fini will only be called during an unload of the file, not during process termination. ( Presently, unloads are disabled and will never occur, but this may change in the future . )

I have just completed the first draft of the Postgres 15 release notes. (It still needs more XML markup and links.) The release note feature count is 186, which is similar to recent major releases, excluding Postgres 14's high feature count. Postgr

PostgreSQL Person of the Week Interview with Haki Benita: I’m Haki Benita. I’m living with my family in a city near Tel-Aviv. My interest in development started at around junior-high, and I’ve been working as a professional developer since I was 18.

In the old days, things were simpler. Computer systems were running in the server room in the office building, hopefully the server was carefully sized, unix operating systems ran processes, and when a server was overloaded, processes waited in the runqueue.

In todays world, this is different. Computer systems are running in the cloud, mostly sizing means you can scale in the cloud, and the linux operating system is running processes and (lots of) threads.

pg_ivm v1.0 was officially released!

pg_ivm is an extension module that provides Incremental View Maintenance (IVM) feature, which is a way to make materialized views up-to-date in which only incremental changes are computed and applied on views rather than recomputing.

In previous articles we discussed query execution stages and statistics . Last time, I started on data access methods, namely Sequential scan . Today we will cover Index Scan. This article requires a basic understanding of the index method interface. If words like "operator class" and "access method properties" don't ring a bell, check out my article on indexes from a while back for a refresher. Plain Index Scan Indexes return row version IDs (tuple IDs, or TIDs for short), which can be handled in one of two ways. The first one is Index scan .

Frequent participants in the Postgres Discord and Slack servers, mailing lists, IRC chat, and other Postgres hangouts often hear a common refrain: I’ve installed Postgres, so now what? We may be tempted to say “Go forth young one, and explore!” Yet there’s a more fundamental question left unanswered: how do you even connect to explore in any capacity? What does that entail? Let’s explore how to connect to a fresh Postgres installation, and see what a new user really needs to know in order to experiment with Postgres.

One of the most requested features by Crunchy Data customers using modern enterprise database environments is some form of data encryption. However, nailing down exactly what someone means when they say "We need our data encrypted" is often a challenge due to the actual requirements not being fully clarified or even understood. So, before anyone tries to implement database encryption it is critically important to understand what needs to be encrypted and what benefit is actually gained by the methods that are employed.

PostgreSQL 15 will include COPY FREEZE support

 In my blog post I reported that a patch to allow to use COPY FREEZE in pgbench was accepted for upcoming PostgreSQL 15  (supposed to be released by the end of 2022). It seems the patch has survived without being reverted!

pgenv 1.3.0 adds a new command: switch

pgenv switch`

pgenv, a simple but great shell script that helps managing several PostgreSQL instances on your machine, have been improved in the last days.

Thanks to the contribution of Nils Dijk @thanodnl on GitHub, there is now a new command named switch that allows you to quickly prepare the whole environment for a different PostgreSQL version without having to start it.

pgAdmin 4 provides 2 different ways to execute queries on the database: * Query tool: 
 You can write and execute custom queries on any table in the connected database. * View/Edit data: 
 View/Edit data can be used to view or modify a particular table's data.

If your organization requires professional assistance on Postgres in Kubernetes with CloudNativePG, you can take advantage of EDB’s leading expertise. Gabriele will be speaking about Kubernetes in Postgres at the Data on Kubernetes Day before KubeCon Europe 2022. [Continue reading...]

© Laurenz Albe 2022

Next to character encoding, time zones are among the least-loved topics in computing. In addition, PostgreSQL’s implementation of timestamp with time zone is somewhat surprising. So I thought it might be worth to write up an introduction to time zone management and recommendations for its practical use.

Time zones and the SQL standard

The SQL standard has rather limited provisions for specifying time zones:

::= ::= [ ] ::=

It has the following to say about time zones: