As the two talks I had submitted did not get picked for this year´s PGconf.de, my prep for the conference was all about booking train tickets from Düsseldorf to Munich. Being an early bird definitely paid off – Bernd Helmle and I enjoyed first class luxury, while our procrastinating colleagues ended up paying more for second-class seats. The Thursday journey was pretty smooth, with the conductor proudly announcing our punctual arrival – surprised it didn’t make it to the news with Deutsche Bahn’s track record. I even managed to get a good amount of work done, working on CYBERTEC’s PostgreSQL E ..read more

These days everybody is talking about time series, time series analysis and alike for performance tuning. Analyzing time series data in PostgreSQL can provide valuable insights, help in making informed decisions and understanding data more deeply. By utilizing PostgreSQL’s powerful features, we can efficiently query all types of measurement data to track trends, patterns, and anomalies over time. However, often there is a tiny little requirement, people are struggling to understand. Consider the following data: CREATE TABLE t_sample ( tstamp timestamptz, sensor_id ..read more

PostgreSQL is very good at keeping your data safe so it doesn’t disappear by itself. Unfortunately, the same holds in reverse–if data has been deleted, it stays unrecoverable. In this article, we’ll explore options for recovering deleted data from PostgreSQL tables. Option 1: cautious people use transactions If you never make any mistakes when working with data, there won’t be any need for emergency procedures. But since we are all human, things like this happen: -- remove an entry from our contact list db=> DELETE FROM addressbook WHERE name = 'Heinz Schmidt'; DELETE 3 -- PANIC: WHY WERE ..read more

After the pgconfeu23 in Prague – which has been an excellent event – I decided to share some of the things I presented as a blog post to maybe shed some light on some of those topics. One of those ideas presented was the way PostgreSQL handles joins and especially join orders. Internally the PostgreSQL does a good job to optimize queries but how does it really work? Let us create some tables first: plan=# SELECT 'CREATE TABLE x' || id || ' (id int)' FROM generate_series(1, 5) AS id; ?column? -------------------------- CREATE TABLE x1 (id int) CREATE TABLE x2 (id i ..read more

How to check if TDE works? Some days ago a customer asked me how to check if TDE works and files on disk are encrypted. So I installed a new virtual machine with Ubuntu and installed our PGEE-Demo on it. After installation, the new created cluster is unencrypted. So we can try to create a new table and insert one record on it with a short text. To make sure that the record is saved into the table file I called a checkpoint. To hexdump the table file I need to know the data directory and filepath, you can see all commands here: postgres@ubuntupgee:~$ psql psql (16.1 EE 1.3.1 (Ubuntu 16.1ee1.3 ..read more

This article is dedicated to all the Excel heroes out there who want to integrate and use data stored in PostgreSQL. This time we’ll cover all the steps needed to do so by utilizing Npgsql, which is an open source ADO.NET Data Provider for PostgreSQL. To learn more about this versatile driver, check out its main project page or directly jump into the code base. Requirements Let me begin with the needed requirements to follow up on this quick tutorial accordingly. Apart from an accessible PostgreSQL server, an appropriate Excel version or license is required to connect to PostgreSQL via Npgsql ..read more

The missing piece When you think about migration from Oracle to Postgres, most people just say that it is easy. And if you consult the various sources of information, nothing tells you differently. And it might not even be untrue. Most databases we create, manage, and run are easy to migrate. If you look at the characteristics of these databases, the large bulk of these: Small in size (some tens of GB’s) It is not super important, as they can be down for hours to days without having a great impact on your business No super complex applications, that use the database Not too busy databases, wi ..read more

This month, we have an invitation from Lætitia to talk about UUIDs. UUIDs are a hot topic, and Cybertec wrote several posts earlier. I have no particular story or use case to share, and then I remembered Nikolay Samokhvalov announced the Postgres.AI bot to the public recently. I had early access to it, so I decided to talk about UUID with the artificial mind. I chose the form of an interview, forcing it to give short and structured answers whenever possible. So here we are! Meet the Postgres.AI one and only! Hi postgres.ai! What is UUID and why everybody in database world obsessed with it? UUI ..read more

PGConfEU2023 is over and a New Year has started. For those who did not manage to make it to Prague to attend the conference, I wanted to use the occasion, to sum up what I tried to communicate during my keynote session and reflect a bit on the importance of true Open Source versus Closed Source as well as on subscription services. Longevity in data processing On August 10th a new baby was born in my family. The question is: Why would anybody care? Kids are born every day all over the world and people leave the face of the earth from time to time. What is the point of all this? Here is why we ..read more

© Laurenz Albe 2024 I recently investigated a surprising behavior of the DBeaver database client in connection with PostgreSQL parallel query, which I’d like to share with you. This might be interesting for everybody who accesses PostgreSQL using the JDBC driver. Some basics about PostgreSQL parallel query Parallel query concepts Parallel query was introduced in PostgreSQL 9.6 and has been improved in later versions. It breaks with the “traditional” PostgreSQL architecture of using a single backend process per database connection to process SQL statements. If the optimizer thinks that paralle ..read more