Recently I was talking in a more general way about some common auditing / change tracking approaches for PostgreSQL…but it also made me curious, how it roughly looks from the performance side? To quickly recap the previous blog post: the most common approaches for tracking important changes are mostly solved with writing some triggers. There […]

After 20 years in professional PostgreSQL support and consulting we are finally able to answer one of the most frequently asked questions: “How can I see all active query plans?” Ladies and gentlemen, let me introduce you to pg_show_plans, an extension which does exactly that. pg_show_plans is Open Source and can be used free of […]

© Laurenz Albe 2020   Embedded SQL is by no means a new feature — in fact it is so old-fashioned that many people may not know about it at all. Still, it has lots of advantages for client code written in C. So I’d like to give a brief introduction and talk about its […]

When designing a database application, the layout of data in disk is often neglected. However, the way data is stored by PostgreSQL can have a major performance impact. Therefore it makes sense to take a look at what can be done to improve speed and throughput. In this post you will learn one of the […]

© Laurenz Albe 2020   Recently, while troubleshooting PostgreSQL performance problems, I ran into problems with subtransactions twice. So I thought this was a nice topic for another blog post. What are subtransactions? Everybody knows database transactions. In PostgreSQL, which is operating in autocommit mode, you have to start a transaction that spans multiple statements […]

If you are running PostgreSQL in production you might have already noticed that adjusting checkpoints can be a very beneficial thing to tune the server and to improve database performance overall. However, there is more to this: Increasing checkpoint distances can also help to actually reduce the amount of WAL that is created in the […]

Last time we imported OpenStreetMap datasets from Iceland to PostGIS. To quickly visualize our results, we will now use QGIS to render our datasets and generate some nice maps on the client. Let’s start with our prerequisites: A PostGIS enabled PostgreSQL database preloaded with OpenStreetMap datasets as described in blogpost https://www.cybertec-postgresql.com/en/open-street-map-to-postgis-the-basics/ An up and running […]

When migrating from MS SQL to PostgreSQL, one of the first things people notice is that in MS SQL, object names such as tables and columns all appear in uppercase. While that is possible on the PostgreSQL side as well it is not really that common. The question therefore is: How can we rename all […]

In this post, we will cover a step by step guide on how to implement a clustering algorithm within PostgreSQL. The clustering algorithm is the well-known k-means, which splits data into groups by minimising the sum of the squared distances of each point to the corresponding group average. This method belongs to the so called […]

Recently, I was asked for advice on how to reasonably implement a common task of table change tracking – meaning a so-called “audit trail” for all row changes over time was required. The keyword “compliance” might also ring a bell in this context, here for those who work in finance or for government institutions. But […]

pg_timetable is a brand-new job scheduler for PostgreSQL implemented completely from scratch (in Go). Traditional job scheduling is quite limited and does not cover all we needed in real life. Therefore, we thought about job execution in more detail and came up with a new concept that has not been seen in the PostgreSQL world […]

It’s been exactly half a year since the last major release of the pgwatch2 Open Source PostgreSQL monitoring tool, and I’m glad to announce that another huge set of useful features and improvements have found their way into the pgwatch2 code repository! The new version is incremented to v1.7 and, continuing the naming tradition, I’m […]