Monitoring and Observability for Postgres

Snowflake for Developers/Postgres/Monitoring and Observability for Postgres

On this page

Monitoring and Observability for Postgres

Postgres

Elizabeth Christensen

Postgres gives you three sources of observability out of the box. System metrics come from the catalog views and the server itself. Query performance data comes from extensions like pg_stat_statements. Logs capture slow queries, errors, and connection events. Put them together and you can see everything from hardware usage down to a single query's behavior.

System Metrics

Postgres exposes system metrics from two places: the statistics views (pg_stat_* and pg_statio_*) that track database activity, and operating system metrics about the machine Postgres runs on.

Catalog Views (pg_stat_*)

Postgres tracks table access, index usage, connections, and background process activity in shared memory, and you read it with plain SQL. Since Postgres 15, these cumulative stats survive a clean restart (they're still reset after a crash), and you can reset them manually with pg_stat_reset().

View	What It Tracks
`pg_stat_activity`	Current connections, query state, wait events
`pg_stat_user_tables`	Row inserts, updates, deletes, live/dead tuples, vacuum activity
`pg_stat_user_indexes`	Index scan counts and tuple reads
`pg_statio_user_tables`	Buffer cache hits vs. disk reads per table
`pg_stat_checkpointer`	Checkpoint counts, timing, and buffers written (Postgres 17+)
`pg_stat_bgwriter`	Background writer activity (also held checkpoint stats before Postgres 17)
`pg_stat_io`	I/O by backend type, object, and context (Postgres 16+)
`pg_stat_replication`	Replication lag, write/flush/replay positions

If you have older checkpoint queries against pg_stat_bgwriter, they'll break on Postgres 17. Point them at pg_stat_checkpointer instead. Postgres 18 also added pg_stat_get_backend_io(), which gives you I/O stats for a single backend by PID. That's handy when one connection is doing something weird and you want to see exactly what.

Server-Level Metrics

Outside the stats views, you'll want metrics about the underlying system:

CPU utilization. High CPU often points to inefficient queries or missing indexes.
Memory usage. Shared buffers, work_mem allocations, and OS page cache behavior.
Disk I/O. Read/write throughput and IOPS, which matter most for write-heavy workloads.
Storage consumption. Table and index growth, plus WAL piling up.
Network I/O. Bytes sent to clients, which helps spot over-fetching.

These usually come from OS tools (vmstat, iostat, pg_top) or a monitoring agent, since Postgres doesn't expose them through SQL.

Snowflake Postgres captures both Postgres metrics and OS-level metrics (CPU, memory, disk, network) into the event table at SNOWFLAKE.TELEMETRY.EVENTS.

The Essential Metrics to Track

Two metrics we'd put on every dashboard first:

Active Connections

Running out of connections is one of the most common causes of Postgres outages. Watch active, idle, and idle-in-transaction connections against your max_connections setting.

-- All connections by state
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

Cache Hit Ratio

Postgres keeps frequently used data in shared buffers. A cache hit ratio below 99% usually means your working set doesn't fit in memory.

-- Cache hit ratio (should be > 0.99)
SELECT
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

Monitoring Slow Queries with pg_stat_statements

For query performance, pg_stat_statements is the single most useful tool you've got. It tracks execution stats for every normalized query your database runs: total time, average time, number of calls, and rows returned.

Enable the Extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The library also has to be in shared_preload_libraries, which needs a restart. Most managed services, Snowflake Postgres included, handle that part for you.

Find Your Worst Offenders

-- Top 10 most frequently executed queries
SELECT
  left(query, 80) AS query_preview,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Top 10 slowest individual queries (by average execution time)
SELECT
  left(query, 80) AS query_preview,
  calls,
  round(mean_exec_time::numeric, 1) AS avg_ms,
  round(max_exec_time::numeric, 1) AS max_ms,
  rows
FROM pg_stat_statements
WHERE calls > 10  -- filter out one-off queries
ORDER BY mean_exec_time DESC
LIMIT 10;

The query eating the most total time usually isn't the slowest one. It's more often a moderately slow query that runs thousands of times. Sort by both and you'll catch both kinds.

What to Look For

High total_exec_time: the biggest overall contributors to database load
High mean_exec_time: individually slow queries, often good index candidates
High calls with moderate time: frequent queries where even small wins add up
High rows relative to calls: queries returning more data than the app probably needs

Vacuum and Bloat Monitoring

Postgres uses MVCC (multi-version concurrency control), so updated and deleted rows aren't removed right away. They become dead tuples, and the vacuum process reclaims that space. If vacuum falls behind, tables bloat, queries slow down, and in extreme cases you can run into transaction ID wraparound.

Monitor Dead Tuples

-- Tables with the most dead tuples
SELECT
  schemaname || '.' || relname AS table_name,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 10;

Key Things to Watch

dead_pct above 25% means vacuum may be falling behind on that table.
last_autovacuum is NULL or old means autovacuum hasn't run recently. Find out why.
Large tables with no recent vacuum are the ones to worry about most, since bloat grows with table size.

Postgres Logging

Logs catch what metrics can't explain on their own: error messages, lock wait details, and connection lifecycle events. Postgres logging is very configurable. These settings get you what you need for performance work without flooding your logs. See the logging guide for more detail.

Recommended Settings

Parameter	Recommended Value	Purpose
`log_min_duration_statement`	`1000` (ms)	Log any query that takes longer than 1 second
`log_checkpoints`	`on`	Log checkpoint activity to spot I/O pressure (default `on` since Postgres 15)
`log_connections`	`on`	Track who connects and when
`log_lock_waits`	`on`	Log when queries wait for locks longer than `deadlock_timeout`
`log_temp_files`	`0`	Log all temporary file usage (queries spilling to disk)

Tuning Tips

Start with log_min_duration_statement = 1000 and lower it once you know your log pipeline can keep up.
log_connections and log_disconnections together help diagnose connection churn. In Postgres 18, log_connections also accepts a list of stages (receipt, authentication, authorization, setup_durations, or all), so you can log just the pieces you care about. setup_durations is a nice one for figuring out where connection time goes.
log_lock_waits is the setting you want for debugging concurrency issues in write-heavy workloads.
log_temp_files = 0 logs every temp file. If that's too noisy, raise it (for example 10240 for 10MB).

Monitoring Inside Snowflake

Snowflake Postgres includes built-in monitoring dashboards in Snowsight that surface the key metrics without you setting anything up.

What the Dashboards Cover

Query performance: slowest queries, most frequent queries, and throughput over time
Connections: active, idle, and idle-in-transaction counts
Replication: lag for read replicas
Resource utilization: CPU, memory, storage, and I/O

You don't need to install an agent or build dashboards to get started. For exact details on what's shown, check the Snowflake Postgres docs linked below.

The Snowflake Event Table

Snowflake Postgres collects Postgres metrics (connections, database size, WAL size, locks) and system metrics (CPU, memory, disk, network) into the event table at SNOWFLAKE.TELEMETRY.EVENTS. A monitoring agent samples them every few seconds to every 30 seconds, depending on the metric.

You can query these metrics with regular Snowflake SQL, build alerts with Snowflake Tasks, or forward them to an external observability platform.

For the full list of metrics and example queries, see Snowflake Postgres metrics.