How can you get the active users connected to a PostgreSQL database via SQL?

Deconstructing active sessions

What are the users up to?

Understanding current database activity isn't just about who's connected. Here's how to get current state and queried command of the active users:

SELECT usename, state, query FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction');


Note: An idle in transaction is like a car with its engine running. Someone may have forgotten to "switch it off" (aka end the transaction)!

Tuning into time-specific activity

Need to analyze database activity for a specific time frame? You got that right, use query_start field:

SELECT usename, query_start, query FROM pg_stat_activity
WHERE query_start >= NOW() - INTERVAL '5 minutes' AND state = 'active';


Privileges required to peak in

Remember: Not everyone can peak into pg_stat_activity. You might need to flash your superuser badge to get that access.

Advanced session details

Levels of activity

With active, idle, idle in transaction states, the field state is your real-time report card of the database.

Interpreting client details and state

For real-time tracking, include client application, IP address and state of connection in your SQL, akin to a security camera in code:

SELECT usename, application_name, client_addr, state FROM pg_stat_activity;


Versions matter

While constructing these queries, remember, different dialects of PostgreSQL languages exist. So, mind the versions.

Bow and arrow: Advanced techniques

Understanding user roles and privileges

Beyond the username, knowing their superpowers (privileges), can be a gamechanger. Join pg_stat_activity and pg_user:

SELECT s.usename, u.usesysid, u.usecreatedb FROM pg_stat_activity s
JOIN pg_user u ON s.usename = u.usename;


Statistical family reunion

For a well-rounded view, you can join the pg_stat_activity with pg_locks or pg_stat_statements.

Setting differences

Different databases are like different humans. They react differently to situations depending on their settings and system configuration. Always adjust your queries according to the environment.