Supporting multiple databases with libpq

[steve-chavez]

Problem

libpq offers a native way to use multiple hosts, yet we don't support it. See:

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS

PostgreSQL libpq 16 will even support load balancing natively on these hosts: https://mydbops.wordpress.com/2023/05/07/postgresql-16-brings-load-balancing-support-in-libpq-psql/

Solution

AFAICT, we already support using the PGHOST env var. But we don't support multiple values, we would need to create multiple instances of our connection pool.

---

We could consider using the HTTP Host header to let the user choose a database.

[mkleczek]

What I have prototyped is something similar: Let hasql-pool implement a kind of a multi-pool where you can create multiple connection pools where all would share a single global pool size limit but also each one would have its own "local" pool size limit. Example:

global size: 30
pool1: maximum size: 25
pool2: maximum size: 25

Additionally Postgrest would keep two sets of pools:

1. A single read/write connection pool
2. A set of read-only connection pools

The whole thing is designed to spread read queries among read-only replicas of a single master.

The set-up is designed to support https://cloudnative-pg.io where separate k8s services are provided for master and replicas.

[wolfgangwalther]

Everything that connects to multiple databases, will need multiple sets of connection credentials etc. - so a hugely different interface for configuration.

I think the best way forward would be to improve the separation between what is part of the postgrest library and what is part of the current single-database executable.

We can then re-use the library for other executables:

• something that maps Host to multiple databases
• or a read/write connection pooler

[steve-chavez]

If we had this I think then an Ubuntu/Debian package would make more sense since it could have a single systemd service. Right now we need systemd services for each db.

[steve-chavez]

Related discussion on #3101 (comment) for using a psql compatible ini file for multiple databases.