Technical Architects Zone (TAZ)

PostgreSQL Monitoring Scripts

Sessions, Connections, Blocking, Locks, Long Queries, Database Size, Replication & Activity Monitoring

Introduction

Effective PostgreSQL monitoring helps DBAs identify abnormal workload patterns, long-running SQL statements, blocking sessions, connection pressure, storage growth, idle transactions, and replication problems before they become major incidents.

PostgreSQL exposes detailed operational information through catalog and statistics views including pg_stat_activity, pg_locks, pg_stat_database, pg_stat_user_tables, and pg_stat_replication.

Important: Monitoring queries provide a point-in-time view of database activity. Use historical monitoring through tools such as PostgreSQL statistics, pg_stat_statements, Prometheus, Grafana, or an enterprise monitoring platform when long-term trends and incident correlation are required.

1. Monitor Database Connections 2. Active Sessions 3. Long-Running Queries 4. Idle-in-Transaction Sessions 5. Blocking Sessions 6. Current Locks 7. Database Sizes 8. Largest Tables 9. Connection Utilization 10. Database Activity Statistics 11. Replication Status 12. Replication Lag

1. Monitor Database Connections

This query displays the number of current PostgreSQL sessions grouped by database.

SELECT
    datname AS database_name,
    COUNT(*) AS connections
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY datname
ORDER BY connections DESC;

2. Active Sessions

Use this query to review sessions currently executing SQL statements.

SELECT
    pid,
    usename,
    datname,
    application_name,
    client_addr,
    state,
    query_start,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

3. Long-Running Queries

The following example identifies active SQL statements running for more than five minutes. Adjust the threshold according to the expected workload.

SELECT
    pid,
    usename,
    datname,
    now() - query_start AS query_duration,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 minutes'
  AND pid <> pg_backend_pid()
ORDER BY query_duration DESC;

Remember: A long-running SQL statement is not automatically a problem. Batch processing, reporting, maintenance, backups, and data-loading operations may legitimately run for longer periods. Investigate the workload before terminating anything.

4. Idle-in-Transaction Sessions

Sessions left idle inside an open transaction can retain locks and prevent cleanup of older row versions.

SELECT
    pid,
    usename,
    datname,
    client_addr,
    xact_start,
    now() - xact_start AS transaction_age,
    state_change,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

5. Blocking Sessions

PostgreSQL provides pg_blocking_pids() to identify sessions preventing another backend from acquiring a required lock.

SELECT
    pid AS blocked_pid,
    usename AS blocked_user,
    datname AS database_name,
    pg_blocking_pids(pid) AS blocking_pids,
    wait_event_type,
    wait_event,
    now() - query_start AS duration,
    query AS blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY query_start;

Caution: Do not terminate a blocking backend simply because it appears in this query. First determine the transaction owner, application impact, rollback implications, and whether the blocking session is performing legitimate work.

6. Current Locks

SELECT
    l.pid,
    a.usename,
    a.datname,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS relation_name,
    a.state,
    a.query
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a
    ON a.pid = l.pid
ORDER BY
    l.granted,
    l.pid;

7. Database Sizes

SELECT
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS database_size,
    pg_database_size(datname) AS size_bytes
FROM pg_database
WHERE datistemplate = false
ORDER BY size_bytes DESC;

8. Largest Tables

This query uses total relation size so the result includes the table and associated indexes and TOAST storage.

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(
        pg_total_relation_size(relid)
    ) AS total_size,
    pg_total_relation_size(relid) AS total_bytes
FROM pg_catalog.pg_statio_user_tables
ORDER BY total_bytes DESC
LIMIT 20;

9. Connection Utilization

SELECT
    current_setting('max_connections')::integer AS max_connections,
    COUNT(*) AS current_connections,
    ROUND(
        COUNT(*) * 100.0 /
        current_setting('max_connections')::integer,
        2
    ) AS connection_utilization_pct
FROM pg_stat_activity;

High connection usage should be investigated before simply increasing max_connections. Review connection leaks, application pooling, session duration, workload concurrency, and tools such as PgBouncer.

10. Database Activity Statistics

SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_data_written,
    deadlocks
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY datname;

11. Replication Status

On a primary server, pg_stat_replication displays information about connected WAL sender processes and standby replication state.

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY application_name;

12. Replication Lag in Bytes

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        replay_lsn
    ) AS replay_lag_bytes,
    pg_size_pretty(
        pg_wal_lsn_diff(
            pg_current_wal_lsn(),
            replay_lsn
        )
    ) AS replay_lag
FROM pg_stat_replication
ORDER BY replay_lag_bytes DESC;

Replication lag measured in bytes and elapsed time represent different dimensions. Monitor both along with WAL generation rate, network throughput, standby replay performance, and the business recovery requirement.

Production Monitoring Checklist

Monitor active and total database connections.
Alert before connection capacity reaches critical levels.
Identify unexpectedly long-running SQL statements.
Monitor idle-in-transaction sessions.
Monitor blocking and lock waits.
Track database and table growth.
Review transaction commits and rollbacks.
Monitor temporary-file generation and deadlocks.
Monitor primary and standby replication state.
Alert on abnormal replication lag.
Capture historical metrics rather than relying only on point-in-time queries.
Correlate PostgreSQL metrics with CPU, memory, storage latency, filesystem capacity, and application telemetry.

Key Takeaways

pg_stat_activity is a primary source for session and query monitoring.
pg_blocking_pids() helps identify sessions responsible for blocking.
pg_locks provides detailed lock-state information.
Idle transactions deserve particular attention because they can retain locks and old row versions.
Database and relation-size functions help track capacity growth.
pg_stat_database provides database-level workload statistics.
pg_stat_replication provides primary-side replication visibility.
Connection pressure should be investigated before increasing max_connections.
A production monitoring strategy should combine PostgreSQL statistics with infrastructure and application telemetry.

Conclusion

PostgreSQL monitoring should provide visibility across sessions, transactions, SQL activity, locks, connections, storage growth, database statistics, and replication. No single query provides a complete picture of database health.

For production systems, these DBA scripts should complement persistent monitoring and alerting so abnormal behavior can be compared against historical baselines. Effective monitoring allows DBAs to identify developing problems before they become application outages or recovery incidents.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone