Connection Pooling

Objectives

By the end of this lesson, you should be able to:

• Explain why a real application uses a connection pool instead of a single client
• Use pg’s Pool to run queries, including concurrent ones
• Check out a single client from a pool when one is genuinely needed

💡 Why this matters: A single Client (last lesson) handles one connection, and one query at a time. A real Express app handles many requests at once, each potentially needing the database simultaneously, that’s exactly what a Pool is built for.

⚠️ A note on verification: every snippet and every result shown below was actually run against a real PostgreSQL server.

The Problem With a Single Client

Opening a new Client and calling .connect() for every request is slow, connecting has real overhead, and a single Client can only run one query at a time, later queries wait in line even if the database itself could easily handle them in parallel.

What a Pool Does

A Pool manages a set of open connections, up to a configured maximum, handing one out whenever pool.query() is called, and returning it to the pool automatically when the query finishes:

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

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'postgres',
  database: 'school',
  max: 10
});

max: 10 caps the pool at 10 simultaneous connections, a sensible default for a small-to-medium app. Requests beyond that wait for a connection to free up, rather than overwhelming the database.

Querying Through a Pool

pool.query() looks identical to client.query(), it checks out a connection, runs the query, and returns it automatically:

await pool.query('CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT NOT NULL, grade INTEGER)');

const result = await pool.query(
  'INSERT INTO students (name, grade) VALUES ($1, $2) RETURNING id, name',
  ['Erin', 9]
);
console.log(result.rows);

[ { id: 1, name: 'Erin' } ]

Running Queries Concurrently

This is where a pool earns its keep, multiple queries can run at the same time, each getting its own connection from the pool:

const names = ['Erin', 'Jordan', 'Maya', 'Sam', 'Casey'];
const inserts = names.map(name =>
  pool.query('INSERT INTO students (name, grade) VALUES ($1, $2) RETURNING id, name', [name, 9])
);
const results = await Promise.all(inserts);
console.log(results.map(r => r.rows[0]));

const countResult = await pool.query('SELECT COUNT(*) FROM students');
console.log(countResult.rows);

[ { id: 1, name: 'Erin' }, { id: 3, name: 'Jordan' }, { id: 2, name: 'Maya' }, { id: 5, name: 'Sam' }, { id: 4, name: 'Casey' } ]
[ { count: '5' } ]

Notice the id values aren’t strictly in insertion order, Jordan got id: 3 even though it’s second in the array. Since these five inserts genuinely ran concurrently, PostgreSQL assigned each row an ID in whatever order it actually processed them, not the order the array listed them in. This is exactly the kind of behavior a single Client, running one query at a time, would never show.

Checking Out a Single Client

Some operations, transactions in particular (covered when Prisma’s multi-step operations come up later), need every query to run on the exact same connection. For that, check out a client directly with pool.connect(), and release it when done:

const client = await pool.connect();
try {
  const result = await client.query('SELECT * FROM students WHERE name = $1', ['Maya']);
  console.log(result.rows);
} finally {
  client.release();
}

[ { id: 2, name: 'Maya', grade: 9 } ]

client.release() in a finally block matters, if it’s skipped (especially after an error), that connection never returns to the pool, and the pool can eventually run out.

Try It

1. Build a small script using Pool, and confirm pool.query() works exactly as shown.
2. Run five inserts concurrently with Promise.all(), as above, and observe whether the returned IDs land in insertion order on your own machine.
3. Check out a client with pool.connect(), run a query, and confirm you remember to call client.release().
4. Explain, in your own words, why an Express app with many simultaneous requests needs a Pool rather than a single Client.

Recap

• A Pool manages multiple connections, handing them out and returning them automatically for pool.query() calls.
• Pools let genuinely concurrent queries run at the same time, instead of queuing behind a single connection.
• pool.connect() checks out a single client for operations that need one connection throughout, always paired with client.release().

Next lesson: keeping database credentials out of source code with environment-based configuration.