Neues vom PostgreSQL Planet
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.
On Ubuntu, pgpool2 ARM64 debian package is made available by the Debian PostgreSQL Maintainers :
$ dpkg -s pgpool2
Maintainer: Debian PostgreSQL Maintainers
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.
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.
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.
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
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.
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)
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.
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.
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:
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.