database-optimizer

Enable the pg_stat_statements extension and query it ordered by total_exec_time or mean_exec_time to find the slowest queries. Then run EXPLAIN (ANALYZE, BUFFERS) on each candidate to capture the actual execution plan and buffer usage as a baseline.

Look for sequential scans on large tables, nested loops with large outer sets, row estimate mismatches indicating stale statistics, and low buffer hit ratios. Each pattern maps to a remedy such as adding a B-tree index, running ANALYZE, or increasing shared_buffers.

A covering index includes all columns a query needs so the database answers it from the index alone without a heap fetch. In PostgreSQL use the INCLUDE clause; in MySQL append the columns to the index. Use it for frequent queries filtering and projecting a stable column set.

Yes, the Skill covers both engines with dedicated references for PostgreSQL tuning and MySQL tuning. It includes engine-specific monitoring queries, configuration examples, and index strategies for each platform.

Every index adds write amplification because inserts and updates must maintain it. Create indexes with CONCURRENTLY in PostgreSQL to avoid table locks, apply one change at a time, and roll back if write performance or replication lag degrades.

Use range or list partitioning when tables grow very large and queries consistently filter on the partition key, such as created_at. Partition pruning then limits scans to relevant partitions, and dropping old partitions becomes a fast metadata operation.