Hello, I have a cluster of pg (using patroni) at the

moment PG14 we wish to upgrade to 16.

The main usage is mostly writing data (metadata and states0, and even some of selects are part of the insert/update transaction operations to determine some stages of a stateless operations.

I have many connections to the db at the peak usage of the system, that generate big load on the primary (can arrive to 200 tasks waiting).

All of the servers (three) are on "bare metal" (self managed proxmox).

The system must be responsive in few milliseconds (the lower the better) with the stateless states and metadata, so it is impossible to use MQ/Task queue and waiting for writing in bulks.

Do you know a good tool/proxy to manage such usage to reduce if possible some of the load, or better way to manage cluster of primary-primary-secondary?

Thank you

If you are using Patroni, take a look at https://www.dbi-services.com/blog/major-postgresql-version-upgrade-in-a-patroni-cluster/ If you aren't using Patroni, think about using it.

PgBouncer is a very good and lightweight connection pooler. But at least it should run in transaction mode, you might have to change your client software. https://www.pgbouncer.org/

thank you

I've written a blog post about poolers https://proopensource.it/blog/postgresql-connection-poolers

Very interesting, great post, I did not know of odyssey. my usage is always with transactions and prepared statements. how does pgpool will find out what is the current primary/leader if it does not handle the cluster?

Patroni can handle that, too. There's a public Ansible project which can handle the whole installation process, https://github.com/vitabaks/postgresql_cluster

I'll take a look at that, thank you

In pgbouncer I can see some dangerous timeouts such as query_wait_timeout etc....should we simply disable them

First check what is running: - long running transactions: check your application, apply changes to reduce them in both, amount and runtime - long running queries: try to optimize them Otherwise you might run into the same problems again. You have hopefully installed https://www.postgresql.org/docs/current/pgstatstatements.html If not, now is the time.

I was testing in dev and I am pretty sure no long running transactions.....I still see query wait timeout error. So I disabled dangerous timeouts

Queries with missing indexes, client connections kept open , configuration failure, the default configuration of PostgreSQL is not useful. Configuration failure in PgBouncer.

Let's say I execute some queries in a session and when exit from session by giving \q. Then when I connect using psql and check in pg_stat_activity I see those queries are in idle . Is there anything fundamentally wrong ?

Let me guess your OS: Windows™