Use a connection pooler
Each Postgres connection uses memory on the database server, so a database can serve only a limited number of them. A connection pooler accepts many client connections and shares a small set of Postgres server connections between them. Use a pooler when an app opens more connections than the database can serve. Orchard runs the pooler with PgBouncer.
New databases with 1 GB of memory or less start with a pooler. A superadmin can change this default in Instance admin > Configuration > Connection pooler for new small databases.
Prerequisites
Section titled “Prerequisites”- Edit access to the project: the Admin or Member project role.
- A dedicated database.
Turn on the pooler
Section titled “Turn on the pooler”- Open the database.
- Go to Settings > Connection pooler.
- Turn on Pool connections.
The Address row shows the pooler address and its status. Wait for the status to change from Starting to Running.
orchard db pooler set <dbId> --enabled onTo read the pooler address and status, run the following command:
orchard db pooler get <dbId>Connect an app to the pooler
Section titled “Connect an app to the pooler”Turning on the pooler does not change the database connection string. Apps keep connecting directly to Postgres until you change them.
-
Copy the address from the Address row. The address has this format:
pg-<name>-pooler-rw.<namespace>.svc.cluster.local:5432 -
Open the app.
-
Go to Variables.
-
Set the connection string variable to a value that uses the pooler address as the host. Keep the same user, password, and database name.
-
Select Save.
The pooler address works only inside the cluster.
Change the pooler settings
Section titled “Change the pooler settings”- Open the database.
- Go to Settings > Connection pooler.
- Update the settings.
- Select Save.
orchard db pooler set <dbId> --mode session --pool-size 20 --max-client-conn 1000Clients reconnect once when the pooler restarts.
| Setting | CLI flag | Default | Range | Description |
|---|---|---|---|---|
| Mode | --mode | transaction | transaction, session | How the pooler assigns server connections. |
| Pool size | --pool-size | 10 | 1 to 100 | Server connections for each database and user. |
| Max client connections | --max-client-conn | 500 | 10 to 5000 | Clients the pooler accepts before it refuses new ones. |
| Not in the dashboard | --instances | 1 | 1 to 3 | Pooler instances. |
Two modes are available:
- Transaction: The pooler assigns a server connection for each transaction. This mode fits most apps.
- Session: The pooler assigns a server connection for each client. Use this mode if the app uses
LISTENor session settings.
Turn off the pooler
Section titled “Turn off the pooler”- Open the database.
- Go to Settings > Connection pooler.
- Turn off Pool connections.
Orchard deletes the pooler. Connections that use its address fail, so point all apps back to the database host first.
Related resources
Section titled “Related resources”- Change Postgres parameters - Raise
max_connectionswhen a pooler is not an option. orchard db poolerreference - Review the pooler commands and their flags.