Mastering SQL: A Developer’s Guide to Precision and Power (PostgreSQL)

Chapter One: The Secret Art of SQL Session Mastery

This article isn’t just about quick fixes; it’s about arming yourself with the refined knowledge to tackle any challenge head-on. Session query mastery is essential for database health and performance in production-grade environments. As we launch further, we’ll uncover more SQL secrets, each chapter a new weapon in your arsenal. Remember, every SQL command is a powerful tool in the hands of a skilled developer/administrator. Starting with this first chapter: a focused guide on SQL Session Management.

“Talk is cheap. Show me the code.” — Linus Torvalds

This belief echoes loudly in database management, especially when dealing with the complexities of production-grade environments. We’ll explore use cases and optimizations crucial for maintaining robust database systems. Each piece of advice is designed to be immediately practical and impactful in your daily work. Let’s enhance our SQL skills together, one query at a time.

Session-related Queries

Check for Locks

SELECT a.datname,
       l.relation::regclass                             AS locked_item,
       l.locktype,
       l.transactionid,
       l.mode,
       l.granted,
       a.query,
       a.pid,
       EXTRACT(EPOCH FROM (now() - a.query_start)) / 60 AS age_minutes
FROM pg_stat_activity a
         JOIN pg_locks l ON l.pid = a.pid
WHERE a.datname = 'your_database_name'
  AND NOT l.granted
  AND a.state = 'active'
ORDER BY age_minutes DESC;

This query analyses ungranted locks in a specific database, merging information from pg_stat_activity and pg_locks. It provides a snapshot of stalled active queries because they wait for others to release locks. The output includes essential details like the affected database and query, lock characteristics, and how long each query has been waiting. This information is crucial for diagnosing and resolving bottlenecks in busy databases, where managing concurrent transactions efficiently is key to maintaining smooth performance.

Check for long-running queries

SELECT pid,
       usename,
       application_name,
       state,
       query,
       TO_CHAR(NOW() - pg_stat_activity.query_start, 'HH24h MI"m" SS"s"') AS duration
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state <> 'idle'
ORDER BY duration DESC
LIMIT 100;

This SQL query identifies queries running over five minutes, excluding idle sessions. It displays user and application details, state, and query duration in seconds, sorted by longest duration. Limited to the top 100 results, it offers a quick overview of the most significant long-running queries in the database. Change the interval according to your definition of “long-running”.

List sessions

SELECT pid           AS process_id,
       usename       AS username,
       datname       AS database_name,
       client_addr   AS client_address,
       application_name,
       backend_start,
       state,
       state_change,
       TO_CHAR(NOW() - backend_start, 'HH24h MI"m" SS"s"') AS duration,
       query
FROM pg_stat_activity
WHERE state != 'idle'
  AND (datname = 'your_database_name' OR datname IS NOT NULL) -- Adjust as needed
  AND (state = 'active' OR query != '<IDLE>') -- Include active queries or non-idle ones
ORDER BY duration DESC
LIMIT 100; -- Adjust the limit as needed

This query offers a detailed snapshot of non-idle sessions tailored for specific databases. The session duration is conveniently converted into hours, minutes and seconds for clearer analysis. Additionally, it reveals the active query for sessions in an active state. The results are sorted by the longest session duration and limited to the top 100, making it easier to identify and focus on the most significant sessions. This query is particularly useful for database administrators monitoring and optimising database performance.

Cancel session

SELECT pid, query, state, pg_cancel_backend(pid) AS cancel_attempted
FROM pg_stat_activity
WHERE pid = [Your Query PID]  -- Replace with your target PID
  AND pid <> pg_backend_pid() -- Prevents canceling the current session
  AND state = 'active'; -- Ensures that only active queries are targeted

This query aims to cancel an active query identified by a specific PID. Including the pid <> pg_backend_pid() condition prevents accidentally cancelling the session that executes the command. The query also checks that the targeted PID represents an active query (state = 'active'), ensuring that it doesn't attempt to cancel idle or already completed processes. Additionally, it returns the PID, the query text, the state of the process, and a boolean indicating if the cancel attempt was made, providing more context about the action and its target, which is especially useful for monitoring and auditing purposes in a production environment.

Terminate session

DO
$$
    DECLARE
        r RECORD;
    BEGIN
        FOR r IN SELECT pid, query
                 FROM pg_stat_activity
                 WHERE usename = 'username'      -- repalce with your username
                   AND datname = 'database_name' -- repalce with your database name
                   AND state = 'active'
                   AND pid <> pg_backend_pid()
                   AND pid = YOUR_PID_TO_TERMINATE -- repalce with your pid to terminate
            LOOP
                RAISE NOTICE 'Terminating PID %: %', r.pid, r.query;
                -- Uncomment the below line to actually terminate the backends
                -- PERFORM pg_terminate_backend(r.pid);
            END LOOP;
    END
$$;

This script targets specific active backend processes for termination, focusing on a particular user and database. It excludes the current executing session for safety and loops through each eligible session, logging its PID and query. This method allows for a preliminary review of the sessions to be terminated, adding a precautionary step. The termination command within the script is initially commented, enabling you to assess the impact before proceeding. This approach is useful for precise session management, reducing the risk of unintentionally disrupting important processes. Furthermore, because I terminated the wrong PID too often!

pg_cancel_backend vs. pg_terminate_backend

pg_terminate_backend and pg_cancel_backend serve different purposes for managing processes. pg_terminate_backend(pid) is the more drastic measure, forcefully ending a backend process. It's effective for unresponsive processes but can leave issues like unfinished transactions. On the other hand, pg_cancel_backend(pid) is gentler, requesting a query to stop without killing the entire process. This allows for tidy cleanup but might not be immediate. Use pg_cancel_backend for safely stopping queries and reserve pg_terminate_backend for critical situations where immediate termination is necessary. Remember, pg_terminate_backend should be used with caution due to its potential side effects.

Do you have any savvy SQL session query insights, or have you faced unique challenges in your PostgreSQL? Share your experiences and top query picks in the comments below. Your contributions will improve our collective understanding and help fellow developers :)

Resources

• PostgreSQL: SQL Session Management, Queries:
  https://gist.github.com/nik-sta/ef5b5577b6d4a449485e723ff478af8b

Feedback and updates matter 📝☕. Enjoy my articles? Show support with claps, follows, and coffee donations. I keep all content regularly updated.

Support: ko-fi.com/niksta | Discord: devhotel.io

Disclosure: This article was assisted by ChatGPT (OpenAI) and refined using Grammarly for spelling and style. Visuals created with Midjourney’s AI tool.