Troubleshooting weird DB connection errors in Rails with PostgreSQL

There are a number of errors that occur when attempting to use a database connection that has suddenly become dead.

Errors like PG::ConnectionBad: PQsocket() can't get socket descriptor:, PG::UnableToSend: no connection to the server, PG::ConnectionBad: PQconsumeInput() server closed the connection unexpectedly, PG::UnableToSend: SSL connection has been closed unexpectedly, and so on.

They may be caused by a few different factors, e.g. long command run, or long SSH connection, or closed connection in separate thread(like in 'parallel' gem), or long code processing before save.

The cause

If you see errors like that, and do have some slow processing, there are several things to check:

1) Rails's database idle_timeout setting, which is 300 seconds by default. This means that if a connection is idle for more than 300 seconds, the server will close it.
2) PgBouncer client_idle_timeout setting. This setting controls how long a connection can be idle before PgBouncer closes it. If this setting is set too low, it can cause the same errors as Rails's idle_timeout.
3) PostgreSQL idle_session_timeout settings (from PostgreSQL 14) and idle_in_transaction_session_timeout(for sessions that were idle in transaction).
4) TCP or other network or firewall timeouts(e.g. there is a case of the timeout in Azure Cloud)

The solution

To solve this problem, there are a few possible solutions:

0) Speed up slow processing
1) Increase the idle_timeout(or another guilty timeout) setting to a higher value to prevent a server from closing idle connections too soon.
2) Add a database setting called reaping_frequency: 10, which will clear and restore connections more frequently to prevent them from becoming idle.
3) Add manual reconnection, like the code snippet below, which releases the connection and re-establishes it before saving a record to the database.

    ActiveRecord::Base.connection_pool.release_connection
    ActiveRecord::Base.connection_pool.with_connection do
      # save record in database
    end

Resources

Links:
(1)(2)(3)(4) Info about reaping_frequency
(5)(6) Info about Rails's idle_timeout
(7)(8)(9) Solutions with manual reconnection