PostgreSQL Connection Pooling

PostgreSQL databases support a limited number of simultaneous direct connections. If your database is approaching or hitting this limit, you can set up connection pooling on Render using PgBouncer.

Using this setup, your other services connect to your PgBouncer instance instead of connecting directly to your database. PgBouncer reuses its pool of active database connections to serve queries from any number of different services.

Setup

You can deploy PgBouncer on Render either by declaring its configuration in a render.yaml blueprint file, or by manually configuring a private service from your dashboard. Both options are covered below.

Deploying with a render.yaml blueprint

1. Create a file named render.yaml in the root of a Git repository. This file describes your PgBouncer instance, along with the database it serves:

   databases:
     - name: mysite
       databaseName: mysite
       user: mysite
   
    services:
    - type: pserv
      name: pgbouncer
      runtime: docker
      plan: standard
      repo: https://github.com/render-oss/docker-pgbouncer
      envVars:
      - key: DATABASE_URL
        fromDatabase:
          name: mysite
          property: connectionString
      - key: POOL_MODE
        value: transaction
      - key: SERVER_RESET_QUERY
        value: DISCARD ALL
      - key: MAX_CLIENT_CONN
        value: 500
      - key: DEFAULT_POOL_SIZE
        value: 50

2. Commit your changes and push them to GitHub/GitLab/Bitbucket.

3. In the Render Dashboard, go to the Blueprints page and click New Blueprint Instance. Select the repository with the blueprint file (give Render permission to access it if you haven’t already) and click Approve on the next screen.

That’s it! Render creates your database and PgBouncer instance.

You can navigate to your new pgbouncer service in the dashboard to find the URL that your applications should connect to. You can connect using the internal connection string from your database, replacing the database host with internal hostname of your PgBouncer instance postgres://USER:PASSWORD@PGBOUNCER_HOST:PORT/DATABASE.

Creating services from the dashboard

1. Create a new PostgreSQL database on Render. Note your database’s internal database URL (you’ll need it in a later step).

2. Create a new Private Service and point it to Render’s PgBouncer Docker Image: https://github.com/render-oss/docker-pgbouncer

3. Select Docker for your private service’s runtime.

4. Add the following environment variables to the private service:

   Key	Value
   DATABASE_URL	The internal database URL for the database you created above
   POOL_MODE	transaction
   SERVER_RESET_QUERY	DISCARD ALL
   MAX_CLIENT_CONN	500
   DEFAULT_POOL_SIZE	50

That’s it! Save your private service to deploy your PgBouncer instance on Render.