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 or GitLab.

  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:

    KeyValue
    DATABASE_URLThe internal database URL for the database you created above
    POOL_MODE transaction
    SERVER_RESET_QUERYDISCARD ALL
    MAX_CLIENT_CONN500
    DEFAULT_POOL_SIZE50

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