pg_stat_database — Query PostgreSQL Database Statistics

Monitor PostgreSQL Databases with pg_stat_database

pg_stat_database is the database-level statistics view in PostgreSQL. It holds one row per database, and each row carries cumulative counters for transactions, block reads, row activity, temporary files and deadlocks. It is a quick first stop when you want to compare every database on a server in a single result set.

A plain SELECT * returns more columns than most health checks need. The query below picks the columns that answer the usual questions and adds a cache hit percentage computed from two of them.

Purpose and Overview

Server-wide tools tell you the instance is busy. They do not tell you which database is responsible. pg_stat_database fills that gap. You can see which database holds the most connections, which one rolls back transactions, which one spills sorts and hashes to temporary files, and which one has seen deadlocks. Every column except one is a running total since the last reset.

That one exception matters when you read the output. According to the manual, numbackends "is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset." So a single run of the query gives you a connection count for right now and totals for everything else. Totals only become useful when you compare them: against the stats_reset timestamp, against an earlier run of the same query, or against another database on the same server.

The query below returns one row per database, ordered by connection count. It computes a cache hit percentage from blks_hit and blks_read, and it formats the temporary file volume with pg_size_pretty.

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: The query names only columns that appear in the PostgreSQL 18 documentation for the view, and none of the columns added in later releases (see Version Compatibility). The datname IS NOT NULL filter drops the row for shared objects, which the manual says has a NULL database name. To reset the counters for the database you are connected to, run SELECT pg_stat_reset(); as a superuser or as a role that has been granted EXECUTE on it, and read the reset warning in Best Practices first.

Code Breakdown

The query reads one view, computes one ratio and formats one size. The sections below follow the columns in the order they appear in the select list.

Connections and Transactions

numbackends is documented as the number of backends currently connected to the database. Connected is not the same as running a query: an idle session counts. If you want to know how many sessions are executing something right now, pg_stat_activity is the view to use for that, because it reports each session separately.

xact_commit and xact_rollback count transactions committed and rolled back in the database. The query shows both so you can see the proportion. A database where rollbacks are a large share of all transactions deserves a look at the application, because the view cannot tell you why a transaction rolled back.

Cache Hit Percentage

blks_hit is the number of times a disk block was found already in the PostgreSQL buffer cache, so a read was not needed. blks_read is the number of disk blocks read. The expression ROUND(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) turns them into a percentage. The cast to numeric avoids integer division, and NULLIF(..., 0) returns NULL instead of raising a division-by-zero error for a database with no block activity yet.

The manual adds a caveat that affects how you read the result: the hit count "only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache." A block counted in blks_read may therefore still have come from the kernel page cache and not from the disk. The statistics chapter says the I/O views "do not differentiate between data which had to be fetched from disk and that which already resided in the kernel page cache" and recommends reading them together with operating system utilities.

Row Activity

tup_returned is documented as the number of live rows fetched by sequential scans plus the number of index entries returned by index scans. tup_fetched is the number of live rows fetched by index scans. The two are not measured in the same unit for index scans, so the pair describes the mix of access methods more than it scores them. tup_inserted, tup_updated and tup_deleted count the rows changed by each kind of statement, which is how you see a write-heavy database next to a read-heavy one.

Conflicts, Deadlocks and Temporary Files

conflicts counts queries canceled because of conflicts with recovery. The manual states that conflicts occur only on standby servers and points to the pg_stat_database_conflicts view for the breakdown. On a primary there is nothing to count there.

deadlocks is the number of deadlocks detected in the database. temp_files and temp_bytes are the number of temporary files created by queries and the total data written to them. The manual notes that all temporary files are counted, "regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting." The query wraps temp_bytes in pg_size_pretty so the figure reads in kilobytes, megabytes or gigabytes.

Reset Time

stats_reset is the time at which these statistics were last reset. Always read it next to the totals. A database with a recent reset and a small deadlock count is not comparable to one with a year of history and the same count.

Key pg_stat_database Columns

The view has more columns than the query selects. Knowing what the others measure tells you when to extend the query.

Session Columns

The manual lists session_time, sessions, sessions_abandoned, sessions_fatal and sessions_killed. sessions is the total number of sessions established to the database. sessions_abandoned counts sessions that ended because the connection to the client was lost, sessions_fatal counts sessions ended by fatal errors, and sessions_killed counts sessions ended by operator intervention. For session_time the manual warns that statistics are only updated when the state of a session changes, so a session that has been idle for a long time does not add that idle time yet.

Checksum Failures

checksum_failures is the number of data page checksum failures detected in the database, or NULL if data checksums are disabled. A NULL here is not a clean bill of health. It means the cluster is not checking page checksums at all.

Parallel Worker Counters

parallel_workers_to_launch and parallel_workers_launched report parallel worker activity for queries on the database. The first is the number of workers planned and the second the number actually started. A persistent gap between the two shows queries that asked for parallel workers and did not get all of them.

The Statistics System Behind the View

The values come from the cumulative statistics system. The manual explains that every process collects statistics locally and updates shared data at appropriate intervals, so a reading can lag the latest activity slightly. To reduce skew between readings, the manual says stats_fetch_consistency can be set to snapshot to minimize skew, at the price of more memory, or to none when statistics are only read once. The track_counts parameter controls whether cumulative statistics are collected about table and index accesses.

Practical Applications

The query is most useful when you run it on a schedule and keep the results, or when you run it twice a known interval apart and subtract. The scenarios below show what to look for.

Finding the Busy Database

On a server that hosts several databases, sort by numbackends to see which one holds the connections, then by xact_commit and the row counters to see which one does the work. A database with many connections and few commits is mostly idle sessions. That is a connection pooling question, not a tuning question.

Spotting Queries That Spill to Disk

A rising temp_files and temp_bytes pair means queries are writing sort or hash data to temporary files. The manual defines work_mem as the memory a query operation, such as a sort or hash table, can use before writing to temporary disk files, and says hash-based operations get work_mem multiplied by hash_mem_multiplier. Turn on log_temp_files to find the statements responsible. The manual says a value of zero logs all temporary file information and that the default of -1 disables the logging. Raising work_mem is one fix, but the manual warns that a complex query can run several sort and hash operations at once and that several sessions can do the same concurrently, so "the total memory used could be many times the value of work_mem."

Judging the Cache Hit Percentage

The percentage tells you how often the PostgreSQL buffer cache already had the block. It does not have a universal pass mark. Compare each database against its own history, and compare after a change. If a low figure persists, the manual's guidance on shared_buffers is a starting point: for a dedicated server with 1GB or more of RAM it calls 25% of system memory "a reasonable starting value" and says an allocation above 40% of RAM is unlikely to work better than a smaller one, because PostgreSQL also relies on the operating system cache.

Investigating Deadlocks

A non-zero deadlocks value means the server detected a deadlock and aborted one of the transactions. The manual says PostgreSQL "automatically detects deadlock situations and resolves them by aborting one of the transactions involved," and that which one is aborted is hard to predict. It also says the best defense is to make every application acquire locks on multiple objects in a consistent order, and that aborted transactions can be retried. To see which statements were waiting, log_lock_waits produces a log message when a session waits longer than deadlock_timeout for a lock.

Checking a Standby

On a hot standby, the conflicts column shows queries canceled by recovery. Run the query there and then look at pg_stat_database_conflicts for the cause of each cancel.

Version Compatibility

The view has grown over time. The release notes for PostgreSQL 14 list the addition of session statistics to pg_stat_database, and the PostgreSQL 18 release notes list parallel_workers_to_launch and parallel_workers_launched as new columns. The sample query uses neither group, so it does not depend on them.

If you extend the query with the session columns, you need version 14 or later. If you add the parallel worker columns, you need version 18. On an older server the statement fails with an error about a column that does not exist. The older columns the query does use, such as xact_commit, blks_hit and deadlocks, appear in the current documentation as ordinary members of the view. This article checked the query's columns against the current documentation only and did not test them on older servers, so run it once on any older server before you schedule it.

Best Practices

• Read stats_reset first — Counters are totals since the last reset, so a figure with no reset time has no meaning.
• Do not reset casually — The manual warns that pg_stat_reset() also resets counters autovacuum uses to decide when to vacuum or analyze, which "can cause autovacuum to not perform necessary work." It recommends a database-wide ANALYZE after a reset.
• Remember what survives a restart — A clean shutdown stores a permanent copy of the statistics so they carry across restarts. After an unclean shutdown, such as an immediate shutdown, a crash, starting from a base backup or point-in-time recovery, all counters are reset.
• Store snapshots and compare them — Save the output on a schedule and subtract one reading from the previous one. That gives you rates, and it is not affected by an earlier reset.
• Grant access deliberately — By default pg_stat_reset() is restricted to superusers. Other users can be granted EXECUTE, so decide who really needs it.
• Pair it with the operating system — Because the buffer cache figures leave out the kernel cache, check disk activity with operating system tools before you change memory settings.

References

• PostgreSQL: pg_stat_database view — column definitions for the view, including numbackends, tup_returned, temp_files, deadlocks and stats_reset.
• PostgreSQL: The Cumulative Statistics System — how statistics are collected, retained across clean shutdowns and reset after unclean ones.
• PostgreSQL: Statistics Functions — pg_stat_reset() and the warning about autovacuum counters.
• PostgreSQL: Deadlocks — automatic detection, aborting one transaction, and consistent lock ordering.
• PostgreSQL: Resource Consumption — work_mem, hash_mem_multiplier and shared_buffers.
• andyatkinson/pg_scripts: pg_stat_database.sql — original seed script, a single select * from pg_stat_database;.