postgresql-optimization

Start by running EXPLAIN ANALYZE on the slow query to identify scan types and join strategies. Then add appropriate indexes, rewrite inefficient joins or subqueries, and verify improvements by comparing execution times before and after changes.

Identify missing indexes from query plans, then create B-tree indexes for equality lookups, composite indexes for multi-column filters, and partial indexes for filtered subsets. Review index usage regularly to remove unused indexes that slow writes.

The key settings are shared_buffers for caching, work_mem for sort and hash operations, effective_cache_size for planner estimates, and checkpoint settings for write behavior. Autovacuum tuning also prevents table bloat that degrades query plans.

Table bloat happens when dead tuples accumulate faster than autovacuum removes them, often from heavy updates or deletes. Schedule regular VACUUM and ANALYZE jobs, monitor autovacuum activity, and adjust its thresholds for high-churn tables.

Avoid applying generic tuning steps without environment-specific validation, since optimal settings depend on hardware, workload, and data volume. Always test changes in staging and consult a database specialist for mission-critical production systems.