TIL: How to Build Scalable Web Apps with Database Connection Pooling

When building web applications that need to handle thousands of concurrent users, one of the most common bottlenecks is database connection management. Each time a request comes in, creating a new database connection from scratch is expensive and slow—this approach can quickly exhaust system resources and crash your app under load. The solution? Database connection pooling—a technique that reuses a fixed number of connections instead of creating new ones for every request.

Here’s a quick example using Node.js with PostgreSQL and the pg library:

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

const pool = new Pool({
  user: 'your_user',
  host: 'localhost',
  database: 'your_db',
  password: 'your_password',
  port: 5432,
  max: 20, // Maximum number of clients in the pool
  idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000, // Return an error after 2 seconds if connection could not be established
});

// Use the pool in your route handler
app.get('/users', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    console.error(err);
    res.status(500).send('Server error');
  }
});

By reusing connections, your app can handle many more requests without overwhelming the database. This simple change dramatically improves performance and scalability, making it a must-know for any developer building production-grade web apps. Always monitor your pool usage to avoid leaks, and adjust max based on your server’s capacity and traffic patterns.