postgresql-optimization

Start by running EXPLAIN ANALYZE on the slow query to identify scan types, join strategies, and execution time bottlenecks. Then rewrite inefficient queries, add appropriate indexes, and test the improvements against baseline performance.

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, and effective_cache_size for planner estimates. Checkpoint settings and autovacuum configuration also significantly impact sustained production performance.

Table bloat occurs when dead tuples accumulate faster than autovacuum removes them. Schedule regular VACUUM and ANALYZE operations, monitor autovacuum activity, and check table bloat statistics to keep storage efficient.

Avoid it when the task does not clearly match PostgreSQL optimization scope, or when required inputs, permissions, or success criteria are missing. Results still require environment-specific validation, testing, and expert review before production changes.