pgcat: a PostgreSQL pooler
Tags: yugabyte, postgres, performance, connectionpool
Published: 2022-11-14 13:57:34

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

[pgcat](https://github.com/levkk/pgcat.git) is a connection pool like [pgbouncer](https://www.pgbouncer.org), 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](https://www.pgbouncer.org) 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](https://www.pgbouncer.org) so_reuseport setting to allow multiple pgbouncers to run on the same port.

[pgcat](https://github.com/levkk/pgcat.git) seems heavy focussed on it's [sharding](https://github.com/levkk/pgcat#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](https://www.yugabyte.com) 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](https://www.yugabyte.com)), and then let the incoming connections be balanced over the servers. When [pgcat](https://github.com/levkk/pgcat.git) 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](https://www.yugabyte.com), if you can use a YugabyteDB driver, such as our [java jdbc driver](https://github.com/yugabyte/pgjdbc), it's highly recommended to do so!

# Building
Installation of [pgcat](https://github.com/levkk/pgcat.git) is not hard, and well described on the pgcat page. Currently, because [pgcat](https://github.com/levkk/pgcat.git) 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](https://rustup.rs), git clone [pgcat](https://github.com/levkk/pgcat.git), and then build it using: `cargo build --release`.

# Configuring for use with [YugabyteDB](https://www.yugabyte.com)
The configuration is actually quite simple. See this configuration for use with my small test cluster:
```shell
[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](https://www.yugabyte.com) cluster, and gave one the role of "primary" and the two others the role of "replica". For [YugabyteDB](https://www.yugabyte.com) YSQL this does not make sense. Currently, [pgcat](https://github.com/levkk/pgcat.git) allows to have the roles to be all "replica", but not have more than one "primary". 

# Running [pgcat](https://github.com/levkk/pgcat.git)
Now that the configuration is done, run [pgcat](https://github.com/levkk/pgcat.git) using [cargo](https://doc.rust-lang.org/cargo/):
```shell
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](https://www.postgresql.org) database. The port can be set using `port = 6432` (example for using the default [pgbouncer](https://www.pgbouncer.org) port). Also mind that [YugabyteDB](https://www.yugabyte.com) by default binds to port 5433.

# Using [YugabyteDB](https://www.yugabyte.com) through [pgcat](https://github.com/levkk/pgcat.git) for load balancing connections
Now that [pgcat](https://github.com/levkk/pgcat.git) is setup and running, we can use [pgcat](https://github.com/levkk/pgcat.git) to connect to the [YugabyteDB](https://www.yugabyte.com) cluster, and get a persistent connection once connected:
```shell
psql -h localhost -p 5432 -U yugabyte
psql (14.5 (Homebrew), server 11.2-YB-2.15.3.2-b0)
Type "help" for help.
```
```sql
yugabyte=# select inet_server_addr();
 inet_server_addr
------------------
 192.168.66.81
```

# Conclusion
The [pgcat](https://github.com/levkk/pgcat.git) project looks like a very promising project, because it allows to use a connection pooler with the simplicity of [pgbouncer](https://www.pgbouncer.org) but have connection distribution over a cluster of [YugabyteDB](https://www.yugabyte.com) YSQL endpoints.

Another advantage is that it uses [Rust](https://rustup.rs), 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.