This post is about a lesser known 'feature' of postgres: the loading or creation of the pg_internal.init files (plural) that happens when logging on to a postgres database. Because YugabyteDB reuses the postgres source, YugabyteDB performs dealing with the pg_internal.init files identical to postgres.

Let me first talk about why I am performing this writeup. During testing I noticed the first connection to postgres taking more time than successive logons. This is easy to miss, because when I tested again, I didn't see it anymore. Then when the postmaster was restarted, the very first connection showed higher latency again. This means that in specific cases, which is if these deal with databases that are accessed just after postmaster startup, there might be extra work performed that increases logon latency. This is important for doing automated tests on newly build or created databases.

Before diving into the details: is this important for concurrent postgres/YugabyteDB access, and does it influence regular database activity: no(!)

The phenomenon of performing extra work to create the pg_internal.init files only happens with the very first backend to logon to a database on a given postmaster. So, why this writeup, where is this important for? This is important for latency testing, to understand this clear outlier.

Closely related to the latency differences for logging on: the first connection latency is influenced by the number of objects in the database, and can get significant with high (relative to processing power ~ > 1000) number of objects, where YugabyteDB is more sensitive to this than stock postgres, because YugabyteDB loads the catalog data from the master leader, whilst stock postgres loads it from local files or the buffer cache.

So, what is happening here?
During the logon to a database in postgres, the backend is created by forking the postmaster, and the backend builds caches in its private memory. To be more precise: each individual backend creates caches with the needed minimal catalog data into its private memory so it has just enough catalog data to function.

As part of the backend creation, it creates a cache called 'relcache' or relation cache, which contains relation descriptors. These descriptors are quite comprehensive, which is why the very first session to enter a database after postmaster startup will gather the global relcache information, and gather the local, database specific relcache information, and write these to a file in memory format. This way, any following session can use the files to load its relcache, without the need to perform the work to obtain all the information for each relation in the relcache.

Again: if you have a normal functioning postgres or YugabyteDB database cluster, this phenomenon should not influence normal, daily operations. However, in specific situations, such as carefully timing logon after builds, this is very important to understand, because the first logon will have a higher latency.

To see the effect of the pg_internal.init files creation, you can use the [connection-profiler project].(https://github.com/fritshoogland-yugabyte/connection-profiler.git).

install connection-profiler

In order to install connection-profiler, you need rust to compile the connection-profiler, and you need git to clone the connection-profiler project.

Get git and gcc

sudo yum install git gcc -y

Clone the connection-profiler repository

git clone https://github.com/fritshoogland-yugabyte/connection-profiler.git

Get rust

curl https://sh.rustup.rs -sSf | sh -s -- -y

Source the rust environment

source "$HOME/.cargo/env"

Go into the connection-profiler directory and build a production (release) binary:

cd connection-profiler
cargo build --release

Once finished, the executable is in the target/release directory with the name connection-profiler.

PostgreSQL

To test with postgres startup the postmaster. Like hopefully is clear from the description: do not attach with any client before the testing, or that client will have performed the initialisation of the pg_internal.init files.

Now run the connection-profiler by performing a number (5) connections, not executing any SQL:

[postgres@localhost connection-profiler]$ ./target/release/connection-profiler -u "user=postgres password=postgres port=5432 host=localhost" -q '' -n 5
create_connection                              5001 us
------------------------------------------------------------
============================================================
create_connection                              2724 us
------------------------------------------------------------
============================================================
create_connection                              2523 us
------------------------------------------------------------
============================================================
create_connection                              2365 us
------------------------------------------------------------
============================================================
create_connection                              2380 us
------------------------------------------------------------
============================================================

Clearly, the first connection is slower than the other connections. If the same sequence of connecting 5 times is repeated, the first connection does not show the higher latency again:

[postgres@localhost connection-profiler]$ ./target/release/connection-profiler -u "user=postgres password=postgres port=5432 host=localhost" -q '' -n 5
create_connection                              2602 us
------------------------------------------------------------
============================================================
create_connection                              3074 us
------------------------------------------------------------
============================================================
create_connection                              2799 us
------------------------------------------------------------
============================================================
create_connection                              2319 us
------------------------------------------------------------
============================================================
create_connection                              2503 us
------------------------------------------------------------
============================================================

YugabyteDB

YugabyteDB uses the postgres source-code, and there behaves identical. However, the principal difference of YugabyteDB versus postgres is that the storage is changed to networked sources. This gives a lot of flexibility and scalability, but currently means extra latency is introduced. This is how the latencies look like on a small, local virtual machine based cluster:

[vagrant@yb-1 connection-profiler]$ ./target/release/connection-profiler -u "user=yugabyte password=yugabyte port=5433 host=localhost" -q '' -n 5
create_connection                            187654 us
------------------------------------------------------------
============================================================
create_connection                             22404 us
------------------------------------------------------------
============================================================
create_connection                             17495 us
------------------------------------------------------------
============================================================
create_connection                             17815 us
------------------------------------------------------------
============================================================
create_connection                             18213 us
------------------------------------------------------------
============================================================

Redoing the repeated connections gives more consistent response times:

[vagrant@yb-1 connection-profiler]$ ./target/release/connection-profiler -u "user=yugabyte password=yugabyte port=5433 host=localhost" -q '' -n 5
------------------------------------------------------------
============================================================
create_connection                             18996 us
------------------------------------------------------------
============================================================
create_connection                             19581 us
------------------------------------------------------------
============================================================
create_connection                             21980 us
------------------------------------------------------------
============================================================
create_connection                             21924 us
------------------------------------------------------------
============================================================

As indicated: the higher latency is something we are working on, on making it better, although it's not extremely far off of native postgres latency.

The first connection above which builds the pg_internal.init files will request catalog data to build these files, which is done to its proxy thread in its local tablet server, which then performs an RPC request to the current master leader for the actual catalog data.