Summary: Find the slowest queries, read their execution plans with EXPLAIN, add the right indexes, and remove N+1 patterns. Many applications get several times faster from this step alone.

Step 1: Find Slow Queries

  • PostgreSQL: the pg_stat_statements extension and log_min_duration_statement.
  • MySQL: slow query log and performance_schema.
  • Sort by total time (frequency × duration), not just the single slowest.

Step 2: Read the Execution Plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Look for: Seq Scan on large tables, big gaps between estimated and actual rows, and sorts spilling to disk. EXPLAIN ANALYZE actually runs the query, so be careful on production for statements that modify data.

Step 3: The Right Index

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

Principles:

  • Indexes follow WHERE and ORDER BY patterns; column order matters.
  • Composite indexes for filter + sort together.
  • Do not over-index: each index slows writes and takes space.
  • On PostgreSQL use CONCURRENTLY so the table is not locked.
  • Drop indexes that are never used.

Common Mistakes

Problem Fix
SELECT * on wide tables Select needed columns
N+1 queries from an ORM Use joins/eager loading or batching
Function on a column in WHERE Functional index or rewrite the condition
Large OFFSET pagination Keyset pagination
LIKE '%term%' Full-text search or trigram index
Mismatched data types Align types so indexes get used

Routine Maintenance

  • Make sure autovacuum and statistics (ANALYZE) run on PostgreSQL.
  • Monitor table bloat and index size.
  • Use connection pooling (PgBouncer) when connections are many.
  • Archive old data or partition very large tables.

Configuration Tuning

Parameters such as shared_buffers, work_mem, and effective_cache_size should match RAM, but only after queries and indexes are fixed. Change one at a time and measure.

Measure Results

Compare endpoint p95 latency, top query durations, and database CPU/IO before and after. Continue to API optimization.

FAQ

Does adding server RAM solve it?

Sometimes it helps, but bad queries stay bad at larger scale. Fix queries first.

Is adding an index in production safe?

Safe when using a non-blocking mode during quiet hours, after testing in staging.

Next steps

Send us your 5 slowest queries, and we will analyze them and recommend fixes.