Database Connection Pooling and Timeout Management

Why Connection Pooling?

Without Pooling (Slow)

// Every request creates new connection
app.get('/users', async (req, res) => {
  const client = await createConnection();  // Slow! ~50-100ms
  const users = await client.query('SELECT id, name, email, created_at FROM users');
  await client.close();
  res.json(users);
});
// With 100 concurrent requests = 5-10 seconds wasted

With Pooling (Fast)

// Reuse connections from pool
const pool = createPool({ size: 10 });

app.get('/users', async (req, res) => {
  const users = await pool.query('SELECT id, name, email, created_at FROM users');  // Fast! ~1-2ms
  res.json(users);
});
// With 100 concurrent requests = ~100ms

Node.js Connection Pooling

PostgreSQL (pg)

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

const pool = new Pool({
  host: 'localhost',
  database: 'myapp',
  user: 'postgres',
  password: 'secret',
  port: 5432,

  // Pool configuration
  min: 2,                    // Minimum connections
  max: 10,                   // Maximum connections
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 2000,  // Wait 2s for connection
  maxUses: 7500,             // Close connection after 7500 uses
});

// Usage
async function getUsers() {
  const result = await pool.query('SELECT id, name, email, created_at FROM users');
  return result.rows;
  // Connection automatically returned to pool
}

// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

MySQL (mysql2)

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'secret',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,       // Max connections
  queueLimit: 0,             // Unlimited queue
  connectTimeout: 10000,     // 10s connection timeout
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

// Usage with async/await
async function getUsers() {
  const [rows] = await pool.execute(
    'SELECT id, name, email, created_at FROM users WHERE is_active = ?',
    [true]
  );
  return rows;
}

// Transaction example
async function transferMoney(fromId, toId, amount) {
  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();

    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [amount, fromId]
    );

    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );

    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();  // Return to pool
  }
}

.NET Connection Pooling

Entity Framework Core

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=myapp;User Id=sa;Password=Secret123;Min Pool Size=5;Max Pool Size=100;Connection Timeout=30;"
  }
}

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection"),
        sqlOptions => {
            sqlOptions.CommandTimeout(30);
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 3,
                maxRetryDelay: TimeSpan.FromSeconds(5),
                errorNumbersToAdd: null
            );
        }
    )
);

ADO.NET

var connectionString = "Server=localhost;Database=myapp;" +
    "User Id=sa;Password=Secret123;" +
    "Min Pool Size=5;" +          // Minimum connections in pool
    "Max Pool Size=100;" +        // Maximum connections
    "Connection Timeout=30;" +    // 30s connection timeout
    "Connection Lifetime=600;";   // Close connections after 10 minutes

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

using var command = new SqlCommand("SELECT * FROM Users", connection);
using var reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync()) {
    Console.WriteLine(reader["Name"]);
}
// Connection automatically returned to pool when disposed

Timeout Configuration

Query Timeout vs Connection Timeout

// Connection timeout - how long to wait for connection
const pool = new Pool({
  connectionTimeoutMillis: 5000  // Wait 5s to get connection from pool
});

// Query timeout - how long query can run
await pool.query('SELECT * FROM huge_table', {
  statement_timeout: 10000  // Kill query after 10s
});

Setting Statement Timeouts

PostgreSQL:

-- Global setting
ALTER DATABASE myapp SET statement_timeout = '30s';

-- Session setting
SET statement_timeout = '10s';
SELECT * FROM slow_query;

MySQL:

-- Global setting
SET GLOBAL max_execution_time = 30000;  -- 30 seconds

-- Per query
SELECT /*+ MAX_EXECUTION_TIME(10000) */ * FROM slow_table;

SQL Server:

command.CommandTimeout = 30;  // 30 seconds

Monitoring Connection Pools

SQL Server Connection Stats

SET NOCOUNT ON;

-- Current connections by database
SELECT
    DB_NAME([dbid]) AS [DatabaseName],
    COUNT([dbid]) AS [ConnectionCount]
FROM [sys].[sysprocesses] WITH(NOLOCK)
WHERE [dbid] > 0
GROUP BY DB_NAME([dbid])
ORDER BY [ConnectionCount] DESC
OPTION (RECOMPILE);

-- See active queries
SELECT
    [session_id] AS [SessionId],
    [login_name] AS [LoginName],
    [status] AS [Status],
    [command] AS [Command],
    [database_id] AS [DatabaseId],
    DB_NAME([database_id]) AS [DatabaseName],
    [wait_type] AS [WaitType],
    [wait_time] AS [WaitTimeMs],
    [cpu_time] AS [CpuTimeMs],
    [logical_reads] AS [LogicalReads],
    [reads] AS [PhysicalReads],
    [writes] AS [Writes]
FROM [sys].[dm_exec_requests] WITH(NOLOCK)
WHERE [session_id] > 50  -- Exclude system sessions
ORDER BY [cpu_time] DESC
OPTION (RECOMPILE);

Application-Level Monitoring

// Log pool stats periodically
setInterval(() => {
  console.log({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount
  });
}, 60000);  // Every minute

// If waiting > 0 consistently, increase pool size

Common Issues and Solutions

1. Connection Leaks

// BAD - connection never released
async function badExample() {
  const client = await pool.connect();
  const result = await client.query(
    'SELECT id, name, email, created_at FROM users WHERE is_active = true'
  );
  // Forgot to release!
  return result.rows;
}

// GOOD - always release
async function goodExample() {
  const client = await pool.connect();
  try {
    const result = await client.query(
      'SELECT id, name, email, created_at FROM users WHERE is_active = true'
    );
    return result.rows;
  } finally {
    client.release();  // Always release
  }
}

// BETTER - use pool.query (auto-releases)
async function betterExample() {
  const result = await pool.query(
    'SELECT id, name, email, created_at FROM users WHERE is_active = true'
  );
  return result.rows;
}

2. Pool Exhaustion

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

// Monitor waiting connections
if (pool.waitingCount > 5) {
  console.warn('Pool exhausted! Consider increasing max connections');
}

3. Idle Connections

// PostgreSQL kills idle connections
const pool = new Pool({
  idleTimeoutMillis: 30000,  // Close after 30s idle
  max: 10,
  min: 2  // Keep minimum of 2 connections
});

Best Practices

1. •

   Set appropriate pool size

   • •Start with: max = (core_count * 2) + effective_spindle_count
   • •For CPU-bound: max = core_count + 1
   • •Monitor and adjust based on actual usage
2. •

   Always release connections

// Use try/finally
const client = await pool.connect();
try {
  await client.query('...');
} finally {
  client.release();
}

3. •Set timeouts

const pool = new Pool({
  connectionTimeoutMillis: 5000,   // Connection timeout
  query_timeout: 30000,            // Query timeout
  statement_timeout: 30000         // Statement timeout
});

4. •Monitor connection usage

// Log pool stats
console.log(`Active: ${pool.totalCount - pool.idleCount}`);
console.log(`Idle: ${pool.idleCount}`);
console.log(`Waiting: ${pool.waitingCount}`);

5. •Graceful shutdown

process.on('SIGTERM', async () => {
  console.log('Closing pool...');
  await pool.end();
  process.exit(0);
});

Production Configuration Example

const pool = new Pool({
  // Connection
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  port: parseInt(process.env.DB_PORT || '5432'),

  // SSL for production
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false,

  // Pool config
  min: 5,                        // Keep 5 connections ready
  max: 20,                       // Up to 20 connections
  idleTimeoutMillis: 30000,      // Close idle after 30s
  connectionTimeoutMillis: 5000, // Wait 5s for connection

  // Timeouts
  query_timeout: 30000,          // 30s query timeout
  statement_timeout: 30000,      // 30s statement timeout

  // Reliability
  keepAlive: true,
  keepAliveInitialDelayMillis: 10000,
});

// Error handling
pool.on('error', (err, client) => {
  console.error('Unexpected pool error:', err);
  // Alert monitoring system
});

Conclusion

Proper connection pooling is crucial for:

• •⚡ Performance (100x faster than creating connections)
• •💰 Resource efficiency (reuse expensive connections)
• •🛡️ Stability (handle traffic spikes gracefully)