© Laurenz Albe 2022 For detailed query performance analysis, you need EXPLAIN (ANALYZE, BUFFERS) output for an SQL statement. With a parameterized statement, it can be difficult to construct a run-able statement for EXPLAIN (ANALYZE). Sometimes, you don’t even know the parameter values. I’ll show you how you can get at least plain EXPLAIN output […]
Rewriting OR is not always the best solution © Laurenz Albe 2022 In my article that reviles OR, I showed how in certain cases, it is possible to rewrite OR in a WHERE condition to a longer query with UNION that can perform much better (the “ugly” OR). Now people have asked me repeatedly why […]
© Laurenz Albe 2022 Recently, a customer asked me why a bulk load into a PostgreSQL table kept slowing down as time went by. They saw that it was a trigger on the table that took longer and longer, and in that trigger, the updates were getting slower all the time. Now slow updates are […]
pgbouncer is one of the most widely used tool for connection pooling. At CYBERTEC, we’ve successfully deployed it in many different situations. It has proven to be reliable as well as useful. Before we dive into different pooling modes and their implications, why do we need a connection pooler in the first place? The reason […]
Did you ever wonder why VACUUM does not make your PostgreSQL tables smaller? Did you ever wonder why VACUUM does not shrink data files? Well, maybe this is the article you have been looking for. The point is: usually, space is not returned to the operating system after a cleanup and it is important to […]
What is LISTEN and NOTIFY? LISTEN / NOTIFY is a feature that enables users to listen to what goes on in the database. It is one of the oldest functionalities in PostgreSQL and is still widely used. The main question is: What is the purpose of the asynchronous query interface (LISTEN / NOTIFY), and what […]
© 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 […]
© Laurenz Albe 2022 (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 […]
© 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 […]
© 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 […]
© 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 […]
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 […]
