How Indexes Work in Databases: Deep Dive into MongoDB & PostgreSQL

Database indexes are the difference between a query scanning millions of rows and one that finds results instantly. B-tree indexes (the default in both MongoDB and PostgreSQL) maintain sorted data in a balanced tree structure — supporting equality, range queries, and sorting with O(log n) lookups. Hash indexes provide O(1) equality lookups but can't handle ranges.

Compound indexes cover multiple fields — field order matters due to the leftmost prefix rule. PostgreSQL additionally offers GIN (full-text search, arrays), GiST (geometric/spatial data), and BRIN (large sequential datasets).

The critical trade-off: indexes speed up reads but slow down writes (every INSERT/UPDATE must maintain the index). Over-indexing wastes storage and degrades write performance. Best practices: index fields in WHERE/JOIN/ORDER BY clauses, use EXPLAIN ANALYZE to verify index usage, and monitor index size relative to table size.