Database Connection Pooling
Overview
Physically opening a TCP connection to a PostgreSQL or MySQL database is incredibly slow and CPU-intensive (it requires DNS lookups, TCP handshakes, and Authentication). If your API opens a brand new DB connection for every single incoming HTTP request, your server will bottleneck and crash at just 50 requests per second. A Connection Pool solves this. When the Node server boots up, it opens a 'Pool' of (e.g., 20) persistent, reusable database connections. When a request comes in, it borrows a connection, runs the query, and instantly returns the connection to the pool for the next user.
Syntax
// npm install pg
const { Pool } = require('pg');
// 1. Initialize the Pool ONCE when the server boots
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 20, // Maximum number of persistent connections in the pool
idleTimeoutMillis: 30000 // Close connections if idle for 30 seconds
});
app.get('/api/users', async (req, res, next) => {
try {
// 2. Borrow a connection from the pool, run the query,
// and AUTOMATICALLY return it to the pool!
const result = await pool.query('SELECT * FROM users');
res.json(result.rows);
} catch (err) {
next(err);
}
});Common Pitfalls
- Leaking Connections. If you manually check out a client (
const client = await pool.connect()) so you can run a multi-query Transaction, you MUST manually release it back to the pool (client.release()). If your code throws an error before hittingrelease(), that connection is permanently orphaned. After 20 errors, your entire pool is empty, and the API will permanently freeze, waiting for a connection that will never arrive. ALWAYS putclient.release()inside afinallyblock. - Setting the Pool size too high. Beginners assume
max: 1000is better thanmax: 20. It is not. PostgreSQL allocates massive amounts of RAM for every active connection. 1000 connections will instantly exhaust the Database Server's memory and crash it. A pool of 20-50 is usually optimal for handling thousands of API requests per second.
Interview Questions
A Client represents exactly one single, dedicated TCP connection to the database. If 5 users try to use it simultaneously, they are forced to queue up synchronously. A Pool is a sophisticated manager of multiple Clients, allowing true asynchronous parallel execution across the available connections.
Real-World Example
Safely executing a Database Transaction using a dedicated Pool Client, ensuring it is always released.
app.post('/api/transfer', async (req, res) => {
// 1. Borrow a dedicated client
const client = await pool.connect();
try {
await client.query('BEGIN'); // Start transaction
await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
await client.query('COMMIT'); // Lock it in!
res.send("Transfer complete");
} catch (e) {
await client.query('ROLLBACK'); // Abort!
res.status(500).send("Transfer failed");
} finally {
// CRITICAL: Return the client to the pool regardless of success or failure!
client.release();
}
});Check Your Knowledge
Test your understanding of Database Connection Pooling with these quick questions.