WittCode💻

Who's Connected to Your Postgres Database?

By

Learn how to analyze active Postgres database connections. This includes idle and active connections, as well as how close we are to hitting Postgres's connection limit.

Table of Contents 📖

pg_stat_activity

Postgres provides the pg_stat_activity system view, which gives real-time insights into active database connections, including user sessions, query states, and even potential bottlenecks. The command below provides a list of all active connections:

SELECT pid, usename, datname, client_addr, application_name, state, query 
FROM pg_stat_activity;

One of these active connections is the one I just ran. We can see this in the query column. From this output, we can see that the pg_stat_activity table gives information such as:

  • Username of connected clients
  • Client IP addresses
  • Current query being executed
  • Transaction state
  • Query start times (to detect long-running queries)

Analyzing Connections

If we want to see active running queries, we can filter by the state column for "active".

SELECT pid, usename, query, state, query_start 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY query_start;

Another state is "idle" which means the connection is consuming resources but isn't doing any work.

SELECT pid, usename, client_addr, state, query 
FROM pg_stat_activity 
WHERE state = 'idle';

WARNING: If you see too many idle connections, your application might not be closing database connections properly.

We can also use this view to see how close we are to hitting Postgres's connection limit.

SELECT count(*) AS active_connections, 
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections 
FROM pg_stat_activity;

This setting determines the total number of concurrent connections that the PostgreSQL server allows, including all clients and administrative connections. If the sum of all active connections from all clients, pools, and applications exceeds max_connections, PostgreSQL will reject new connection attempts.

Who's Connected to Your Postgres Database?