Introduction

The purpose of this blog post is twofold:

  • Firstly, to introduce a new Postgres C extension to record subtransactions counters
  • Secondly, to share its Rust version

Introduce the extension

The new extension, namely pg_subxact_counters can be found in this repo.

Subtransactions can lead to performance issue, as mentioned in some posts, see for example:

The purpose of this extension is to provide counters to monitor the subtransactions (generation rate, overflow, state).

One idea could be to sample the pg_subxact_counters view (coming with the extension) on a regular basis.

Example:

postgres=# \dx+ pg_subxact_counters
Objects in extension "pg_subxact_counters"
       Object description
--------------------------------
 function pg_subxact_counters()
 view pg_subxact_counters
(2 rows)

postgres=# select * from pg_subxact_counters;
 subxact_start | subxact_commit | subxact_abort | subxact_overflow
---------------+----------------+---------------+------------------
             0 |              0 |             0 |                0
(1 row)

postgres=# begin;
BEGIN
postgres=*# savepoint a;
SAVEPOINT
postgres=*# savepoint b;
SAVEPOINT
postgres=*# commit;
COMMIT

postgres=# select * from pg_subxact_counters;
 subxact_start | subxact_commit | subxact_abort | subxact_overflow
---------------+----------------+---------------+------------------
             2 |              2 |             0 |                0
(1 row)

Fields meaning

  • subxact_start: number of substransactions that started
  • subxact_commit: number of substransactions that committed
  • subxact_abort: number of substransactions that aborted (rolled back)
  • subxact_overflow: number of times a top level XID have had substransactions overflowed

Remarks

  • subxact_start - subxact_commit - subxact_abort: number of subtransactions that have started and not yet committed/rolled back.
  • subxact_overflow does not represent the number of substransactions that exceeded PGPROC_MAX_CACHED_SUBXIDS.
  • the counters are global, means they record the activity for all the databases in the instance.

Sharing a Rust version

As you can see the repo is made of two subdirectories:

  • c
  • rust

The extension has been initially written in C.

While doing so, and as I’m in my journey of learning Rust, I thought it would be a great learning experience to also write it in Rust.

Why?

Why Rust?
Rust is very popular and it also provides a lot of existing modules/crates.
One could imagine using some of those existing modules/crates to build Postgres extensions.

Why providing C and Rust code?
For people that are:

  • in their journey of learning to write Postgres extensions in Rust
  • used to write C extensions

having a simple example (as this one) written in both languages might help.

Remark

The Rust part relies on pgrx: this is an awesome framework for developing PostgreSQL extensions in Rust.