Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 55 Minuten 7 Sekunden

Paul Ramsey: Generating JSON Directly from Postgres

14. Juli 2021 - 18:18

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.

Hans-Juergen Schoenig: PostgreSQL: LIMIT vs FETCH FIRST ROWS … WITH TIES

14. Juli 2021 - 10:00

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.

Neil Chen: First contact with the pg_filedump

14. Juli 2021 - 4:34

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;


Luca Ferrari: How much data goes into the WALs?

13. Juli 2021 - 2:00

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

Michał Mackiewicz: What happens to logical replication after running pg_upgrade?

12. Juli 2021 - 18:51

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.

Andreas 'ads' Scherbaum: Jean-Christophe Arnu

12. Juli 2021 - 16:00
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.

Alexey Lesovsky: pgSCV 0.7.0 released

12. Juli 2021 - 15:24

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

Weaponry Weaponry: pgSCV 0.7.0 released

12. Juli 2021 - 15:24

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

cary huang: Using GDB To Trace Into a Parallel Worker Spawned By Postmaster During a Large Query

9. Juli 2021 - 23:08
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.

Hans-Juergen Schoenig: Understanding LATERAL joins in PostgreSQL

8. Juli 2021 - 10:00

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:

Amit Khandekar: A quick sanity testing of pgpool-II on ARM64

7. Juli 2021 - 21:52

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

Jonathan Katz: The Next Generation of Kubernetes Native Postgres

7. Juli 2021 - 17:30

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

Alexander Sosna: PostgreSQL, Memory and the Cloud™

7. Juli 2021 - 2:01
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.

Jeremy Schneider: Paranoid SQL Execution on Postgres

6. Juli 2021 - 13:05

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.

Kaarel Moppel: Simulating temporal tables with dblink and replication delay

6. Juli 2021 - 10:00

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

David E. Wheeler: Password Storage Update

6. Juli 2021 - 1:12

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.

Andreas 'ads' Scherbaum: Daniel Westermann

5. Juli 2021 - 16:00
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.

Ryan Lambert: Postgres Permissions and Materialized Views

5. Juli 2021 - 3:30

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.

Hubert 'depesz' Lubaczewski: Buffers I/O information on

4. Juli 2021 - 21:59
I just released first version of change to 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"