Never lose a PostgreSQL transaction with pg_receivewal
© Laurenz Albe 2019 “Durability”, the D of ACID, demands that a committed database transaction remains committed, no matter what. For normal outages like a power failure, this is guaranteed by the transaction log (WAL). However, if we want to guarantee durability even in the face of more catastrophic outages that destroy the WAL, […]
Manage Linux control groups in PostgreSQL with pg_cgroups
In another blog post, I described how Linux control groups work and how they are useful for PostgreSQL. Here I will present my little PostgreSQL plugin pg_cgroups which makes this simple to handle for the DBA. What is pg_cgroups good for? If you have a machine dedicated to a single PostgreSQL cluster, you normally […]
Tracking view dependencies in PostgreSQL
We all know that in PostgreSQL we cannot drop an object if there are view dependencies on it: Some people like it because it keeps the database consistent; some people hate it because it makes schema modifications more difficult. But that’s the way it is. In this article I want to explore the mechanics […]
ICU collations against glibc 2.28 data corruption
As announced by the glibc mailing list and discussed by Daniel Vérité, version 2.28 of the GNU C library will ship with many changes to the collations. PostgreSQL uses the operating system’s collations by default. When this version hits your Linux operating system as part of a routine bugfix upgrade, and you don’t use […]
Abusing SECURITY DEFINER functions
Functions defined as SECURITY DEFINER are a powerful, but dangerous tool in PostgreSQL. The documentation warns of the dangers: Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to ensure that the function cannot be misused. For security, search_path should be set to exclude […]
PostgreSQL v12 new feature: optimizer support for functions
PostgreSQL commit 74dfe58a5927b22c744b29534e67bfdd203ac028 has added “support functions”. This exciting new functionality that allows the optimizer some insight into functions. This article will discuss how this will improve query planning for PostgreSQL v12. If you are willing to write C code, you can also use this functionality for your own functions. Functions as “black boxes” […]
Triggers to enforce constraints
Sometimes you want to enforce a condition on a table that cannot be implemented by a constraint. In such a case it is tempting to use triggers instead. This article describes how to do this and what to watch out for. It will also familiarize you with the little-known PostgreSQL feature of “constraint triggers”. […]
PostgreSQL count(*) made fast
It is a frequent complaint that count(*) is so slow on PostgreSQL. In this article I want to explore the options you have get your result as fast as possible. Why is count(*) so slow? Most people have no trouble understanding that the following is slow: After all, it is a complicated query, and […]
“LOCK TABLE” can harm your database’s health
© Laurenz Albe 2019 Many people know that explicit table locks with LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance. Through a recent support case I learned that there are even worse effects of explicit table locks. Table locks […]
“Exclusive backup” method is deprecated – what now?
The “exclusive backup” method of calling pg_start_backup(‘label’) before backup and pg_stop_backup() afterwards is scheduled for removal in the future. This article describes the problems with the old method and discusses the options for those who still use this backup method. The “exclusive” backup method Before pg_basebackup was invented, there was only one online file-system […]
pgbouncer authentication made easy
pgbouncer is the most widely used connection pooler for PostgreSQL. This blog will provide a simple cookbook how to configure user authentication with pgbouncer. I wrote this cookbook using Fedora Linux and installed pgbouncer using the PGDG Linux RPM packages available from the download site. But it should work pretty similarly anywhere. What is […]
Be prepared for prepared transactions
Prepared transactions are disabled in PostgreSQL by default, since the parameter max_prepared_transactions has the default value 0. You don’t need prepared transactions in most cases. However, they can cause nasty problems, so I think that everybody who runs a PostgreSQL database should understand them. To illustrate these problems, I’ll show you how to use prepared […]