Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 1 Stunde 49 Minuten

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"

Bo Peng: Disaster Recovery Strategies for PostgreSQL Deployments on Kubernetes (Part 2)

4. Juli 2021 - 18:24

In my previous post, I described how to use multi-cluster architecture provided by Crunchy PostgreSQL Operator to achieve disaster recovery and high availability on Kubernetes.

sait talha nisanci: Citus Tips: JOINs between local & distributed Postgres tables

2. Juli 2021 - 18:05

If you have a large PostgreSQL database that runs on a single node, eventually the single node’s resources—such as memory, CPU, and disk—may deliver query responses that are too slow. That is when you may want to use the Citus extension to Postgres to distribute your tables across a cluster of Postgres nodes.

Dinesh Chemuduru: Announcing Credcheck extension to enforce username and password checks in PostgreSQL

2. Juli 2021 - 17:15

PostgreSQL includes robust security standards around all aspects such as authentication, authorization, accounting and auditing. User management and role based segregation that satisfy enterprise standards can be implemented at ease. Similarly, organizations may be willing to enforce certain compliance around the password standards such as the minimum upper case characters or minimum number of numbers and special characters and the length of the password. Such username password checks are important in an environment where strong security policies are a must.

Regina Obe: PostGIS 3.1.3

2. Juli 2021 - 2:00

The PostGIS Team is pleased to release PostGIS 3.1.3! Best Served with PostgreSQL 14 beta2.

This release is a bug fix release, addressing issues found in the previous 3.1 release.

  • #4929, Fix missing error from GetRingEdges when invoked with unexistent topology or edge (Sandro Santilli)

Gilles Darold: Ora2PG now supports oracle_fdw to increase the data migration speed

1. Juli 2021 - 22:06

It has been 20 Years since i have been maintaining the Ora2Pg project, an Open Source software for Oracle to PostgreSQL migrations. The first version of Ora2Pg released on 9th May, 2001. Since then, there have been several features related to schema conversions and data migrations. Over a period of time, i have witnessed several tens of thousands of migrations using Ora2Pg that also increased the need of several optimizations.

Egor Rogov: Locks in PostgreSQL: 2. Row-level locks

1. Juli 2021 - 2:00

Last time, we discussed object-level locks and in particular relation-level locks. In this article, we will see how row-level locks are organized in PostgreSQL and how they are used together with object-level locks. We will also talk of wait queues and of those who jumps the queue.

Row-level locks Organization

Let's recall a few weighty conclusions of the previous article.

Florian Nadler: PostGIS setup with Ubuntu 20.04.2

30. Juni 2021 - 10:30
PostGIS setup with Ubuntu 20.04.2

In one of his last blogposts, our CEO Hans-Jürgen Schönig explained how to set up PostgreSQL on Ubuntu. He consciously avoided any discussion of how to deal with our beloved PostGIS extension. Let’s fix that up right now by following these steps to implement PostGIS setup:

Yugo Nagata: Implementing Incremental View Maintenance for PostgreSQL (Part III)

30. Juni 2021 - 2:00

In the previous post, I showed some performance evaluation of Incremental View Maintenance (IVM) feature that is under development. In this article, I will explain the implementation of IVM and how it works.

Simple Example of Incremental View Maintenance

First, I will explain the process of IVM using a simple example.

Andreas 'ads' Scherbaum: Stefan Keller

28. Juni 2021 - 16:00
PostgreSQL Person of the Week Interview with Stefan Keller: I’m a Professor for Data Engineering since about 20 years and director of Institute for Software at Eastern University of Switzerland. I’m a spatialist; my focus is on spatial data engineering, spatial data analytics and open source, open data and educational resources.