Why performance tuning matters in PostgreSQL - See It in Action

Why Performance Tuning Matters in PostgreSQL

📖 Scenario: You are managing a small online bookstore database using PostgreSQL. The store has a table called books that stores information about each book, including its title, author, and price. As the number of books grows, you notice that queries to find books by a specific author are getting slower. You want to understand why performance tuning matters and how to improve query speed.

🎯 Goal: Build a simple PostgreSQL setup with a books table, add some sample data, and then create an index on the author column to improve query performance. This project will show why performance tuning matters by making queries faster.

📋 What You'll Learn

Create a books table with columns id, title, author, and price

Insert 5 sample book records with exact values

Create a variable to hold the author name to search for

Write a query to select all books by the given author

Create an index on the author column to improve query speed

💡 Why This Matters

🌍 Real World

Online stores and many applications use databases to store and search data quickly. Performance tuning helps keep searches fast as data grows.

💼 Career

Database administrators and developers must know how to optimize queries and use indexes to improve application speed and user experience.

1

Create the books table and insert sample data

Create a table called books with columns id (integer primary key), title (text), author (text), and price (numeric). Then insert these exact 5 rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99), (2, '1984', 'George Orwell', 8.99), (3, 'To Kill a Mockingbird', 'Harper Lee', 7.99), (4, 'Animal Farm', 'George Orwell', 6.99), (5, 'The Catcher in the Rye', 'J.D. Salinger', 9.99).

PostgreSQL

-- Create the books table and insert 5 rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set the author name to search for

Create a variable called search_author and set it to the text 'George Orwell' to use in your query.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, author, price) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
  (2, '1984', 'George Orwell', 8.99),
  (3, 'To Kill a Mockingbird', 'Harper Lee', 7.99),
  (4, 'Animal Farm', 'George Orwell', 6.99),
  (5, 'The Catcher in the Rye', 'J.D. Salinger', 9.99);

-- Create a variable search_author with value 'George Orwell'
-- Your code here

Need a hint?

Use the \set command in psql to create a variable.

3

Write a query to select books by the author

Write a SQL query that selects all columns from books where the author equals the variable :search_author.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, author, price) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
  (2, '1984', 'George Orwell', 8.99),
  (3, 'To Kill a Mockingbird', 'Harper Lee', 7.99),
  (4, 'Animal Farm', 'George Orwell', 6.99),
  (5, 'The Catcher in the Rye', 'J.D. Salinger', 9.99);

\set search_author 'George Orwell'

-- Write a SELECT query to get all books where author = :search_author
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE author = :'search_author' to filter by the variable.

4

Create an index on the author column

Create an index called idx_author on the author column of the books table to improve query performance.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, author, price) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
  (2, '1984', 'George Orwell', 8.99),
  (3, 'To Kill a Mockingbird', 'Harper Lee', 7.99),
  (4, 'Animal Farm', 'George Orwell', 6.99),
  (5, 'The Catcher in the Rye', 'J.D. Salinger', 9.99);

\set search_author 'George Orwell'

SELECT * FROM books WHERE author = :'search_author';

-- Create an index idx_author on the author column
-- Your code here

Need a hint?

Use CREATE INDEX idx_author ON books(author); to create the index.