Benchmark Connection Pooling in Node.js

Build and load test a PostgreSQL API to prove why connection pooling matters.

Introduction

30 Second Summary

Every time an app talks to its database, it opens a connection. Opening a brand new connection for every single request is like hanging up the phone and redialing after every sentence in a conversation. It works, but it is painfully slow.

In this project, you will build a Node.js API backed by PostgreSQL, test it with and without connection pooling, and see the dramatic performance difference for yourself using a load testing tool.

What You'll Build

A simple Express API that serves product data from PostgreSQL, benchmarked two ways: one with a naive "new connection per request" approach and one with a reusable connection pool.

By the end of this project, you'll have:

A working Express API with product endpoints that query PostgreSQL using both unpooled and pooled database access.
Benchmark results from autocannon showing the throughput and latency difference between both approaches.
A pool monitoring endpoint that exposes live connection pool stats so you can observe the pool in action during load tests.
Secret Mission: a health check endpoint with pool exhaustion detection and automated pool size experiments that find the sweet spot for your workload.

Are there any prerequisites?

You should have basic familiarity with JavaScript and REST APIs. You also need Node.js and PostgreSQL installed on your machine. The first step walks you through confirming both are ready.

Set Up the Project and Seed the Database

Every benchmark needs realistic data to be meaningful. Before you can test how your API handles load, you need a database filled with enough rows to make queries behave like they would in production.

Your Node.js project is already initialized with Express, pg, and autocannon installed. In this step, you'll create a PostgreSQL database, write a seed script that fills it with 100 sample products, and verify everything is ready.

In this step, get ready to:

Create a pooling_lab database in PostgreSQL.
Write a seed script that creates a products table and inserts 100 rows.
Run the seed script and verify the data is ready.

Create the pooling_lab database

Your API will read product data from a PostgreSQL database. You need to create this database before the seed script can connect to it.

Create the database by running this command in your terminal:

createdb -U postgres pooling_lab

What does this command do?

createdb is a PostgreSQL utility that creates a new empty database. The -U postgres flag runs the command as the postgres superuser.

If the command completes without any output, the database was created successfully.

Seeing a 'role does not exist' error?

Some PostgreSQL installations (especially via Homebrew on macOS) create a superuser matching your OS username instead of postgres. Try running the command without the -U postgres flag.

If you see database "pooling_lab" already exists, that means it was already created. You can safely move on.

Help me troubleshoot my PostgreSQL createdb command

Write the seed script

The seed script connects to your database, creates a products table, and fills it with 100 rows of sample data. This gives you a realistic dataset to benchmark against.

Create a new file called seed.js inside your pooling-lab folder.
Add the following imports and database configuration at the top of seed.js:

// Import the pg library for PostgreSQL access
import pg from 'pg';
const { Client } = pg;

// Configure the connection to our pooling_lab database
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'pooling_lab',
  user: 'postgres',
  password: 'postgres',
});

What does this code do?

import pg from 'pg' loads the node-postgres library. The destructured Client class creates a single direct connection to PostgreSQL.
The config object tells the client where to find your database: localhost:5432 is the default PostgreSQL address, and pooling_lab is the database you just created.

Adjust your PostgreSQL credentials

If your local PostgreSQL uses a different username or password, update the user and password fields to match your setup. These credentials appear in every server file you create in this project, so keep them consistent.

Below the client config in seed.js, add the seed function that creates the table and inserts data:

async function seed() {
  await client.connect();

  // Drop and recreate the products table
  await client.query(`
    DROP TABLE IF EXISTS products;
    CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      name TEXT NOT NULL,
      price NUMERIC(10, 2) NOT NULL,
      category TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);

  // Generate 100 products across 5 categories
  const categories = ['Electronics', 'Books', 'Clothing', 'Food', 'Tools'];
  const values = [];
  const params = [];
  let paramIndex = 1;

  for (let i = 1; i <= 100; i++) {
    const category = categories[i % categories.length];
    const price = (Math.random() * 100 + 1).toFixed(2);
    values.push(`($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2})`);
    params.push(`Product ${i}`, price, category);
    paramIndex += 3;
  }

  // Insert all products in a single batch query
  await client.query(
    `INSERT INTO products (name, price, category) VALUES ${values.join(', ')}`,
    params
  );

  // Verify the insert count
  const result = await client.query('SELECT COUNT(*) FROM products');
  console.log(`Seeded ${result.rows[0].count} products`);

  await client.end();
}

// Run the seed function and handle errors
seed().catch((err) => {
  console.error('Seed failed:', err);
  process.exit(1);
});

What does this code do?

DROP TABLE IF EXISTS products clears any old data so the script is safe to rerun. The CREATE TABLE statement defines five columns: an auto-incrementing id, a name, a price, a category, and a created_at timestamp.
The for loop generates 100 products with random prices, spread across five categories. It builds parameterized placeholders ($1, $2, $3) to prevent SQL injection.
After inserting, the script runs SELECT COUNT(*) to verify all 100 rows were inserted, then closes the connection with client.end().

Save seed.js.

✔️ Awesome, I've got everything!

Double check you've saved seed.js before moving on.

ⓧ I'd like to double check the full code

import pg from 'pg';
const { Client } = pg;

const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'pooling_lab',
  user: 'postgres',
  password: 'postgres',
});

async function seed() {
  await client.connect();

  await client.query(`
    DROP TABLE IF EXISTS products;
    CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      name TEXT NOT NULL,
      price NUMERIC(10, 2) NOT NULL,
      category TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);

  const categories = ['Electronics', 'Books', 'Clothing', 'Food', 'Tools'];
  const values = [];
  const params = [];
  let paramIndex = 1;

  for (let i = 1; i <= 100; i++) {
    const category = categories[i % categories.length];
    const price = (Math.random() * 100 + 1).toFixed(2);
    values.push(`($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2})`);
    params.push(`Product ${i}`, price, category);
    paramIndex += 3;
  }

  await client.query(
    `INSERT INTO products (name, price, category) VALUES ${values.join(', ')}`,
    params
  );

  const result = await client.query('SELECT COUNT(*) FROM products');
  console.log(`Seeded ${result.rows[0].count} products`);

  await client.end();
}

seed().catch((err) => {
  console.error('Seed failed:', err);
  process.exit(1);
});

Run the seed script and verify

Your package.json already has a seed script that runs node seed.js. Time to put it to work.

Run the seed script by running this command:

npm run seed

You should see Seeded 100 products printed in your terminal.

What just happened?

The script connected to pooling_lab, created the products table, inserted 100 rows with random prices across five categories, verified the count, and closed the connection.

You can safely rerun this script any time. The DROP TABLE IF EXISTS line wipes the old data first, so you always get a fresh set of 100 products.

Seeing 'Seed failed' or a connection error?

Make sure PostgreSQL is running. On macOS with Homebrew, run brew services list and check that postgresql shows as started.
If you see database "pooling_lab" does not exist, go back and run the createdb command from the previous substep.
If you see password authentication failed, update the user and password fields in seed.js to match your local PostgreSQL credentials.

Help me fix my seed script connection error

Your database is loaded with 100 products across five categories. Next up, you'll build an Express API that serves this data using a brand new database connection for every single request.

Build the API Without Connection Pooling

Your database is seeded with 100 products and ready to serve data. Now it's time to build the API that reads from it.

You'll use Express to handle HTTP requests and pg's Client to talk to PostgreSQL. This first version creates a brand new database connection for every single request. That means a full TCP handshake and authentication each time someone hits your API.

In this step, get ready to:

Create an Express server with a /products endpoint.
Add a /products/:id endpoint for fetching a single product.
Start the server and test both endpoints in your browser.

Create the Express server with a /products endpoint

Every time your API receives a request, it needs to talk to PostgreSQL. The simplest approach is to open a new connection, run the query, and close it immediately afterward.

Create a new file called server-no-pool.js in your pooling-lab folder and add the following code:

import express from 'express';
import pg from 'pg';
const { Client } = pg;

// Set up the Express app
const app = express();
const PORT = 3000;

// Database connection settings
const DB_CONFIG = {
  host: 'localhost',
  port: 5432,
  database: 'pooling_lab',
  user: 'postgres',
  password: 'postgres',
};

// GET /products - fetch the first 20 products
app.get('/products', async (req, res) => {
  // Create a brand new client for this single request
  const client = new Client(DB_CONFIG);
  try {
    await client.connect();
    const result = await client.query('SELECT * FROM products LIMIT 20');
    res.json(result.rows);
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  } finally {
    // Close the connection when the request is done
    await client.end();
  }
});

What does this code do?

DB_CONFIG stores the connection details for your local PostgreSQL instance. Every endpoint will use this config to open a new connection.

The /products handler creates a new Client, connects, runs the query, returns the results, and then disconnects inside finally. This connect-query-disconnect cycle happens on every single request.

You won't see a change yet. You'll start the server after adding the second endpoint.

Why is a new connection per request a problem?

Each new connection to PostgreSQL costs around 20-30ms for the TCP handshake and authentication alone. That overhead adds up fast when your server handles hundreds of concurrent requests.

You are intentionally building it this way first so you can measure the problem before solving it.

Add the /products/:id endpoint

The second endpoint fetches a single product by its ID. It follows the exact same pattern: new client, connect, query, disconnect.

In server-no-pool.js, find the closing }); of the /products endpoint, then copy the following code below it:

// GET /products/:id - fetch a single product by ID
app.get('/products/:id', async (req, res) => {
  // Another brand new client, just for this one request
  const client = new Client(DB_CONFIG);
  try {
    await client.connect();
    const result = await client.query(
      'SELECT * FROM products WHERE id = $1',
      [req.params.id]
    );
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Product not found' });
    } else {
      res.json(result.rows[0]);
    }
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  } finally {
    await client.end();
  }
});

// Start the server
app.listen(PORT, () => {
  console.log(`Server (NO POOL) running on http://localhost:${PORT}`);
});

What does this code do?

The /products/:id endpoint uses a parameterized query ($1) to safely look up a single product. If no product matches the ID, it returns a 404 error.

The app.listen call at the bottom starts the Express server on port 3000 and logs a confirmation message.

Save server-no-pool.js.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete server-no-pool.js file. Make sure yours matches exactly.

import express from 'express';
import pg from 'pg';
const { Client } = pg;

const app = express();
const PORT = 3000;

const DB_CONFIG = {
  host: 'localhost',
  port: 5432,
  database: 'pooling_lab',
  user: 'postgres',
  password: 'postgres',
};

app.get('/products', async (req, res) => {
  const client = new Client(DB_CONFIG);
  try {
    await client.connect();
    const result = await client.query('SELECT * FROM products LIMIT 20');
    res.json(result.rows);
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  } finally {
    await client.end();
  }
});

app.get('/products/:id', async (req, res) => {
  const client = new Client(DB_CONFIG);
  try {
    await client.connect();
    const result = await client.query(
      'SELECT * FROM products WHERE id = $1',
      [req.params.id]
    );
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Product not found' });
    } else {
      res.json(result.rows[0]);
    }
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  } finally {
    await client.end();
  }
});

app.listen(PORT, () => {
  console.log(`Server (NO POOL) running on http://localhost:${PORT}`);
});

Start the server and test the endpoints

Your package.json already has a start:no-pool script that runs server-no-pool.js. Time to start it up and see your API in action.

Start the server by running this command:

npm run start:no-pool

You should see this message in your terminal:

Server (NO POOL) running on http://localhost:3000

Open your browser and navigate to http://localhost:3000/products.

You should see a JSON array of 20 product objects, each with an id, name, price, category, and created_at field.

Now test the single-product endpoint by navigating to http://localhost:3000/products/1.

You should see a single JSON object for Product 1 with its name, price, and category.

Try navigating to http://localhost:3000/products/999 to confirm the 404 response works.

You should see {"error":"Product not found"} in the browser.

Not seeing any data?

Check that PostgreSQL is running. If you stopped it earlier, restart it with brew services run postgresql@18.
Make sure you seeded the database in Step 1. Run npm run seed again if you're not sure.
Verify your PostgreSQL username and password match the DB_CONFIG values in server-no-pool.js. The defaults are postgres / postgres.

My server started but I'm getting a database error in the browser. Help me debug.

Keep the server running

Leave this terminal window open with the server running. You'll need it running for the load tests in the next step. When you need to stop it later, press Ctrl+C.

Both endpoints work, but every request is paying the full cost of opening and closing a database connection. Next up, you'll throw 50 concurrent users at this server with autocannon and see just how expensive that pattern really is.

Load Test the Unpooled API

Your API works perfectly when you test it with a single browser request. Production servers don't have the luxury of one user at a time.

In this step, you'll simulate real traffic by firing 50 concurrent requests at your API for 10 seconds. These results become your baseline for comparison after you add connection pooling.

In this step, get ready to:

Run a load test against the /products endpoint with 50 concurrent connections.
Run a load test against the /products/:id endpoint with the same settings.
Record your baseline performance numbers for later comparison.

Load test the /products endpoint

Your server should still be running from the previous step. You'll need a second terminal window to run the load testing commands.

Server not running?

If you stopped your server, restart it by running npm run start:no-pool in your pooling-lab directory. You should see Server (NO POOL) running on http://localhost:3000 in the output.

Open a new terminal tab by pressing Cmd+T (macOS) or Ctrl+Shift+T (Windows).
Navigate to your project folder in the new tab by running:

cd ~/Desktop/pooling-lab

Right now, every request to your API creates a brand new PostgreSQL connection from scratch. That means a TCP handshake and database authentication for every single request.

What is load testing?

Load testing simulates many users hitting your server at the same time. It reveals how your application behaves under pressure, exposing bottlenecks that single-request testing can never catch.

Fire 50 concurrent connections at your /products endpoint for 10 seconds by running:

npx autocannon -c 50 -d 10 http://localhost:3000/products

What does this command do?

npx autocannon runs the autocannon load testing tool from your project's devDependencies.
-c 50 opens 50 concurrent HTTP connections to your server.
-d 10 keeps the test running for 10 seconds.
The URL at the end is the endpoint being tested.

The test takes about 10 seconds. When it finishes, you'll see a results table with rows for Latency, Req/Sec, and Bytes/Sec.

Not seeing any results?

Make sure your server is still running in the first terminal tab. You should see Server (NO POOL) running on http://localhost:3000 in that terminal's output.
Confirm you're in the pooling-lab directory by running pwd in your second terminal.
Help me troubleshoot why autocannon can't connect to my server on localhost:3000.

Load test the /products/:id endpoint

The /products endpoint returns 20 rows per request. Testing a single-row endpoint like /products/:id shows whether the connection overhead persists regardless of query size.

Run the same load test against a single product by running:

npx autocannon -c 50 -d 10 http://localhost:3000/products/1

You'll see another results table with the same format as before. The numbers may differ slightly from the first test.

Test completed but all requests failed?

Check that product ID 1 exists in your database. You seeded 100 products, so IDs 1 through 100 should all work.
If your server crashed between tests, restart it with npm run start:no-pool in the first terminal.
Help me figure out why all my autocannon requests to /products/1 are failing.

Record your baseline numbers

You now have two sets of benchmark results. These are your "unpooled" baseline numbers that you'll compare against the pooled version later.

What do the key metrics mean?

Req/Sec (Avg) is the average number of requests completed per second. This measures throughput. Higher is better.
Latency (Avg) is the average latency in milliseconds for a request to get a response. Lower is better.
Latency (99%) is the p99 latency. The slowest 1% of requests took at least this long. It reveals worst-case performance under load.
Errors is the number of failed requests. Any non-zero value means some requests could not complete.

Write down these four numbers from each test: Req/Sec average, Latency average, Latency 99%, and error count.
Keep these numbers handy. You'll compare them directly against the pooled results in a later step.

Seeing errors or high latency? That's the point.

Each of those 50 concurrent requests creates its own database connection from scratch. Every connection requires a TCP handshake and PostgreSQL authentication, costing 20-30ms of overhead per request.

Under sustained load, connections pile up and PostgreSQL starts to slow down or reject new ones. This is exactly the problem connection pooling solves.

You've captured the cost of creating a new database connection on every request. Next up, you'll refactor the API to reuse connections with a pool and run these same tests again.

Refactor to Use Connection Pooling

Your benchmark results from the last step paint a clear picture. Every request to the unpooled API forces a brand new database connection, which means a fresh TCP handshake and authentication round-trip before the query even starts.

Connection pooling eliminates that overhead by keeping a set of ready-to-use connections open. In this step, you'll create a new server file that uses pg's built-in Pool to reuse connections instead of creating them from scratch.

In this step, get ready to:

Create a new server file with a long-lived connection pool.
Refactor both API endpoints to use pool.query().
Add error handling and graceful shutdown for the pool.

Create the pooled server file

pg's Pool class manages a group of database connections behind the scenes. When your code needs to run a query, the pool checks out an available connection. When the query finishes, the connection goes back to the pool for the next request.

What is connection pooling?

Think of a connection pool like a group of phone lines at a call center. Instead of installing a new phone line every time someone calls (and ripping it out when they hang up), the center keeps a fixed set of lines ready. Callers get the next available line, use it, and the line goes back into rotation.

Without pooling, each request pays 20-30ms just for the connection handshake. With pooling, that cost drops to near zero because the connection is already open.

Create a new file called server.js in your pooling-lab folder, then paste this code:

import express from 'express';
import pg from 'pg';
const { Pool } = pg;

const app = express();
const PORT = 3000;

// Create a single pool instance that lives for the entire app lifecycle
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'pooling_lab',
  user: 'postgres',
  password: 'postgres',
  max: parseInt(process.env.POOL_MAX) || 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

What do these pool settings control?

max: 20 sets the maximum number of connections the pool keeps open at once. The default is 10, but 20 gives more headroom under load.
idleTimeoutMillis: 30000 closes connections that sit idle for 30 seconds. This frees resources on the database side.
connectionTimeoutMillis: 2000 makes a request fail fast (after 2 seconds) if no connection is available, instead of waiting forever.
parseInt(process.env.POOL_MAX) || 20 lets you override the pool size with an environment variable. You'll use this in the Secret Mission to experiment with different sizes.

Save server.js.

You won't see a change yet. The next chunk adds the route handlers that actually use this pool.

Why a single Pool instead of a Client per request?

The pg docs are explicit about this: the pool should be a long-lived object in your application. You create it once when the app starts and use the same instance for the entire lifetime of the process.

Your server-no-pool.js does the opposite. It creates and destroys a connection on every single request. The benchmark results you recorded show exactly what that costs.

Refactor both endpoints to use pool.query()

With the pool in place, your route handlers become much simpler. Instead of creating a new Client, connecting, querying, and disconnecting in a try/finally block, you call pool.query(). The pool handles checkout and release automatically.

In server.js, add the /products endpoint below the pool configuration:

// Return the first 20 products
app.get('/products', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM products LIMIT 20');
    res.json(result.rows);
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  }
});

What changed compared to the unpooled version?

Notice what's gone. There's no new Client(), no client.connect(), and no client.end() in a finally block.

pool.query() does all of that in one call. It checks out a connection from the pool, runs the query, and returns the connection automatically. Your code gets simpler and faster at the same time.

Save server.js.

You won't see a change yet. The next chunk adds the second endpoint.

Below the /products endpoint, add the /products/:id endpoint:

// Return a single product by ID
app.get('/products/:id', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT * FROM products WHERE id = $1',
      [req.params.id]
    );
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Product not found' });
    } else {
      res.json(result.rows[0]);
    }
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  }
});

What does this endpoint do?

This follows the exact same pattern as /products, with one addition. The parameterized query $1 safely passes the ID from the URL into the SQL query. If no row matches, the endpoint returns a 404 status.

Save server.js.

You won't see a change yet. The next chunk adds the server startup and error handling.

Add error handling and graceful shutdown

A long-lived pool needs two safety measures. First, it needs to catch errors from idle connections that fail unexpectedly. Second, it needs a clean shutdown path so connections are properly released when the server stops.

In server.js, find the closing }); of the pool configuration. Add this code directly below it, before the /products endpoint:

// Catch errors from idle connections to prevent app crashes
pool.on('error', (err) => {
  console.error('Unexpected idle client error:', err.message);
});

At the very bottom of server.js (below the /products/:id endpoint), add the server startup and shutdown handler:

// Start the server and display the pool size in the console
const server = app.listen(PORT, () => {
  console.log(`Server (POOLED, max=${pool.options.max}) running on http://localhost:${PORT}`);
});

// Gracefully drain pool connections when the server receives a shutdown signal
process.on('SIGTERM', async () => {
  console.log('SIGTERM received. Draining pool...');
  await pool.end();
  server.close(() => {
    console.log('Server shut down.');
    process.exit(0);
  });
});

What does the SIGTERM handler do?

pool.on('error') catches errors from connections sitting idle in the pool. Without this, a dropped idle connection would crash your entire Node.js process.
pool.end() drains all active connections, disconnects them, and shuts down the pool's internal timers. This is the clean way to close a pool.
server.close() stops the Express server from accepting new requests, then exits the process once existing requests finish.

Save server.js.
If the unpooled server is still running from the previous step, stop it by pressing Ctrl+C in the terminal.
Start the pooled server by running:

npm start

You should see this message in your terminal:

Server (POOLED, max=20) running on http://localhost:3000

Open your browser and go to http://localhost:3000/products.

You should see the same JSON array of 20 products you saw with the unpooled server. The output looks identical, but the way connections are handled behind the scenes is completely different.

Test the single product endpoint by going to http://localhost:3000/products/1.

You should see a single product object with an id, name, price, category, and created_at fields.

Server won't start or endpoints return errors?

If you see EADDRINUSE, the unpooled server is still running on port 3000. Press Ctrl+C in the terminal where it's running, then try npm start again.
If you see Connection refused or ECONNREFUSED, make sure PostgreSQL is still running on your machine.
Check that the pool configuration in server.js uses const { Pool } = pg (capital P), not Client.

My pooled server won't start or returns database errors. Help me debug.

✔️ Awesome, I've got everything!

Great. Double check you've saved server.js and keep the server running for the next step.

ⓧ I'd like to double check the full code

Here is the complete server.js file. Compare it with yours and fix any differences.

import express from 'express';
import pg from 'pg';
const { Pool } = pg;

const app = express();
const PORT = 3000;

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'pooling_lab',
  user: 'postgres',
  password: 'postgres',
  max: parseInt(process.env.POOL_MAX) || 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

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

app.get('/products', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM products LIMIT 20');
    res.json(result.rows);
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  }
});

app.get('/products/:id', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT * FROM products WHERE id = $1',
      [req.params.id]
    );
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Product not found' });
    } else {
      res.json(result.rows[0]);
    }
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  }
});

const server = app.listen(PORT, () => {
  console.log(`Server (POOLED, max=${pool.options.max}) running on http://localhost:${PORT}`);
});

process.on('SIGTERM', async () => {
  console.log('SIGTERM received. Draining pool...');
  await pool.end();
  server.close(() => {
    console.log('Server shut down.');
    process.exit(0);
  });
});

Your pooled server is live and serving the same data as before. The real question is: how much faster is it under pressure? Next up, you'll run the same load tests and compare the numbers side by side.

Load Test the Pooled API and Compare

In the last step, you replaced the per-request client with a shared connection pool. The code is cleaner, but the real question is whether it performs better under pressure.

You'll run the exact same autocannon benchmarks against the pooled server and compare the numbers side by side. Then you'll add a monitoring endpoint that lets you peek inside the pool's internal state in real time.

In this step, get ready to:

Run autocannon load tests against the pooled API.
Compare pooled vs unpooled benchmark results.
Add a /pool-stats monitoring endpoint.

Run autocannon against the pooled API

Your pooled server should still be running from the previous step. Time to throw the same 50-connection load test at it and see what connection reuse does for performance.

If your server isn't running, start it by running this command:

npm start

Open a second terminal window.
Run autocannon against the /products endpoint with 50 concurrent connections for 10 seconds by running this command:

npx autocannon -c 50 -d 10 http://localhost:3000/products

Wait for the test to complete. You should see a results table with requests per second, latency percentiles, and throughput.

Write down four numbers from the output: the requests/sec average, the average latency, the p99 latency, and the error count.
Now run the same test against /products/1 by running this command:

npx autocannon -c 50 -d 10 http://localhost:3000/products/1

Record the same four metrics for this endpoint too.

Seeing connection errors or timeouts?

Make sure your server is running in the other terminal. You should see Server (POOLED, max=20) running on http://localhost:3000 in that terminal's output.

If PostgreSQL is not running, autocannon will report errors on every request. Confirm PostgreSQL is up by running psql -U postgres -c 'SELECT 1' in a separate terminal.

Help me troubleshoot my autocannon load test against the pooled server.

Compare your pooled vs unpooled results

Pull up the numbers you recorded for the unpooled server in Step 3. Place them next to the pooled results you just collected.

Compare these four metrics between the unpooled and pooled runs:

Requests per second (higher is better).
Average latency (lower is better).
p99 latency (the slowest 1% of requests).
Error count (zero is the goal).

What should I expect to see?

The pooled server should handle significantly more requests per second. Average and p99 latency should drop dramatically.

The unpooled server may have shown connection errors under load because PostgreSQL has a default limit of 100 connections. The pooled server reuses just 20 connections, so it stays well under that limit.

Your exact numbers depend on your machine, but the pattern is always the same. Pooling wins by a wide margin.

Why is the pooled version so much faster?

Without pooling, every single request pays the cost of a full TCP handshake plus PostgreSQL authentication. That overhead adds roughly 20-30ms per connection.

With pooling, connections are created once and reused across requests. The pool.query() method checks out an existing connection, runs the query, and returns it to the pool. That reuse drops per-query overhead to a fraction of a millisecond.

Add a pool monitoring endpoint

You've seen the speed difference, but what's actually happening inside the pool during a load test? The pg Pool instance exposes three properties that tell you its internal state at any moment.

In server.js, find the /products/:id route handler.
Add this new route below it, before the const server = app.listen(...) line:

app.get('/pool-stats', (req, res) => {
  // Return live pool metrics as JSON
  res.json({
    totalCount: pool.totalCount,     // all connections created by the pool
    idleCount: pool.idleCount,       // connections not currently in use
    waitingCount: pool.waitingCount, // queries waiting for a free connection
  });
});

What do these pool stats mean?

totalCount is the number of connections the pool has created so far. It grows up to the max setting (20 in your config).

idleCount is how many of those connections are sitting idle, ready to be checked out for a query.

waitingCount is the number of queries waiting in line because all connections are busy. A high number here signals pool exhaustion.

Save server.js.
Switch to the terminal where your server is running.
Stop the server by pressing Ctrl+C.
Restart the server by running this command:

npm start

Open your browser and visit http://localhost:3000/pool-stats.

You should see a JSON response with totalCount, idleCount, and waitingCount values.

Watch the pool in action

Try running an autocannon test in your second terminal while refreshing /pool-stats in your browser.

You'll see totalCount climb toward 20 and waitingCount spike during the load test. Once the test finishes, idleCount rises as connections return to the pool.

Not seeing the /pool-stats endpoint?

Make sure you stopped the old server with Ctrl+C and restarted it with npm start. Node.js does not pick up file changes automatically.

Check that the new route is placed before the app.listen() call in server.js. Routes registered after the server starts listening still work, but placement errors can cause confusion.

Help me debug my /pool-stats endpoint.

✔️ Awesome, I've got everything!

Great. Double check you've saved server.js and your server is running with the new endpoint.

ⓧ I'd like to double check the full code

Here is the complete server.js file with all routes including the new /pool-stats endpoint.

import express from 'express';
import pg from 'pg';
const { Pool } = pg;

const app = express();
const PORT = 3000;

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'pooling_lab',
  user: 'postgres',
  password: 'postgres',
  max: parseInt(process.env.POOL_MAX) || 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

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

app.get('/products', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM products LIMIT 20');
    res.json(result.rows);
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  }
});

app.get('/products/:id', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT * FROM products WHERE id = $1',
      [req.params.id]
    );
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Product not found' });
    } else {
      res.json(result.rows[0]);
    }
  } catch (err) {
    console.error('Query error:', err.message);
    res.status(500).json({ error: 'Database error' });
  }
});

app.get('/pool-stats', (req, res) => {
  res.json({
    totalCount: pool.totalCount,
    idleCount: pool.idleCount,
    waitingCount: pool.waitingCount,
  });
});

const server = app.listen(PORT, () => {
  console.log(`Server (POOLED, max=${pool.options.max}) running on http://localhost:${PORT}`);
});

process.on('SIGTERM', async () => {
  console.log('SIGTERM received. Draining pool...');
  await pool.end();
  server.close(() => {
    console.log('Server shut down.');
    process.exit(0);
  });
});

Secret mission

Pool Tuning Lab

You've seen the difference between pooled and unpooled connections. But how do you know 20 is the right pool size? In this mission, add a health check endpoint that detects pool exhaustion, then run automated experiments across multiple pool sizes to find the sweet spot.

Before you go

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

PostgreSQL database pooling_lab with a products table (100 seeded rows).
Node.js project folder with package.json, seed.js, server-no-pool.js, and server.js.
Installed node_modules dependencies (express, pg, autocannon).
Local Express server process (if still running).

Keep everything running

No action needed. Choose this if you're still actively experimenting with pool sizes or want to revisit the benchmarks later.

Your PostgreSQL database and project files stay as they are.
If your Express server is still running in the terminal, you can leave it or stop it with Ctrl+C. You can restart it any time.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database stay intact so you can pick up where you left off.

Stop the Express server by pressing Ctrl+C in the terminal where it is running.
Optionally stop the PostgreSQL service to free up memory. Run this command:

brew services stop postgresql@18

When you come back, start PostgreSQL again with brew services run postgresql@18 and run your server.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes the database and all project files permanently.

Stop the Express server by pressing Ctrl+C in the terminal.
Drop the PostgreSQL database by running:

dropdb pooling_lab

Delete the project folder from your Desktop by running:

rm -rf ~/Desktop/pooling-lab

That's it. The database, project files, and dependencies are all removed.

Mission accomplished

That's a Wrap!

Nice work! You just proved why connection pooling exists by building both sides of the story and measuring the difference yourself.

You've learned how to:

Built an Express API with PostgreSQL using both unpooled and pooled database access patterns.
Load tested both approaches with autocannon and measured the dramatic difference in requests/sec, latency, and errors.
Added a pool monitoring endpoint to observe live connection lifecycle stats like totalCount, idleCount, and waitingCount in real time.
Secret Mission: Built a health check endpoint with pool exhaustion detection and ran automated pool size experiments to find the sweet spot for your workload.

Ready to quiz yourself?