Be Aware of Max Connections!

If you are using a connection pool to connect to a Postgres database, and dynamically scale your application, make sure that the total number of connections from all sources does not exceed Postgres's max_connections.

Table of Contents 📖

• Postgres max_connections
• Exceeding max_connections
• Example Node Connection Pool

Postgres max_connections

If you are using a connection pool to connect to a Postgres database, you should be aware of the max_connections setting inside Postgres's main configuration file: postgresql.conf. You can either check the setting directly inside postgres.conf, or you can use the following command:

SHOW max_connections;
max_connections
100

This setting determines the total number of concurrent connections that the PostgreSQL server allows, including all clients and administrative connections. If the sum of all active connections from all clients, pools, and applications exceeds max_connections, PostgreSQL will reject new connection attempts.

ERROR: If you have multiple applications or services connecting to the same database, ensure the combined pool sizes do not exceed max_connections.

Exceeding max_connections

If the combined number of connections from all sources exceeds max_connections, new connection attempts will fail with an error like the following, causing timeouts or degraded performance.

FATAL: too many connections for role "postgres"

Example Node Connection Pool

To demonstrate setting a connection pool size, you can use the Node pg library. When creating this pool, you can set the pool size by using the max key, the maximum number of client connections that the connection pool will maintain.

import {Pool} from 'pg';

class BaseDao {
    static instance = null; // Holds the single instance of BaseDao

    /**
     * Private constructor to prevent direct instantiation.
     * @param {Object} config - Database configuration
     */
    constructor(config) {
        if (BaseDao.instance) {
            throw new Error('BaseDao is a singleton class. Use BaseDao.getInstance() instead.');
        }

        if (!config) {
            throw new Error('Database configuration is required');
        }

        this.pool = new Pool({
            user: config.user || 'postgres',
            host: config.host || 'localhost',
            database: config.database || 'my_database',
            password: config.password || '',
            port: config.port || 5432,
            max: config.poolSize || 10, // Pool size
            idleTimeoutMillis: config.idleTimeoutMillis || 30000, // Close idle clients after 30 seconds
            connectionTimeoutMillis: config.connectionTimeoutMillis || 2000, // Wait 2 seconds to get a connection
        });

        // Event listeners for debugging
        this.pool.on('connect', () => {
            console.log('A client connected to the database');
        });

        this.pool.on('remove', () => {
            console.log('A client was removed from the pool');
        });

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

        BaseDao.instance = this; // Assign instance to the static property
    }

    /**
     * Returns the single instance of BaseDao. If it doesn't exist, it creates one.
     * @param {Object} config - Database configuration (only needed for first call)
     * @returns {BaseDao} - The single instance of BaseDao
     */
    static getInstance(config) {
        if (!BaseDao.instance) {
            if (!config) {
                throw new Error('Configuration is required for the first initialization of BaseDao');
            }
            new BaseDao(config); // Create a new instance
        }
        return BaseDao.instance;
    }
}

export default BaseDao;

INFO: The poolSize in your Node connection pool does not need to match max_connections in PostgreSQL, but the total number of connections from all sources must stay below max_connections.

If this Node application was Dockerized, and can be dynamically scaled, we would need to ensure that the total number of connections from all sources does not exceed max_connections.