Database Indexing Strategies That Actually Matter

A missing index on a frequently queried column can turn a 15ms query into a 4-second full table scan as your data grows past 100,000 rows. I have personally debugged production slowdowns in pharmacy POS systems where the sales_date column on a ledger table with 500,000+ records had no index — causing the daily sales report to take 12 seconds to load instead of 200ms.

The fix took 30 seconds (CREATE INDEX idx_sales_date ON sales(sale_date)), but finding the problem required analyzing slow query logs, running EXPLAIN ANALYZE, and tracing the issue through three layers of application code. The lesson: index planning should happen during schema design, not during incident response.

A composite index on (tenant_id, created_at) is not the same as an index on (created_at, tenant_id). The column order determines which queries the index can accelerate. Place the most selective column (the one that filters the most rows) first, and the range/sort column second.

For multi-tenant applications, the pattern is almost always: (tenant_id, date_column) because every query is scoped to a single tenant first, then filtered or sorted by date. This single composite index often eliminates the need for separate indexes on each column.

Indexes are not free. Every index consumes disk space and slows down INSERT, UPDATE, and DELETE operations because the database must update the index tree alongside the table data. For write-heavy tables (like event logs, sensor readings, or audit trails), adding too many indexes can degrade write performance by 30-50%.

The rule of thumb: index columns that appear in WHERE, JOIN, and ORDER BY clauses of your most frequent queries. Do not index boolean columns with low cardinality (e.g., is_active with only true/false values) — the index will not help because it cannot filter enough rows to justify the lookup cost.

PostgreSQL supports partial indexes: CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'active'. This index is smaller and faster because it only includes rows matching the condition. Use this pattern for "hot" queries that only care about a subset of your data (active orders, unread notifications, pending approvals).

Expression indexes are equally powerful: CREATE INDEX idx_lower_email ON users(LOWER(email)). This makes case-insensitive email lookups fast without requiring your application to normalize email casing before every query.