Usage with pg-bouncer

[timsuchanek]

We should research how to set up Prisma Client together with pg-bouncer.

[divyenduz]

Related issue: prisma/prisma#556

[pimeys]

I'll write a document about pgbouncer today.

[pimeys]

Summary: yes we can.
Longer summary: https://www.notion.so/prismaio/Pgbouncer-Evaluation-71076db4699e4ba48bf13df879d485a7

The work so far in two branches:

• Query engine working in transaction mode pgbouncer prisma-engines#403
• Try to avoid prepared statements when starting Quaint quaint#67

[pimeys]

Merged to master now, instructions how to enable the transaction mode in query engine can be found here:

prisma/prisma-engines#403

[pimeys]

Threw this back to @timsuchanek now. You should make a decision how and when to enable the flag in query engine, if wanting to support pgbouncer transaction mode in the client.

[timsuchanek]

Awesome @pimeys! We need to make a product decision, how we expose this in Prisma Client. cc @sorenbs

[pimeys]

We now run all the tests against pgbouncer in transaction mode btw.

[pimeys]

I did an initial test how Heroku's Pgbouncer offering could work together with Prisma. Summarizing what needs to be done for Pgbouncer:

If in session mode, everything works just normally. Just point Prisma to the Pgbouncer URL and call it a day. (This will not help you consume less connections)

If in transaction mode, certain settings need to be modified in Pgbouncer config:

pool_mode = transaction
server_reset_query = DEALLOCATE ALL
server_reset_query_always = 1

And of course the Prisma Client needs to start the server in the right mode, e.g. this spec should be implemented.

Heroku allows changing Pgbouncer's settings using environment variables. For now only two of the three settings can be changed:

> heroku config:add PGBOUNCER_POOL_MODE=transaction
> heroku config:add PGBOUNCER_SERVER_RESET_QUERY="DEALLOCATE ALL"

The third setting should be available when the needed pull request lands.

For other important providers, as discussed with @divyenduz, he will make sure the settings are available. When all is set, these need to be communicated with our users through the documentation.