1

We have setup a streaming replication on Postgres 13 and it all works fine. Current setup is as follows.

We have one Primary Postgres and one Secondary Postgres connected via streaming replication. This replication and cluster management of failover is managed by Patroni. We have PGBouncer for connection pooling and it is currently connected to PG Master. This works fine without issues.

PgBouncer-to-PG

We wanted to use the secondary postgres for read purposes to reduce the read load on Primary. So we wanted to introduce PGPool in the eco system to get benefit of read/write splits. Couple of articles suggested that we could use both PGBouncer and PgPool together to get better results from both tools (https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/). So now our setup adds PgPool-II between PgBouncer and PG with connection pooling disabled.

enter image description here

This setup also works fine. However I am not able to understand about connection pooling. I could see many connections in pgpool when i do SHOW POOL_POOLS;

  • Who is holding on to the connections for connection pooling ? PgBouncer/PgPool ?
  • Why I see connections when I issue command show pool_pools in pgpool when connection pooling is disabled.
  • Is this setup right and scalable? What configurations I need to change to get better of both tools ?
Improve this question
4
  • Since pgPool is also a connection pooler, it doesn't make sense to me to use both pgBouncer and pgPool.
    – Laurenz Albe
    Commented Oct 27, 2021 at 14:35
  • @LaurenzAlbe true but pgpool connection pooling is not that performant and takes more memory as well while pgbouncer is light weight and performs much better. If not pgpool are there any tools which allow us to do read and write splits without changing application code ?
    – Pramod
    Commented Oct 29, 2021 at 5:30
  • I understand your reluctance to use pgPool, but if you want to use it for separating reads and writes, you might as well use it for pooling too. Or does that really cause additional pain? No tool can reliably tell if a statement will only read. I personally would opt for changing the application. Are you aware that you might not see a change performed on the primary on the standby immediately?
    – Laurenz Albe
    Commented Oct 29, 2021 at 6:53
  • Hi @LaurenzAlbe, we have a similar situation as we having application layer connection pooling using pg.pool client lib in node.js forwarding to an LB then routing to two pgbouncer. Later we planning to change pooling method to transaction in pgbouncer. Can you advise is it good or bad to have two layer of pooling?
    – goodfella
    Commented Mar 14 at 7:32

1 Answer 1

Reset to default
-1

I am currently setting up a Postgres HA solution on Kubernetes (postgres-crunchy-data). I have utilized a combination of pgbouncer and pgpool. Using pgbench, I benchmarked ReadOnly queries (comparing TPS) between two architectures (Architecture-I: utilizing crunchydata postgres pgbouncer solution , Architecture-II: using a customized pgbouncer and pgpool combination). The final result indicates that when increasing the number of Read-only queries, we observed a high TPS for the second architecture. The same observation applies to increasing the number of Postgres standby replicas.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.