Node.js에서 PostgreSQL 연결 풀 최적화

Introduction

PostgreSQL connections are expensive resources. Each connection consumes memory on both the client and server side, and too many connections can exhaust server resources. Proper connection pool configuration is essential for scalable Node.js applications.

Environment

node --version
# v20.11.0

npm list pg pg-pool
# my-app@1.0.0
# +-- pg@8.11.3
# +-- pg-pool@3.6.1

psql --version
# psql (PostgreSQL) 15.4

Problem

Under high load, the application was experiencing connection errors:

Error: Connection pool exhausted
    at IdleTimeoutError (node:internal/streams/readable:758:22)

FATAL: too many connections for role "myuser"

The default pool configuration was insufficient for production traffic:

const { Pool } = require('pg');

const pool = new Pool({
  user: 'myuser',
  password: 'mypassword',
  host: 'localhost',
  port: 5432,
  database: 'mydb'
  // Uses default pool settings
});

Analysis

The default pg-pool configuration:

// Default values in pg-pool
{
  max: 10,                    // Maximum connections
  min: 0,                     // Minimum connections
  idleTimeoutMillis: 10000,   // Close idle connections after 10s
  connectionTimeoutMillis: 0, // No timeout
  allowExitOnIdle: false      // Keep pool alive
}

The problem is that the default max: 10 is too low for most applications, while too high values exhaust PostgreSQL's max_connections setting.

Solution

Solution 1: Configure pool for production

const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME,
  
  // Pool configuration
  max: 20,                    // Maximum connections
  min: 5,                     // Keep 5 connections ready
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Timeout after 5s
  
  // Statement timeout
  statement_timeout: 30000,   // Kill queries after 30s
  
  // SSL for production
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false
});

// Handle pool errors
pool.on('error', (err) => {
  console.error('Unexpected pool error:', err);
});

pool.on('connect', () => {
  console.log('New client connected to pool');
});

pool.on('remove', () => {
  console.log('Client removed from pool');
});

Solution 2: Monitor pool statistics

class PoolMonitor {
  constructor(pool) {
    this.pool = pool;
    this.stats = {
      totalConnections: 0,
      idleConnections: 0,
      waitingClients: 0,
      queryCount: 0,
      errorCount: 0
    };
  }
  
  getStats() {
    return {
      total: this.pool.totalCount,
      idle: this.pool.idleCount,
      waiting: this.pool.waitingCount
    };
  }
  
  async checkHealth() {
    try {
      const client = await this.pool.connect();
      const result = await client.query('SELECT NOW()');
      client.release();
      
      return {
        status: 'healthy',
        timestamp: result.rows[0].now,
        pool: this.getStats()
      };
    } catch (err) {
      return {
        status: 'unhealthy',
        error: err.message,
        pool: this.getStats()
      };
    }
  }
}

// Usage
const monitor = new PoolMonitor(pool);

// Log pool stats every 30 seconds
setInterval(() => {
  console.log('Pool stats:', monitor.getStats());
}, 30000);

// Health check endpoint
app.get('/health/db', async (req, res) => {
  const health = await monitor.checkHealth();
  res.status(health.status === 'healthy' ? 200 : 503).json(health);
});

Solution 3: Connection pool with retry logic

class ResilientPool {
  constructor(config) {
    this.config = config;
    this.pool = null;
    this.retryCount = 0;
    this.maxRetries = 5;
    this.init();
  }
  
  init() {
    this.pool = new Pool(this.config);
    
    this.pool.on('error', (err) => {
      console.error('Pool error, attempting reconnect:', err.message);
      this.reconnect();
    });
  }
  
  async reconnect() {
    if (this.retryCount >= this.maxRetries) {
      console.error('Max reconnection attempts reached');
      return;
    }
    
    this.retryCount++;
    const delay = Math.min(1000 * Math.pow(2, this.retryCount), 30000);
    
    console.log(`Reconnecting in ${delay}ms (attempt ${this.retryCount})`);
    
    setTimeout(() => {
      this.pool = new Pool(this.config);
      this.retryCount = 0;
    }, delay);
  }
  
  async query(text, params) {
    try {
      const result = await this.pool.query(text, params);
      return result;
    } catch (err) {
      if (err.code === 'ECONNREFUSED' || err.code === '57P01') {
        await this.reconnect();
        return this.pool.query(text, params);
      }
      throw err;
    }
  }
  
  async getClient() {
    return this.pool.connect();
  }
}

Solution 4: PgBouncer for production

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

Lessons Learned

1. Set max connections based on PostgreSQL max_connections - Do not exceed server limits
2. Monitor pool usage - Track idle, active, and waiting connections
3. Use connection timeouts - Prevent hanging connections
4. Implement retry logic - Connections can fail under load
5. Consider PgBouncer for very high connection counts

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.