Database Performance with PostgreSQL

Optimize a 100k-row product catalog API and deploy it to AWS RDS.

Introduction

30 Second Summary

Every app hits a moment where the database becomes the bottleneck. A query that returned instantly during development suddenly crawls when real data shows up.

In this project, you will build a product catalog API backed by PostgreSQL and seed it with 100,000 products. You will diagnose why your queries are slow using EXPLAIN ANALYZE, fix them with targeted indexes, and provision a production-grade database on AWS RDS.

What You'll Build

You will fire a request at your API and watch it return filtered, sorted products from 100,000 rows in under 10ms, powered by an AWS RDS instance you provisioned and secured yourself.

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

  • A running product catalog API where you query 100,000 products by category and price range, with response times dropping from 200ms+ to under 10ms after optimization.
  • Hands-on experience reading EXPLAIN ANALYZE output to identify sequential scans, index scans, and bitmap heap scans in real query plans.
  • A live AWS RDS PostgreSQL instance you provisioned using Standard Create, making every infrastructure decision from VPC placement to security group rules to parameter group tuning.
  • Secret Mission: add full-text search using GIN indexes on JSONB product attributes.

Are there any prerequisites?

This project assumes you have Node.js, PostgreSQL 18, the AWS CLI configured with an AWS account, and Kiro installed on your machine.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up the Project and Seed the Database

To expose real performance problems, you need a realistic dataset. A toy database with 10 rows will never reveal a bottleneck because PostgreSQL is smart enough to scan 10 rows instantly.

In this step, you'll set up a Node.js project, create a local PostgreSQL database, and seed it with 100,000 product records that have varied categories, prices, and JSONB attributes.

In this step, get ready to:
  • Initialize a Node.js project and install dependencies.
  • Create a local PostgreSQL database and a connection module.
  • Seed 100,000 product records and verify they loaded.
Initialize the project and install dependencies
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir product-catalog-api
cd product-catalog-api
  • Confirm the folder was created by running:
ls

The folder is empty for now. You should see no output.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Kiro and press Enter to open it.
  • In Kiro, click File in the top menu bar.
  • Click Open Folder.
  • Select the product-catalog-api folder on your Desktop, then click Open.
  • Create a new file called package.json in the product-catalog-api folder and paste the following code into it:
{
  "name": "product-catalog-api",
  "version": "1.0.0",
  "description": "Product catalog API for database performance engineering",
  "main": "server.js",
  "scripts": {
    "start": "node server.js",
    "seed": "node seed.js"
  },
  "dependencies": {
    "express": "5.2.1",
    "pg": "8.23.0"
  }
}

What does this file do?

The package.json file defines your project and its dependencies. It pins Express at 5.2.1 for the web server and pg at 8.23.0 for connecting to PostgreSQL.

The scripts section adds two shortcuts: npm start to run the server and npm run seed to populate the database.

  • Save package.json.
  • Install the dependencies by running this command in your terminal:
npm install
  • Confirm the install succeeded by running:
ls

You should see node_modules, package.json, and package-lock.json in the output.

Seeing permission errors or EACCES?

Make sure your terminal is inside the product-catalog-api folder. Run pwd to check your current directory.

If you see a different folder, run cd ~/Desktop/product-catalog-api and try npm install again.

Help me fix npm install errors in my product-catalog-api project.

✔️ Awesome, I've got everything!

Great. Double check you've saved package.json and move on to the next substep.

ⓧ I'd like to double check the full code

{
  "name": "product-catalog-api",
  "version": "1.0.0",
  "description": "Product catalog API for database performance engineering",
  "main": "server.js",
  "scripts": {
    "start": "node server.js",
    "seed": "node seed.js"
  },
  "dependencies": {
    "express": "5.2.1",
    "pg": "8.23.0"
  }
}
Create the database and connection module

Your project needs a PostgreSQL database to store products. You also need a connection module that your scripts and server can share.

  • Create the local database by running this command:
createdb productcatalog
  • Verify the database exists by running:
psql -d productcatalog -c "SELECT current_database();"

You should see productcatalog printed in the output. This confirms PostgreSQL accepted the connection.

Command not found or connection refused?

If createdb or psql is not found, your PostgreSQL bin directory may not be in your system PATH. Check that PostgreSQL 18 is installed and its bin folder is accessible.

If you see a connection refused error, make sure your local PostgreSQL server is running.

Help me connect to my local PostgreSQL installation.

  • In Kiro, create a new file called db.js in the product-catalog-api folder and paste the following code into it:
const { Pool } = require('pg');

// Configure the connection pool with environment variable overrides
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'productcatalog',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || '',
  max: 20,                       // Maximum clients in the pool
  idleTimeoutMillis: 30000,      // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000, // Fail if connection takes over 2 seconds
});

module.exports = pool;

What does this code do?

This module creates a connection pool using the pg library. A pool keeps multiple database connections open and reuses them across requests, which is far faster than opening a new connection for every query.

Each setting falls back to a default value (localhost, port 5432, database productcatalog) but can be overridden with environment variables. You'll use that override in Step 5 when you connect to a cloud database.

PostgreSQL requires a password?

If your local PostgreSQL installation uses password authentication, set the DB_PASSWORD environment variable before running any scripts. On macOS or Linux, run export DB_PASSWORD=yourpassword in your terminal. On Windows, run set DB_PASSWORD=yourpassword.

Similarly, if your PostgreSQL user is not postgres, set DB_USER to your actual username.

  • Save db.js.
  • Test the database connection by running this command:
node -e "const pool = require('./db'); pool.query('SELECT NOW()').then(r => { console.log('Connected:', r.rows[0].now); pool.end(); })"

You should see Connected: followed by the current timestamp. This proves your Node.js code can talk to your PostgreSQL database.

Seeing a connection error?

If you see a password authentication error, set the DB_PASSWORD environment variable as described in the tip above, then run the command again.

If you see a MODULE_NOT_FOUND error for pg, make sure you ran npm install successfully and your terminal is inside the product-catalog-api folder.

Help me fix my database connection error.

✔️ Awesome, I've got everything!

Great. Double check you've saved db.js and move on to creating the seed script.

ⓧ I'd like to double check the full code

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

const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'productcatalog',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || '',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

module.exports = pool;

Now you need a script that fills the database with 100,000 realistic product records. Each product will have a category, a random price, and a JSONB column storing attributes like color, brand, and material.

  • In Kiro, create a new file called seed.js in the product-catalog-api folder and paste the following code into it:
const pool = require('./db');

// Sample data arrays for generating realistic products
const categories = ['Electronics', 'Clothing', 'Home', 'Sports', 'Books', 'Toys', 'Food', 'Beauty', 'Automotive', 'Garden'];
const colors = ['red', 'blue', 'green', 'black', 'white', 'silver', 'gold', 'pink'];
const brands = ['Acme', 'Globex', 'Initech', 'Umbrella', 'Stark', 'Wayne', 'Wonka', 'Aperture'];
const materials = ['plastic', 'metal', 'wood', 'fabric', 'glass', 'ceramic', 'leather', 'rubber'];

// Pick a random element from an array
function randomItem(arr) {
  return arr[Math.floor(Math.random() * arr.length)];
}

// Generate a random price between $1 and $1000
function randomPrice() {
  return (Math.random() * 999 + 1).toFixed(2);
}

// Build a JSONB attributes object with random values
function randomAttributes() {
  return JSON.stringify({
    color: randomItem(colors),
    brand: randomItem(brands),
    material: randomItem(materials),
    weight_kg: (Math.random() * 10 + 0.1).toFixed(1),
  });
}

What does this code do?

This first section sets up the raw data and helper functions the seed script will use. The categories array gives each product one of 10 possible categories, which is what you'll filter and index later.

The randomAttributes() function builds a JSON object with color, brand, material, and weight. This becomes the JSONB column you'll search with a GIN index in the Secret Mission.

  • Still in seed.js, add the seed function below the helper functions. This creates the table and sets up the batch insert loop:
async function seed() {
  // Drop and recreate the products table
  await pool.query(`
    DROP TABLE IF EXISTS products;
    CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      category VARCHAR(100) NOT NULL,
      price NUMERIC(10, 2) NOT NULL,
      description TEXT,
      attributes JSONB,
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);

  // Insert products in batches of 1,000 for efficiency
  const batchSize = 1000;
  const totalProducts = 100000;

  for (let i = 0; i < totalProducts; i += batchSize) {
    const values = [];
    const params = [];
    for (let j = 0; j < batchSize; j++) {
      const idx = i + j;
      const offset = j * 5;
      values.push(`($${offset + 1}, $${offset + 2}, $${offset + 3}, $${offset + 4}, $${offset + 5})`);
      params.push(
        `Product ${idx + 1}`,
        randomItem(categories),
        randomPrice(),
        `Description for product ${idx + 1}`,
        randomAttributes()
      );
    }

What does this code do?

The CREATE TABLE statement defines seven columns including an attributes column typed as JSONB. JSONB stores structured data inside a single column, which is common for product metadata that varies across categories.

The loop builds batches of 1,000 parameterized inserts. Inserting 100,000 rows one at a time would be painfully slow. Batching keeps the total seeding time under a minute.

  • Confirm your project files are in place by running:
ls

You should see db.js, node_modules, package.json, package-lock.json, and seed.js listed.

  • Back in seed.js, add the remaining code directly below the closing } of the inner for loop. This executes each batch insert, closes the pool, and handles errors:
    // Execute the batch insert query
    await pool.query(
      `INSERT INTO products (name, category, price, description, attributes) VALUES ${values.join(', ')}`,
      params
    );
    console.log(`Seeded ${i + batchSize} products`);
  }

  console.log('Seeding complete!');
  await pool.end();
}

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

What does this code do?

The pool.query() call sends each batch of 1,000 rows to PostgreSQL using parameterized values. Parameterized queries prevent SQL injection and let PostgreSQL reuse its query plan.

The seed().catch() at the bottom invokes the function and catches any errors. If something goes wrong, it logs the error and exits with a non-zero code so you know it failed.

  • Save seed.js.

✔️ Awesome, I've got everything!

Great. Double check you've saved seed.js and move on to running the seed script.

ⓧ I'd like to double check the full code

Your seed.js should contain three sections in this order. Scroll up and confirm each is present:

  • The require('./db') import, four data arrays (categories, colors, brands, materials), and three helper functions (randomItem, randomPrice, randomAttributes).
  • The async function seed() opening, the CREATE TABLE query, and the outer for loop that builds values and params arrays.
  • The batch INSERT query execution inside the loop, pool.end(), and the seed().catch() invocation at the bottom.
Run the seed script and verify the data

With the script ready, it's time to fill the database. The seeding process will insert 100 batches of 1,000 products each, logging progress as it goes.

  • Run the seed script by running this command:
node seed.js

You'll see progress lines printed as each batch completes. The final line should read Seeding complete! after about 30 to 60 seconds.

Seeding failed with a connection error?

Make sure the productcatalog database exists by re-running createdb productcatalog. If it already exists, you'll see a message saying so, which is fine.

If you set DB_PASSWORD earlier in a different terminal session, you need to set it again in your current terminal before running the seed script.

Help me fix my seed script error.

Before you verify the count, what do you think the query will return? You seeded 100 batches of 1,000 rows each.

  • Verify the data loaded by running this command:
psql -d productcatalog -c "SELECT count(*) FROM products;"

You should see 100000 as the count. That confirms all 100,000 product records are in your database and ready to query.

Your database is loaded with 100,000 products. Next up, you'll build the API that queries this data and start measuring exactly how slow those queries are.

Build the Product Catalog API

Your database is loaded with 100,000 products. Now you need a way to query them and measure how long each query takes.

Without measurable endpoints, you would be guessing about what is slow instead of proving it. In this step, you will build an Express API that logs every query's execution time to the console so you can see the problem with your own eyes.

In this step, get ready to:
  • Create an Express server with response time logging middleware.
  • Build a product listing endpoint with filtering, sorting, and pagination.
  • Test the API and observe slow query times on 100,000 rows.
Set up the server with response time logging

Every request to your API needs a stopwatch. Express middleware lets you wrap every request with timing logic so you can see exactly how long each response takes.

  • Create a new file called server.js in your product-catalog-api folder (the same folder that contains db.js and seed.js).
  • Paste the following code into server.js to set up the Express app, logging middleware, and server listener:
const express = require('express');
const pool = require('./db');

const app = express();
const PORT = process.env.PORT || 3000;

// Log the total response time for every incoming request
app.use((req, res, next) => {
  const start = Date.now();
  res.on('finish', () => {
    const duration = Date.now() - start;
    console.log(`${req.method} ${req.originalUrl} - ${duration}ms`);
  });
  next();
});

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

What does this code do?

The app.use() block registers middleware that runs on every request. It records the current time when the request arrives, then listens for the finish event to calculate how many milliseconds the entire response took.

The pool import reuses the connection pool you set up in db.js during Step 1. This keeps your database connection logic in one place.

  • Save server.js.
  • Start the server by running this command in your terminal:
node server.js

You should see Server running on http://localhost:3000 printed in the terminal. The server is running, but it has no routes yet.

  • Press Ctrl+C in the terminal to stop the server so you can edit the file.

Seeing an error about the port or module not found?

  • If you see Cannot find module './db', make sure server.js is in the same folder as db.js.
  • If the port is already in use, you may have another process on port 3000. Stop it or set a different port with PORT=3001 node server.js.
  • Still stuck? Help me debug why my Express server won't start.
Add the product listing route with filters

Your API needs a route that queries the products table and lets callers filter by category and price range. You will build the SQL query dynamically based on which query parameters the caller provides.

  • In server.js, find the app.listen block at the bottom of the file.
  • Add the following route handler directly above app.listen. This is the first half of the route. The function continues in the next code block:
app.get('/api/products', async (req, res) => {
  // Extract all query parameters from the URL
  const { category, minPrice, maxPrice, sortBy, order, limit, offset } = req.query;

  // Start building the SQL query dynamically
  let query = 'SELECT * FROM products WHERE 1=1';
  const params = [];
  let paramIndex = 1;

  // Add a WHERE clause for each provided filter
  if (category) {
    query += ` AND category = $${paramIndex++}`;
    params.push(category);
  }
  if (minPrice) {
    query += ` AND price >= $${paramIndex++}`;
    params.push(minPrice);
  }
  if (maxPrice) {
    query += ` AND price <= $${paramIndex++}`;
    params.push(maxPrice);
  }

What does this code do?

The WHERE 1=1 trick gives you a base query that is always true. Every filter you add can start with AND without worrying about whether it is the first condition or not.

The $${paramIndex++} syntax creates parameterized queries. PostgreSQL replaces each $1, $2 placeholder with the corresponding value from the params array. This protects against SQL injection.

  • Directly below the code you just pasted (still inside the same route handler function), add the sorting, pagination, and query execution logic:
  // Validate sort column to prevent SQL injection
  const validSortColumns = ['price', 'name', 'created_at'];
  if (sortBy && validSortColumns.includes(sortBy)) {
    const sortOrder = order === 'desc' ? 'DESC' : 'ASC';
    query += ` ORDER BY ${sortBy} ${sortOrder}`;
  } else {
    query += ' ORDER BY price ASC';
  }

  // Add pagination with defaults of 20 results starting at 0
  query += ` LIMIT $${paramIndex++}`;
  params.push(parseInt(limit) || 20);

  query += ` OFFSET $${paramIndex++}`;
  params.push(parseInt(offset) || 0);

  // Execute the query and measure how long it takes
  const queryStart = Date.now();
  const result = await pool.query(query, params);
  const queryDuration = Date.now() - queryStart;

  // Log query performance to the console
  console.log(`  DB query: ${queryDuration}ms, rows: ${result.rowCount}`);

  res.json({
    products: result.rows,
    count: result.rowCount,
    queryTimeMs: queryDuration,
  });
});

What does this code do?

The validSortColumns array is a whitelist. Only column names in this list are allowed in the ORDER BY clause. This prevents an attacker from injecting arbitrary SQL through the sort parameter.

The queryStart and queryDuration variables measure only the database query time, separate from the total request time logged by the middleware. This lets you see exactly how much time is spent in PostgreSQL versus your application code.

  • Save server.js.

✔️ Awesome, I've got everything!

Great. Make sure you have saved server.js before moving on.

ⓧ I'd like to double check the full code

Your server.js should contain all of these sections, in this order. Scroll up and confirm each one is present:

  • The require imports for express and ./db, followed by app and PORT setup.
  • The app.use() response time logging middleware.
  • The app.get('/api/products', ...) route handler with filter building, sort validation, pagination, query execution, and the res.json() response.
  • The app.listen() call at the very bottom of the file.

Seeing a syntax error when you save?

  • Make sure the two code blocks are pasted inside the same route handler. The closing }); of the second block closes the app.get() call that opens in the first block.
  • Check that the route handler is placed above app.listen(), not below it.
  • Help me fix a syntax error in my server.js file.
Start the server and test the endpoint

Your API is ready to serve product queries. Time to start it up and see what happens when PostgreSQL has to search through 100,000 rows with no indexes.

  • Start the server by running:
node server.js

You should see Server running on http://localhost:3000 in your terminal. The server is now listening for requests.

Before you test the endpoint, think about this: your products table has 100,000 rows and no indexes beyond the primary key. How fast do you think a filtered query will be?

  • Open a new terminal tab or window (keep the server running in the first one).
  • Test the product listing endpoint by running:
curl "http://localhost:3000/api/products?category=Electronics&minPrice=50&maxPrice=500"

You will see a JSON response with products in your terminal. Now switch back to the terminal where your server is running and look at the log output.

The console shows two log lines. The DB query line shows the raw database execution time, which should be somewhere between 150-300ms. That is how long PostgreSQL takes to scan all 100,000 rows looking for matches.

Why is it so slow?

Without any indexes, PostgreSQL performs a sequential scan. It reads every single row in the table, checks whether each one matches your filters, then sorts the results. For 100,000 rows, that means 100,000 comparisons even though only a few dozen match.

In the next step, you will use EXPLAIN ANALYZE to see exactly what PostgreSQL is doing behind the scenes.

Not seeing any products or getting a connection error?

  • Make sure your local PostgreSQL server is running. If you stopped it since Step 1, restart it before hitting the endpoint.
  • Verify the database name in db.js matches the database you created. It should be productcatalog.
  • If you see an empty products array, confirm the seed script ran successfully in Step 1.
  • Help me debug why my product catalog API returns no data.

Your API works, but those query times are painful. In a production app, 200ms per request would mean frustrated users and overloaded servers. Next up, you will use EXPLAIN ANALYZE to crack open the query planner and see exactly where all that time is going.

Diagnose Slow Queries with EXPLAIN ANALYZE

Your product catalog API is live and returning results. The problem is that every query takes 150-300ms.

Before reaching for fixes, you need evidence. PostgreSQL has a built-in diagnostic tool called EXPLAIN ANALYZE that shows you exactly what the database engine does when it runs a query. It reveals whether PostgreSQL is scanning every row, how it sorts results, and how long each operation takes.

In this step, get ready to:
  • Run EXPLAIN ANALYZE on a filter query and identify the Seq Scan node.
  • Compare query plans with and without sorting to see how PostgreSQL handles ORDER BY.
  • Document baseline execution times before any optimization.
Run EXPLAIN ANALYZE on the filter query

Your API builds SQL queries dynamically based on query parameters. To diagnose performance, you need to take one of those queries and ask PostgreSQL to show its execution plan.

  • Your server is still running in your current terminal. Open a new terminal window by pressing Cmd+T (macOS) or Ctrl+Shift+T (Windows).
  • Connect to your productcatalog database by running:
psql -d productcatalog

What is EXPLAIN ANALYZE?

Prefixing any SQL query with EXPLAIN ANALYZE tells PostgreSQL to actually execute the query and report back on how it did it. You get the execution plan (what operations it chose), real row counts, and the actual time each operation took.

Your API's /api/products endpoint filters by category and price range. Here is the equivalent SQL.

Before you run this, think about what PostgreSQL has to do to find Electronics products priced between 50 and 500. Does it jump straight to the matching rows, or does it have to check every single one of the 100,000?

  • Run this query in your psql session:
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 50 AND 500;

You should see a query plan with a Seq Scan node. That confirms it: PostgreSQL is reading every single row in the table, checking each one against your filters.

How to read the EXPLAIN ANALYZE output

The output is a tree of operations PostgreSQL performed. Look for these key pieces:

  • Seq Scan on products means PostgreSQL scanned every row in the table. There is no shortcut to the rows it needs.
  • rows= shows both the planner's estimate and the actual rows returned. Comparing these tells you how accurate the planner's statistics are.
  • actual time= shows two numbers: the startup time (time to return the first row) and the total time (time to return all rows), in milliseconds.
  • Execution Time at the bottom is the total wall-clock time for the entire query.

Not seeing any output?

  • Make sure you are connected to the correct database. Run \conninfo in psql to verify it says productcatalog.
  • Check that your query ends with a semicolon. psql waits for more input until it sees one.

Ask for help: My EXPLAIN ANALYZE query is not returning any output in psql.

Add sorting and compare query plans

Your API always sorts results by price and returns a page of 20. That means PostgreSQL has to do more than just filter. It also has to sort all the matching rows, then throw away everything except the first 20.

What do you think will change in the query plan when you add ORDER BY price LIMIT 20? Will it get faster since you are returning fewer rows, or slower because sorting costs something extra?

  • Run this updated query in psql:
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 50 AND 500 ORDER BY price LIMIT 20;

Look at the plan now. You should see a Sort node sitting on top of the Seq Scan. PostgreSQL still scans every row, finds all the matches, sorts all of them by price, and only then picks the top 20.

Why is sorting so expensive here?

The LIMIT 20 at the end might look cheap. But PostgreSQL cannot return the 20 cheapest products without first finding every matching product and sorting them all. With roughly 10,000 Electronics products in the table, it is sorting thousands of rows just to hand you 20.

The Sort node in the plan also shows the sort method. If you see Sort Method: quicksort, the sort happened in memory. If the dataset were larger, PostgreSQL might spill to disk, making it even slower.

Not seeing a Sort node?

  • Make sure your query includes ORDER BY price LIMIT 20. Without these, PostgreSQL skips the sort.
  • Check the indentation of the plan output. The Sort node appears above the Seq Scan, closer to the left edge. The Seq Scan is indented underneath it.

Ask for help: Help me understand my EXPLAIN ANALYZE output for a sorted query.

Document your baseline execution times

You now have concrete evidence of what PostgreSQL is doing. Before you optimize anything, record the baseline so you can measure the improvement later.

  • Look at the Execution Time line at the bottom of each EXPLAIN ANALYZE output. Note it down for both queries.
  • Note the plan type for each query. Both should show Seq Scan. The second query should also show a Sort node.

Let's confirm your baseline one more time. What execution time do you expect to see for the sorted, filtered query?

  • Re-run the full query to confirm:
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 50 AND 500 ORDER BY price LIMIT 20;

The Execution Time at the bottom should be over 50ms. The plan shows a Seq Scan feeding into a Sort node. That is your baseline.

What does this baseline tell you?

Two bottlenecks are visible in the plan. First, the Seq Scan means PostgreSQL reads every row in the table to find matches. Second, the Sort node means it sorts all matching rows before picking the top 20.

Both problems have the same root cause: PostgreSQL has no fast path to the data it needs. In the next step, you will create indexes that give it that fast path.

You now have hard evidence that every query hits a full table scan and an in-memory sort. Next up, you will create targeted indexes that eliminate both bottlenecks and drop query times from 50ms+ to under 1ms.

Fix Performance with Targeted Indexes

In the previous step, you used EXPLAIN ANALYZE to see exactly what PostgreSQL is doing under the hood. The evidence is clear: sequential scans across 100,000 rows, a Sort node stacking on top, and execution times over 50ms.

Now you can apply targeted indexes that directly address each bottleneck. Indexes are not magic, though. The wrong index wastes disk space and slows down writes without helping reads. In this step, you will create two indexes, measure each one with EXPLAIN ANALYZE, and prove that the right index drops your API response time from 200ms+ to under 10ms.

In this step, get ready to:
  • Create a B-tree index on the category column and measure the query plan change.
  • Create a composite index on (category, price) to eliminate the Sort node entirely.
  • Confirm the API endpoint responds in under 10ms.
Create a B-tree index on the category column

A B-tree index is PostgreSQL's default index type. It organizes column values in a sorted tree structure so the database can jump straight to matching rows instead of scanning every single one.

  • Your server is still running in its terminal. In a separate terminal, connect to your database by running:
psql -d productcatalog
  • Create a B-tree index on the category column by running:
CREATE INDEX idx_products_category ON products (category);

You should see CREATE INDEX printed as confirmation.

What does this index do?

Without an index, PostgreSQL reads every row in the table to find products in a specific category. That is the Seq Scan you saw in the previous step.

The B-tree index on category lets PostgreSQL jump directly to the rows that match your category filter. It handles equality checks (category = 'Electronics') efficiently by traversing the tree instead of scanning the whole table.

Before you run the next command, think about what you expect the EXPLAIN ANALYZE output to look like now. Will the Seq Scan be gone? Will the Sort node still be there?

  • Re-run the same EXPLAIN ANALYZE query from the previous step to see how the plan changed:
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 50 AND 500 ORDER BY price LIMIT 20;

Look at the output. The Seq Scan node is gone, replaced by an Index Scan or Bitmap Index Scan using idx_products_category. The execution time has dropped significantly from your 50ms+ baseline.

Notice, however, that a Sort node is still present in the plan. PostgreSQL uses the index to find the right category, but it still has to sort those matching rows by price.

Why is the Sort node still there?

The single-column index on category only knows how to find Electronics products quickly. It has no information about price, so PostgreSQL still has to collect all matching rows and sort them by price before returning the top 20.

That Sort node is doing real work. With roughly 10,000 Electronics products, PostgreSQL sorts all of them just to return 20 rows.

Still seeing a Seq Scan?

  • Make sure the CREATE INDEX command printed CREATE INDEX as confirmation. If you see an error, check for typos in the table or column name.
  • Verify the index exists by running \di in psql. You should see idx_products_category listed.
  • PostgreSQL may still choose a Seq Scan if the category value matches a very large percentage of rows. Try a less common category in your query to confirm the index works.

Why isn't my B-tree index being used in the EXPLAIN ANALYZE output?

Create a composite index on category and price

The single-column index solved the filter problem but left the sort problem untouched. A composite index on both columns lets PostgreSQL filter and sort in a single index traversal.

  • Create a composite B-tree index on (category, price) by running:
CREATE INDEX idx_products_category_price ON products (category, price);

You should see CREATE INDEX printed again.

What does a composite index do differently?

A composite index on (category, price) stores entries sorted first by category, then by price within each category. When PostgreSQL looks up category = 'Electronics', the matching rows are already in price order.

This means PostgreSQL can satisfy the WHERE filter, the BETWEEN range check, the ORDER BY, and the LIMIT all from a single index walk. No separate Sort node needed.

Before you run the query again, predict what will change. Do you think the Sort node will still be in the plan? What execution time do you expect?

  • Run the same EXPLAIN ANALYZE query again to see the new plan:
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 50 AND 500 ORDER BY price LIMIT 20;

The Sort node is gone. You should see an Index Scan using idx_products_category_price with no separate Sort node in the plan. The execution time should now be under 1ms.

Why keep the single-column index?

The composite index on (category, price) is strictly better for queries that filter by category and sort by price. But the single-column index on category is still useful for queries that filter by category alone without involving price at all.

In practice, you would evaluate your query patterns and decide whether the single-column index pulls its weight. Every index adds overhead to inserts and updates because PostgreSQL has to maintain the index data alongside the table data.

Sort node still showing up?

  • Confirm the composite index exists by running \di in psql. You should see both idx_products_category and idx_products_category_price listed.
  • Check that the column order in your index is (category, price), not (price, category). Column order matters in composite indexes. The leading column must match the equality filter.

Why is PostgreSQL still sorting after I created a composite index?

Confirm the API responds in under 10ms

The psql benchmarks prove the indexes work at the database level. Now let's verify that the performance improvement carries through to your API.

Before you hit the endpoint, think about what the console will show. Your server logs both the total request time and the raw database query time. Do you think both will drop, or just one?

  • In a separate terminal from your running server and psql session, hit the API endpoint by running:
curl "http://localhost:3000/api/products?category=Electronics&minPrice=50&maxPrice=500"
  • Switch to the terminal running your server and check the console output.

The console should show a DB query time under 10ms. Compare that to the 150-300ms you saw before the indexes existed. The JSON response also includes a queryTimeMs field that confirms the improvement.

Why is the API time higher than the psql time?

In psql, the query ran in under 1ms. Through the API, the time is a few milliseconds higher because it includes connection pool overhead, Node.js event loop processing, and JSON serialization of the result rows. Under 10ms total is excellent for a 100,000-row table.

Still seeing high query times?

  • Make sure your server is connected to the same productcatalog database where you created the indexes. Check the connection settings in db.js.
  • If you restarted the server, the first request may be slower because the connection pool is warming up. Hit the endpoint a second time and check the query time on the second request.
  • If the curl command fails with a connection error, make sure your server is still running. Check the terminal where you started node server.js.

My API response time is still high even after creating indexes. What could be wrong?

Your queries went from scanning 100,000 rows to walking an index in under a millisecond. That is the power of evidence-driven optimization. Next up, you will take this locally-tuned database to the cloud by provisioning a production-grade PostgreSQL instance on AWS RDS.

Provision PostgreSQL on AWS RDS

Your API now responds in under 10ms thanks to those targeted indexes. But there's a catch: your database lives on your laptop. The moment you close the lid, every user loses access.

In production, databases run on dedicated cloud infrastructure that stays online 24/7. In this step, you'll provision a PostgreSQL instance on Amazon RDS using Standard Create, making every configuration decision yourself: engine version, instance size, storage type, VPC placement, security group rules, and parameter tuning.

In this step, get ready to:
  • Create a custom parameter group and provision an RDS PostgreSQL 17 instance with Standard Create.
  • Configure VPC networking with a security group and DB subnet group.
  • Connect your API to the cloud database and verify performance.
Create a custom parameter group and configure the instance

RDS uses parameter groups to control PostgreSQL's configuration settings. Default parameter groups are read-only, so you need to create a custom one to modify anything.

  • In the AWS Management Console, type RDS in the top search bar and select RDS.
  • In the left sidebar, click Parameter groups.
  • Click Create parameter group.
  • Set Parameter group family to postgres17.
  • Set Group name to product-catalog-params.
  • Enter a description like Custom parameters for product catalog project.
  • Click Create.

You should see product-catalog-params in your parameter groups list.

Next, you need to modify one parameter so your Node.js app can connect without SSL certificate configuration.

  • Click the parameter group name product-catalog-params to open it.
  • Click Edit.
  • In the filter/search bar, type rds.force_ssl.
  • Change the value of rds.force_ssl from 1 to 0.
  • Click Save changes.

You should see a confirmation that your parameter group was updated.

Why disable forced SSL for this project?

PostgreSQL 17 on RDS defaults to rds.force_ssl = 1, which rejects any connection that doesn't use SSL. The pg Node.js library doesn't auto-negotiate SSL, so connections would fail unless you add SSL certificates to your code.

For this learning project, disabling forced SSL keeps the connection simple. In production, you'd keep rds.force_ssl = 1 and configure your application with proper SSL certificates.

What are static vs dynamic parameters?

Static parameters require an instance reboot to take effect. Dynamic parameters apply immediately without downtime.

Since you're setting rds.force_ssl before creating the instance, the instance starts with the value already applied. You won't need a reboot.

Now you'll create the RDS instance itself using Standard Create.

  • In the left sidebar, click Databases.
  • Click Create database.
  • Select Standard create.

Why Standard Create instead of Easy Create?

Easy Create hides VPC, security group, parameter group, and storage choices behind defaults. Standard Create forces you to make every infrastructure decision yourself. That hands-on decision-making is exactly what employers look for in a backend engineer.

  • Under Engine options, select PostgreSQL.
  • For Engine version, select the latest 17.x version available.
  • Under Templates, select Free tier.
  • Under Settings, set DB instance identifier to product-catalog-db.
  • Leave Master username as postgres.
  • Enter a Master password you'll remember and confirm it.
  • Under Instance configuration, confirm that db.t3.micro is selected (the Free tier template sets this automatically).
  • Under Storage, confirm Storage type is General Purpose SSD (gp2) and Allocated storage is 20 GiB.

What is gp2 storage?

RDS offers multiple storage types. General Purpose SSD (gp2) balances price and performance for most workloads. Provisioned IOPS SSD (io1/io2) delivers higher throughput for write-heavy databases but costs significantly more.

For a learning project, 20 GB of gp2 is covered by the Free Tier and provides more than enough performance.

Don't click Create database yet. You still need to configure networking in the next substep.

Set up VPC networking and launch the instance

Your RDS instance needs to live inside a VPC (Virtual Private Cloud) with a security group that controls who can connect. Before you finish the RDS form, you'll create a dedicated security group that only allows your machine to reach port 5432.

  • Open a new browser tab and navigate to the EC2 console (search for EC2 in the top search bar).
  • In the left sidebar under Network & Security, click Security Groups.
  • Click Create security group.
  • Set Security group name to rds-product-catalog-sg.
  • Set Description to Allow PostgreSQL access for product catalog project.
  • Make sure the VPC dropdown shows your default VPC.
  • Under Inbound rules, click Add rule.
  • Set Type to PostgreSQL (this auto-fills port 5432).
  • Set Source to My IP. This auto-fills your current public IP address so only your machine can connect.
  • Click Create security group.

You should see your new security group rds-product-catalog-sg in the list with one inbound rule for port 5432.

Don't see your security group?

Make sure you selected the correct VPC (your default VPC) and that the inbound rule type is set to PostgreSQL, not a custom TCP rule. If your IP address changes later (switching Wi-Fi networks, for example), you'll need to update the inbound rule with your new IP.

Need help? Help me troubleshoot my security group setup for RDS.

Now switch back to the RDS Standard Create form in your other browser tab.

  • Scroll down to the Connectivity section.
  • For Virtual private cloud (VPC), select your default VPC.
  • For DB subnet group, select default. RDS creates a default DB subnet group spanning multiple Availability Zones automatically when you use the default VPC.
  • Set Public access to Yes.
  • Under VPC security group, select Choose existing.
  • Remove any pre-selected security groups, then select rds-product-catalog-sg.

Why set Public Access to Yes?

For this learning project, Public Access lets your local machine connect to the RDS instance over the internet. Your security group still restricts access to only your IP address, so it's not open to the world.

In production, you'd set Public Access to No and place your application inside the same VPC as the database. The application would connect over the VPC's private network, and the database would have no public IP at all.

  • Scroll down and expand Additional configuration.
  • Under Database options, set Initial database name to productcatalog.
  • Under DB parameter group, select product-catalog-params.
  • Leave all other settings at their defaults.
  • Click Create database.

RDS takes 5-15 minutes to provision the instance. You'll see the status change from Creating to Available.

Use the wait time wisely

While the instance provisions, RDS is launching the underlying EC2 instance, attaching the gp2 storage volume, configuring networking, and starting the PostgreSQL 17 engine with your parameter group settings. Refresh the page every couple of minutes to check the status.

  • Wait until the Status column shows Available next to product-catalog-db.
  • Click on product-catalog-db to open its detail page.
  • Under Connectivity & security, find the Endpoint value. Copy this endpoint. You'll need it to connect your API.

Instance stuck in Creating?

RDS provisioning can take up to 20 minutes in some regions. If the status doesn't change after 20 minutes, check the Events section on the instance detail page for error messages. Common causes include insufficient subnet coverage in your DB subnet group or a parameter group family mismatch.

Still stuck? Help me troubleshoot my RDS instance creation.

Connect your API to the cloud database

Your db.js file already reads connection settings from environment variables with local fallback defaults. To point your app at RDS, you just need to set those environment variables when running the server and seed script.

Why environment variables instead of editing db.js?

Hardcoding a database host into your source code means you'd need to change the file every time you switch between local and cloud. Environment variables let you swap the connection target without touching any code. This is the standard approach in production deployments.

First, stop the Express server that's currently running against your local database.

  • In the terminal where your server is running, press Ctrl+C to stop it.

Now seed the RDS database with 100,000 products by running the seed script with your RDS connection details.

macOS

  • Run the seed script against your RDS database by running this command:
DB_HOST=[[RDS_ENDPOINT="your-rds-endpoint.region.rds.amazonaws.com"]] DB_USER=postgres DB_PASSWORD=your-rds-password DB_NAME=productcatalog node seed.js

What do these environment variables do?

  • DB_HOST points to your RDS endpoint instead of localhost.
  • DB_USER and DB_PASSWORD use the master credentials you set during RDS creation.
  • DB_NAME connects to the productcatalog database you specified as the initial database name.

Windows

  • Run the seed script against your RDS database by running these commands in PowerShell:
$env:DB_HOST="[[RDS_ENDPOINT="your-rds-endpoint.region.rds.amazonaws.com"]]"
$env:DB_USER="postgres"
$env:DB_PASSWORD="your-rds-password"
$env:DB_NAME="productcatalog"
node seed.js

What do these environment variables do?

  • DB_HOST points to your RDS endpoint instead of localhost.
  • DB_USER and DB_PASSWORD use the master credentials you set during RDS creation.
  • DB_NAME connects to the productcatalog database you specified as the initial database name.

The seed script takes a few minutes to insert 100,000 rows over the network. You should see Seeded 1000 products, Seeded 2000 products, and so on until Seeding complete!.

Connection refused or timeout?

Check that your RDS endpoint is copied correctly with no extra spaces. Verify that your security group inbound rule uses your current public IP address. If you switched Wi-Fi networks since creating the security group, your IP has changed. Update the inbound rule in the EC2 console with your new IP.

Also confirm that the RDS instance status is Available, not Creating or Modifying.

Help me debug my RDS connection.

Your RDS database now has 100,000 products but no indexes. The seed script created the table from scratch, so you need to recreate the indexes you built in Step 4.

  • Connect to your RDS database with psql by running this command:
psql -h [[RDS_ENDPOINT="your-rds-endpoint.region.rds.amazonaws.com"]] -U postgres -d productcatalog
  • Enter your master password when prompted.
  • Create both indexes by running these SQL commands:
CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_products_category_price ON products (category, price);
  • Verify the data loaded correctly by running:
SELECT count(*) FROM products;

You should see 100000 as the result.

  • Exit psql by typing \q and pressing Enter.

Now for the moment of truth. Before you start the server, predict what you think will happen. Will the query times be the same sub-10ms you saw locally, or will they be different?

macOS

  • Start the server pointing at your RDS database by running:
DB_HOST=[[RDS_ENDPOINT="your-rds-endpoint.region.rds.amazonaws.com"]] DB_USER=postgres DB_PASSWORD=your-rds-password DB_NAME=productcatalog node server.js

Windows

  • Start the server pointing at your RDS database by running these commands in PowerShell:
$env:DB_HOST="[[RDS_ENDPOINT="your-rds-endpoint.region.rds.amazonaws.com"]]"
$env:DB_USER="postgres"
$env:DB_PASSWORD="your-rds-password"
$env:DB_NAME="productcatalog"
node server.js

You should see Server running on http://localhost:3000 in the terminal.

  • Open your browser and go to:
http://localhost:3000/api/products?category=Electronics&minPrice=50&maxPrice=500

Your browser shows a JSON response with products from your cloud database. Check your terminal output.

The console shows query times under 50ms. That's higher than the sub-1ms you saw locally, and that's expected. The extra time is network latency: your query travels from your machine to the RDS instance in an AWS data center and back. The database engine itself is still fast thanks to the indexes you just created.

Why are cloud query times higher than local?

Locally, your query travels from Node.js to PostgreSQL on the same machine, which takes microseconds. Over the network, each query incurs a round trip to the AWS region hosting your RDS instance.

In production, your application server and database would both live in the same VPC, often in the same Availability Zone. That cuts the round trip to under 1ms.

Seeing query times over 200ms?

If your query times are much higher than 50ms, your indexes may not have been created successfully on RDS. Reconnect with psql and run \di to list indexes. You should see idx_products_category and idx_products_category_price in the output.

Help me troubleshoot my RDS query performance.

Your API is now powered by a cloud database you provisioned and secured yourself. You chose the engine, sized the instance, locked down the network, and proved the indexes carry over. Ready for a bonus challenge?

Secret mission

Full-Text Search with GIN Indexes on JSONB

Products have a JSONB attributes column with nested key-value pairs like color, brand, and material. Can you build a search endpoint that queries these attributes and make it fast with the right index type? B-tree indexes cannot help with nested, unstructured data. This mission introduces GIN indexes, the interview-grade knowledge that separates you from most candidates.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project created an AWS RDS instance that may incur charges depending on your account's Free Tier status.

Cost warning

Your RDS PostgreSQL instance (db.t3.micro with 20 GB gp2 storage) is covered by the AWS Free Tier for eligible accounts. For accounts created before July 15, 2025, this includes 750 hours/month of db.t3.micro for 12 months plus 20 GB of gp2 storage. For accounts created after that date, RDS offers $100 in credits at sign-up plus up to $100 additional.

If you have exceeded your Free Tier period, this instance costs approximately $0.017/hour (~$12.50/month). Storage costs approximately $0.115/GB-month ($2.30/month for 20 GB). If you are not continuing to the next project, choose Pause or Delete to stop charges.

Resources you used:

  • AWS RDS PostgreSQL instance (db.t3.micro, 20 GB gp2 storage).
  • Custom DB parameter group.
  • DB subnet group.
  • VPC security group (inbound rule for TCP port 5432).
  • Local PostgreSQL database productcatalog.
  • Local project files (db.js, seed.js, server.js, package.json, and node_modules).

Keep everything running

No action needed. Choose this if you are still actively building or plan to continue to the next project in the series.

  • Your RDS instance continues running within Free Tier limits for eligible accounts.
  • Your local database and project files remain on your machine.
  • Your Node.js server is not running unless you start it manually.

Pause - I'll come back to this later

Stop your RDS instance to pause compute charges while keeping your data and configuration intact.

  • Open the RDS console at https://console.aws.amazon.com/rds/.
  • In the navigation pane, choose Databases, then select your DB instance.
  • Choose Actions, then choose Stop temporarily.
  • Acknowledge the auto-restart warning, then choose Stop temporarily.

What happens when you stop the instance?

RDS automatically restarts your instance after 7 consecutive days to apply required maintenance updates. Storage charges still apply while the instance is stopped.

If you need to keep it stopped longer than 7 days, you will need to stop it again after each automatic restart.

Delete - I don't want to use this again

Remove all project resources completely. This stops all charges and deletes your data permanently.

Start by deleting the RDS instance:

  • Open the RDS console at https://console.aws.amazon.com/rds/.
  • In the navigation pane, choose Databases, then select your DB instance.
  • Choose Actions, then choose Delete.
  • Clear Create final snapshot? and Retain automated backups if you do not need the data.
  • Type delete me in the confirmation box.
  • Choose Delete.

Next, delete the custom parameter group:

  • In the RDS console navigation pane, choose Parameter groups.
  • Select the custom parameter group you created for this project.
  • Choose Actions, then choose Delete.
  • Confirm the deletion.

Delete the DB subnet group:

  • In the RDS console navigation pane, choose Subnet groups.
  • Select the subnet group you created for this project.
  • Choose Delete, then confirm.

Delete the VPC security group:

  • Open the VPC console at https://console.aws.amazon.com/vpc/.
  • In the navigation pane, choose Security groups.
  • Select the security group you created for this project (the one with the inbound rule for TCP port 5432).
  • Choose Actions, then choose Delete security groups.
  • Confirm the deletion.

Drop the local PostgreSQL database by running:

psql -d postgres -c "DROP DATABASE productcatalog;"

Finally, delete the local project files by navigating to your Desktop and removing the project folder.

macOS

  • Run the following commands in your terminal:
cd ~/Desktop
rm -rf product-catalog-api

Windows

  • Run the following commands in PowerShell:
cd $HOME\Desktop
Remove-Item -Recurse -Force product-catalog-api

Nice Work!

Nice Work!

Nice work! You just built a product catalog API backed by PostgreSQL, diagnosed why it was slow, fixed it with targeted indexes, and deployed the whole thing to a cloud database on AWS RDS.

You've learned how to:

  • Use EXPLAIN ANALYZE to diagnose sequential scans and sort overhead on a 100,000-row dataset, then apply B-tree and composite indexes to drop query times from 200ms+ down to under 10ms.
  • Provision an AWS RDS PostgreSQL instance using Standard Create, making deliberate decisions about VPC, security groups, DB subnet groups, parameter groups, and storage types.
  • Connect a local Express API to a cloud database and verify query performance over the network.
  • Secret Mission: Built a JSONB search endpoint with a GIN index using jsonb_path_ops and observed BitmapAnd query plans combining B-tree and GIN indexes.

Ready to quiz yourself?