← All posts

Database Optimization: Six Strategies That Cut Query Time by 60%

Profiling, indexes, ORM refactors, materialized views, connection pooling, and cache — a practical PostgreSQL and MySQL guide.

Tags
BackendDatabasesNewsPerformance
Contents

In brief

When an app feels slow, the database is usually the bottleneck — not the framework or CDN. A Dev.to guide from a nine-year backend veteran lists six strategies that cut query time by up to 60% on real projects.

What happened

The author’s rule: never optimize a query you have not profiled. Step one — slow query log (~200 ms threshold in PostgreSQL and MySQL) and a weekly review. Step two — EXPLAIN ANALYZE for real timings, not paper plans.

Production example: a vacancy search did a sequential scan over 200k rows, filtered 187k, and took 1.8 s. A composite index on (status, created_at DESC) dropped that to 12 ms — a 99.3% win. Next: partial indexes (only status = 'active', ~85% smaller), covering indexes with INCLUDE for index-only scans without heap access.

ORM pitfalls get their own section: N+1 (150 queries per page → 8 after eager loading), correlated subqueries vs JOINs, analytics with window functions instead of five app round-trips. For read-heavy dashboards — materialized views (cron refresh), strategic denormalization (JSONB stats on an organisation row).

Infrastructure: PgBouncer (500 client connections on 25 backend), read replicas for search and reporting, Redis with TTL and event-driven invalidation (vacancy:created).

Why it matters

Full-stack teams often tune React while p95 API latency comes from one heavy SELECT. Random indexes can slow writes; without EXPLAIN you will not see the plan change. Materialized views and cache are not “anti-patterns” when staleness is measured in minutes, not milliseconds.

In practice

  1. Enable slow query logging; weekly, take the top 20 slowest queries.
  2. Run EXPLAIN ANALYZE on each; hunt seq scans on tables >10k rows and sorts without index support.
  3. Composite indexes: column order must match filter and sort order in the query.
  4. Audit ORM output: N+1, withCount, replace subqueries with JOINs/CTEs.
  5. Read-heavy dashboards — materialized view + periodic REFRESH CONCURRENTLY.
  6. Pooling (PgBouncer) and replicas before caching what SQL could fix.

Takeaway

Database optimization is ongoing engineering, not a one-off sprint. The six layers in the article (profile → indexes → SQL → schema → connections → cache) are a solid checklist before the next “make the frontend faster” ticket.