This post is about a new PostgreSQL connection pooler that I came across recently: pgcat, which provides some useful features that I have not seen with the other poolers. Please mind the pgcat connection pooler is in beta, and they actually are looking for beta testers! If you are interested, read the pgcat beta test announcement/request.

pgcat is a connection pool like pgbouncer, and shares part of the logical way it's working, but it has a fundamental difference: it allows to use multiple databases for a single client connection. pgbouncer only ever connects a client to a single database. It should be mentioned that there is a way to perform load balancing by using the pgbouncer so_reuseport setting to allow multiple pgbouncers to run on the same port.

pgcat seems heavy focussed on it's sharding functionality, which allows the connection pool to spread the queries to the shards. It performs quite nifty things if you want to use the sharding features, such as interpretation of the SQL to route the queries to the correct shards.

My interest is because of the distributed YugabyteDB database. This pooler allows to define the PostgreSQL endpoints of a YugabyteDB cluster in a servers list (with "primary"/"replica" roles, which I don't like, because we don't have these in YugabyteDB), and then let the incoming connections be balanced over the servers. When pgcat detects a database connection to be dead, it will reconnect it with the next available one in the pool!

Of course an external connection pool like this should only be used if you cannot use an application connection pool, and for the use of YugabyteDB, if you can use a YugabyteDB driver, such as our java jdbc driver, it's highly recommended to do so!

Building

Installation of pgcat is not hard, and well described on the pgcat page. Currently, because pgcat is in beta, you have to build the executable yourself, or use the example Docker deployment.

Building it yourself is really simple, because all you need to do is install Rust, git clone pgcat, and then build it using: cargo build --release.

Configuring for use with YugabyteDB

The configuration is actually quite simple. See this configuration for use with my small test cluster:

[general]
prometheus_exporter_port = 9930
admin_username = "admin_user"
admin_password = "admin_pass"
[pools.yugabyte]
pool_mode = "session"
default_role = "any"
query_parser_enabled = false
primary_reads_enabled = true
sharding_function = "pg_bigint_hash"
[pools.yugabyte.users.0]
username = "yugabyte"
password = "yugabyte"
pool_size = 50
statement_timeout = 0
[pools.yugabyte.shards.0]
servers = [
    [ "192.168.66.80", 5433, "primary" ],
    [ "192.168.66.81", 5433, "replica" ],
    [ "192.168.66.82", 5433, "replica" ]
]
database = "yugabyte"

These are the minimal settings required for the current version.

I created a pool called "yugabyte" (pools.yugabyte), with pool mode 'session', so that a session remains connected to a backend.
I created a pool of servers in pools.yugabyte.shards.0 (yugabyte shards pool 0), with all three of the YSQL nodes in the YugabyteDB cluster, and gave one the role of "primary" and the two others the role of "replica". For YugabyteDB YSQL this does not make sense. Currently, pgcat allows to have the roles to be all "replica", but not have more than one "primary".

Running pgcat

Now that the configuration is done, run pgcat using cargo:

RUST_LOG=info cargo run --release
    Finished release [optimized] target(s) in 0.11s
     Running `target/release/pgcat`
[2022-11-14T13:24:03.175058Z INFO  pgcat] Welcome to PgCat! Meow. (Version 0.6.0-alpha1)
[2022-11-14T13:24:03.184314Z INFO  pgcat] Running on 0.0.0.0:5432
[2022-11-14T13:24:03.184323Z INFO  pgcat::config] Ban time: 60s
[2022-11-14T13:24:03.184325Z INFO  pgcat::config] Healthcheck timeout: 1000ms
[2022-11-14T13:24:03.184328Z INFO  pgcat::config] Connection timeout: 1000ms
[2022-11-14T13:24:03.184329Z INFO  pgcat::config] Shutdown timeout: 60000ms
[2022-11-14T13:24:03.184331Z INFO  pgcat::config] Healthcheck delay: 30000ms
[2022-11-14T13:24:03.184333Z INFO  pgcat::config] TLS support is disabled
[2022-11-14T13:24:03.184337Z INFO  pgcat::config] [pool: yugabyte] Maximum user connections: 50
[2022-11-14T13:24:03.184342Z INFO  pgcat::config] [pool: yugabyte] Pool mode: Session
[2022-11-14T13:24:03.184367Z INFO  pgcat::config] [pool: yugabyte] Connection timeout: 1000ms
[2022-11-14T13:24:03.184370Z INFO  pgcat::config] [pool: yugabyte] Sharding function: pg_bigint_hash
[2022-11-14T13:24:03.184372Z INFO  pgcat::config] [pool: yugabyte] Primary reads: true
[2022-11-14T13:24:03.184374Z INFO  pgcat::config] [pool: yugabyte] Query router: false
[2022-11-14T13:24:03.184376Z INFO  pgcat::config] [pool: yugabyte] Number of shards: 1
[2022-11-14T13:24:03.184378Z INFO  pgcat::config] [pool: yugabyte] Number of users: 1
[2022-11-14T13:24:03.184380Z INFO  pgcat::config] [pool: yugabyte][user: yugabyte] Pool size: 50
[2022-11-14T13:24:03.184456Z INFO  pgcat::config] [pool: yugabyte][user: yugabyte] Statement timeout: 0
[2022-11-14T13:24:03.185020Z INFO  pgcat::pool] [pool: yugabyte][user: yugabyte] creating new pool
[2022-11-14T13:24:03.185099Z INFO  pgcat::pool] Creating a new server connection Address { id: 0, host: "192.168.66.80", port: 5433, shard: 0, database: "yugabyte", role: Primary, replica_number: 0, address_index: 0, username: "yugabyte", pool_name: "yugabyte" }
[2022-11-14T13:24:03.196798Z INFO  pgcat::pool] Creating a new server connection Address { id: 1, host: "192.168.66.81", port: 5433, shard: 0, database: "yugabyte", role: Replica, replica_number: 0, address_index: 1, username: "yugabyte", pool_name: "yugabyte" }
[2022-11-14T13:24:03.209956Z INFO  pgcat::pool] Creating a new server connection Address { id: 2, host: "192.168.66.82", port: 5433, shard: 0, database: "yugabyte", role: Replica, replica_number: 1, address_index: 2, username: "yugabyte", pool_name: "yugabyte" }
[2022-11-14T13:24:03.222501Z INFO  pgcat] Config autoreloader: false
[2022-11-14T13:24:03.222540Z INFO  pgcat::stats] Events reporter started
[2022-11-14T13:24:03.222556Z INFO  pgcat] Waiting for clients

The above logging is because of the setting of RUST_LOG=info on the same line as cargo run --release.

Please mind that the default setting binds to port 5432 on all network interfaces, which can conflict with a local running PostgreSQL database. The port can be set using port = 6432 (example for using the default pgbouncer port). Also mind that YugabyteDB by default binds to port 5433.

Using YugabyteDB through pgcat for load balancing connections

Now that pgcat is setup and running, we can use pgcat to connect to the YugabyteDB cluster, and get a persistent connection once connected:

psql -h localhost -p 5432 -U yugabyte
psql (14.5 (Homebrew), server 11.2-YB-2.15.3.2-b0)
Type "help" for help.

yugabyte=# select inet_server_addr();
 inet_server_addr
------------------
 192.168.66.81

Conclusion

The pgcat project looks like a very promising project, because it allows to use a connection pooler with the simplicity of pgbouncer but have connection distribution over a cluster of YugabyteDB YSQL endpoints.

Another advantage is that it uses Rust, which provides C/C++ speed with memory safety.

However, it has to be noted that the project is in an early stage so it should be very well tested before considering using this in a production situation.

ps.
I found one issue already: if dbname is not specified, pgcat returns an error, whilst the same connection to YSQL (=PostgreSQL) succeeds, because if the dbname is not specified, it should take the username.