Neues vom PostgreSQL Planet

Too often, web tiers are full of boilerplate that does nothing except convert a result set into JSON. A middle tier could be as simple as a function call that returns JSON. All we need is an easy way to convert result sets into JSON in the database.

Most people in the SQL and in the PostgreSQL community have used the LIMIT clause provided by many database engines. However, what many do not know is that LIMIT / OFFSET are off standard and are thus not portable. The proper way to handle LIMIT is basically to use SELECT … FETCH FIRST ROWS. However, there is more than meets the eye.

During my most recent expedition of going through PostgreSQL source code, I ran into some mysterious variables in the page structure of some indexes. I will explain later why i am calling these mysterious but first have a look at these variables…

typedef struct HashPageOpaqueData
{
...
uint16 hasho_page_id; /* for identification of hash indexes */
} HashPageOpaqueData;

or

What is the amount of traffic generated in the Write Ahead Logs?

Even if logical replication is a method of major upgrade itself, sometimes you’ll want to mix and match them. Consider the following scenario: a big and busy database that has a logical replica, which has its specific tables and indexes. When it’s time to upgrade, you will probably want to upgrade both instances – the publisher and the subscriber. As the database is big an busy, using logical replication itself or dump-and-restore aren’t the best choices – those methods will require massive amount of storage, and dump-and-restore will also require a long period of downtime.

PostgreSQL Person of the Week Interview with Jean-Christophe Arnu: Hi, I’m Jean-Christophe. I live in the south of France, near Toulouse. As far as I can remember, I have always been interested in computer science and programming. I moved a lot when I was a child but I settled in the Toulouse area when I started university. I am the father of 3 children, which is my first full time job, my second being a PostgreSQL database consultant in a PostgreSQL and Cloud dedicated company (we all are remote workers): LOXODATA.

New pgSCV 0.7.0 has been released, with new features, bug fixes and minor improvements.

New pgSCV 0.7.0 has been released, with new features, bug fixes and minor improvements.

1. Introduction

I am working on a new PostgreSQL feature that redefines the way a tuple’s visibility status is determined. The feature is working very nicely until I start doing a large SELECT query, which triggers PostgreSQL to spawn multiple parallel workers to process the request. When this happens, the feature I am working on start to yield incorrect results. A good portion of the data tuples returned are missing because they are considered as invisible, while some portion of it remains visible.

We recently announced the release of version 5.0 of  PGO, the open source Postgres Operator from Crunchy Data.

LATERAL joins are one of the lesser-known features of PostgreSQL and other relational databases such as Oracle, DB2 and MS SQL. However, LATERAL joins are a really useful feature, and it makes sense to take a look at what you can accomplish with them.

Inspecting FROM more closely

Before we dive into LATERAL, it makes sense to sit back and think about SELECT and FROM clauses in SQL on a more philosophical level. Here is an example:

pgpool-II is a well-known tool to pool PostgreSQL connections and load-balance work loads. In this blog, we will verify whether pgpool works well on ARM64 architecture.

ARM64 packages

On Ubuntu, pgpool2 ARM64 debian package is made available by the Debian PostgreSQL Maintainers :

$ dpkg -s pgpool2
Maintainer: Debian PostgreSQL Maintainers
Architecture: arm64

We're excited to announce the release of PGO 5.0, the open source Postgres Operator from Crunchy Data.

Intro to Memory Management Most software can not predict the exact memory needs during compile time, so memory is dynamically allocated during run time. When a programm needs more memory it requests a new chunk of memory from the operating system. The OS can either grant or deny the request. Most modern software does not always care very much about the amount that is actually needed and tries to get a lot more than is currently necessary - just to have some spare for bad times.

Suppose that you want to be completely over-the-top paranoid about making sure that when you execute some particular SQL statement on your Postgres database, you’re doing it in the safest and least risky way?

For example, suppose it’s the production database behind your successful startup’s main commercial website. If anything even causes queries to block/pause for a few minutes then people will quickly be tweeting about how they can’t place orders and it hurt both your company’s revenue and reputation.

On some rare occasions, I’ve been asked if Postgres supports system-versioned temporal tables – in other words, time travel on table data for your queries. As quite often with Postgres, thanks to its extensibility, I can answer: well, officially, it does not…but, you can make it work, nevertheless

Just a quick note to say that PGXN Manager has been updated with more secure password storage. Existing passwords are unmodified, but the next time you change your password, it will be upgraded to a more robust password hashing algorithm that’s more resisant to attacks. I recommend everyone update their passwords. If you have a PGXN Manager account, just hit password reset, enter your username or email address, and check your mail for a reset link.

PostgreSQL Person of the Week Interview with Daniel Westermann: I grew up in Germany exactly at the border to Switzerland and I am still living in this area. For my entire life I have been enjoying the region where France, Switzerland and Germany come together. Today I live very close to the city of Basel, but still in Germany.

Materialized views in Postgres are a handy way to persist the result of a query to disk. This is helpful when the underlying query is expensive and slow yet high performance SELECT queries are required. When using materialized views they need to be explicitly refreshed to show changes to the underlying table. This is done through the REFRESH MATERIALIZED VIEW <name>; syntax.

I just released first version of change to explain.depesz.com that displays buffer I/O information, as described recently. You can see it in here. There are two new columns in there, showing how much data given node read from disk, and how much it wrote. It's definitely not 100% OK now, as you can see, speed … Continue reading "Buffers I/O information on explain.depesz.com"