PostgreSQL Database Statistics with pg_stat_database

calendar Apr 15, 2026 / · 4 min read · postgresql administration monitoring database performance pg_stat_database  ·
Share on: linkedin copy

Monitor PostgreSQL Databases with pg_stat_database

PostgreSQL tracks activity at the database level in the pg_stat_database view. Every database on the server has a row, and each row contains counters for commits, rollbacks, cache hits, disk reads, deadlocks, temp file usage, and more. This is one of the first places to look when you need a quick health check across all databases.

A simple SELECT * returns everything, but the view has over 30 columns. The query below selects the most actionable columns and formats them for easy reading.

Sample Code

 1SELECT
 2    datname                                                    AS database,
 3    numbackends                                                AS connections,
 4    xact_commit                                                AS commits,
 5    xact_rollback                                              AS rollbacks,
 6    ROUND(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2)
 7                                                               AS cache_hit_pct,
 8    blks_read,
 9    blks_hit,
10    tup_returned,
11    tup_fetched,
12    tup_inserted,
13    tup_updated,
14    tup_deleted,
15    conflicts,
16    deadlocks,
17    temp_files,
18    pg_size_pretty(temp_bytes)                                 AS temp_size,
19    stats_reset
20FROM
21    pg_stat_database
22WHERE
23    datname IS NOT NULL
24ORDER BY
25    numbackends DESC;

Notes: Works on all supported PostgreSQL versions. The datname IS NOT NULL filter excludes internal rows for shared objects. Counters accumulate since the last pg_stat_reset() call or server restart. Run SELECT pg_stat_reset() within a specific database to reset its counters.

Code Breakdown

  • pg_stat_database — A system view with one row per database (plus one for shared objects). Populated by the statistics collector.
  • numbackends — The number of active client connections to this database right now. Unlike other columns, this is a live count, not a cumulative counter.
  • xact_commit — Total transactions committed since the last reset. A healthy workload shows this growing steadily.
  • xact_rollback — Total transactions rolled back. A high rollback rate relative to commits may indicate application errors or frequent transaction conflicts.
  • blks_hit / blks_read — Blocks served from the shared buffer cache vs blocks read from disk. The cache hit percentage is derived from these two values.
  • NULLIF(blks_hit + blks_read, 0) — Prevents division by zero on a database with no activity.
  • tup_returned — Rows returned by sequential scans and index scans. A high value relative to tup_fetched can indicate over-scanning (too many sequential scans returning more rows than needed).
  • tup_fetched — Rows actually fetched by index scans. A high ratio of tup_fetched to tup_returned is a sign of efficient index use.
  • conflicts — Queries canceled on standby due to conflicts with recovery. Relevant only on hot standby replicas.
  • deadlocks — Total deadlocks detected. Any non-zero value warrants investigation.
  • temp_files / temp_bytes — Number of temporary files created and their total size. Temp files are written when a sort or hash operation exceeds work_mem.
  • stats_reset — The timestamp of the last counter reset for this database.

Key Points

  • Sort by numbackends DESC to see the most active databases first. Sort by deadlocks DESC or temp_files DESC to surface problem databases.
  • A cache hit percentage below 95% suggests the database is reading too much from disk. Consider increasing shared_buffers or adding indexes to reduce full table scans.
  • Growing temp_files values mean queries are spilling to disk. Increasing work_mem for those sessions can eliminate temp file writes, at the cost of more memory per connection.

Insights and Explanations

Cache hit ratio:

The cache hit ratio is the percentage of block reads served from shared_buffers rather than from disk. A well-tuned OLTP database typically runs at 99% or higher. If you see a database consistently below 95%, it may be underconfigured for memory, running full table scans that should be index scans, or handling a workload that genuinely requires a lot of disk I/O.

Rollback ratio:

Calculate the rollback ratio as xact_rollback / (xact_commit + xact_rollback). A ratio above 1–2% is worth investigating. Common causes include application-level errors that trigger rollbacks, explicit ROLLBACK calls in error handlers, or lock timeouts.

Deadlocks:

Deadlocks in PostgreSQL are detected automatically and one of the involved transactions is canceled. A non-zero deadlock count does not mean the database is broken, but it does mean application transactions are acquiring locks in conflicting orders. Check pg_log for ERROR: deadlock detected entries to identify the queries involved.

Additional Considerations

  • Permissions: Superusers see all rows. Regular users see only databases they have CONNECT permission on, plus a row for their own database.
  • Counter resets: Counters reset on server restart and when pg_stat_reset() is called. The stats_reset column shows when the last reset happened, which is important context for interpreting counts.
  • Shared objects row: The row where datname IS NULL contains statistics for shared system catalogs. The filter in this query excludes it, but you can remove the WHERE clause to include it.

References

Posts in this series