pg-pool CloudSQL Reconnection

Problem

Node.js applications using pg-pool with CloudSQL (or other managed PostgreSQL) fail to recover from connection resets. Retry logic appears to work but keeps failing with the same ECONNRESET error because the pool caches dead connections.

Context / Trigger Conditions

• ECONNRESET errors that persist despite retry logic
• Error messages like:
  • read ECONNRESET
  • Connection terminated
  • connection already closed
  • remaining connection slots are reserved for non-replication superuser connections
• High-concurrency applications (parallel batch processing)
• CloudSQL with small instance tiers (db-f1-micro, db-g1-small)
• Long-running scripts with periods of inactivity followed by bursts

Root Cause

pg-pool maintains a pool of database connections. When CloudSQL resets connections (due to timeout, maintenance, or connection limits), the pool still holds references to those dead connections. Simply retrying the operation uses the same dead connection from the pool.

Solution

Instead of just retrying, recreate the entire pool on connection reset errors:

export class PostgresDatabase {
  private pool: Pool;
  private config: PostgresConfig;

  constructor(config: PostgresConfig) {
    this.config = config;
    this.pool = this.createPool();
  }

  private createPool(): Pool {
    const pool = new Pool({
      ...this.config,
      max: 25,                          // Adequate for concurrency
      idleTimeoutMillis: 30000,         // Close idle connections
      connectionTimeoutMillis: 10000,   // Timeout for new connections
      keepAlive: true,                  // TCP keepalive
    });

    pool.on('error', (err) => {
      console.error('Pool error:', err.message);
    });

    return pool;
  }

  private async reconnect(): Promise<void> {
    console.log("Reconnecting to database...");
    try {
      await this.pool.end();
    } catch {
      // Ignore cleanup errors
    }
    this.pool = this.createPool();
    await this.pool.query("SELECT 1"); // Verify connection
    console.log("Database reconnected");
  }

  private async withRetry<T>(
    operation: () => Promise<T>,
    maxRetries = 3
  ): Promise<T> {
    for (let attempt = 0; attempt < maxRetries; attempt++) {
      try {
        return await operation();
      } catch (error) {
        const msg = error instanceof Error ? error.message : String(error);
        const needsReconnect =
          msg.includes("ECONNRESET") ||
          msg.includes("Connection terminated") ||
          msg.includes("connection already closed") ||
          msg.includes("remaining connection slots");

        if (!needsReconnect || attempt === maxRetries - 1) {
          throw error;
        }

        const delay = 1000 * Math.pow(2, attempt);
        console.log(`Connection error, reconnecting in ${delay}ms...`);
        await new Promise(r => setTimeout(r, delay));

        // KEY: Recreate the pool, don't just retry
        await this.reconnect();
      }
    }
    throw new Error("Max retries exceeded");
  }
}

Key Insight

Retrying with the same pool uses cached dead connections. The fix is to:

1. Detect connection reset errors
2. Call pool.end() to close all connections
3. Create a new pool instance
4. Verify the new connection works
5. Then retry the operation

CloudSQL-Specific Considerations

1. Instance tier matters: db-f1-micro only supports ~25 connections. Use db-g1-small or larger for concurrent workloads.

2. Check max_connections:

   gcloud sql instances describe INSTANCE --format='value(settings.tier)'
   

3. Cloud SQL Proxy: Doesn't help with connection pooling - it just tunnels TCP. Consider PgBouncer for true connection pooling.

Verification

After implementing:

1. Run high-concurrency workload
2. Intentionally cause connection reset (restart proxy, wait for idle timeout)
3. Verify operations resume with "Reconnecting to database..." log
4. No more cascading failures

Notes

• This pattern applies to any managed PostgreSQL, not just CloudSQL
• Consider PgBouncer for production workloads with very high concurrency
• The pool.on('error') handler prevents uncaught exceptions from crashing the app
• Set max pool size to match your concurrency level plus headroom