PSQL Database stops responding after a while

Question

I've been using PSQL for over 2 years now, this issue started occurring 3 Months ago. The database would stop responding after a day of runtime until the affected Node.js process is restarted. 4 Days ago this issue got much worse, unless the host OS was restarted the database stops responding within minutes or less of process runtime.

This issue occurs in only one Node.js process, I have about 4 other Node.js processes running perfectly fine, so it's most likely an issue with my code.

Highest statistics for the affected process:

10 Sessions (constantly stays at that number)
90000 Transactions Per Second (Transactions)
140 Tuples in (Updates)
8000000 Tuples out (Returned)
180000 Block I/O (Hits)

I have tried:

Re-starting Postgres
Re-installing Postgres
using pg-pool (Runs into error: Connection timed out)
using pg-promise (I'm not sure how to apply this module without spamming tasks or connections)

No Errors are emitted, and the connection becomes increasingly slow over several minutes until the pgAdmin Dashboard basically flatlines and no further response is received.

Code: Pool creation (initiated on startup):

const { Pool } = require('pg');
const auth = require('./auth.json');
const ch = require('./ClientHelper');

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'Ayako-v1.5',
  password: auth.pSQLpw,
  port: 5432,
});

pool.query('SELECT NOW() as now;', (err) => {
  if (err) {
    ch.logger("| Couldn't connect to DataBase", err.stack);
  } else {
    console.log('| Established Connection to DataBase');
  }
});

pool.connect((err) => {
  if (err) {
    ch.logger('Error while logging into DataBase', err.stack);
  }
});

pool.on('error', (err) => {
  ch.logger('Unexpected error on idle pool client', err);
});

module.exports = pool;

Queries are executed via:

const query = async (query, arr, debug) => {
    const pool = require('./DataBase');

    if (debug === true) console.log(query, arr);

    return pool.query(query, arr).catch((err) => {
      console.log(query, arr);
      module.exports.logger('Pool Query Error', err);
      return null;
    });

Queries arrive at the above query function but never receive a response. File Links:

https://github.com/Larsundso/Ayako-v1.5/blob/main/Files/BaseClient/DataBase.js

https://github.com/Larsundso/Ayako-v1.5/blob/f2110f3cd73325b35a617fe58d19d8d9c46659d9/Files/BaseClient/ClientHelper.js#L215

Versions PSQL - v14 | Node.js - v17.8.0 | Linux - Ubuntu 20.04.4 LTS

What has changed right before the problems started? And when did you roll out the last patches for all products involved? PostgreSQL v14 could be 14.0 or 14.1 or 14.2 or 14.3 or 14.4. Not to mention alpha and beta versions. — Frank Heikens, Jul 31 '22 at 18:39
`pool.connect(…);` - don't do that, it's creating a client that is never disposed. (Shouldn't be the cause of your problem though) — Bergi, Jul 31 '22 at 21:14
"*Runs into error: Connection timed out*" - does your server have any kind of network problem, failing to connect to the database server (or maybe failing to resolve its hostname)? Or is it really on localhost? — Bergi, Jul 31 '22 at 21:15
Try to add logging also on the [`connect`, `acquire` and `remove` events of the pool](https://node-postgres.com/api/pool#events). Also try logging the [`pool.waitingCount`](https://node-postgres.com/api/pool#properties) (regularly, or when the database is no longer responding) — Bergi, Jul 31 '22 at 21:25
Do you have any long-running queries in your application? Once you have 9 of them, your pool is maxed out and will queue all other queries. Try [looking at the activity stats](https://stackoverflow.com/questions/27435839/how-to-list-active-connections-on-postgresql), what `query`s do you see there for the 10 sessions? — Bergi, Jul 31 '22 at 21:29

score 0 · Accepted Answer · answered Jul 31 '22 at 22:09

I appreciate everyone's help here, logging executed queries brought me on the right track.

The issue was the number of requests handled exceeded PostgreSQL's capabilities, making the queries stack up into timing out.

My solution to this is coupling redis with pSQL to circumvent unnecessarily accessing the Database.

PSQL Database stops responding after a while

1 Answers1