Postgres pg_stat_database: The System-Wide Counters Every Operator Should Watch

Postgres ships a small set of statistical views that together cover most of what you need to know about how a database is being used. The most underused of these is pg_stat_database, which exposes one row per database with about two dozen cumulative counters. The counters answer the questions an operator actually asks during incidents and capacity planning: how many transactions per second, what is the cache hit ratio, how many deadlocks have happened recently, how many temporary files are being written, how often is autovacuum cleaning up, and is anything aborting transactions. None of this requires extensions or external tooling, and yet a substantial fraction of production Postgres deployments either do not sample these counters at all or sample them without computing the rates that make them meaningful.

What the view contains

The columns fall into four conceptual groups. The transaction counters (xact_commit, xact_rollback) tell you the offered workload at coarse granularity. The block IO counters (blks_read, blks_hit) tell you how the buffer cache is performing. The tuple counters (tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted) tell you the row-level workload mix. The remainder are operational signals: deadlocks, temp_files, temp_bytes, conflicts, checksum_failures, and the session_time and active_time additions in Postgres 14 and later.

The counters are cumulative since the last stats reset, which is the source of the most common mistake in using this view: reading the values directly and trying to draw conclusions from absolute numbers. A 99 percent buffer cache hit ratio over the lifetime of a server that has been up for two years tells you nothing about current performance. The right pattern is to sample the counters at intervals (every minute is fine for most uses) and compute the deltas, then derive rates from the deltas.

The cache hit ratio

The single most-cited metric derived from pg_stat_database is the cache hit ratio: blks_hit divided by (blks_hit + blks_read). The traditional rule of thumb is that this should be above 99 percent for a healthy OLTP workload. The rule is approximately right, but it has the same problem as the rest of the view: computed over the cumulative counters, it is dominated by historical workload that may have nothing to do with current state. Compute it on the deltas instead.

A hit ratio that drops below 95 percent on a delta basis is a signal worth investigating. The usual causes are: working set has grown past shared_buffers and you need to either grow the cache or add more memory, a particular workload is doing large sequential scans that pollute the cache, or autovacuum is reading large numbers of cold pages during its passes. The diagnosis depends on which other counters change at the same time.

What the cache hit ratio does not tell you is whether the reads that miss the buffer cache hit the OS page cache or actually go to disk. This is the gap that pg_stat_io filled in Postgres 16, and the two views are complementary rather than substitutes.

The temp_files counter

The temp_files and temp_bytes counters track sort and hash operations that spilled to disk because work_mem was not large enough. This is one of the highest-leverage diagnostic signals in the view because it points directly at a specific cause and a specific remedy. A query that spills to disk runs an order of magnitude slower than the same query that completes in memory. If temp_files is growing at any meaningful rate, the right next step is to identify the queries causing the spills (auto_explain with log_temp_files = 0 captures every query that uses temp space) and either increase work_mem locally for those queries via SET LOCAL, or rewrite them to avoid the large sort or hash.

The work_mem setting is one of the easier knobs to get wrong in either direction. Setting it too low produces constant disk spills and slow queries. Setting it too high produces out-of-memory crashes under load, because every active query can allocate work_mem independently for each sort or hash node. The right pattern is to set the global default conservatively (16-64MB is typical) and elevate per-query for specific known-large operations.

The deadlocks counter

The deadlocks counter is a small integer that should almost always be zero. When it is not, it means application code has hit a circular lock dependency that the deadlock detector resolved by aborting one transaction. Postgres logs the details to the server log by default, but the counter is useful for trend detection: if deadlocks per hour starts climbing, application code has acquired a new lock ordering pattern that conflicts with existing patterns, and the fix is to enforce a consistent lock order across all writers.

The conflicts counter on the same row tracks conflicts between the recovery process on standbys and active queries. On a primary it is always zero. On a standby with hot_standby_feedback off and read queries running against catalog or tuple state that the recovery process needs to reclaim, it counts how many queries were cancelled to let recovery proceed. A non-zero conflicts counter on a standby is a signal that you need to either turn on hot_standby_feedback (with the cost of increased bloat on the primary) or accept periodic query cancellation as the cost of replication freshness.

The checksum_failures counter

This is the one counter that should produce immediate alerts. It counts the number of times Postgres detected a checksum mismatch on a data page, which means the page on disk has been corrupted. The corruption is usually a hardware failure (failing disk, faulty RAM, bad cable) but can be software (kernel bug, filesystem bug, storage controller bug). A non-zero value means data has already been lost or corrupted and the database may not be recoverable without restoring from backup. Alert on any non-zero value, and treat the host as suspect until the root cause is found.

Checksums must be enabled at initdb time or via pg_checksums (in Postgres 12+) on a stopped cluster. They are not on by default in older Postgres versions because of the modest CPU cost. On any modern hardware the cost is negligible and the early warning of corruption is worth far more than the CPU cycles. Every new cluster should have checksums enabled.

The session counters added in Postgres 14

session_time, active_time, idle_in_transaction_time, sessions, sessions_abandoned, sessions_fatal, and sessions_killed were added in Postgres 14. The most operationally useful is idle_in_transaction_time, which tracks how much wall-clock time sessions spent idle inside open transactions. Idle-in-transaction time is one of the largest correlates of dead tuple accumulation and replication lag in production Postgres, because transactions in this state hold their snapshot and prevent autovacuum from cleaning up rows that would otherwise be reclaimable. A growing idle_in_transaction_time rate is a signal that your application is opening transactions, doing some work, and then leaving the transaction open while waiting for external IO. The fix is either to restructure the application to keep transactions short, or to set idle_in_transaction_session_timeout as a safety net.

The sessions counters together tell you about connection turnover. A high sessions_abandoned rate (the client disconnected without disconnecting cleanly) usually points at a connection pool or load balancer with timeouts shorter than the database expects. A high sessions_killed rate points at administrative cancellations, usually statement_timeout firings.

The reset story

pg_stat_reset() resets all the per-table counters in the current database. pg_stat_reset_shared('archiver'), pg_stat_reset_shared('bgwriter'), and pg_stat_reset_shared('io') reset their respective shared views. For pg_stat_database itself, pg_stat_reset_single_table_counters() does not apply (it is per-database not per-table), and the per-database counters are reset only by pg_stat_reset() in that database. The typical pattern is to capture the cumulative state at a baseline event (deploy, configuration change, query optimization) and compare counters before and after.

A common mistake is to reset stats casually during incident response. The cumulative state is a baseline that the rest of your monitoring depends on. Reset only when you have a specific reason and document the reset so future diagnoses do not get confused by the discontinuity.

What pg_stat_database does not show

This view is per-database, so it does not surface per-table or per-query information. For that, pg_stat_user_tables and pg_stat_statements are the right tools. It also does not show per-connection state, which is in pg_stat_activity. It does not show wait events, which are also in pg_stat_activity. It does not retain history: it is a snapshot of cumulative counters that must be sampled and stored externally if you want trends over time. And it does not show the OS page cache, which is the most common cause of cache-hit-ratio misinterpretation.

The right way to use pg_stat_database in production is to sample it at one-minute intervals into a separate metrics store (a small Postgres table works fine at our scale, or a Prometheus exporter at larger scale), compute deltas, derive the standard rates (transactions per second, hit ratio, temp files per minute, deadlock rate), and alert on thresholds appropriate to your workload. The view itself is one of the cheapest sources of operational signal in the database.

Our use across the four products

Our four products (DocuMint, CronPing, FlagBit, WebhookVault) all currently run on SQLite, which does not have an equivalent of pg_stat_database. The closest analog is the .stats command in the sqlite3 shell and the PRAGMA stats interface, both of which expose a much narrower set of counters. When we migrate the database-heavier products (FlagBit and WebhookVault are the leading candidates) to Postgres, sampling pg_stat_database at one-minute intervals into a metrics table will be part of the launch checklist before we start serving production traffic.

The deeper observation is that running Postgres in production is mostly understanding the operational characteristics of conceptually simple mechanisms. pg_stat_database is a few cumulative counters in a small system view. The value comes from sampling them at the right rate, computing the right derived metrics, and alerting on the right thresholds. The view is one of the cheapest sources of operational signal in any production database, and it tends to be underused only because the rate-computation discipline takes a small upfront investment to set up.