© Laurenz Albe 2022 Most people are aware of transaction ID wraparound. The concept has been well explained in Hans’ article, so I won’t repeat all that here. But for most people it is an abstract concept, a bogeyman lurking in the dark around the corner. Many people know horror stories about anti-wraparound autovacuum tanking […]

usql? But why? usql is a universal command-line interface for many database. But why are we still using CLI (command line utilities) in the 21st century? And what is wrong with psql? Despite the widespread availability and ease of use of GUI-based tools, many people still prefer to use command line utilities for a number […]

As of version 15, PostgreSQL offers you the ability to write the server log in JSON format. This feature has been desired for a long time and has finally made it to PostgreSQL core. In this post we will discuss how JSON logs can be configured and what this brand new feature does for users. […]

SQL and integer notations NEW in PostgreSQL 16 – support for non-decimal notation of integer constants! PostgreSQL already has powerful support for string constants, with E’\t’, E’\011′, E’\u0009′ and U&’\0009′ all meaning the same thing (a “horizontal tab” character). However, PostgreSQL’s support for numerical constants has – up until now – been rather limited; it […]

© Laurenz Albe 2022 A while ago, I wrote about the performance impact of query parameter data types. Recently I encountered a similar problem with UNION ALL that baffled me for a while, so I’d like to share it with you. UNION ALL to implement polymorphism Different entities can have something in common. For example, […]

In PostgreSQL 15, a fundamental change took place which is relevant to every user who happens to work with permissions: The default permissions of the public schema have been modified. This is relevant because it might hurt you during application deployment. You need to be aware of how it may affect you. Creating users Many […]

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 […]

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. […]

I’ve recently seen some really broad tables (hundreds of columns) in a somewhat inefficiently structured database. Our PostgreSQL support customer complained about strange runtime behavior which could not be easily explained. To help other PostgreSQL users in this same situation, I decided to reveal the secrets of a fairly common performance problem many people don’t […]

© 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 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 […]