How do you monitor database performance and what do you do when CPU usage is high or queries are slow?

Database performance monitoring typically involves built-in DBMS tools and external tools:

• Built-in tools: for example, in PostgreSQL — pg_stat_statements for query analysis, EXPLAIN ANALYZE for execution plan assessment.
• System metrics: CPU, memory, I/O via top, htop, iostat, or specialized monitoring systems (Prometheus, Zabbix).
• Slow query logs: enabling slow query logging to identify problematic queries.

In case of high CPU or slow queries:

1. Analyze the most resource-intensive queries using pg_stat_statements or similar.
2. Optimize queries: add indexes, rewrite queries, reduce data volume.
3. Check execution plans (EXPLAIN ANALYZE) to identify bottlenecks.
4. Consider caching or denormalizing data.
5. If load is related to locks — analyze locks and transactions.
6. If necessary, scale the system: replication, sharding.

Example query to identify the most costly queries in PostgreSQL:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;