Strapi v4 + Postgres + Render.com = Error "Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?"

[terchris]

@shrikant

module.exports = ({ env }) => ({
connection: {
client: ‘postgres’,
connection: {
host: env(‘DATABASE_HOST’, ‘127.0.0.1’),
port: env.int(‘DATABASE_PORT’, 5432),
database: env(‘DATABASE_NAME’, ‘strapi’),
user: env(‘DATABASE_USERNAME’, ‘strapi’),
password: env(‘DATABASE_PASSWORD’, ‘strapi’),
schema: env(‘DATABASE_SCHEMA’, ‘public’), // Not required
ssl: env(‘DATABASE_SSL’, false)
},
acquireConnectionTimeout: 1000000,
pool: {
min: 0,
max: 5,
acquireTimeoutMillis: 300000,
createTimeoutMillis: 300000,
destroyTimeoutMillis: 300000,
idleTimeoutMillis: 30000,
reapIntervalMillis:1000,
createRetryIntervalMillis: 2000
},
debug: false,
},
});

Thank you @shrikant for sharing. As I did not understand the consequences of these settings, I asked chatGPT to explain the settings (I hope it is telling the truth). Some of the values are defaults and do not change anything. Others have consequences, and I’m trying to understand them so that I don’t create new problems after adjusting.
Can you tell me more about why you set the values?

This is what chatgpt told me:

In the provided configuration code, several Knex.js connection and pool settings are set. Here’s an explanation of the parameters from acquireConnectionTimeout and down:

1. acquireConnectionTimeout: The time (in milliseconds) to wait before raising an error when a connection cannot be acquired from the pool. Default value: 60000 (60 seconds). In your example, it is set to 1000000 ms (1000 seconds).
2. pool: An object containing the connection pool settings.

• min: The minimum number of connections in the pool. Default value: 2. In your example, it is set to 0.
• max: The maximum number of connections in the pool. Default value: 10. In your example, it is set to 5.
• acquireTimeoutMillis: The time (in milliseconds) to wait for a connection to be acquired from the pool before raising an error. Default value: 60000 (60 seconds). In your example, it is set to 300000 ms (300 seconds).
• createTimeoutMillis: The time (in milliseconds) to wait for a connection to be created before raising an error. Default value: 30000 (30 seconds). In your example, it is set to 300000 ms (300 seconds).
• destroyTimeoutMillis: The time (in milliseconds) to wait for a connection to be destroyed before raising an error. Default value: 5000 (5 seconds). In your example, it is set to 300000 ms (300 seconds).
• idleTimeoutMillis: The time (in milliseconds) after which idle connections will be released. Default value: 30000 (30 seconds). In your example, it is set to 30000 ms (30 seconds).
• reapIntervalMillis: The time (in milliseconds) between pool reaping events, during which idle connections are released. Default value: 1000 (1 second). In your example, it is set to 1000 ms (1 second).
• createRetryIntervalMillis: The time (in milliseconds) between connection creation retry attempts. Default value: 200. In your example, it is set to 2000 ms (2 seconds).

3. debug: A boolean indicating whether to enable query debugging. If set to true, Knex.js will log every executed query. Default value: false. In your example, it is set to false.

The provided configuration sets custom values for many of the Knex.js pool settings. Keep in mind that setting extreme values for these settings may affect the performance and stability of your application. Make sure to test and monitor your application to find the optimal configuration.