What methods will you use to identify the causes of database performance degradation?

To identify the causes of database performance degradation, the following methods can be used:

• Log and metric analysis: study query logs, execution times, and lock counts.
• Query profiling: use EXPLAIN or similar tools to evaluate query execution plans.
• Resource monitoring: check CPU, memory, disk, and network load on the database server.
• Identification of "heavy" queries: find queries with long execution times or high resource consumption.
• Index checks: ensure appropriate indexes are used and there are no redundant or missing ones.
• Analysis of locks and contention: identify issues with parallelism and deadlocks.

An example in Go for executing an EXPLAIN query and analysis:

 db, _ := sql.Open("postgres", "connection_string")
 rows, err := db.Query("EXPLAIN ANALYZE SELECT * FROM users WHERE age > $1", 30)
 if err != nil {
     log.Fatal(err)
 }
 var plan string
 for rows.Next() {
     var line string
     rows.Scan(&line)
     plan += line + "\n"
 }
 fmt.Println(plan)

This approach helps understand which queries and operations slow down the database and where optimization is needed.