Database connection timeout error (ECONNRESET) and keepalive config

Sang -

Sometimes, you observe that the database servers (postgres, redis, etc.) being disconnected after being idle for a while.

This is due to the keepalive config, in which tcp connections are killed if they are detected not being used for a specific time range. This configuration is imposed by numerous components in your network stack:

  • The client OS.
  • The server OS.
  • The server process (postgres, redis, mongodb, etc.).
  • Router.
  • Internet provider.
  • VPS provider.

The most common components which cause the issue are the two first. Next, I will show how to check and update this setting in your OS, and how to configure your client app to bypass this check.

Get the current configuration

For the Linux OS:

sysctl net.ipv4.tcp_keepalive_time
sysctl net.ipv4.tcp_keepalive_intvl
sysctl net.ipv4.tcp_keepalive_probes

Default value:

net.ipv4.tcp_keepalive_time = 7200
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9

If for 7200 seconds, there is no activity in the tcp connection. The OS will send 9 probe requests for each 75-second interval. If there is no response, the OS will kill this tcp connection.

For the PostgresQL server

Docs

In the sql console:

show tcp_keepalives_idle;
show tcp_keepalives_interval;
show tcp_keepalives_count;

Default value: same as of the Linux OS. If the configured values are 0, postgresql server will use the OS setting.

Update the configuration

You can update these configurations:

Linux OS

sudo sysctl -w net.ipv4.tcp_keepalive_time=7200
sudo sysctl -w net.ipv4.tcp_keepalive_intvl=75
sudo sysctl -w net.ipv4.tcp_keepalive_probes=9

SQL console

SET tcp_keepalives_idle = 7200;
SET tcp_keepalives_interval = 75;
SET tcp_keepalives_count = 9;

The configuration by SQL command is effective within the current connection lifetime.

Configuration for nodejs postgres driver

You can configure pg-postgres to automatically send keepalive requests to avoid the database connection being detected as idle. By default, this feature is disabled. In the connection config, add:

{
	keepAliveInitialDelayMillis: 10_000,
	keepAlive: true,
}

This setting is directly passed to NodeJS socket's setKeepAlive:

In node-postgres package, there is another setting, called idleTimeoutMillis: https://github.com/brianc/node-postgres/blob/3e53d06cd891797469ebdd2f8a669183ba6224f6/packages/pg/lib/defaults.js#L39

In this setting, node-postgres close and destroy the connection from the pool if it is unused for this time. Default value: 30 seconds.