Neues vom PostgreSQL Planet

PostgreSQL continues to dominate in growth and interest by many people and teams across the globe. In my role at Redgate I’m often brought into conversations with long-time SQL Server developers and DBA’s that are now being tasked with learning Postgres. I was one of those people myself a few years ago. Aside from the ... Read more

Sometimes, when you are working on a migration to PostgreSQL, you can encounter features that do not exist in PostgreSQL. This especially happens when we do not have any extension to emulate the feature you are looking at. At HexaCluster Corp, we often face these kind of situations. When it is possible to create an […]

The post Partitioning by reference – Oracle vs PostgreSQL appeared first on HexaCluster.

Everyone is now talking about AI, and modern databases like PostgreSQL® are increasingly being adopted in companies' AI journey as sources of data or key pieces of the AI infrastructure. Moreover there's a new set projects that are solving PostgreSQL problems with AI.

PRs are welcome!

A database password reuse policy is a set of rules that govern the use of passwords within a database system. The policy is designed to ensure that users create strong and unique passwords, and that they do not reuse the same password. By implementing a database password reuse policy, organizations can reduce the risk of […]

The post PostgreSQL security – Password Reuse Policy appeared first on HexaCluster.

The topic of the ltree data type has come up a few times recently. This intersects with a common type of query used in PostGIS: nested geometries. An example of nested geometries is the state of Colorado exists within the United States. The PgOSM Flex project calculates and stores nested polygon data from OpenStreetMap places into a handful of array (TEXT[], BIGINT[]) columns.

How do you feel when your day doesn’t go as planned? In this episode of the Path To Citus Con, the podcast for developers who love Postgres, guest Derk van Veen joins co-hosts Claire Giordano and Pino de Candia to talk about his journey from Java developer to Postgres specialist.

SQL is a straightforward and expressive language, but it sometimes lacks constructs for writing queries in a simple way. It's more complicated than it should be to write a query to get, e.g., only the most expensive order for every customer of the current fiscal year. You can use PostgreSQL's vendor-specific DISTINCT ON feature or window functions for every other database - like MySQL.

A boolean value should only contain two values, True or False, but is it correct? Usually people assume so, but sometimes miss the fact that there could be the absence of the value all-together. In databases this is absence is usually stored as NULL and this blog showcases how to find them, use them properly and 11 lessons to learn to be a NULL Pro!

Keep in mind, it's not only booleans that can contain NULL values, it's all the columns where you don't define a NOT NULL constraint!

Explore the synergy between PostgreSQL and Kubernetes through CloudNativePG — a transformative operator discussed in this article. Discover how this powerful open source stack empowers organizations to free themselves from vendor lock-in and to create a seamless microservice database environment, enhancing innovation, operational efficiency and velocity. This article provides a refreshed perspective on “ Why Run Postgres in Kubernetes?” from 2022.

I’m pleased to announce that some pals and I have organized and will host the (first annual?) Extension Ecosystem Summit at PGConf.dev in Vancouver (and more, see below) on May 28:

Oracle and PostgreSQL differ significantly in their transaction models, and AUTOCOMMIT is one of those differences. We see many of our customers successfully migrate code, but still encounter variations in behavior and even runtime errors related to transaction control. Many issues happen because AUTOCOMMIT settings differ between Oracle and PostgreSQL. It is definitely necessary to […]

A ROW expression allows you to construct ROW values, which can represent anonymous records, specific table row types, or custom composite types. Its uses include processing records within multiple expressions (using operators like =, <>, <, <=, >, or >=), evaluation with subqueries, and combining ROW values to build composite types. You can even export these to JSON format (using functions like rowtojson). Some key operations you can perform with a ROW constructor in Conversion or migration are :

The PostgreSQL RPM repository for SuSE Enterprise Linux has supported SLES for quite some time. We followed the usual conventions and used zypper addrepo … to add the repositories.

Continue reading "Installing PostgreSQL on SLES 15 just got easier – and better!"

This blog post explores the recent change to the fdw_tuple_cost parameter in PostgreSQL, examining the problem it addresses and the reasoning behind the new default value (0.2).

The post The default value of fdw_tuple_cost was updated to 0.2. What does that mean? appeared first on Stormatics.

I’m pleased to announce that some pals and I have organized and will host the (first annual?) Extension Ecosystem Summit at PGConf.dev in Vancouver (and more, see below) on May 28:

Explore how pgvector's indexes work, choose the right one for your needs, and find the best option for your critical data.

The post Understanding Indexes in pgvector appeared first on Stormatics.

Introduction

Since the PostgreSQL 9.6 version was released, the feature related to query parallelism has appeared and has become a good option to improve query performance. Since then, the evolution of parallelism has been growing, resulting in better performance in the database. To manage the behavior of parallelism, there are some parameters that you can tune, for example:

Recently I have been working with some queries in Postgres where I noticed either it has decided not to use an index and perform a sequential scan, or it decided to use an alternative index over a composite partial index. This was quite puzzling, especially when you know there are indexes in the system that can perform these queries faster. So what gives? After some research, I stumbled upon random_page_cost (ref).

pgagroal, the fast connection pooler for PostgreSQL, has reached a new stable release!

pgagroal 1.6.0 has been released

A couple of days ago, pgagroal version 1.6.0 has been released.

Introduction

I recently came across this email thread discussion from several years ago, which discussed ways to enable a client to choose from a list of client certificates to send to the server. The benefit is obvious; when a client has to communicate with different PostgreSQL servers with different TLS settings and trust structure, the feature can help reduce the application’s effort to figure out what certificate to send to what server.