Neues vom PostgreSQL Planet

While a recent Postgres survey indicates that email lists are an impediment to new people getting involved with the project, email continues to be the primary means of communication in the community. Some of the reasons are historical, some are based on email's distributed nature, and some on email's flexibility of expression.

Markus Winand wrote a great article about why and how users should use LIKE operator in their workloads. It covers different approaches like Full-Text Search, Partial Index, Trigram Index, Materialized View, and query optimization.

How a simple snippet can allow you to save time and improve your PostgreSQL code quality.

Using Emacs and YASnippet to quickly write PostgreSQL functions

I love Emacs, and I also love PostgreSQL.
Whenever I have to write PostgreSQL code, I use Emacs.
Emacs can help me improving code quality, for example to write PostgreSQL functions. I use YASnippet as a package to provide the basic template for a PostgreSQL function.

CloudNativePG has a native mechanism that provides an automated way to manage physical replication slots in a high availability Postgres cluster, with one or more hot standby replicas, and lets them survive a failover. This article describes why this is important for self-healing in Kubernetes. [Continue reading...]

On 29th of August 2023, Daniel Gustafsson committed patch: Allow \watch queries to stop on minimum rows returned   When running a repeat query with \watch in psql, it can be helpful to be able to stop the watch process when the query no longer returns the expected amount of rows. An example would be … Continue reading "Waiting for PostgreSQL 17 – Allow \watch queries to stop on minimum rows returned"

Introduction pg_stat_statements is a built-in PostgreSQL extension that keeps track of SQL statements executed by the database server. It records details about query execution counts, total execution time and I/O related info. By capturing this information, database administrators can identify bottlenecks and optimize queries for better overall performance. In this blog, we’ll take a  look […]

For years, Postgres has been considered among the most popular and advanced open source OLTP databases. Interestingly, 20 years ago, it was a competition between Postgres and MySQL and this debate is still going on today. 🙂 Over the last few years, Postgres added more features and its ecosystem added more extensions and now the comparison list keeps growing.

Increase performance in pgvector using HNSW indexes

In Oracle, right or wrong, I was always taught to try to avoid “row movement” between partitions due to the general thought that the extra workload of a “delete” + “insert” (rewrite of the row) should be avoided due to the extra I/O, index fragmentation and the associated risks of a migrating ROWID in the cases where the app developers might have used it in their code (now that’s a whole other problem). Oracle didn’t even let you do it by default.

Table by table, you had to explicitly set:

Ivan Panchenko wrote a great article about why and how businesses should start using Postgres. It covers Postgres's structural benefits like vendor independence, code quality, and technology like NoSQL. It also covers the challenges that could be faced, like cost, time, and in-house expertise.

Is it a Muffin or a Chihuahua?

I tried to solve the famous meme using #PostgreSQL pgvector extension. The results are really impressive!

Do you want to try? I created a Python notebook that can give you a jump start, check it out at https://go.aiven.io/muffin-vs-chihuahua

You can find the training dataset at https://www.kaggle.com/datasets/samuelcortinhas/muffin-vs-chihuahua-image-classification

Exclusion constraints are a feature that is not very well known, but can be used to implement highly sophisticated constraints. A few years ago, Hans wrote his blog post about EXCLUDE USING GIST… WITH.

In part one, Nathan Koopmans, cloud platform engineer at OptimaData, showed how to create a simple plain PostgreSQL setup. Even though this works fine, you still want more data security. Therefore, in this second part, we look at the CloudNativePG operator for Kubernetes.

We continue our exploration of PostgreSQL build system performance. A long time ago, I wrote an article about how to optimize the performance of make install. This was quite helpful, as it reduced the time from 10.493 s by default to 1.654 s with some tweaks (6x faster). Now, with different hardware, a much newer PostgreSQL, and a new build system looming, let’s take another look.

Increase performance in pgvector using HNSW indexes

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 16 Windows 64-bit.

To use these, copy the files into your PostgreSQL 16 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

These were compiled against PostgreSQL 16rc1, but should work fine against EDB PostgreSQL 16beta3.

On 26th of August 2023, Michael Paquier committed patch: Generate new LOG for "trust" connections under log_connections   Adding an extra LOG for connections that have not set an authn ID, like when the "trust" authentication method is used, is useful for audit purposes.

Related to my previous blog entry about bait and switch, I have heard people struggle to distinguish between binaries based on source code released by the Postgres community and binaries based on modifications of the community source code. The term "community edition" has often been used to represent binaries in the first category.

On 23rd of August 2023, Nathan Bossart committed patch: Add to_bin() and to_oct().   This commit introduces functions for converting numbers to their equivalent binary and octal representations. Also, the base conversion code for these functions and to_hex() has been moved to a common helper function.   Co-authored-by: Eric Radman Reviewed-by: Ian Barwick, Dag Lem, … Continue reading "Waiting for PostgreSQL 17 – Add to_bin() and to_oct()."

Postgres’ pgvector extension recently added HNSW as a new index type for vector data. This levels up the database for vector-based embeddings output by AI models. A few months ago, we had written about approximate nearest neighbor pgvector performance using the available list-based indexes. Now, with the addition of HNSW, pgvector can use the latest graph based algorithms to approximate nearest neighbor queries.