Indexing Internals and Usage

How indexing works

Most relational DBMS engines use B-tree indexes. Instead of scanning every row, the engine navigates index pages to quickly find matching record pointers.

CREATE TABLE enrollments (
  enrollment_id BIGSERIAL PRIMARY KEY,
  student_id BIGINT NOT NULL,
  course_id BIGINT NOT NULL,
  enrolled_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_date ON enrollments(course_id, enrolled_at);

Index trade-offs

• Fast reads, slower writes due to maintenance overhead.
• Composite index column order is crucial.
• Unused indexes increase storage and planning complexity.