How did you handle long/slow queries in PostgreSQL?

To handle long or slow queries in PostgreSQL, I use several approaches:

• Query optimization: analyzing execution plans with EXPLAIN ANALYZE, indexing necessary fields, avoiding unnecessary JOINs and subqueries.
• Application-level timeouts: in Go, I set timeouts for queries using context.Context to prevent resource blocking.
• Pagination and limits: breaking large result sets into smaller parts to reduce load.
• Caching: caching results of frequently repeated queries.
• Asynchronous processing: if possible, executing heavy queries in the background, returning the user an intermediate result or status.

Example with timeout in Go:

ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
defer cancel()

row := db.QueryRowContext(ctx, "SELECT * FROM users WHERE id = $1", userID)
// Process the result

This approach allows controlling execution time and preventing hangs due to long queries.