© Laurenz Albe 2023 The PostgreSQL documentation has some information about row locks. But typically, you cannot see them in pg_locks, and not everybody knows how they work and how to track and debug row locks. This article intends to give some insight into how PostgreSQL row locks work “under the hood”. Why are there […]

Running ALTER TABLE … ADD COLUMN can have side effects which have the potential to cause serious issues in production. Changing data structures is an important issue and often comes up, therefore it’s important to understand what is really going on. Let’s dive in and see how to run ALTER TABLE … ADD COLUMN in […]

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

Creating auto increment columns in PostgreSQL is easy. Simply use two pseudo data types serial and serial8, respectively, then PostgreSQL will automatically take care of your auto increment columns. However, once in a while problems can still occur. Let us take a look and see. Sequences: Avoid manual values To understand the underlying problem, one […]

© Laurenz Albe 2020   (Last updated on 2023-01-24) Recently, while troubleshooting PostgreSQL performance problems, I ran into problems with subtransactions twice. So I thought this was a nice topic for another blog post. What are subtransactions? Everybody knows database transactions. In PostgreSQL, which is operating in autocommit mode, you have to start a transaction […]

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

xmax is a PostgreSQL system column that is used to implement Multiversion Concurrency Control (MVCC). The documentation is somewhat terse: The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting […]

To handle transactions PostgreSQL uses a mechanism called MVCC (Multi Version Concurrency Control). The core idea of this machinery is to allow the storage engine to keep more than just one version of the row.