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

PostgreSQL uses several types of locks to coordinate parallel transactions and manage access to resources like tuples, tables, and in-memory data structures. Heavyweight locks are used to control access to tables. Lightweight locks (LWLocks) manage access to data structures, such as adding data to the write-ahead log (WAL). Row-level locks control access to individual tuples. For example, tuples need to be locked when executing an SQL statement like SELECT * FROM table WHERE i > 10 FOR UPDATE;. The tuples returned by the query are internally locked with an exclusive lock (LOCK_TUPLE_EXCLUSIVE). Another transaction attempting to lock the same tuples must wait until the first transaction releases the locks. In this article, we discuss the tool pg_row_lock_tracer, which uses eBPF and UProbes to trace PostgreSQL’s row-locking behavior. The tool can be downloaded from the pg-lock-tracer project website. This is the third article in a series about tracing PostgreSQL locks. The first article covers the tracing of heavyweight locks, and the second article focuses on LW locks.

When working with the internals of PostgreSQL, it is helpful to navigate the source code quickly and look up symbols and definitions efficiently. I use VS Code for programming. However, finding definitions does not always work reliably, and the full-text search is slow and often returns too many results, missing the desired hit (e.g., the definition of a function). For a long time, I kept the Doxygen build of PostgreSQL open in my browser. However, Doxygen can be cumbersome to use and only shows the current version of PostgreSQL. Sometimes, the source code for an older version is needed. To address these issues, 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.