Like many relational DBMSs, PostgreSQL uses multi-version concurrency control (MVCC) to support parallel running transactions and coordinate parallel access to tuples. Snapshots are used to determine which version of a tuple is visible in which transaction. Each transaction that modifies data, has a transaction ID (txid). Tuples are stored together with two attributes (xmin, xmax) that determine in which snapshots (and in which transactions) they are visible. This blog post discusses some of the implementation details of snapshots.

PostgreSQL uses several types of locks to coordinate parallel running transactions and grant access to resources like tuples, tables, and in-memory data structures. Heavy locks are used to control the access to tables. Lightweight locks (LWLocks) control access to data structures, such as adding data to the write-ahead-log (WAL). Row-level locks are used to control access to tuples. For example, individual tuples need to be locked when an SQL statement like SELECT * FROM table WHERE i > 10 FOR UPDATE;. The tuples that are returned by the query are internally locked with an exclusive lock (LOCK_TUPLE_EXCLUSIVE). Another transaction that tries to lock the same tuples has to wait until the first transaction unlocks the tuples. In this article, the tool pg_row_lock_tracer is discussed. The tool employs eBPF and UProbes to trace the row-locking behavior of PostgreSQL. It can be downloaded from the website of the pg-lock-tracer project. This is the third article that deals with the tracing of PostgreSQL locks. The first article deals with the tracing of heavyweight locks. The second article deals with LW locks.

While working with the internals of PostgreSQL, it is helpful to be able to navigate around the source code quickly and look up symbols and definitions fast. I use VS Studio code for programming. However, finding definitions does not always work reliably, and the full-text search is slow and often returns many results and not the desired hot (e.g., a definition of a function). For a long time, I had the Doxygen build of PostgreSQL open in my browser. However, Doxygen is sometimes cumbersome to use and it only shows the current version of PostgreSQL. Sometimes, the source code for an older version is needed. To solve these problems, I set up a local copy of the Elixir Cross Referencer.

The eBPF technology of the Linux kernel allows it to monitor applications with minimal overhead. UProbes can be used to trace the invocation and exit of functions in programs. Modern tools to observe databases (like pg-lock-tracer) are built on top of eBPF. However, these fully flagged tools are often written in C and Python and require some development effort. Sometimes, a ‘quick and dirty’ solution for a particular observation would be sufficient. With bpftrace, users can create eBPF programs with a few lines of code. In this article, we develop a simple bpftrace program to observe the execution of vacuum calls in PostgreSQL and analyze the delay.

To store sets of integer values efficiently, PostgreSQL uses internally a data structure called Bitmapset. A wide range of operations are supported on the Bitmapset.