search

PostgREST / postgrest

REST API for any Postgres database
https://postgrest.org
MIT License
23.51k stars 1.03k forks source link

Supporting multiple databases with libpq #2798

Open steve-chavez opened 1 year ago

steve-chavez commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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:

steve-chavez commented 1 year ago

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 commented 10 months ago

Related discussion on https://github.com/PostgREST/postgrest/issues/3101#issuecomment-1910760422 for using a psql compatible ini file for multiple databases.