Monitoring your database without pg_stat_activity and pg_stat_user_tables is like checking your health by only looking at your temperature. You can’t really figure out where the problem is if you’re just looking at the big picture. These two key PostgreSQL commands help you not just watch, but actually dig into what’s happening in your database.
What is pg_stat_activity?
pg_stat_activity is a PostgreSQL system view that shows info about all connections to your database. It answers questions like: who’s connected, what queries are running right now, and which connections are just “hanging out” in idle state. This is your go-to tool for checking out what’s currently happening on your server.
Let’s break down the main fields available in pg_stat_activity. The datname field has the name of the database the client is connected to, and usename shows the username that made the connection. application_name tells you the name of the app using the connection, client_addr holds the client’s IP address. backend_start shows when the client connected to the server, state reflects the current connection state (active, idle, idle in transaction), and query contains the query that’s running or was last run.
Example 1: Viewing All Active Connections
To see active connections, run this query:
SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';
Check out the query field. It shows the queries that are currently running. If a query is taking too long, something might be off with it.
Example 2: Analyzing Transaction States
Sometimes connections get “stuck” in the idle in transaction state. This means a transaction was started but not finished, which can lead to locks.
SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';
How do you fix this? If you find a “stuck” transaction, you can end it with this command:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';
Some devs get a little too excited with this. We recommend checking with your team before you “kill” a process. Oops, sorry, I mean — terminate the connection.
Monitoring Table Usage: The pg_stat_user_tables View
If pg_stat_activity lets you watch connections, pg_stat_user_tables tells you about table performance. With it, you’ll find out: how often data is read from or written to tables, which tables are used the most, and where performance bottlenecks might be hiding.
Here are the main pg_stat_user_tables fields to help you analyze tables. relname is the table name, seq_scan shows the number of sequential scans, idx_scan — the number of index scans. n_tup_ins is the number of rows inserted, n_tup_upd — number of rows updated, and n_tup_del — number of rows deleted from the table.
Example 1: Comparing Index Usage and Sequential Scans
If an index is barely used (idx_scan close to zero), your queries to this table can probably be optimized.
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
Sample Result:
If you see that the orders table has a ton of sequential scans (seq_scan), think about adding an index. Imagine the orders table with 3500 sequential scans and only 100 index scans, while the employees table has 50 sequential scans and 1000 index scans — that’s a clear sign you need to optimize.
Example 2: Analyzing Table Operation Counts
To see how “alive” your table data is, check out info about inserted, updated, and deleted rows:
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;
What can you learn? Tables with a high number of inserts (n_tup_ins) and deletes (n_tup_del) might be hot spots in your database. That means their performance deserves some extra attention.
Practical Use: Combining pg_stat_activity and pg_stat_user_tables for Performance Analysis
When you’re analyzing database performance, you can combine info from both sources. First, find long-running queries with pg_stat_activity, then check which tables those queries are hitting using pg_stat_user_tables. If queries are slow on tables with high seq_scan, try optimizing the queries or adding an index.
Example query:
WITH active_queries AS (
SELECT pid, query
FROM pg_stat_activity
WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';
GO TO FULL VERSION