Index

What is a Database Index

A database index is a data structure that enhances the speed of data retrieval operations in a database. Instead of scanning every row in a table when you query a database, an index allows the database to quickly locate the data.

You can think of it like a book's index: it helps you find the relevant page containing the information you're seeking.

How Indexes Work

The most common type of index is the B-tree index. B-tree indexes are versatile and efficient for a wide range of query operations, including point queries, range queries, and sorting. They are also used to enforce uniqueness constraints.

A B-tree index is a self-balancing tree data structure employed by database systems to efficiently store and retrieve data. Its design minimizes the number of disk I/O operations required for search, insert, and delete operations, making it suitable for large datasets.

In essence, a B-tree organizes data in a way that facilitates quick location. Think of it as a well-organized bookshelf, divided into sections and shelves, with the data (like books) stored in a specific order. This structure allows you to swiftly narrow your search by checking the appropriate section and then quickly finding the right shelf.

Creating Indexes

Primary Index

Automatically created when a primary key is defined for a table, ensuring each row has a unique identifier.

Example:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

Using UUID:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50)
);

Composite Index

Created on multiple columns to optimize queries that involve those columns.

Example:

CREATE INDEX idx_users_name 
  ON users (first_name, last_name);

Unique Index

Ensures that all values in a specified column, or set of columns, are unique.

Example:

CREATE UNIQUE INDEX idx_unique_username 
  ON users (username);

When to Use Indexes

• Frequently Queried Columns: If certain columns are frequently searched or filtered, creating indexes on those columns can significantly enhance query performance.

• Join Conditions: Indexes on columns used in join operations can accelerate queries that combine data from multiple tables.

• Sorting and Grouping: Indexes can improve the efficiency of queries that involve sorting or grouping data based on specific columns.

The Downsides

Indexes come with costs. Creating and maintaining indexes can introduce overhead for database operations like insert and update. In some cases, indexes may occupy more space than the actual data.