Technical Architects Zone (TAZ)

PostgreSQL Health Check Scripts

Instance Health, Database Growth, Connections, VACUUM, Dead Tuples, Replication, XID Age & DBA Checks

Introduction

A PostgreSQL health check should examine more than whether the database service is running. DBAs need visibility into database availability, uptime, connection pressure, storage growth, dead tuples, VACUUM and ANALYZE activity, transaction age, replication, indexes, and long-running transactions.

The following scripts provide practical checks that can be incorporated into daily DBA reviews, troubleshooting activities, operational runbooks, and environment assessments.

Important: Health-check results should be evaluated against the normal baseline for the specific PostgreSQL environment. A high value is not automatically a problem, and a low value does not automatically mean that the system is healthy.

Contents

1. PostgreSQL Version 2. PostgreSQL Uptime 3. Database Size Check 4. Connection Health 5. Dead Tuple Check 6. VACUUM and ANALYZE History 7. Tables Needing VACUUM Attention 8. Invalid Indexes 9. Transaction ID Age 10. Long-Running Transactions 11. Replication Health 12. Replication Slot Health 13. Important Configuration Settings

1. PostgreSQL Version

Start a health check by confirming the exact PostgreSQL server version. This is important when reviewing compatibility, patching, features, and support status.

SELECT
    version() AS postgresql_version,
    current_setting('server_version') AS server_version,
    current_setting('server_version_num') AS server_version_number;

2. PostgreSQL Uptime

Database uptime can help identify unexpected restarts and establish when the current PostgreSQL server process started.

SELECT
    pg_postmaster_start_time() AS server_start_time,
    clock_timestamp() - pg_postmaster_start_time() AS uptime;

An unexpectedly recent start time should be correlated with PostgreSQL logs, operating-system events, maintenance activity, failover events, or infrastructure changes.

3. Database Size Check

Monitor database size regularly so unexpected growth can be identified before it becomes a filesystem or storage-capacity issue.

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;

4. Connection Health

This query compares current backend usage with the configured max_connections value.

SELECT
    current_setting('max_connections')::integer AS max_connections,
    COUNT(*) AS current_connections,
    COUNT(*) FILTER (
        WHERE state = 'active'
    ) AS active_connections,
    COUNT(*) FILTER (
        WHERE state = 'idle'
    ) AS idle_connections,
    ROUND(
        COUNT(*) * 100.0 /
        current_setting('max_connections')::integer,
        2
    ) AS utilization_pct
FROM pg_stat_activity;

Important: Do not increase max_connections automatically when utilization becomes high. Investigate application connection pooling, leaked sessions, idle connections, workload concurrency, and memory implications first.

5. Dead Tuple Check

PostgreSQL's MVCC architecture can leave old row versions after UPDATE and DELETE operations. VACUUM is responsible for making reusable space available and maintaining database health.

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    ROUND(
        100.0 * n_dead_tup /
        NULLIF(n_live_tup + n_dead_tup, 0),
        2
    ) AS dead_tuple_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Caution: Statistics such as n_live_tup and n_dead_tup are estimates. Use them to identify candidates requiring investigation rather than treating the values as exact row counts.

6. VACUUM and ANALYZE History

Review when user tables were last vacuumed or analyzed by manual operations or autovacuum.

SELECT
    schemaname,
    relname AS table_name,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    last_analyze,
    last_autoanalyze,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY
    schemaname,
    relname;

7. Tables Needing VACUUM Attention

The following script provides a practical list of tables with significant dead tuple counts. The appropriate threshold depends on table size and workload.

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Remember: A fixed threshold such as 10,000 dead tuples is only an example. For large tables, evaluate the dead-tuple ratio, update/delete rate, autovacuum settings, table size, transaction behavior, and available maintenance capacity.

8. Invalid Indexes

Invalid indexes may remain after certain interrupted or failed index operations and should be investigated by the DBA.

SELECT
    n.nspname AS schema_name,
    t.relname AS table_name,
    i.relname AS index_name,
    x.indisvalid,
    x.indisready,
    x.indislive
FROM pg_index AS x
JOIN pg_class AS i
    ON i.oid = x.indexrelid
JOIN pg_class AS t
    ON t.oid = x.indrelid
JOIN pg_namespace AS n
    ON n.oid = t.relnamespace
WHERE NOT x.indisvalid
   OR NOT x.indisready
ORDER BY
    n.nspname,
    t.relname,
    i.relname;

Caution: Do not automatically drop an invalid index. Determine how it became invalid, whether a concurrent index operation failed, and whether the index is required before deciding on the corrective action.

9. Transaction ID Age

Transaction ID wraparound protection is a critical PostgreSQL maintenance responsibility. Monitor database transaction age so autovacuum has sufficient opportunity to freeze old tuples.

SELECT
    datname AS database_name,
    age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Compare with Autovacuum Freeze Configuration

SELECT
    name,
    setting,
    unit,
    source
FROM pg_settings
WHERE name IN (
    'autovacuum_freeze_max_age',
    'vacuum_freeze_table_age',
    'vacuum_freeze_min_age'
)
ORDER BY name;

Very important: Do not wait until transaction age approaches the wraparound protection limit. Monitor XID age proactively and investigate databases where VACUUM cannot make sufficient progress.

10. Long-Running Transactions

Long-running transactions can retain older row versions and interfere with normal VACUUM cleanup.

SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    xact_start,
    clock_timestamp() - xact_start AS transaction_age,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start;

Caution: Do not terminate a long-running transaction without understanding its application purpose and rollback impact. Large transactions may legitimately require significant time to complete or roll back.

11. Replication Health

Run this query on a primary PostgreSQL server to review connected streaming replication clients.

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;

Check Whether the Current Server Is in Recovery

SELECT
    CASE
        WHEN pg_is_in_recovery()
        THEN 'Standby / Recovery'
        ELSE 'Primary'
    END AS server_role;

12. Replication Slot Health

Replication slots can retain WAL required by consumers. Inactive or abandoned slots can therefore contribute to excessive WAL retention.

SELECT
    slot_name,
    slot_type,
    database,
    active,
    active_pid,
    restart_lsn,
    confirmed_flush_lsn,
    wal_status
FROM pg_replication_slots
ORDER BY slot_name;

Estimate Retained WAL

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    pg_size_pretty(
        pg_wal_lsn_diff(
            pg_current_wal_lsn(),
            restart_lsn
        )
    ) AS retained_wal
FROM pg_replication_slots
WHERE restart_lsn IS NOT NULL
ORDER BY
    pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        restart_lsn
    ) DESC;

Very important: Do not drop an inactive replication slot simply to free disk space. Confirm the owning replica, CDC process, logical replication subscriber, or integration first. Dropping a required slot may require the consumer to be rebuilt or resynchronized.

13. Important Configuration Settings

This query provides a quick view of several settings commonly reviewed during PostgreSQL health and configuration assessments.

SELECT
    name,
    setting,
    unit,
    source,
    pending_restart
FROM pg_settings
WHERE name IN (
    'max_connections',
    'shared_buffers',
    'effective_cache_size',
    'work_mem',
    'maintenance_work_mem',
    'autovacuum',
    'autovacuum_max_workers',
    'autovacuum_naptime',
    'autovacuum_freeze_max_age',
    'wal_level',
    'max_wal_size',
    'min_wal_size',
    'max_replication_slots',
    'max_wal_senders'
)
ORDER BY name;

Important: There is no universal "best" PostgreSQL configuration. Settings must be evaluated against available memory, CPU, storage latency, database size, connection model, query workload, maintenance requirements, replication architecture, and recovery objectives.

Daily PostgreSQL Health Check Summary

Area	Check	Investigate When
Availability	Uptime and restart history	Restart was unexpected
Connections	Current and active sessions	Usage deviates materially from the normal baseline or approaches capacity
Storage	Database and filesystem growth	Growth becomes abnormal or free space approaches operational thresholds
VACUUM	Dead tuples and autovacuum history	Dead tuples accumulate or autovacuum cannot keep pace
XID	Transaction ID age	Age rises unexpectedly toward configured freeze limits
Indexes	Validity and readiness	Invalid or not-ready indexes appear
Replication	Standby state and lag	Standby disconnects or lag exceeds business tolerance
Slots	Slot activity and retained WAL	Inactive slots retain unexpected amounts of WAL

Production Health Check Checklist

• Confirm PostgreSQL instance availability and version.
• Review uptime and investigate unexpected restarts.
• Monitor database and filesystem growth.
• Review connection utilization and connection-pool behavior.
• Identify long-running and idle-in-transaction sessions.
• Review dead tuples and autovacuum activity.
• Review ANALYZE activity and optimizer-statistics freshness.
• Monitor transaction ID age and freeze activity.
• Investigate invalid indexes.
• Monitor replication state and lag.
• Review inactive replication slots and WAL retention.
• Verify backup and WAL-archive health separately.
• Review PostgreSQL logs for errors, warnings, deadlocks, and failed operations.
• Compare important configuration settings with the approved production baseline.
• Correlate database findings with CPU, memory, disk latency, filesystem space, network, and application metrics.

Key Takeaways

• PostgreSQL health checks should cover availability, capacity, maintenance, transactions, indexes, and replication.
• Database uptime helps identify unexpected restart or failover events.
• Connection pressure should be investigated before simply increasing max_connections.
• Dead tuple statistics help identify tables that may require VACUUM investigation.
• VACUUM and ANALYZE history provides visibility into PostgreSQL maintenance activity.
• Transaction ID age is a critical PostgreSQL health metric.
• Long-running transactions can prevent normal removal of old row versions.
• Invalid indexes should be investigated rather than automatically dropped.
• Inactive replication slots can retain significant amounts of WAL.
• Health-check values should always be interpreted against workload-specific baselines.

Conclusion

A PostgreSQL health check should provide a structured view of database availability, capacity, connection usage, MVCC maintenance, transaction age, index state, replication, WAL retention, and important configuration settings.

The greatest value comes from performing these checks consistently and comparing the results against historical baselines. A single snapshot may show the current state, but monitoring trends helps a DBA identify gradual deterioration before it becomes a production incident.

For critical systems, these scripts should be combined with automated monitoring, alerting, backup validation, restore testing, infrastructure telemetry, and a documented operational runbook.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning