B-tree index (default) behavior in PostgreSQL - Mini Project: Build & Apply

Understanding B-tree Index Behavior in PostgreSQL

📖 Scenario: You are managing a small library database. You want to speed up searches for books by their title using PostgreSQL's default B-tree index.

🎯 Goal: Create a table for books, add some sample data, create a B-tree index on the title column, and write a query that uses this index to find books with titles starting with 'The'.

📋 What You'll Learn

Create a table called books with columns id (integer primary key) and title (text).

Insert exactly these three rows into books: (1, 'The Hobbit'), (2, 'The Catcher in the Rye'), (3, 'A Tale of Two Cities').

Create a B-tree index named idx_books_title on the title column of books.

Write a SELECT query to find all books where title starts with 'The'.

💡 Why This Matters

🌍 Real World

B-tree indexes are the default and most common index type in PostgreSQL. They help speed up searches on columns with sortable data like text and numbers.

💼 Career

Database administrators and backend developers use B-tree indexes to improve query performance in real applications, making data retrieval faster and more efficient.

1

Create the books table and insert data

Create a table called books with columns id as integer primary key and title as text. Then insert these exact rows: (1, 'The Hobbit'), (2, 'The Catcher in the Rye'), and (3, 'A Tale of Two Cities').

PostgreSQL

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

Need a hint?

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

2

Create a B-tree index on the title column

Create a B-tree index named idx_books_title on the title column of the books table.

PostgreSQL

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

INSERT INTO books (id, title) VALUES
    (1, 'The Hobbit'),
    (2, 'The Catcher in the Rye'),
    (3, 'A Tale of Two Cities');

-- Create the B-tree index on title
-- Your code here

Need a hint?

Use CREATE INDEX with USING btree on the title column.

3

Write a query to find books with titles starting with 'The'

Write a SELECT query to get all columns from books where the title starts with the string 'The'. Use the LIKE operator with the pattern 'The%'.

PostgreSQL

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

INSERT INTO books (id, title) VALUES
    (1, 'The Hobbit'),
    (2, 'The Catcher in the Rye'),
    (3, 'A Tale of Two Cities');

CREATE INDEX idx_books_title ON books USING btree (title);

-- Write the SELECT query to find titles starting with 'The'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title LIKE 'The%' to find matching titles.

4

Verify the index usage with EXPLAIN

Write an EXPLAIN statement before the SELECT query to check that the B-tree index idx_books_title is used when searching for titles starting with 'The'.

PostgreSQL

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

INSERT INTO books (id, title) VALUES
    (1, 'The Hobbit'),
    (2, 'The Catcher in the Rye'),
    (3, 'A Tale of Two Cities');

CREATE INDEX idx_books_title ON books USING btree (title);

-- Add EXPLAIN before the SELECT query
-- Your code here

Need a hint?

Use EXPLAIN before the SELECT query to see the query plan and confirm index usage.