--- title: Connection pooling | Tiger Data Docs description: Create and manage connection poolers in Tiger Console to improve database performance --- You can scale your Tiger Cloud service connections and improve its performance by using connection poolers. Tiger Cloud uses `pgBouncer` for connection pooling. If your service needs a large number of short-lived connections, a connection pooler is a great way to improve performance. For example, web, serverless, and IoT applications often use an event-based architecture where data is read or written from the database for a very short amount of time. Your application rapidly opens and closes connections while the pooler maintains a set of long-running connections to the service. This improves performance because the pooler opens the connections in advance, allowing the application to open many short-lived connections, while the service opens few, long-lived connections. ## User authentication By default, the poolers have authentication to the service, so you can use any custom users you already have set up without further configuration. You can continue using the `tsdbadmin` user if that is your preferred method. However, you might need to add custom configurations for some cases such as `statement_timeout` for a pooler user. 1. **Connect to your service as the `tsdbadmin` user and create a new role** ``` CREATE ROLE LOGIN PASSWORD ''; ``` 2. **Change the `statement_timeout` settings to 2 seconds for this user** ``` ALTER ROLE my_app SET statement_timeout TO '2s'; ``` 3. **In a new terminal window, connect on the pooler with the new user ``** Terminal window ``` PGPASSWORD= psql 'postgres://my_app@service.project.tsdb.cloud.timescale.com:30477/tsdb?sslmode=require' ``` The output looks something like this: ``` psql (15.3 (Homebrew), server 15.4 (Ubuntu 15.4-1.pgdg22.04+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. ``` 4. **Check that the settings are correct** ``` SELECT current_user; ┌──────────────┐ │ current_user │ ├──────────────┤ │ my_app │ └──────────────┘ (1 row) ``` Check the `statement_timeout` setting is correct for the `` user: ``` tsdb=> show statement_timeout; ┌───────────────────┐ │ statement_timeout │ ├───────────────────┤ │ 2s │ └───────────────────┘ (1 row) ``` ## Pool types When you create a connection pooler, there are two pool types to choose from: session or transaction. Each pool type uses a different mode to handle connections. Session pools allocate a connection from the pool until they are closed by the application, similar to a regular PostgreSQL connection. When the application closes the connection, it is sent back to the pool. Transaction pool connections are allocated only for the duration of the transaction, releasing the connection back to the pool when the transaction ends. If your application opens and closes connections frequently, choose the transaction pool type. By default, the pooler supports both modes simultaneously. However, the connection string you use to connect your application is different, depending on whether you want a session or transaction pool type. When you create a connection pool in the Tiger Console, you are given the correct connection string for the mode you choose. For example, a connection string to connect directly to your service looks a bit like this: ``` postgres://:@service.example.cloud.timescale.com:30133/tsdb?sslmode=require ``` A session pool connection string is the same, but uses a different port number, like this: ``` postgres://:@service.example.cloud.timescale.com:29303/tsdb?sslmode=require ``` The transaction pool connection string uses the same port number as a session pool connection, but uses a different database name, like this: ``` postgres://:@service.example.cloud.timescale.com:29303/tsdb_transaction?sslmode=require ``` Make sure you check the Tiger Console output for the correct connection string to use in your application. ## Connection pool sizes A connection pooler manages connections to both the service itself, and the client application. It keeps a fixed number of connections open with the service, while allowing clients to open and close connections. Clients can request a connection from the session pool or the transaction pool. The connection pooler will then allocate the connection if there is one free. The number of client connections allowed to each pool is proportional to the `max_connections` parameter set for the service. The session pool can have a maximum of `max_connections - 17` client connections, while the transaction pool can have a maximum of `(max_connections - 17) * 20` client connections. Of the 17 reserved connections that are not allocated to either pool, 12 are reserved for the database superuser by default, and another 5 for Tiger Cloud operations. For example, if `max_connections` is set to 500, the maximum number of client connections for your session pool is `483 (500 - 17)` and `9,660 (483 * 20)` for your transaction pool. The configurable range of `max_connections` depends on your service‘s compute size. See [Min and max connection ranges](#min-and-max-connection-ranges). ## Add a connection pooler When you create a new service, you can also create a connection pooler. Alternatively, you can add a connection pooler to an existing service in Console. 1. **Log in to Console and click the service you want to add a connection pooler to** 2. **In `Operations`, click `Connection pooling` > `Add pooler`** Your pooler connection details are displayed in the `Connection pooling` tab. Use this information to connect to your transaction or session pooler. For more information about the different pool types, see the [pool types](#pool-types) section. ## Remove a connection pooler If you no longer need a connection pooler, you can remove it in Console. When you have removed your connection pooler, make sure that you also update your application to adjust the port it uses to connect to your service. 1. **In Console, select the service you want to remove a connection pooler from** 2. **Select `Operations`, then `Connection pooling`** 3. **Click `Remove connection pooler`** Confirm that you want to remove the connection pooler. After you have removed a pooler, if you add it back in the future, it uses the same connection string and port that was used before. ## pgBouncer statistics commands 1. **Connect to your service** 2. **Switch to the `pgbouncer` database: `\\c pgbouncer`** 3. **Run any read-only command for the pgBouncer CLI (for example, `SHOW STATS;`)** For full options, see the pgBouncer [docs here](https://www.pgbouncer.org/usage.html). ## VPC and connection pooling VPCs are supported with connection pooling. It does not matter the order you add the pooler or connect to a VPC. Your connection strings will automatically be updated to use the VPC connection string. ## Min and max connection ranges You can set `max_connections` within the following ranges, based on your service memory and CPU configuration: | Configuration | Minimum | Maximum | | -------------------------------------- | ------- | ------- | | Shared memory and CPU | 25 | 25 | | From 0.5 CPU / 2 GiB to 4 CPU / 16 GiB | 25 | 500 | | 8 CPU / 32 GiB and more | 25 | 2000 | `max_connections` of the primary service cannot be higher than its replica’s.