You don't need to be a DBA to understand indexing. Knowing the basics helps you design better APIs and debug performance issues.
What is an Index?
An index is like a book's table of contents. Instead of scanning every row (full table scan), the database jumps directly to matching rows.
Types of Indexes
B-Tree (Default)
Balanced tree structure. Great for equality and range queries. The most common index type.
Hash Index
Only for equality comparisons. Faster than B-tree for exact lookups but can't do ranges.
GIN (Generalized Inverted Index)
For array and full-text search columns.
Composite Index
When to Index
- Columns in WHERE clauses
- Columns in JOIN conditions
- Columns in ORDER BY
- Foreign key columns
When NOT to Index
- Small tables (< 1000 rows)
- Columns with low cardinality (boolean, status with 3 values)
- Tables with heavy write operations (indexes slow down inserts)
- Columns rarely used in queries