I’m using Postgres on my (now) Nodejs server. During development, I don’t run into any issues.

However, whenever I create a production or preview build, some requests fail with a timeout error.

On server boot I create and export the client instance:

// src/db/client.tsx
const isDev = process.env.NODE_ENV === 'development';

export const dbClient = new pg.Pool({
    user: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD,
    host: process.env.POSTGRES_HOST,
    port: parseInt(process.env.POSTGRES_PORT),
    database: process.env.POSTGRES_DB,
    max: 20,
    idleTimeoutMillis: 2e3,
    connectionTimeoutMillis: isDev ? 1e6 : 3e4,
});

And then in my startServer function I call connect:

const startServer = async () => {
    // do some stuff

    await Promise.all([
        // register routes and controllers,
        // register middleware,
        // register plugins,
        dbClient.connect(), // connect the client
    ]);

    // do some other stuff
}

Then, I import this instance into another file, which creates a query wrapper:

// src/db/executeQuery.tsx
import { dbClient } from './client'

export const executeQuery = async ({
    tableName,
    query,
    returnSingleton,
    values = [],
}) => {
    try {
        const { rows } = await dbClient.query(query, values);
        
        if (returnSingleton) {
            return rows?.[0] ?? null;
        }

        return rows;
    } catch (err) {
        console.error(`Error caught with query in ${tableName}`);
        console.error(query);

        throw err;
    }
};

And finally, I use executeQuery wherever required:

// src/api/users
export const getUserById = async (req, res) => {
    const user = await executeQuery({
        tableName: 'user',
        query: 'select * from user where id = $1',
        values: [123]
    });

    return res.send({ user });
}

Everything works just fine in development mode. But once deployed to production, I sometimes catch the following error:

Error caught with query in user
    Connection terminated due to connection timeout

I have been ripping my hair out over this problem and I’m unsure how to fix it.

A couple things that I should probably mention:

• Note how I call dbClient.connect() once throughout the lifecycle of the server
• I’m using a pg.Pool not pg.Client (I haven’t been able to quite understand the difference between these two, as both work and behave the same for me
• I use the redis adapter with socket.io, in order to be able to broadcast events to all or a subset of clients (I’ve set up load balancing with nginx for all environments other than development, because during development, I only use one port and CPU anyway)
• I’m using Fastify