DEV Community

leroykayanda
leroykayanda

Posted on • Edited on

postgres troubleshooting queries

get long running queries

select
    pg_stat_activity.usename,
    pid,
    pg_stat_activity.query_start,
    now() - pg_stat_activity.query_start AS duration,
    query,
state
FROM pg_stat_activity
where state = 'active' and (now() - pg_stat_activity.query_start) > interval '60 minute'
Enter fullscreen mode Exit fullscreen mode

query to find query that has locked rows

SELECT
    concat(client_addr, ':', client_port) AS origin_of_the_statement,
    datname AS database_name,
    usename AS database_user,
    pid,
    usename,
    pg_blocking_pids (pid) AS blocked_by_pid,
    concat(wait_event_type, ':', wait_event) wait_event,
    query AS blocked_statement
FROM
    pg_stat_activity
WHERE
    CARDINALITY(pg_blocking_pids (pid)) > 0
    AND pid <> pg_backend_pid();
Enter fullscreen mode Exit fullscreen mode

Then to get process details

SELECT datname,pid,usename,query_start,wait_event_type,wait_event,state,query
FROM pg_stat_activity where pid=27581;

To get queries that have been running for a long time.

select
    pg_stat_activity.usename,
    pid,
    pg_stat_activity.query_start,
    now() - pg_stat_activity.query_start AS duration,
    query,
state
FROM pg_stat_activity
where state = 'active' and (now() - pg_stat_activity.query_start) > interval '0 minute'
Enter fullscreen mode Exit fullscreen mode

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up