ECONNRESET with Node.js and PostgreSQL

Hi there,

I’m using Postgres.js with Node.js and a PostgreSQL service:

// database.js
const sql = postgres();

export async function getCourses() {
  const courses = await sql`
    SELECT * FROM courses
  `;
  return courses;
}

This is working for the most part, but after a while the connection fails somehow with a mysterious ECONNRESET error, and then all database queries fail for a few minutes (maybe until Postgres.js reconnects?). The errors unfortunately do not shed much light about the cause of the problem:

Apr 27 10:22:53 PM  node:internal/process/promises:246
Apr 27 10:22:53 PM            triggerUncaughtException(err, true /* fromPromise */);
Apr 27 10:22:53 PM            ^
Apr 27 10:22:53 PM  
Apr 27 10:22:53 PM  Error: read ECONNRESET    at TCP.onStreamRead (node:internal/stream_base_commons:211:20) {
Apr 27 10:22:53 PM    errno: -104,
Apr 27 10:22:53 PM    code: 'ECONNRESET',
Apr 27 10:22:53 PM    syscall: 'read'
Apr 27 10:22:53 PM  }
Apr 27 10:22:54 PM  error Command failed with exit code 1.

In the issue on the Postgres.js repo, they discuss changing the connection timeout from 30 seconds (default) to 10 seconds.

Before I do this, I’d like to hear from the Render support team to know if there are any such timeouts that exist with connections, if I can do any further configuration here or if there are some general recommendations. I will also continue debugging more from my side to make sure I’m not doing something wrong.

I never had any issues with Postgres.js with Heroku’s PostgreSQL service.

Edit: Opened a new issue on the Postgres.js repo: ECONNRESET after a while, reconnecting takes a few minutes :( · Issue #179 · porsager/postgres · GitHub

Thanks!

So I may have made some progress here.

It seems like on Render, idle (persistent) PostgreSQL connections are being automatically closed (terminated) after some time.

This behavior is a problem for the Postgres.js client library, because it keeps connections open by default:

Connections will by default not close until .end() is called

So reading further, there is another option called idle_timeout:

but often it is useful to have them close when there is no activity or if using Postgres.js in eg. Lamdas / Serverless environments. This can be done using the idle_timeout option to specify the amount of seconds to wait before automatically closing an idle connection.

When I changed the setting idle_timeout option in Postgres.js to a low amount of seconds, then the ECONNRESET errors stopped (at least, they have not shown up for a few hours now).

So the question remains for the Render support team: why can this terminating behavior be observed? Is there a way to configure Render PostgreSQL like Heroku so that idle connections can stay open?

cc @dan @shantanu @jake

1 Like

Hi Karl,

Thanks for sharing your findings. I’d love to dig into this more and will reach out over DM to collect more info.