Category: Laurenz Albe
Laurenz Albe is a senior consultant and support engineer at CYBERTEC. He has been working with and contributing to PostgreSQL since 2006, has written patches for core, and wrote oracle_fdw. He holds a Master’s degree in Mathematics from the University of Vienna and a Master’s in Computer Science from the Technical University of Vienna.
How to corrupt your PostgreSQL database
Of course most people do not want to corrupt their databases. These people will profit from avoiding the techniques used in this article. But for some, it might be useful to corrupt a database, for example if you want to test a tool or procedure that will be used to detect or fix data corruption. […]
Better SQL functions in PostgreSQL v14
We all know and value SQL functions as a handy shortcut. PostgreSQL v14 has introduced a new, better way to write SQL functions. This article will show the advantages of the new syntax. An example of an SQL function Let’s create a simple example of an SQL function with the “classical” syntax so that we […]
Transaction anomalies with SELECT FOR UPDATE
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 table of bills […]
Debugging deadlocks in PostgreSQL
(Updated 2023-04-07) 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 the […]
Case-insensitive pattern matching in PostgreSQL
Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. This works like a charm, except if you want to perform pattern matching. So let’s have a closer look at the […]
How to cancel a hanging PostgreSQL query
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 the faint of […]
Time zone management in PostgreSQL
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 SQL standard The […]
How to DROP ROLE or DROP USER in PostgreSQL
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 ROLE … CASCADE? […]
Query parameter data types and performance
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 universally understood, which […]
Pipeline mode for better PostgreSQL performance on slow networks
It is known that high network latency is bad for database performance. PostgreSQL v14 has introduced “pipeline mode” for the libpq C API, which is particularly useful to get decent performance over high-latency network connections. If you are using a hosted database in “the cloud”, then this article might be interesting for you. The […]
Automatic partition creation in PostgreSQL
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 time, for example […]
Entity-attribute-value (EAV) design in PostgreSQL – don’t do it!
Customers have often asked me what I think of “Entity-attribute-value” (EAV) design. So I thought it would be a good idea to lay down my opinion in writing. What is entity-attribute-value design? The idea is not to create a table for each entity in the application. Rather, you store each attribute as a separate […]