pgcat as an alternative to pgbouncer and more

[rcknr]

I have recently deployed a cluster using this playbook and am very pleased with the outcome. As I learn capabilities of each component I also keep discovering new tools that can enhance database performance in my production scenario.
Recently I came across pgcat which is designed to be a replacement for pgbouncer but also could be a way to natively balance reads/writes as well as sharded datasets.
For now I'm testing it in parallel with haproxy/pgbouncer in my cluster populating members using confd/etcd.
Do you think it makes sense to include it into this playbook as an alternative to pgbouncer and haproxy? Does anyone have any experience working with pgcat and can share it here?

[vitabaks]

Yes, I have an idea to add pgcat as an alternative to pgbouncer so that the user can choose which connection pooler to use.

I need to take the time to do this, or you can suggest a PR.

[rcknr]

Currently, the playbook installs a copy of pgbouncer to each cluster node. With pgcat I currently test it with a single instance replacing haproxy as well. If you install pgcat instead of pgbouncer which topology would you use?

[vitabaks]

In the same configuration

by default, it is placed locally on the database server before Postgres.

Balancers (optional) -> pgcat -> Postgres

[TheOriginalGraLargeShrimpakaReaper]

I opened an Discussion on PgCat.
My Idea was, that PgCat replaced HAproxy and PgBouncer because it is a Proxy and Loud Balancer with SQL Inspect for Splitting Read/Write.
The Problem is, it seems that PgCat is not able to Check the Patroni State.

@vitabaks what did you find out about this?

[rcknr]

@TheOriginalGraLargeShrimpakaReaper why do you think it is a problem? pgcat claims to have its own healthcheck performed by connecting to PostgreSQL directly. Cluster configuration can be populated from etcd with confd the same way HAProxy is configured by this playbook. pgcat even reloads its configuration on its own.

[vitabaks]

My Idea was, that PgCat replaced HAproxy and PgBouncer because it is a Proxy and Loud Balancer with SQL Inspect for Splitting Read/Write.
The Problem is, it seems that PgCat is not able to Check the Patroni State.

In this case, you need to understand that it will have fewer features than a load balancer integrated with the Patroni REST API. For example, we can now define sync and async replicas separately, automatically exclude replicas with a lag of more than patroni_maximum_lag_on_replica and use user tags (balancer_tags), for example, to be able to split read traffic across different datacenters. In the case of pgcat, as I understand it, this will be the usual load balancing for reading, but perhaps this will be enough for many.

By default, HAProxy and PgBouncer will be specified, but those who know what they are doing (understand the limitations) will be able to select pgcat to use its functions.

If we consider it not only as a connection pooler but also as a load balancer, then its servers should be defined in the "balancers" group to be able to control which servers to deploy it on (dedicated or database servers).

[rcknr]

After trying out pgcat I have to say I was disappointed. It's load balancing feature is not capable to distinguish read and write operations properly and lacks the ability for fine tuning. You can see similar feedback in their issues on Github (example). Also, they haven't produced a release since February and the current version is 0.1.0. So I strongly advise against using it.

[vitabaks]

@rcknr Ok, thank you for your feedback. We will be watching the next releases.