9.1 Cumulative Statistics & Activity Monitoring

9.1 Cumulative Statistics & Activity Monitoring

[!NOTE] Core Observability Architecture: PostgreSQL separates system observability into two distinct paradigms: real-time dynamic activity inspection (which reveals what connected backends are doing right now) and cumulative statistics collection (which tracks historical counters of page reads, cache hits, tuple modifications, and transaction commits since the last server reset). Mastering both domains is foundational for diagnosing operational bottlenecks, clearing blocking locks, and maintaining cluster health.

A reliable database administrator must be able to pinpoint runaway queries within seconds, identify rogue client connections that imperil cluster-wide vacuuming, and interpret cumulative statistics views to diagnose cache deficiencies and table bloat before performance degrades.

---

Real-Time Session Inspection: pg_stat_activity

The pg_stat_activity dynamic system view is the primary administrative tool for real-time connection inspection. Every connected client backend, background worker, autovacuum worker, and replication process displays as a single row in this view.

-- Inspect active and idle-in-transaction client sessions
SELECT 
    pid,
    datname,
    usename,
    application_name,
    client_addr,
    backend_start,
    xact_start,
    query_start,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY xact_start ASC NULLS LAST;

Critical Columns in pg_stat_activity

• pid: The operating system process ID of the server backend process handling the connection. This identifier is passed to cancellation and termination functions.
• datname: The name of the specific database to which the backend is connected.
• usename: The name of the role authenticated for this session.
• application_name: The client-provided connection identifier (e.g., psql, pgAdmin 4, order-service-prod, sidekiq). Highly valuable for identifying the originating application tier.
• client_addr & client_port: The IP address and TCP port of the remote client host. For local Unix domain socket connections, client_addr is NULL.
• backend_start: Timestamp when the client process originally connected to the PostgreSQL server.
• xact_start: Timestamp when the current transaction commenced. If no transaction is currently open, this column evaluates to NULL. A wide gap between xact_start and current clock time indicates an open long-running transaction!
• query_start: Timestamp when the currently active query began executing, or when the last query began if the state is idle.
• state_change: Timestamp when the session last transitioned between states (e.g., from active to idle).
• wait_event_type & wait_event: When a backend is blocked waiting for an external event (such as a lightweight lock LWLock, a heavyweight relation lock Lock, disk I/O IO, or network socket ClientRead), these columns name the precise wait event. If the backend is running unimpeded on CPU, both columns return NULL.
• query: The text of the most recently executed query (or currently running query) in this session. Truncated at track_activity_query_size bytes (default 1024 bytes).

---

Session Execution States

The state column reflects the immediate operational condition of the backend process. PostgreSQL defines six valid state values:

---

The Hazard of idle in transaction Sessions

Among all connection states, idle in transaction poses the greatest operational threat to a PostgreSQL cluster.

Why idle in transaction Is Dangerous

1. Snapshots Remain Pinned: When a transaction begins, PostgreSQL assigns it an active transaction ID (XID) and an MVCC snapshot horizon based on the cluster's oldest active transaction.
2. VACUUM Is Completely Blocked: Standard VACUUM and the autovacuum daemon cannot remove any dead tuple version whose deleting transaction ID (xmax) is newer than the oldest active transaction snapshot across the entire cluster! A single abandoned session left in idle in transaction on one database prevents autovacuum from reclaiming dead tuples in every table across the entire database cluster.
3. Catastrophic Table and Index Bloat: As normal application traffic continues performing UPDATE and DELETE operations, millions of dead tuples accumulate in table pages and B-Tree indexes. The engine cannot clean them up because doing so might make them invisible to the pinned session's theoretical future read. Table size balloons, query times degrade, and buffer caches are polluted.
4. Exclusive Lock Retention: If the uncommitted transaction modified a table (INSERT, UPDATE, DELETE) or acquired explicit locks, those row-level or relation-level locks remain held. Subsequent transactions attempting to alter the table or acquire conflicting locks will queue up indefinitely, eventually causing connection pool exhaustion.

Automated Defense: idle_in_transaction_session_timeout

To protect clusters against poorly written applications that open transactions and fail to close them (e.g., hanging while waiting for a remote third-party HTTP call), PostgreSQL provides the idle_in_transaction_session_timeout configuration parameter:

-- Terminate any session that remains idle within an open transaction for more than 60 seconds
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

-- Can also be set at the user or database level
ALTER ROLE app_web_user SET idle_in_transaction_session_timeout = '30s';

When the timeout threshold is breached, the PostgreSQL engine terminates the offending backend process with a FATAL error and rolls back the uncommitted transaction.

---

Remediating Rogue Sessions: pg_cancel_backend vs. pg_terminate_backend

When an administrator identifies a rogue, runaway, or blocked session in pg_stat_activity, two administrative functions are available to remediate the problem. Understanding the precise distinction between them is a standard certification topic.

-- Identify long-running transactions (> 5 minutes)
SELECT 
    pid,
    usename,
    client_addr,
    now() - xact_start AS transaction_age,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle' 
  AND xact_start < now() - INTERVAL '5 minutes'
ORDER BY transaction_age DESC;

1. pg_cancel_backend(pid) (Gentle Cancellation)

SELECT pg_cancel_backend(12845);

• Mechanism: Sends a SIGINT operating system signal to the target backend process.
• Behavior: PostgreSQL cleanly cancels the currently running query (ERROR: canceling statement due to user request), but leaves the client's TCP connection and session open.
• Use Case: Best for canceling a runaway analytical query or unindexed search without disrupting the client application connection pool or requiring the application to reconnect.
• Limitation: Does NOT affect sessions in idle in transaction! If a query is not actively executing, SIGINT is ignored because there is no statement in flight to cancel.

2. pg_terminate_backend(pid) (Forceful Termination)

SELECT pg_terminate_backend(12845);

• Mechanism: Sends a SIGTERM operating system signal to the target backend process.
• Behavior: PostgreSQL forcefully terminates the entire backend process, immediately closing the client connection (FATAL: terminating connection due to administrator command). Any uncommitted transaction is rolled back, and all acquired locks are released.
• Use Case: Mandatory for clearing idle in transaction sessions, stuck locks, or unresponsive client sessions that fail to respond to pg_cancel_backend.
• Privilege Requirement: Regular users can only cancel or terminate backends owned by their own role. To cancel or terminate backends belonging to other users, the executing role must possess the pg_signal_backend predefined role or be a SUPERUSER.

---

Cumulative Statistics Views

While pg_stat_activity provides dynamic snapshots of current state, PostgreSQL's statistics subsystem maintains continuous counters that record cumulative activity across databases, tables, and indexes.

1. pg_stat_database & Cache Hit Ratio

pg_stat_database records cumulative cluster-level and database-level metrics including committed transactions (xact_commit), rolled-back transactions (xact_rollback), disk blocks read from storage (blks_read), disk blocks hit in PostgreSQL's shared buffer cache (blks_hit), and rows fetched (tup_fetched).

SELECT 
    datname,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    ROUND(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

The Buffer Cache Hit Ratio Formula

$\text{Cache Hit Ratio} = \frac{\text{blks\_hit}}{\text{blks\_hit} + \text{blks\_read}} \times 100$

• Interpretation: Measures the percentage of requested 8KB data blocks served directly from PostgreSQL memory (shared_buffers) without issuing a physical read request to the host operating system.
• Production Target: On Online Transaction Processing (OLTP) workloads, the buffer cache hit ratio should consistently exceed 99%. A cache hit ratio slipping below 95% indicates that active working sets exceed shared_buffers, causing excessive disk I/O and query latency.

2. pg_stat_user_tables (Table Access Patterns & Bloat Tracking)

Provides granular operational counters for every table in the user schemas:

• seq_scan: Number of sequential scans initiated on this table.
• seq_tup_read: Number of live rows fetched by sequential scans.
• idx_scan: Number of index scans initiated on this table.
• idx_tup_fetch: Number of live rows fetched by index scans.
• n_live_tup: Estimated number of live tuples currently in the table.
• n_dead_tup: Estimated number of dead tuples awaiting vacuum reclamation.
• n_mod_since_analyze: Estimated modifications since the last ANALYZE.

-- Spot tables suffering from excessive sequential scans and high dead tuple counts
SELECT 
    relname AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_tuple_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

[!TIP] If a large table displays a high seq_scan count paired with a low idx_scan count, queries against that table are scanning every disk block rather than traversing indexes. This is a primary indicator of missing index coverage on frequently filtered columns.

3. pg_stat_user_indexes (Detecting Unused Indexes)

Every index on a table consumes physical disk space and incurs write amplification during INSERT, UPDATE, and DELETE operations. The pg_stat_user_indexes view tracks how often each index is actually utilized by the optimizer:

-- Find unused indexes consuming disk space and slowing down writes
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE indisunique IS FALSE  -- Do not drop unique constraints!
  AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Indexes displaying idx_scan = 0 after months of production traffic represent dead weight: they are never selected by the query planner, yet every DML write to the base table must update them.

---

Resetting Statistics Counters

Cumulative counters in pg_stat_* views increment monotonically from cluster initialization or the last manual reset. To benchmark an application or measure baseline performance over a specific test window, administrators can reset the counters:

-- Reset all cumulative performance counters for the current database
SELECT pg_stat_reset();

-- Reset counters for a single specific table or index
SELECT pg_stat_reset_single_table_counters('customer_orders'::regclass);

---

Exam Tips and Common Pitfalls

• Exam Trap: pg_cancel_backend vs. pg_terminate_backend: Remember that pg_cancel_backend uses SIGINT and keeps the client connection open, making it completely ineffective against idle in transaction sessions (since no query is running to cancel). To disconnect an idle in transaction session and free its locks, you must use pg_terminate_backend (SIGTERM).
• Exam Trap: Scope of idle in transaction Bloat: An abandoned transaction in idle in transaction does not merely bloat the specific table it accessed—it pins the oldest transaction ID (xmin horizon) across the entire database cluster, preventing autovacuum from cleaning dead tuples on any table.
• Exam Trap: Buffer Cache Hit Ratio Formula: On the exam, verify the formula: blks_hit / (blks_hit + blks_read) * 100. Notice that blks_read represents disk reads, so higher blks_hit yields a higher ratio (target > 99%).