Transaction anomalies with SELECT FOR UPDATE

© Laurenz Albe 2022 I was recently confronted with a nice example of how adding FOR UPDATE to a query can introduce transaction anomalies. This article will explain how that happens and how you can cope with the problem. Be ready to learn some PostgreSQL implementation details! The example Setting the stage We have a […]

Read more

Debugging deadlocks in PostgreSQL

© Laurenz Albe 2022 Even if you understand what a deadlock is, debugging deadlocks can be tricky. This article shows some techniques on how to figure out the cause of a deadlock. A simple deadlock example Setting the stage We will test our techniques for debugging deadlocks with the following example: The deadlock To provoke […]

Read more

View permissions and row-level security in PostgreSQL

© Laurenz Albe 2022 The details of how view permissions are checked have long puzzled me. PostgreSQL v15 introduces “security invoker” views, which change how permissions are checked. The new security invoker views make it possible to use row-level security effectively. I’ll use this opportunity to explain how view permissions work and how you can […]

Read more

Help, I cannot cancel a PostgreSQL query!

© Laurenz Albe 2022 Sometimes a PostgreSQL query takes forever. Usually, it is easy to interrupt (cancel) such a statement, but sometimes it can turn into a problem, and a query cannot be canceled. This article discusses what might be the cause. I’ll also show you a trick how to solve the problem (not for […]

Read more

Time zone management in PostgreSQL

© Laurenz Albe 2022 Next to character encoding, time zones are among the least-loved topics in computing. In addition, PostgreSQL’s implementation of timestamp with time zone is somewhat surprising. So I thought it might be worth to write up an introduction to time zone management and recommendations for its practical use. Time zones and the […]

Read more

Find and fix a missing PostgreSQL Index

Spot a missing PostgreSQL index Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible. 🙂  However, if you want to ensure that your database performs well and if you are generally not in favor of user complaints – better watch out […]

Read more

Multiranges in PostgreSQL 14

Understanding Multiranges Range types have been around in PostgreSQL for quite some time and are successfully used by developers to store various kinds of intervals with upper and lower bounds. However, in PostgreSQL 14 a major new feature has been added to the database which makes this feature even more powerful: multiranges. To help you […]

Read more

How to DROP ROLE or DROP USER in PostgreSQL

© Laurenz Albe 2022 You might, at first glance, believe that DROP ROLE (or DROP USER, which is the same) is a simple matter. However, that’s not always the case. So I thought it might be a good idea to show you the problems involved and how to solve them. Why is there no DROP […]

Read more

Query parameter data types and performance

© Laurenz Albe 2022 Recently, I could help solve a “mysterious” performance problem for a customer. The problem turned out to be a badly chosen query parameter type, which led to poor query performance. After working on this customer’s case, I realized that the meaning of the data type of a query parameter is not […]

Read more

Automatic partition creation in PostgreSQL

© Laurenz Albe 2022 Table partitioning is one of the best-liked features out of the more recent PostgreSQL developments. However, there is no support for automatic partition creation yet. This article shows what you can do to remedy that. Use cases for automatic partition creation There are essentially two use cases: Create partitions triggered by […]

Read more