A connection pool is a cache of reusable database connections. It allows serving many HTTP requests using few database connections. Every request to an API resource borrows a connection from the pool to start a transaction.
Minimizing connections is paramount to performance. Each PostgreSQL connection creates a process, having too many can exhaust available resources.
For connecting to the database, the pool requires a connection string. You can configure it using db-uri.
Dynamic Connection Pool
To conserve system resources, PostgREST uses a dynamic connection pool. This enables the number of connections in the pool to increase and decrease depending on request traffic.
If all the connections are being used, a new connection is added. The pool can grow until it reaches the db-pool size. Note that it’s pointless to set this higher than the
max_connectionssetting in your database.
If a connection is unused for a period of time (db-pool-max-idletime), it will be released.
Long-lived PostgreSQL connections can consume considerable memory (see here for more details). Under a busy system, the db-pool-max-idletime won’t be reached and the connection pool can be full of long-lived connections.
To avoid this problem and save resources, a connection max lifetime (db-pool-max-lifetime) is enforced. After the max lifetime is reached, connections from the pool will be released and new ones will be created. This doesn’t affect running requests, only unused connections will be released.
If all the available connections in the pool are busy, an HTTP request will wait until reaching a timeout (db-pool-acquisition-timeout).
If the request reaches the timeout, it will be aborted with the following response:
HTTP/1.1 504 Gateway Timeout
"message":"Timed out acquiring connection from connection pool."}
Getting this error message is an indicator of a performance issue. To solve it, you can:
Reduce your queries execution time.
Check the request Execution plan to tune your query, this usually means adding indexes.
Reduce the amount of requests.
Reduce read requests. Use Resource Embedding. Combine unrelated data into a single request using custom database views or functions.
Use Stored Procedures for combining read and write logic into a single request.
Increase the db-pool size.
Not a panacea since connections can’t grow infinitely. Try the previous recommendations before this.
The server will retry reconnecting to the database if connection loss happens.
It will retry forever with exponential backoff, with a maximum backoff time of 32 seconds between retries. Each of these attempts are logged.
It will only stop retrying if the server deems the error to be fatal. This can be a password authentication failure or an internal error.
The retries happen immediately after a connection loss, if db-channel-enabled is set to true (the default). Otherwise they’ll happen once a request arrives.
To notify the client of the next retry, the server sends a
503 Service Unavailablestatus with the
Retry-After: xheader. Where
xis the number of seconds programmed for the next retry.
Automatic recovery can be disabled by setting db-pool-automatic-recovery to
Using External Connection Poolers
It’s possible to use external connection poolers, such as PgBouncer. Session pooling is compatible, while transaction pooling requires db-prepared-statements set to
false. Statement pooling is not compatible with PostgREST.
Also set db-channel-enabled to
LISTEN is not compatible with transaction pooling. Although it should not give any errors if left enabled.
It’s not recommended to use an external connection pooler. Our benchmarks indicate it provides much lower performance than PostgREST built-in pool.