B-Tree Index - LabsICT

B-tree is the default index type in PostgreSQL and the one you will use most often. It stands for "balanced tree" and is excellent for equality and range queries. If you have ever used a binary search tree in a programming class, B-tree indexes work on a similar principle but are optimized for disk storage.

Creating a B-Tree Index

When you create an index without specifying a type, you get a B-tree:

CREATE INDEX idx_students_email ON students (email);

-- This is the same as:
CREATE INDEX idx_students_email ON students USING btree (email);

The B-tree index keeps the data sorted, which allows PostgreSQL to use it for:

= equality comparisons
<, >, <=, >= range comparisons
BETWEEN range queries
IN list membership
LIKE 'abc%' prefix pattern matching

Composite Indexes

You can index multiple columns together:

CREATE INDEX idx_students_name ON students (last_name, first_name);

This is useful for queries that filter on both columns:

SELECT * FROM students
WHERE last_name = 'Smith' AND first_name = 'Alice';

The order of columns in the index matters. The index can also be used for queries that only filter on last_name (the leftmost column), but not for queries that only filter on first_name.

Unique Indexes

A unique index enforces uniqueness in addition to providing fast lookups:

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

This is equivalent to adding a UNIQUE constraint on the column. It prevents duplicate values and creates an index at the same time.

Partial Indexes

Sometimes you only want to index a subset of rows:

CREATE INDEX idx_active_students ON students (email)
WHERE status = 'active';

This only indexes active students. The index is smaller and faster because it excludes inactive students. Very useful when you frequently query a specific subset of data.

When B-Tree Is NOT the Answer

B-tree indexes are great for most cases, but they are not ideal for:

Full-text search (use GIN or GiST instead)
Searching within arrays (use GIN)
Geospatial queries (use GiST with PostGIS)
Pattern matching with LIKE '%abc' (leading wildcard)

For these cases, PostgreSQL offers specialized index types which we will cover next.