Skip to content
Tutorials Center
My learning

Topics

Lesson 78 of 90 ยท Security and Administration

Monitoring and System Catalogs

Monitor PostgreSQL with pg_stat_activity, pg_stat_database and the system catalogs: find long queries, blockers, table sizes and cache hit ratio, and read the logs.

A running PostgreSQL server is not a black box. It keeps live statistics about every connection, every table and every block it reads, and it describes its own structure in ordinary tables called the system catalogs. You query all of it with plain SQL. Once you know a handful of views, you can answer the questions that come up in every incident: who is connected, what are they running, who is blocking whom, which tables are huge, and is the cache doing its job?

The statistics views and catalogs belong to the server, so the PostgreSQL queries in this lesson are read-only. SQLite has a much smaller catalog (sqlite_master and pragma_* functions), and you can query that live, which makes a useful contrast. A third live example turns the idea of "find the long-running query" into a query over a sample table.

Where PostgreSQL tells you things

Activity pg_stat_activity shows who runs what, right now.
Statistics pg_stat_database and pg_stat_user_tables count what happened.
Catalogs pg_class, pg_attribute and friends describe every object.
Logs The server log records slow queries, waits and errors.
1 / 2

Who is connected: pg_stat_activity

pg_stat_activity has one row per server process: client connections plus background workers such as the autovacuum launcher. These columns do most of the work:

Column Meaning
pid process id; use it to cancel or terminate
usename, datname, application_name, client_addr who, where and from what
state active, idle, idle in transaction, ...
query, query_start the current or last statement and when it began
xact_start when the open transaction began
wait_event_type, wait_event what the process is waiting for, if anything
backend_type client backend, autovacuum worker, walwriter, ...

Two queries cover most days. The first lists what is running longest:

SQL
SELECT pid, usename, state, now() - query_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

The second hunts for the sessions that do the most hidden harm, transactions left open and doing nothing. An idle in transaction session holds locks and stops vacuum from cleaning up dead rows:

SQL
SELECT pid, usename, now() - xact_start AS open_for, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

You only see other users' query text if you are a superuser or a member of pg_read_all_stats; for other sessions the query column is hidden. To find who is blocked and who blocks them, combine wait_event_type = 'Lock' with pg_blocking_pids(pid):

SQL
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

SQLite has no sessions to list, but the "longest running first" logic is pure SQL. This stand-in stores a few fake activity rows with fixed times and flags the ones that deserve attention:

SQL A stand-in for pg_stat_activity: find the trouble

Press Run (or Ctrl + Enter) to see the result here.

Session 4102 has been idle inside a transaction for almost 49 minutes, which is the pattern that quietly causes bloat and lock pile-ups.

Stopping a query: cancel or terminate

Once you have a pid, two functions act on it:

SQL
SELECT pg_cancel_backend(4103);      -- cancels the current query, keeps the connection
SELECT pg_terminate_backend(4102);   -- closes the whole connection (rolls back its transaction)

Try pg_cancel_backend first. It is the polite option: the query stops with "canceling statement due to user request" and the application can carry on. pg_terminate_backend is for sessions that don't respond or sit idle in a transaction, and it rolls back whatever that session had not committed. You need to be the same role as the target, a member of its role, or a member of pg_signal_backend. To prevent the problem rather than fix it, set statement_timeout, idle_in_transaction_session_timeout and lock_timeout for application roles.

Counting what happened: statistics views

The cumulative statistics views accumulate counters since the last reset (pg_stat_reset() or a crash). The most useful ones:

  • pg_stat_database: one row per database with xact_commit, xact_rollback, blks_read (from disk or the OS cache), blks_hit (found in PostgreSQL's shared buffers), deadlocks, temp_files and temp_bytes.
  • pg_stat_user_tables: per table, seq_scan, idx_scan, n_live_tup, n_dead_tup, last_autovacuum and last_autoanalyze. A big table with many sequential scans and no index scans is a tuning candidate.
  • pg_stat_user_indexes: index usage; idx_scan = 0 over a long time marks an unused index (see index maintenance).
  • pg_stat_bgwriter, pg_stat_wal, pg_stat_replication: checkpoints, WAL volume and replica lag.

The cache hit ratio compares blocks found in shared buffers with blocks that had to be read:

SQL
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       xact_commit, xact_rollback, deadlocks, temp_files
FROM pg_stat_database
WHERE datname = current_database();

On a busy OLTP system you usually expect well above 99 percent. A lower number means the working set doesn't fit in memory or a query reads far more than it needs. Treat it as a hint, not a verdict: blks_read can still be served by the operating system's cache, and a nightly report that scans everything will lower the ratio without any real problem. This is the same formula as a live query over sample numbers:

SQL Cache hit ratio from sample counters

Press Run (or Ctrl + Enter) to see the result here.

NULLIF(..., 0) turns a division by zero into NULL, which matters for an idle database like scratch.

Sizes: where did the disk go?

PostgreSQL has a family of size functions. Wrap them in pg_size_pretty for human-readable output:

SQL
SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS total,   -- table + indexes + TOAST
       pg_size_pretty(pg_relation_size(oid))       AS heap_only,
       pg_size_pretty(pg_indexes_size(oid))        AS indexes
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

pg_total_relation_size is the number to compare with your disk. If the indexes are bigger than the table, look for duplicates or unused ones.

Catalogs: information_schema vs pg_catalog

PostgreSQL describes itself in two places. information_schema is the SQL-standard view of the metadata (tables, columns, table_constraints, routines). It looks the same in MySQL and SQL Server, and it only shows objects you may access. pg_catalog holds the real tables the server itself uses, with everything PostgreSQL-specific: pg_class (tables, indexes, views, sequences), pg_attribute (columns), pg_namespace (schemas), pg_index, pg_type, pg_proc (functions), pg_roles, pg_settings.

SQL
-- Portable: works on other databases too
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;

-- PostgreSQL-specific and more detailed
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS type, a.attnotnull
FROM pg_attribute a
WHERE a.attrelid = 'public.orders'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Use information_schema for portable tooling and pg_catalog when you need PostgreSQL details (index definitions, partitioning, comments, enum values). Two handy shortcuts are the regclass type ('public.orders'::regclass turns a name into a table's object id) and pg_get_indexdef(indexrelid), which rebuilds the CREATE INDEX statement. psql's \d commands are just prepared queries against these catalogs: start psql with -E to see them.

SQLite keeps the same information in a single table, sqlite_master, and exposes table details through table-valued pragma_ functions. Try both on the bookstore sample:

SQL The SQLite catalog (sqlite_master and pragma_table_info) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The contrast is useful: SQLite's catalog is one table you can read and one function per detail, while PostgreSQL spreads the same facts over many catalogs and adds statistics, sessions and settings that SQLite doesn't have.

Logs and settings

Statistics tell you how much; logs tell you what and when. These settings in postgresql.conf make the log useful:

TEXT
logging_collector = on
log_min_duration_statement = 500ms     # log every statement slower than this
log_lock_waits = on                    # report waits longer than deadlock_timeout
log_checkpoints = on
log_temp_files = 0                     # log every temp file (sorts that spilled to disk)
log_line_prefix = '%m [%p] %q%u@%d '   # time, pid, user, database

Check current values with SHOW log_min_duration_statement; or SELECT name, setting, source FROM pg_settings WHERE name LIKE 'log%'; (the source column tells you where each value came from, which solves most "why is this setting different" puzzles). For aggregated query statistics see pg_stat_statements.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which state in pg_stat_activity is a warning sign because it holds locks and blocks vacuum while doing no work?

    An idle in transaction session has an open transaction. Its locks and old snapshot can block other sessions and prevent cleanup of dead rows.
  2. 2. What is the difference between pg_cancel_backend(pid) and pg_terminate_backend(pid)?

    Cancel is the gentle option that interrupts the running statement. Terminate ends the whole session and rolls back its open transaction.
  3. 3. How is the cache hit ratio calculated from pg_stat_database?

    It is the share of block requests found in shared buffers: hits divided by hits plus reads.
  4. 4. When would you query pg_catalog instead of information_schema?

    information_schema is the portable standard view; pg_catalog holds the full PostgreSQL-specific detail.

Summary

  • pg_stat_activity lists sessions: look for long active queries, idle in transaction sessions and blocked sessions (pg_blocking_pids).
  • Cancel a query with pg_cancel_backend, close a session with pg_terminate_backend, and prevent trouble with statement_timeout, idle_in_transaction_session_timeout and lock_timeout.
  • pg_stat_database and pg_stat_user_tables accumulate counters; cache hit ratio is blks_hit / (blks_hit + blks_read).
  • Use pg_total_relation_size and pg_size_pretty for sizes.
  • information_schema is portable; pg_catalog is complete. SQLite has just sqlite_master and pragma_ functions.
  • Turn on useful logging (log_min_duration_statement, log_lock_waits, log_temp_files) and read it with the statistics.

Next: PostgreSQL from Python, Node.js, PHP and Java.