Database Optimization

Introduction

Backends die at the database first. Knowing the top 6 optimizations puts you ahead of 90% of teams.

Understanding the topic

The high-impact list, in order:

• 📈 Indexes — on every FK, every WHERE column, composite for multi-column filters.
• 🛑 Kill N+1 — join fetch or @EntityGraph on hot endpoints.
• 📦 Batch inserts/updates — hibernate.jdbc.batch_size: 50.
• 🪞 Read replicas for read-heavy workloads.
• 🗜 Pagination + projections — never load whole rows you don't need.
• 🧊 Cache hot reads in Redis with sensible TTLs.

Best practices

• Always run EXPLAIN ANALYZE on slow queries before adding hardware.
• Use Hibernate statistics or p6spy in dev to count actual SQL per request.
• Track p95/p99, not averages.