Document PgBouncer mode

[janpio]

See prisma/prisma#2520 for what exactly is implemented.

Additional information bits from previous documentation issue prisma/prisma-client-js#503 (which is now incorrect, thus this extract here):

1. pgBouncer must run in transaction mode.

Other modes like session do not make any sense:

Earlier notes from @pimeys on pgBouncer (with modifications by @janpio) that we might want to incorporate into the documentation in some way:

---

Pgbouncer is a lightweight connection pooler for PostgreSQL. Instead of running
inside the application code, pgbouncer is a separate service offering support
for postgres line protocol and handling the pooling logic outside of
applications using the database.

Typically postgres supports only a certain amount of concurrent connections and
this limit can be reached quite fast when the service usage goes up.
This means the developer cannot just launch more instances of the service due to
the already running instances are reseving all the connections.

Pgbouncer can be run in three different modes, them being session,
transaction and statement mode.

Session mode gives one connection per client, and is useful mainly to define
limits per team in an organization. For common users this mode doesn't allow any
advantages over just connecting to postgres directly.

Statement mode shares a connection with every query coming to the system. It
prevents certain useful features, such as transactions or prepared statements
and is not really useful with Prisma.

Transaction mode offers a connection for every transaction. With this mode and
certain other settings enabled, the prisma query is able to operate normally.
Needed settings for transaction mode:

• Prisma needs to be started in a mode that triggers a transaction in every
  case, even when just reading data. This allows us to use prepared statements
  in Prisma.
• We need to clean up possibly already present prepared statements in the connection. To do that we run the command DEALLOCATE ALL before trying to prepare and execute the real query.

Downsides

• Slight performance impact due to extra roundtrips for DEALLOCATE ALL, BEGIN and COMMIT every single query.

2. Migration Engine and Introspection Engine will not work with pgBouncer (in transaction mode)

image

use the real database directly instead of the pgBouncer credentials to work around this

3. If you are using pgBouncer, but do not set the query param you will get an error message similar to this:

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("26000"), message: "prepared statement \"s0\" does not exist", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("prepare.c"), line: Some(505), routine: Some("FetchPreparedStatement") }) }) })

2 and 3 best should be confirmed before documenting.

[danba340]

What do you mean by 'but do not set the query param'?
Would be great if you could clarify 👍

[janpio]

To enable PgBoucner mode in Prisma you need to add a query parameter &pgbouncer=true to the connection string of your database.