Neues vom PostgreSQL Planet

Greetings readers, today we're going to take a semi-break from my “doing data science in SQL” series to cover a really cool use case I just solved with regular expressions (regex)

Real time, fast update of materialized views

Big data has an unfortunate tendency to get messy. A few years in, a growing database that use to be small, lean, and well-designed, has better odds than not of becoming something large, bloated, and with best practices tossed aside and now considered unsalvageable.

There’s a few common reasons that this happens, some better than others:

AWS announces Babelfish: open source Postgres with SQL Server compatibility

At AWS re:Invent’s first keynote, Andy Jassy has announced a major breakthrough for Postgres: SQL Server compatibility for Postgres. Open Source.

In one of my previous blog posts, Why PostgreSQL WAL Archival is Slow, I tried to explain three of the major design limitations of PostgreSQL’s WAL archiver which is not so great for a database with high WAL generation. In this post, I want to discuss how pgBackRest is addressing one of the problems (cause number two in the previous post) using its Asynchronous WAL archiving feature.

PostgreSQL 13 has been released over a month ago. The most important changes and new features have already been announced in various ways and (online) stages, mostly by well-known contributors or influential Postgres community players. In this blog we take a look at those new features, changes and handy facts about PostgreSQL 13. We give an overview of interesting articles about the important major but also perhaps neglected non-major changes.

As a consultant, I frequently need to install Postgres for customers. Of course I send them a list or prerequisites beforehand but, I don’t know why, there’s always a problem with one of them and, most often, the problem concerns port opening. Tools? Who needs tools ? Sysadmins will recommend you to use either nmap or netcat or netstat and you might need to combine those tools (which syntax you will keep forgetting) with grep to find if your tool is listed.

PostgreSQL Person of the Week Interview with Stéphane Schildknecht: I live in a small town in the East of France. After graduating in Computational Chemistry, I jumped into consulting at the beginning of the century. I was already convinced by OpenSource. I had the opportunity to discover MySQL and PostgreSQL at that time. I worked on a big PostgreSQL project, and understood PostgreSQL will be a big game changer in the database industry.

 In my previous blog post, I have introduced how to monitor Pgpool-II and PostgreSQL cluster using Pgpool-II Exporter. As I mentioned in the previous post, Pgpool-II Exporter exposes metrics that Prometheus can collect. Prometheus is one of the most popular monitoring tools used on Kubernetes.

A few months ago I started experimenting with a few project ideas involving data over space and time. Naturally, I want to use Postgres and PostGIS as the main workhorse for these projects, the challenge was working out exactly how to pull it all together. After a couple false starts I had to put those projects aside for other priorities. In my free time I have continued working through some related reading on the topic.

What is your first association with the concept of B-tree? Mine is 'old and well researched, or in other words boring'. And indeed apparently it was first introduced in 1970! Not only that, already in 1979 they were ubiquitous. Does it mean there is nothing exciting left any more? It turns out that there are multitude of interesting ideas and techniques around B-Trees. They're all coming from desire to cover different (often incompatible) needs, as well as adapt to emerging hardware. In this blog post I'll try to show this, and we will be concerned mostly with B-tree as a data structure.

I was looking for something to add to my Christmas wish list (yes, my parents are retired and they asked for this wish list for the last 3 months) and I remembered I had seen a blog post a few years ago where you could print your own code on clothes. I had a good memory of a particularly nice dress (and I found out later it even had pockets! Yay!

Among a lot of other changes, the year 2020 brings Online Conferences to us. In the Postgres community too we now record our talks at home and send a video file to be playedto a virtual audience, and sometimes shared later in a platform online. So this yeah I did participate in Postgres Vision 2020 where I did deliver a talk about The Art of PostgreSQL. This a talk all about the book that I have written and self-publish at The Art of PostgreSQL: learn how to turn thousands of lines of code into simple SQL queries.

Debezium is a popular Open Source change data capture (CDC) solution.

My previous blog on “PostgreSQL High Availability: Considerations and Candidates” mostly talked about defining an HA considerations for PostgreSQL, RPO and RTO and briefly touched on some of the open source solutions available. Granted that I didn’t list them all and people shared some additional ones that I may end up reviewing as well.

In the last two blog posts on data science in Postgres, we got our data ready for regression analysis and had predictive variables that are on wildly different scales. Another example of data on different scales would be annual income versus age.

This is my third blog about Stored Procedure support in PostgreSQL, the previous two blogs are reachable from the HighGo CA blogs site https://www.highgo.ca/author/ahsan-h/. The first blog was introduction and usage of Stored Procedures and its difference with Stored Functions, the second blog focussed on creating and using Procedures with Definer and Invoker rights. The purpose of this blog is to demonstrate how to execute a Stored procedure from Java.

A few years ago, I wrote a short post on a similar topic; since then, I’ve often seen that the whole concept of suggesting to do more INSERT-s in critical parts of the code seems pretty strange to most customers. It’s even alien to those who are not new to databases in general. So I thought that the technique could use another go-round since I think it’s quite a nice design trick that can alleviate some certain problems when used correctly.

The question was asked relatively recently on irc. And it proved to be non-trivial. Surely, if you want to have one row per category (one address per user), it's trivial – add user_id column to addresses, make it unique, and we're done. But what if we want to allow five addresses? Or five thousands? Let's … Continue reading "How to limit rows to at most N per category"

At most of the places I've worked, the primary language used was not what I gravitated to naturally. If you're going to ask for a language of choice personally, it's python. I appreciate the explicit nature, that it's often pseudocode that can execute and it has a rich ecosystem of libraries (though that’s most languages these days). But as much as anything I latched onto Django in its early days.