14

We have a production deployed Node js application which services a good amount of traffic throught the day. The application is in loopback and connects to postgres db as with its postgres connector. The connector leverages Node-Postgres as its pg client. We generally have kept max pool size as 50 and min as 5. There are three instances running on an average for the application.

The issue we are facing is that every now and then the application is unable to make a DB connection with error - reason: Error: timeout exceeded when trying to connect. We have tried to debug the same but there is no load on the DB while this happens - no cpu spike, no memory utilisation spike. FYI we use AWS Postgres RDS.

We have kept the following configuration as a part of connection parameters

    connector: 'postgresql',
    host: host,
    user: user,
    password: password,
    database: database,
    min: 5,
    max: 50,
    idleTimeoutMillis: 60000,
    query_timeout: 5000,
    connectionTimeoutMillis: 10000

Can someone please give us an idea why this can happen so frequently. Currently this is happening on a weekly basis. We are able to solve it only after application restart. This likely frees up the pool and terminates idle connections. But we are already doing it via application.

Improve this question
4
  • 1
    Sounds like you app is leaking connections. Once the pool has 50, no new connections are allowed and will eventually timeout. What do you see in pg_stat_activity while this is happening? Commented Apr 29, 2022 at 14:00
  • Will update with you with findings if this happens next @jjanes. We were checking the AWS RDS performance insights - top sql/top hosts/ top waits/top sessions. At neither of the places we saw any anomaly. As per your suggestion will also check pg_stat_activity. Can you let me know which are the params where I should be interested in? Commented Apr 30, 2022 at 8:18
  • We upgraded our node and postgres dependencies and resized our managed RDS. Since then we have not seen this issue popping up again. Another backup plan that we had was to use redis in conjugation with Postgres for persistence. Read will happen on redis and write on postgres @ShlomoLevi. Sorry for the late response. Was inactive for a while. Commented Nov 14, 2022 at 15:13
  • Most likely incorrect connection management, which results in leaking connections. And if the issue is gone after update, then perhaps one of the updated dependencies was managing connections incorrectly. Commented Nov 24, 2022 at 10:31

3 Answers 3

Reset to default
0

Try to add "maxUses" to connection settings

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
0

Are you using transactions? If so then you should definitely release the client after your transaction is committed or rolled back.

Changing the configuration connector: 'postgresql', host: host, user: user, password: password, database: database, min: 5, max: 50, idleTimeoutMillis: 60000, query_timeout: 5000, connectionTimeoutMillis: 10000 here won't give a long lasting result, you have to make changes in your code.

//write statement that denotes the start of your transaction.

//code to COMMIT/ROLLBACK your transaction

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
0

We faced the same problem, mostly due to eventual network failures.

In some cases, dead connections are not flushed from the pool if they are not properly closed (due to socket timeouts, etc...).

I finally developed a wrapper over node-postgres that handles those situation and it is working well for us:

You can find it at NPM as ha-pg.

I'd like to transpose those tweaks PRs to the actual node-postgres repo, but I never find the time.

Hope it cou

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.