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

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

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

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

Hint

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

Practice

1. Why is performance tuning important for a PostgreSQL database?

easy

A. It changes the database structure randomly.

B. It makes the database use more disk space.

C. It deletes old data automatically.

D. It helps the database run faster and handle more users efficiently.

Solution

1. Step 1: Understand the goal of performance tuning

   Performance tuning aims to improve speed and efficiency of database operations.

2. Step 2: Identify the correct effect of tuning

   Faster queries and better handling of many users are direct benefits of tuning.

3. Final Answer:

   It helps the database run faster and handle more users efficiently. -> Option D

4. Quick Check:

   Performance tuning = faster, efficient database [OK]

Hint: Performance tuning improves speed and efficiency [OK]

Common Mistakes:

• Thinking tuning deletes data
• Believing tuning increases disk usage unnecessarily
• Assuming tuning changes data structure randomly

2. Which of the following is the correct way to create an index on the column email in PostgreSQL?

easy

A. CREATE INDEX idx_email ON users (email);

B. MAKE INDEX idx_email ON users (email);

C. CREATE INDEX ON users email;

D. INDEX CREATE idx_email users (email);

Solution

1. Step 1: Recall the syntax for creating an index

   The correct syntax is CREATE INDEX index_name ON table_name (column_name);.

2. Step 2: Match the syntax with options

   CREATE INDEX idx_email ON users (email); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX idx_email ON users (email); -> Option A

4. Quick Check:

   CREATE INDEX syntax = CREATE INDEX idx_email ON users (email); [OK]

Hint: Use 'CREATE INDEX index_name ON table (column);' [OK]

Common Mistakes:

• Using wrong keywords like MAKE or INDEX CREATE
• Missing parentheses around column name
• Incorrect order of keywords

3. Consider this query on a large table without indexes:
SELECT * FROM orders WHERE customer_id = 123;
What is the likely effect on performance before and after adding an index on customer_id?

medium

A. Query runs faster after adding the index.

B. Query runs slower after adding the index.

C. Query result changes after adding the index.

D. Query causes an error after adding the index.

Solution

1. Step 1: Understand how indexes affect query speed

   Indexes help the database find rows faster by avoiding full table scans.

2. Step 2: Predict the query performance change

   Adding an index on customer_id speeds up queries filtering by that column.

3. Final Answer:

   Query runs faster after adding the index. -> Option A

4. Quick Check:

   Index on filter column = faster query [OK]

Hint: Indexes speed up filtered queries [OK]

Common Mistakes:

• Thinking indexes slow down SELECT queries
• Expecting query results to change
• Assuming indexes cause errors

4. You wrote this query to improve performance:
CREATE INDEX idx_date ON sales (sale_date);
SELECT * FROM sales WHERE DATE(sale_date) = '2023-01-01';
But the query is still slow. What could be the problem?

medium

A. The index was created on the wrong column.

B. The query uses a function on the column, preventing index use.

C. PostgreSQL does not support indexes on dates.

D. The table has no data.

Solution

1. Step 1: Check if query uses functions on indexed column

   If the query applies a function like DATE(sale_date), the index may not be used.

2. Step 2: Understand index usage rules

   Indexes work best when the column is used directly without transformations.

3. Final Answer:

   The query uses a function on the column, preventing index use. -> Option B

4. Quick Check:

   Functions on column block index use [OK]

Hint: Avoid functions on indexed columns in WHERE clause [OK]

Common Mistakes:

• Assuming PostgreSQL can't index dates
• Ignoring function usage on columns
• Thinking empty table causes slowness

5. A growing app has a users table with millions of rows. You notice slow login queries filtering by username. Which combined approach best improves performance?

hard

A. Store usernames in a separate table without indexes.

B. Drop all indexes and rely on sequential scans.

C. Add an index on username and analyze query plans regularly.

D. Increase server RAM without changing queries or indexes.

Solution

1. Step 1: Identify indexing as key for fast lookups

   Adding an index on username helps queries find users quickly.

2. Step 2: Use query plan analysis to maintain performance

   Regularly checking query plans helps spot slow parts and optimize further.

3. Final Answer:

   Add an index on username and analyze query plans regularly. -> Option C

4. Quick Check:

   Index + query plan analysis = best tuning [OK]

Hint: Combine indexing with query plan checks [OK]

Common Mistakes:

• Removing indexes causes slower queries
• Ignoring query plan analysis
• Relying only on hardware upgrades