Database Performance

Indexes are the primary tool for accelerating query execution. Follow these best practices:

• Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
• Avoid over-indexing; each index adds write overhead.
• Use covering indexes to eliminate lookups.
• Periodically review and drop unused indexes.

Scenario	Recommended Index Type
Range queries on dates	B-tree on date column
Full-text search	GIN/TSVector index
Unique constraints	Unique index

Well‑written SQL is essential for performance.

1. Prefer explicit column lists over SELECT *.
2. Limit result sets with LIMIT when appropriate.
3. Avoid OR conditions that can be rewritten as UNION ALL.
4. Use EXISTS instead of IN for sub‑queries returning large sets.

-- Bad
SELECT * FROM orders WHERE status = 'shipped' OR status = 'delivered';

-- Good
SELECT order_id, customer_id, total FROM orders 
WHERE status IN ('shipped','delivered');

Cache frequently accessed query results to reduce load:

• Use in‑memory stores like Redis or Memcached.
• Cache at application layer with sensible TTL.
• Invalidate cache on data changes.

Regular maintenance keeps the database healthy.

• Rebuild fragmented indexes.
• Update statistics after bulk loads.
• Archive old data to separate partitions.