Diagnose and Fix a Slow PostgreSQL Database

Use EXPLAIN ANALYZE and indexes to find and fix real PostgreSQL performance problems.

Introduction

30 Second Summary

Every application eventually hits the moment where a page takes forever to load or a report grinds the server to a halt. Knowing how to find and fix these database bottlenecks is what separates a database administrator from someone who just writes queries.

In this project, you will install PostgreSQL 18 on a Linux machine and load it with over a million rows of realistic e-commerce data. Then you will use the same diagnostic tools that professional DBAs rely on every day to find and fix real performance problems.

What You'll Build

You will re-run a query that just crawled through a million rows, see it finish in a fraction of the time after your fix, and know exactly which diagnostic tool showed you the problem.

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

  • Run a slow query, used EXPLAIN ANALYZE to see exactly why it was slow, added a targeted index, and watched the same query complete in milliseconds.
  • Read a query plan and identified whether the database chose a fast index scan or a slow sequential scan.
  • Monitored your database's overall health using built-in statistics views that show which tables need maintenance and which queries are running right now.
  • Secret Mission: an optional challenge to push your database monitoring skills even further.

Are there any prerequisites?

This project assumes you know basic SQL (SELECT, JOIN, GROUP BY, WHERE). You also need a Linux machine with sudo access running Ubuntu/Debian or RHEL/Rocky/AlmaLinux.

Before We Start

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

Install PostgreSQL 18 and Connect

You need a running PostgreSQL server before you can diagnose or fix anything. Every task in this project depends on having a live database to work with.

In this step, you will install PostgreSQL 18 from the official PGDG (PostgreSQL Global Development Group) repository. This gives you the latest stable version (18.6) with proper package management instead of an outdated snapshot from your distro's default repository.

In this step, get ready to:
  • Install PostgreSQL 18 from the official PGDG repository.
  • Initialize the database cluster and start the PostgreSQL service.
  • Connect to the server via psql and verify the version.
Add the PGDG repository and install PostgreSQL 18

Your Linux distribution ships a default PostgreSQL version, but it is usually older than what you need. The PGDG repository provides every supported major version and handles updates automatically.

First, check whether PostgreSQL is already installed on your machine.

  • Check for an existing PostgreSQL installation by running:
psql --version

✔️ I see PostgreSQL 18

PostgreSQL 18 is already installed. Skip ahead to the "Connect to PostgreSQL and verify the version" substep below.

ⓧ I see an older version

The PGDG repository installs each major version into its own directory, so PostgreSQL 18 will install alongside your existing version without overwriting it.

Continue with the install instructions below.

ⓧ Command not found

PostgreSQL is not installed yet. Continue with the install instructions below.

The install commands differ between Ubuntu/Debian and RHEL/Rocky/AlmaLinux. Pick the tab that matches your system.

Ubuntu / Debian

  • Set up the PGDG Apt repository and install PostgreSQL 18 by running these commands one at a time:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

What does this script do?

This script automatically adds the official PGDG Apt repository to your system. It will print a summary and prompt you to Press Enter to continue, or Ctrl-C to abort. Press Enter to confirm.

  • Once the repository is set up, update your package list and install PostgreSQL 18 by running:
sudo apt update
sudo apt install -y postgresql-18

What happens after install on Ubuntu?

On Ubuntu, the postgresql-18 package automatically initializes the database cluster and starts the PostgreSQL service. No extra steps needed.

  • Confirm the service is running by checking its status:
sudo systemctl status postgresql

You should see active (exited) or active (running) in the output. This confirms PostgreSQL is up and ready.

Service not active?

  • If you see inactive or failed, try starting the service manually with sudo systemctl start postgresql and check the status again.
  • Make sure you ran the apt.postgresql.org.sh script before installing. Without it, apt may pull an older version from the default repository.

Still stuck? Help me troubleshoot why my PostgreSQL service is not starting on Ubuntu after installing from the PGDG repository.

RHEL / Rocky / AlmaLinux

  • Install the PGDG Yum repository RPM by running:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Running a different RHEL version?

The command above targets EL-9 on x86_64. If you are on EL-8, change EL-9 to EL-8 in the URL. If you are on an ARM system, change x86_64 to aarch64.

  • Disable the built-in PostgreSQL module so it does not conflict with the PGDG packages:
sudo dnf -qy module disable postgresql
  • Install the PostgreSQL 18 server package by running:
sudo dnf -y install postgresql18-server

Why disable the built-in module?

RHEL-family distributions include a built-in postgresql module that can conflict with the PGDG repository packages. Disabling it ensures dnf resolves all PostgreSQL packages from the PGDG repository.

On RHEL-family distributions, PostgreSQL does not auto-initialize or auto-start after installation. You need to do this manually.

  • Initialize the database cluster, enable the service to start on boot, and start it now by running these commands:
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable postgresql-18
sudo systemctl start postgresql-18

What do these commands do?

  • initdb creates the initial database files in /var/lib/pgsql/18/data. This only runs once.
  • systemctl enable tells the system to start PostgreSQL automatically on every reboot.
  • systemctl start starts the service right now.
  • Confirm the service is running by checking its status:
sudo systemctl status postgresql-18

You should see active (running) in the output. This confirms PostgreSQL is up and ready.

Service not active?

  • If initdb failed, double-check that you ran the command with sudo and that the data directory /var/lib/pgsql/18/data does not already contain files from a previous attempt.
  • If the service fails to start, check the logs with sudo journalctl -u postgresql-18 for specific error messages.
  • Make sure you disabled the built-in postgresql module before installing. Package conflicts can cause silent failures.

Still stuck? Help me troubleshoot why my PostgreSQL 18 service is not starting on RHEL/Rocky/AlmaLinux after installing from the PGDG Yum repository.

Connect to PostgreSQL and verify the version

PostgreSQL creates a system user called postgres during installation. This user is the default superuser and uses peer authentication, which means it only accepts connections from the matching Linux user account.

Before you run the next command, what version number do you expect to see in the output?

  • Verify your PostgreSQL server version by running:
sudo -u postgres psql -c "SELECT version();"

What does this command do?

  • sudo -u postgres runs the following command as the postgres Linux user.
  • psql is the interactive terminal for PostgreSQL.
  • -c "SELECT version();" sends a single SQL command and exits. This is a quick way to run one query without opening an interactive session.

The output shows a line starting with PostgreSQL 18 followed by build details. That confirms your server is running the correct version.

Seeing a permission error?

  • Make sure you included sudo -u postgres at the start of the command. Running psql as your own user fails because peer authentication expects the postgres system user.
  • If psql is not found, the PostgreSQL client binary might not be in your PATH. On RHEL, try the full path: /usr/pgsql-18/bin/psql.

Still stuck? Help me connect to PostgreSQL 18 as the postgres superuser on Linux. I am getting a permission or authentication error.

Your PostgreSQL 18 server is installed and running. Next up, you will build a realistic test database with over a million rows of generated data.

Build a Realistic Test Database

Your PostgreSQL 18 server is up and running. Now you need a database with enough data to expose real performance problems.

Small tables fit in a single disk page and always scan fast. That hides the exact issues you need to learn to diagnose. In this step, you will build an e-commerce database with over 1.6 million rows, which forces PostgreSQL to make meaningful choices about how to access data.

In this step, get ready to:
  • Create the dba_lab database and build three e-commerce tables with foreign key relationships.
  • Populate the tables with over 1.6 million rows of realistic test data using generate_series.
  • Run a JOIN query to confirm the data loaded correctly and see initial query performance.
Create the dba_lab database and e-commerce tables

The dba_lab database will hold all the test data you will diagnose and optimize throughout this project. You will create three tables that simulate a simple e-commerce system: customers, orders, and order items.

  • Create the dba_lab database and connect to it by running these commands in your psql session:
-- Create a new database for our DBA lab
CREATE DATABASE dba_lab;
  • Connect to the new database by running:
\c dba_lab

You should see a message that says You are now connected to database "dba_lab" as user "postgres".

Seeing "database already exists"?

If you already created dba_lab in a previous attempt, drop it first with DROP DATABASE dba_lab; and then re-run the CREATE DATABASE command.

help me fix a CREATE DATABASE error in psql

  • Create the three e-commerce tables by running this SQL:
-- Create tables for simulated e-commerce system
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_name VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL
);

What do these tables represent?

These three tables model a simplified e-commerce system. customers stores user profiles. orders tracks each purchase. order_items holds the individual products within each order.

The REFERENCES keyword creates foreign key constraints. orders.customer_id points back to customers, and order_items.order_id points back to orders. This ensures every order belongs to a real customer and every item belongs to a real order.

After each statement, psql prints CREATE TABLE to confirm the table was created. You should see this message three times.

Seeing a "relation already exists" error?

This means the tables already exist in your dba_lab database. Drop them in reverse order (because of foreign key dependencies) by running DROP TABLE order_items, orders, customers; and then re-run the CREATE TABLE statements above.

help me fix my CREATE TABLE errors

Populate the tables with test data

Empty tables do not expose performance problems. You need hundreds of thousands of rows to force PostgreSQL into making real decisions about how to access data.

What is generate_series?

generate_series is a PostgreSQL function that produces a sequence of numbers (like 1, 2, 3, ... 100000). Combined with INSERT INTO ... SELECT, it lets you generate thousands of rows in a single statement. Each number in the sequence becomes one row in the target table.

  • Generate 100,000 customer records by running this SQL:
-- Populate customers (100,000 rows)
INSERT INTO customers (first_name, last_name, email, created_at)
SELECT
    'Customer' || gs,
    'Last' || (gs % 500),
    'customer' || gs || '@example.com',
    timestamp '2022-01-01' + (random() * interval '730 days')
FROM generate_series(1, 100000) AS gs;

What does this insert do?

generate_series(1, 100000) produces 100,000 numbers. For each number, the SELECT builds a row with a unique first name, a rotating last name (500 variations via gs % 500), a unique email, and a random creation date spread across two years.

You should see INSERT 0 100000 confirming all 100,000 rows were inserted.

  • Generate 500,000 orders and 1,000,000 order items by running this SQL:
-- Populate orders (500,000 rows)
INSERT INTO orders (customer_id, order_date, status, total_amount)
SELECT
    (random() * 99999 + 1)::integer,
    timestamp '2024-01-01' + (random() * interval '730 days'),
    (ARRAY['pending', 'shipped', 'delivered', 'cancelled'])[floor(random() * 4 + 1)::integer],
    (random() * 500 + 5)::numeric(10, 2)
FROM generate_series(1, 500000) AS gs;

What does this insert do?

Each row picks a random customer_id between 1 and 100,000 (matching your existing customers), a random date spanning two years, a random status from four options, and a random total between $5 and $505.

You should see INSERT 0 500000. This insert takes a few seconds because PostgreSQL is generating half a million rows with random values.

  • Generate 1,000,000 order item records by running:
-- Populate order_items (1,000,000 rows)
INSERT INTO order_items (order_id, product_name, quantity, unit_price)
SELECT
    (random() * 499999 + 1)::integer,
    'Product-' || (gs % 200),
    floor(random() * 10 + 1)::integer,
    (random() * 100 + 1)::numeric(10, 2)
FROM generate_series(1, 1000000) AS gs;

What does this insert do?

Each row picks a random order_id between 1 and 500,000, one of 200 product names, a random quantity, and a random unit price. This is your largest table at one million rows.

You should see INSERT 0 1000000. This is the longest insert and may take several seconds.

Seeing a foreign key violation error?

The random() function can occasionally produce a value outside the valid range due to rounding. If this happens, re-run the failed INSERT statement. The random values will be different on the second run and the insert will almost certainly succeed.

help me fix a foreign key violation during data generation

Confirm the data is loaded

Before you move on, you need to confirm that all three tables have the expected row counts. This also gives you your first look at how long queries take against tables of this size.

  • Verify the row counts across all three tables by running:
-- Verify row counts
SELECT 'customers' AS table_name, count(*) FROM customers
UNION ALL
SELECT 'orders', count(*) FROM orders
UNION ALL
SELECT 'order_items', count(*) FROM order_items;

What should I see?

You should see three rows: customers with 100,000, orders with 500,000, and order_items with 1,000,000. If any count is off, re-run the corresponding INSERT statement from the previous substep.

Now try a query that actually uses this data. Before you run the next query, take a guess: how long do you think it will take to join half a million orders to 100,000 customers?

  • Enable timing and run a JOIN query to see initial performance by running:
-- Enable timing to see how long queries take
\timing on

SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
LIMIT 10;

\timing off

What does the timing tell you?

The \timing on command makes psql print the wall-clock time after every query. Even though this query returns only 10 rows, PostgreSQL still has to scan through the orders table to find them. Note the execution time. You will compare it against much faster results once you add indexes in Step 4.

✔️ Awesome, I've got everything!

Great. You have 1.6 million rows of test data loaded across three related tables. No indexes exist beyond the primary keys, which is exactly what you need for the next step.

ⓧ I'd like to double check all the SQL

Scroll up and confirm you have run each of these SQL blocks in order:

  • CREATE DATABASE dba_lab; and \c dba_lab to create and connect to the database.
  • Three CREATE TABLE statements for customers, orders, and order_items.
  • Three INSERT INTO ... SELECT ... FROM generate_series statements populating 100,000 customers, 500,000 orders, and 1,000,000 order items.
  • The UNION ALL row-count verification query showing 100,000, 500,000, and 1,000,000.

You have a realistic database with 1.6 million rows and no indexes beyond primary keys. Next up, you will run a slow query, crack open its query plan with EXPLAIN ANALYZE, and see exactly why it crawls.

Diagnose a Slow Query with EXPLAIN ANALYZE

Your dba_lab database is loaded with over 1.6 million rows across three tables. That is enough data for PostgreSQL to make real choices about how it accesses data.

Right now, your tables have no indexes beyond their primary keys. Any query that filters on a non-key column like order_date or status forces PostgreSQL to read every single row in the table. This is the most common performance problem in production databases, and EXPLAIN ANALYZE is the primary tool DBAs use to see exactly what is happening inside the database engine.

In this step, get ready to:
  • Run EXPLAIN and EXPLAIN (ANALYZE, BUFFERS) on a slow query.
  • Read the query plan and identify Seq Scan nodes.
  • Record a baseline execution time with \timing.
Run the query with EXPLAIN and EXPLAIN ANALYZE

PostgreSQL has two diagnostic modes for looking at queries. EXPLAIN shows the planner's estimated plan without actually running the query. EXPLAIN (ANALYZE, BUFFERS) runs the query for real and shows what actually happened.

  • In your psql session connected to dba_lab, see the planner's estimated plan by running this query:
-- First, see the planner's estimated plan (does not execute the query)
EXPLAIN
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

What does EXPLAIN show?

EXPLAIN returns the query planner's estimated execution plan. It shows which tables will be scanned, in what order, and how the planner expects to join them.

The query itself does not run. You only see estimates, not actual performance numbers.

You should see a plan with node types like Seq Scan and Hash Join, along with estimated cost and row count numbers.

Now for the real test. Before you run the next command, think about this: with no index on order_date, how do you think PostgreSQL will find orders from January to March 2025? Will it jump straight to matching rows, or will it have to read all 500,000?

  • Run the same query with EXPLAIN (ANALYZE, BUFFERS) to see what actually happens when PostgreSQL executes it:
-- Now run it for real and see actual execution statistics
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

What's different with ANALYZE?

Adding ANALYZE makes PostgreSQL actually execute the query. You now see real execution time, actual row counts, and loop counts alongside the estimates.

The BUFFERS option shows buffer I/O statistics. In PostgreSQL 18, ANALYZE implicitly enables BUFFERS, but including it explicitly makes your intent clear.

Look at the output. You should see Seq Scan on the orders table. PostgreSQL is reading every single row because there is no index on order_date to help it skip to the matching range.

Seeing an error instead of a query plan?

  • If you see a "relation does not exist" error, make sure you are connected to dba_lab. Run \c dba_lab to reconnect.
  • If you see a syntax error, check that the entire query is pasted correctly, including the closing ; at the end.
  • Still stuck? Help me troubleshoot my EXPLAIN ANALYZE query in psql.
Read the query plan output

The output from EXPLAIN (ANALYZE, BUFFERS) is dense, but every field tells you something specific about how PostgreSQL processed the query. Let's break down the key fields you see on each plan node.

What do these query plan fields mean?

  • cost: Two numbers separated by .. (for example, cost=0.00..18345.00). The first is startup cost, the second is total cost. These are relative units the planner uses to compare different strategies.
  • rows: The estimated number of rows the node will output. Compare the planner's estimate with the actual rows field from ANALYZE. Large mismatches hint that the planner's table statistics are out of date.
  • loops: How many times this node was executed. A value higher than 1 means the node ran inside a loop (common in nested-loop joins). Multiply actual rows by loops to get the true number of rows processed.
  • Buffers: shared hit/read: How many 8 KB data pages PostgreSQL accessed. shared hit means the page was already in memory. shared read means it came from disk. High read counts point to I/O-heavy operations.
  • Look at the top-level node's actual time value in your output. This is the total execution time in milliseconds.
  • Find the Seq Scan node on the orders table. Notice the Filter: line underneath it, which shows the conditions PostgreSQL checked against every row.
  • Compare the estimated rows value with the actual rows value on the Seq Scan node. If they are far apart, it means the planner's statistics are outdated.

Why is Seq Scan a problem here?

A Seq Scan (sequential scan) means PostgreSQL reads every row in the table from the first page to the last. For your 500,000-row orders table, that means reading thousands of disk pages just to find the small fraction of rows that match your date range and status filter.

An Index Scan, by contrast, would let PostgreSQL jump directly to the matching rows. You will create that index in the next step and see the difference.

Record your baseline execution time

The actual time inside the EXPLAIN ANALYZE output measures only the executor's work. The \timing command in psql captures the full wall-clock time, including network round-trip and result formatting. This gives you a baseline number you can compare against after adding indexes.

Before you run this, predict: how many milliseconds do you think this query takes to filter and join across 500,000 orders with no index?

  • Enable timing, run the query, and disable timing by pasting this into psql:
-- Enable timing to see wall-clock time
\timing on

SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

\timing off

What does \timing show?

After the query results, psql prints a line like Time: 345.678 ms. This is your end-to-end wall-clock time. Write this number down. You will compare it against the same query after adding an index in the next step.

  • Note the Time: value printed after the query results. You should see several hundred milliseconds or more.

That timing confirms what the query plan already told you. PostgreSQL is doing a full sequential scan of the orders table. You now have a concrete baseline to beat.

Seeing Time: 0.000 ms or very low numbers?

  • If the time seems impossibly fast, check that your tables actually have data. Run SELECT count(*) FROM orders; and confirm it returns 500,000.
  • Make sure you ran \timing on before the query, not after. The backslash command must come first.
  • Need help? Help me figure out why my query timing looks wrong in psql.

✔️ Awesome, I've got everything!

Great. Make sure you have your baseline execution time written down. You will need it for the before/after comparison in the next step.

ⓧ I'd like to double check the full code

Here is everything you should have run in this step. Compare it against your psql history.

-- Step 3: Diagnose a slow query

-- First, see the planner's estimated plan (does not execute the query)
EXPLAIN
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

-- Now run it for real and see actual execution statistics
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

-- Enable timing to see wall-clock time
\timing on

SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

\timing off

You have diagnosed exactly why this query is slow: a full sequential scan on 500,000 rows with no index to speed things up. Next, you will fix it by creating a targeted B-tree index and prove the improvement with the same EXPLAIN (ANALYZE, BUFFERS) query.

Fix the Query and Prove the Improvement

In Step 3, you ran EXPLAIN (ANALYZE, BUFFERS) and saw Seq Scan nodes scanning through all 500,000 rows in the orders table. You recorded a baseline execution time. Now you know exactly where the bottleneck is.

In this step, you will apply targeted fixes and measure each improvement with hard numbers. The key DBA skill is not just adding indexes blindly. It is proving that each change made a measurable difference by re-running EXPLAIN ANALYZE and comparing the before/after plans.

In this step, get ready to:
  • Create a B-tree index on the order_date column and re-run EXPLAIN ANALYZE to compare before/after.
  • Update planner statistics with ANALYZE and observe how fresh statistics affect query plans.
  • Create a compound index for multi-column queries and test its performance.
Create a B-tree index on the order_date column

The Seq Scan you saw in Step 3 happened because PostgreSQL had no way to jump directly to the rows matching your date range. A B-tree index on order_date gives the query planner a sorted lookup structure, so it can go straight to the matching rows instead of reading every single one.

  • In your psql session connected to dba_lab, create the index by running this command:
-- Create an index on order_date
CREATE INDEX idx_orders_order_date ON orders (order_date);

PostgreSQL responds with CREATE INDEX to confirm the index was built. This may take a moment since it needs to read and sort all 500,000 order_date values.

What is a B-tree index?

A B-tree (balanced tree) index organizes column values into a sorted tree structure. Instead of scanning every row to find matches, PostgreSQL walks the tree to jump directly to the range it needs.

Think of it like the index at the back of a textbook. You look up "Chapter 7" in the index and flip straight to page 142, rather than reading every page from the start.

Now for the moment of truth. Before you re-run the query, think about what you expect to see. Will the plan still show a Seq Scan, or something different? How much faster do you think it will be?

  • Re-run the exact same EXPLAIN (ANALYZE, BUFFERS) query from Step 3 to compare the new plan:
-- Re-run the same EXPLAIN ANALYZE to compare
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

What changed in the query plan?

Compare this output to the Seq Scan plan you saw in Step 3. Look for these differences:

  • The scan type on the orders table should now say Index Scan or Bitmap Index Scan instead of Seq Scan.
  • The actual rows scanned should be much smaller. The index lets PostgreSQL skip rows outside the date range entirely.
  • The Buffers: shared hit/read count should be lower. Fewer disk pages touched means less I/O.
  • The Execution Time should have dropped dramatically compared to your Step 3 baseline.

Compare the execution time you see now to the baseline you recorded in Step 3. You should see a 10x or greater improvement.

Still seeing a Seq Scan?

If the plan still shows a Seq Scan on orders, the planner may have decided the index is not worth using based on stale statistics. You will fix this in the next substep with ANALYZE.

Also confirm the index was created by running \di idx_orders_order_date in psql. You should see a row listing the index name, table, and column.

Need more help? Help me figure out why my index is not being used in the query plan.

Update planner statistics with ANALYZE

PostgreSQL's query planner relies on table statistics to decide which plan to use. These statistics include the number of rows, the distribution of values in each column, and how many distinct values exist. When you loaded 500,000 rows in Step 2, the planner's statistics may still reflect the empty table.

Stale statistics can cause the planner to make bad choices even when the right index exists. Running ANALYZE forces PostgreSQL to re-sample the table data and update its internal statistics.

  • Update the planner statistics for both tables by running these commands:
-- Update planner statistics
ANALYZE orders;
ANALYZE customers;

PostgreSQL responds with ANALYZE after each command, confirming it sampled the table and updated its statistics.

Why do stale statistics matter?

The planner uses statistics to estimate how many rows each step of the plan will return. If it thinks a filter matches 90% of the table (because statistics are stale), it will choose a Seq Scan even when an index exists. Accurate statistics let the planner see that the filter matches only a small fraction of rows, making the index worthwhile.

PostgreSQL's autovacuum daemon runs ANALYZE automatically based on table activity. In production, you rarely need to run it manually. But after a bulk data load, running it immediately ensures the planner has accurate numbers right away.

Do you think the query plan will look any different now that the planner has fresh statistics? Think about whether the cost estimates or the chosen plan type might change.

  • Re-run EXPLAIN (ANALYZE, BUFFERS) on the same query to see if the updated statistics changed the plan or cost estimates:
-- Re-run after ANALYZE to see if the plan improves further
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'pending';

What should I compare?

Look at the estimated rows vs actual rows in each plan node. Before ANALYZE, the estimates may have been wildly off. After ANALYZE, the estimates should closely match the actual row counts.

The cost values may also shift. More accurate statistics give the planner better cost estimates, which can lead to different join strategies or scan methods.

Estimates still look off?

If the estimated rows are still far from the actual rows after running ANALYZE, the default statistics target might be too low for your data distribution. This is normal for columns with skewed values. In production, DBAs increase the statistics target with ALTER TABLE orders ALTER COLUMN order_date SET STATISTICS 500; then re-run ANALYZE.

Need help? Help me understand why my estimated row counts are still inaccurate after running ANALYZE.

Create a compound index for multi-column filtering

Some queries filter on more than one column at once. A compound index covers multiple columns in a single B-tree, so PostgreSQL can satisfy both filters with one index lookup instead of scanning the table for the second condition.

  • Create a compound index on customer_id and order_date by running this command:
-- Create a compound index for queries filtering on customer_id and order_date
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

PostgreSQL responds with CREATE INDEX to confirm the compound index was built.

Why does column order matter in a compound index?

The index idx_orders_customer_date is sorted by customer_id first, then by order_date within each customer. This means it works well for queries that filter on customer_id alone, or on both columns together.

It would NOT help a query that filters only on order_date. That is why you still need the single-column idx_orders_order_date index from earlier.

Before you run the next query, predict what you will see. This query filters on both customer_id and order_date. Will PostgreSQL use the new compound index, one of the single-column indexes, or a Seq Scan?

  • Test a query that benefits from the compound index by running:
-- Test a query that benefits from the compound index
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.customer_id = 42
  AND o.order_date >= '2025-01-01';

What does this plan tell you?

The plan should show an Index Scan using idx_orders_customer_date. PostgreSQL walks the compound index to find customer 42's orders, then narrows to only those after 2025-01-01. Both filters are satisfied in a single index lookup.

Notice how few rows and buffers the scan touches compared to the original Seq Scan in Step 3. A single customer's orders are a tiny fraction of 500,000 total rows.

The execution time for this query should be well under a millisecond. Compare that to the hundreds of milliseconds you recorded in Step 3.

Seeing a Bitmap Heap Scan instead of Index Scan?

A Bitmap Heap Scan is still a good result. It means PostgreSQL used the index to build a bitmap of matching rows, then fetched them from the table in physical order. This is common when the index matches a moderate number of rows. The performance is still dramatically better than a full Seq Scan.

Still stuck? Help me understand the difference between Index Scan and Bitmap Heap Scan in my query plan.

✔️ Awesome, I've got everything!

Great. You have created both indexes, updated statistics, and compared query plans. All the commands from this step are saved in fix_and_verify.sql for reference.

ⓧ I'd like to double check the full code

Scroll up and confirm you have run each of these commands in order:

  • CREATE INDEX idx_orders_order_date ON orders (order_date);
  • EXPLAIN (ANALYZE, BUFFERS) on the original join query (first re-run after index creation).
  • ANALYZE orders; and ANALYZE customers; to update planner statistics.
  • EXPLAIN (ANALYZE, BUFFERS) on the original join query (second re-run after ANALYZE).
  • CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
  • EXPLAIN (ANALYZE, BUFFERS) on the compound index test query filtering on customer_id = 42 and order_date >= '2025-01-01'.

You have proven that targeted indexes and fresh statistics make a measurable, dramatic difference. You went from a query that crawled through every row to one that jumps straight to the data it needs. Next up, you will learn how to monitor the health of your entire database like a professional DBA.

Monitor Database Health Like a DBA

You diagnosed a slow query, added the right indexes, and proved every fix with EXPLAIN ANALYZE. You now know how to solve a single performance problem with hard numbers.

A DBA needs to monitor the health of the entire database, not just one query at a time. PostgreSQL ships with built-in statistics views that reveal which tables need maintenance, how much bloat has accumulated, and what queries are running right now.

In this step, get ready to:
  • Check table health statistics using pg_stat_user_tables.
  • Simulate and fix table bloat with VACUUM VERBOSE.
  • Monitor live database sessions with pg_stat_activity.
Check table health with pg_stat_user_tables

PostgreSQL tracks detailed statistics about every table in your database. The pg_stat_user_tables view shows how many live and dead rows each table has, when it was last vacuumed or analyzed, and how often it gets scanned sequentially versus with an index.

  • In your psql session connected to dba_lab, check the health of all your tables by running this query:
-- Check vacuum and analyze status for all user tables
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    seq_scan,
    idx_scan
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC;

What do these columns mean?

  • n_live_tup and n_dead_tup show how many rows are active versus how many are leftover from updates or deletes. Dead tuples take up space until VACUUM reclaims them.
  • last_vacuum and last_autovacuum tell you when the table was last cleaned up, either manually or by PostgreSQL's automatic maintenance process.
  • seq_scan and idx_scan count how many times the table was scanned sequentially versus using an index. A table with high seq_scan and zero idx_scan is a strong candidate for a new index.

You should see a row for each of your three tables: customers, orders, and order_items. Notice that orders shows a last_analyze timestamp from when you ran ANALYZE in the previous step.

Seeing all NULLs for vacuum and analyze columns?

If every timestamp column shows NULL, the table has never been manually vacuumed or analyzed. Check last_autovacuum and last_autoanalyze instead. PostgreSQL's autovacuum may have already run in the background.

help me understand why my pg_stat_user_tables shows NULL for all vacuum and analyze timestamps

Simulate and fix table bloat

When you update or delete rows in PostgreSQL, the old row versions are not immediately removed. They become dead tuples that sit on disk, wasting space and slowing down scans. This is called table bloat.

  • Simulate bloat by archiving a large batch of delivered orders. Run this UPDATE in psql:
-- Simulate table bloat with a large update
UPDATE orders
SET status = 'archived'
WHERE order_date < '2024-07-01'
  AND status = 'delivered';

What does this update do?

This marks all delivered orders before July 2024 as archived. PostgreSQL does not overwrite the old rows in place. Instead, it writes new row versions and marks the old ones as dead. Those dead tuples pile up until VACUUM cleans them up.

You should see output like UPDATE 62000 (the exact count depends on your random data). That many old row versions are now sitting on disk as dead tuples.

  • Check how many dead tuples piled up by running this query:
-- Check dead tuples after the update
SELECT
    relname,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

What should I see?

The n_dead_tup column should now show a large number (roughly matching the update count). These dead tuples are consuming disk space and making sequential scans slower because PostgreSQL has to skip over them.

Now clean up those dead tuples. VACUUM reclaims the space occupied by dead tuples. The VERBOSE option prints a detailed report of exactly what it did.

  • Reclaim the dead tuples by running:
-- Run VACUUM VERBOSE to reclaim space
VACUUM VERBOSE orders;

What does the VACUUM output mean?

The VERBOSE output shows how many index entries and dead tuples were removed, how many pages were scanned, and how much free space was recovered. This is the same output a production DBA reads to confirm that maintenance is working correctly.

What is autovacuum?

PostgreSQL's autovacuum daemon runs in the background and automatically schedules VACUUM and ANALYZE based on table activity. It is enabled by default.

You are running VACUUM manually here to see the results immediately, but in production, autovacuum handles most of this automatically. DBAs monitor pg_stat_user_tables to make sure autovacuum is keeping up.

Before you check the results, what do you think happened to the n_dead_tup count?

  • Confirm that VACUUM worked by checking the dead tuple count again:
-- Check dead tuples again after vacuum
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

The n_dead_tup count should have dropped to zero (or near zero). The last_vacuum column should show a timestamp from moments ago.

Dead tuple count did not drop?

If n_dead_tup is still high, another transaction may be holding a lock that prevents VACUUM from reclaiming those rows. Make sure you do not have any other open transactions in a second terminal. Run VACUUM VERBOSE orders; again after closing any other sessions.

help me understand why VACUUM did not reduce the dead tuple count on my orders table

Monitor live sessions with pg_stat_activity

The pg_stat_activity view shows every active connection to the PostgreSQL server in real time. DBAs use it to spot long-running queries, blocked sessions, and idle connections that are holding resources.

  • See your own session and any background processes by running this query:
-- View live database activity
SELECT
    pid,
    usename,
    state,
    query,
    query_start,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE datname = 'dba_lab';

What do these columns tell you?

  • pid is the process ID of each connection. You can use it to cancel or terminate a runaway query.
  • state shows whether the session is active (running a query right now), idle (connected but doing nothing), or idle in transaction (a transaction was started but not committed).
  • query shows the last (or current) SQL statement the session ran.
  • wait_event_type and wait_event tell you if the session is blocked and what it is waiting on. A session waiting on a lock is a red flag.

You should see at least one row: your own psql session, with the query column showing the SELECT you just ran against pg_stat_activity.

To see multiple sessions at once, open a second terminal on your Linux machine.

  • Open a new terminal window or tab.
  • Connect to dba_lab in the new terminal by running:
sudo -u postgres psql -d dba_lab
  • Switch back to your original psql session and re-run the pg_stat_activity query:
SELECT
    pid,
    usename,
    state,
    query,
    query_start,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE datname = 'dba_lab';

What do you expect to see now that a second session is open?

You should see two rows. One is your original session running the SELECT query (state: active). The other is your new session sitting in state idle, waiting for you to type something.

Only seeing one session?

Make sure your second terminal connected to dba_lab specifically, not the default postgres database. The WHERE datname = 'dba_lab' filter only shows connections to that database. Check your second terminal's prompt to confirm it shows dba_lab=#.

help me see both psql sessions in pg_stat_activity

✔️ Awesome, I've got everything!

Great. Confirm you have saved your monitor_health.sql file with all the queries from this step.

ⓧ I'd like to double check the full code

Your monitor_health.sql file is over 30 lines, so scroll up through this step and confirm you have each of these query blocks in order:

  • The pg_stat_user_tables health check query (selecting relname, n_live_tup, n_dead_tup, and vacuum/analyze timestamps).
  • The UPDATE orders statement that simulates table bloat.
  • The dead tuple check query filtering on relname = 'orders'.
  • VACUUM VERBOSE orders;
  • The post-vacuum check query (selecting n_dead_tup and last_vacuum).
  • The pg_stat_activity query showing live sessions.

Secret mission

Track the Slowest Queries with pg_stat_statements

In production, you rarely know which query is slow until users complain. Can you set up automatic query tracking to catch the slow ones before anyone notices?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local Linux machine with no cloud resources or paid services. PostgreSQL will continue running as a system service until you stop or remove it.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • PostgreSQL 18 installed and running as a system service.
  • The dba_lab database with customers, orders, and order_items tables containing over 1.6 million rows of test data.

Keep everything running

No action needed. Choose this if you plan to use PostgreSQL for future projects or practice.

  • PostgreSQL uses minimal resources when idle.
  • Your dba_lab database, all indexes, and your pg_stat_statements configuration will remain available.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up system resources. Your database and configuration stay on disk so you can start it again anytime.

Stop the service by running the command that matches your Linux distribution.

Ubuntu/Debian

  • Stop the service by running:
sudo systemctl stop postgresql
  • When you are ready to resume, start it again by running:
sudo systemctl start postgresql

RHEL/Rocky/AlmaLinux

  • Stop the service by running:
sudo systemctl stop postgresql-18
  • When you are ready to resume, start it again by running:
sudo systemctl start postgresql-18

Delete - I don't want to use this again

Remove PostgreSQL and all project data from your machine. This cannot be undone.

Ubuntu/Debian

  • Stop the PostgreSQL service by running:
sudo systemctl stop postgresql
  • Remove the PostgreSQL 18 packages by running:
sudo apt remove postgresql-18

The package manager removes the binaries but leaves the data directory on disk. Delete it manually by running:

sudo rm -rf /var/lib/postgresql/18/main

RHEL/Rocky/AlmaLinux

  • Stop the PostgreSQL service by running:
sudo systemctl stop postgresql-18
  • Remove the PostgreSQL 18 server package by running:
sudo dnf remove postgresql18-server

The package manager removes the binaries but leaves the data directory on disk. Delete it manually by running:

sudo rm -rf /var/lib/pgsql/18/data

Nice Work!

Nice Work!

Nice work! You just diagnosed and optimized a PostgreSQL database using the same tools and techniques that professional DBAs rely on every day.

You've learned how to:

  • Diagnosed slow queries using EXPLAIN (ANALYZE, BUFFERS) and learned to read query plans, understanding the difference between Seq Scan and Index Scan, cost estimates, actual row counts, and buffer I/O.
  • Fixed performance problems by creating targeted B-tree indexes and updating planner statistics with ANALYZE, proving each fix with measurable before/after execution times.
  • Monitored database health using pg_stat_user_tables for vacuum status and dead tuples, resolved table bloat with VACUUM VERBOSE, and tracked live sessions with pg_stat_activity.
  • Secret Mission: Set up pg_stat_statements to automatically track and rank the slowest queries across the entire server.

Ready to quiz yourself?