Sammlung von Newsfeeds

Learn how to set up a high-availability 3-node PostgreSQL cluster with Patroni on Ubuntu 24.04, ensuring your database is always online and resilient to failures.

The post Setting Up a High Availability 3-Node PostgreSQL Cluster with Patroni on Ubuntu 24.04 appeared first on Stormatics.

When your company has decided it's time to invest in more open source, Postgres is the obvious choice. Managing databases is not new and you already have established practices and requirements for rolling out a new database. One of the big requirements we frequently help new customers with on their Postgres adoption is data encryption. While the question is simple, there's a few layers to it that determine which is the right approach for you. Here we'll walk through the pros and cons of approaches and help you identify the right path for your needs.

Protect personally identifiable information (PII) in PostgreSQL databases, a critical requirement for financial organizations.

The post Protecting Personally Identifiable Information in PostgreSQL: A Critical Requirement for Financial Organizations appeared first on Stormatics.

Since the beginning of my journey into the data world I've been keen on making professionals better at their data job. In the previous years that took the shape of creating materials in several different forms that could help people understand, use, and avoid mistakes on their data tool of choice. But now there's much more into it: a trusted AI solution to help data professional in their day to day optimization job.

© Laurenz Albe 2024

Sometimes one of our customers looks at the most time consuming statements in a database (either with pg_stat_statements or with pgBadger) and finds COMMIT in the high ranks. Normally, COMMIT is a very fast statement in PostgreSQL, so that is worth investigating.

Learn how to monitor replication lag in a PostgreSQL multi-master cluster using Postgres Enterprise Manager (PEM).

The post Monitoring Replication Lag with PEM in Multi-Master Cluster appeared first on Stormatics.

During the last PGSession organized by Dalibo, I wrote and led a workshop (french) on the migration to PostgreSQL using Foreign Data Wrappers, or FDW. This was an opportunity to present to the general public the db_migrator extension for which I wrote an article on this blog.

Database triggers allow encapsulation of multiple functionalities that are automatically invoked on specific events or DML operations like INSERT, UPDATE, or DELETE. The invocation of triggers can be controlled as BEFORE or AFTER the triggering events, either for each change or per statement. In migrating from Oracle to PostgreSQL, it is important to be aware of triggers conversion gotchas.

This article explores the pgrx framework, which simplifies the creation of custom PostgreSQL extensions to bring more logic closer to your database. Traditionally, writing such extensions required familiarity with C and a deep understanding of PostgreSQL internals, which could be quite challenging. pgrx lowers the barrier and allows developers to use Rust, known for its safety and performance, making the process of creating efficient and safe database extensions much more accessible.

I love taking random spatial data and turning it into maps. Any location data can be put into PostGIS in a matter of minutes. Often when I’m working with data that humans collected, like historic locations or things that have not yet traditionally been done with computational data, I’ll find traditional Degrees, Minutes, Seconds (DMS) data. To get this into PostGIS and QGIS, you’ll need to convert this data to a different system for decimal degrees. There’s probably proprietary tools that will do this for you, but we can easily write our own code to do it.

Learn how to deploy a highly available and scalable PostgreSQL multi-master multi-zone architecture on AWS, ensuring fault tolerance and geographically distributed write capabilities for your databases.

The post Deploying PostgreSQL Multi-Master Multi-Zone Architecture on AWS appeared first on Stormatics.

Recently on the Postgres Slack, I encountered an interesting performance issue involving a SQL query that joins two tables with an ANY filter applied to one of the tables.

The problematic SQL was similar to the following:

SELECT tbl1.col1 FROM tbl1 INNER JOIN tbl2 ON tbl1.col1 = tbl2.col1 WHERE tbl2.col1 IN (1,2,3);

Table tbl1 is joined with tbl2 on the column col1 from their respective tables.

I recently learned about this project, and I can’t stop thinking about it, and how it is strangely connected to what I was doing for a big portion of my professional life.

First of all, what is this project about? Since people rarely click the links, let me copy a project description here:

Last week, a few members of the community got together for for the sixth and final Postgres Extension Ecosystem Mini-Summit. Follow these links for the video and slides:

TLDR: I was starting to think that the best choice of default DB collation (for sort order, comparison, etc) in Postgres might be ICU. But after spending some time reviewing the landscape, I now think that code-point order is the best default DB collation – mirroring Db2 and Oracle – and linguistic sorting can be used via SQL when it’s actually needed for the application logic. In existing versions of Postgres, this would be something like C or C.UTF-8 and Postgres 17 will add the builtin collation provider (more details at the bottom of this article).

This post contained content that could not be rendered in the Atom feed. Please use the official post link: https://tembo.io/blog/prometheus-data-in-postgresql

I thought about it for quite some time, whether I should write about it, and how. That's why there is delay since: On 6th of April 2024, Alexander Korotkov committed patch: Implement ALTER TABLE ... MERGE PARTITIONS ... command   This new DDL command merges several partitions into the one partition of the target table. … Continue reading "Waiting for PostgreSQL 17 – MERGE / SPLIT partitions"

Sixteen years ago, on May 21, 2008, we proudly announced the opening of the Italian branch of 2ndQuadrant, marking a milestone in PostgreSQL consulting and training in Europe. Simon Riggs, the visionary founder of 2ndQuadrant, placed immense trust in our small team from Prato, Tuscany. Tragically, Simon passed away on March 26, 2024, leaving a profound void. This article is my small tribute to Simon, his extraordinary leadership, and his invaluable contributions to the PostgreSQL community.