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 pillars of observability out of the box: system metrics from catalog views and the server itself, query performance data through extensions like pg_stat_statements, and logs that capture slow queries, errors, and connection events. Together, these give you a complete picture of database health — from hardware resource usage to individual query behavior.

System Metrics

Postgres exposes system metrics through two complementary sources: the statistics catalog views (pg_stat_* and pg_statio_*) that track database-level activity, and the operating system metrics monitored by the server process itself.

Catalog Views (pg_stat_*)

The statistics collector aggregates data about table access, index usage, connections, and background processes. These views are queryable with standard SQL and reset on server restart (or 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_bgwriter`	Checkpoint and background writer activity
`pg_stat_replication`	Replication lag, write/flush/replay positions

Server-Level Metrics

Beyond the catalog views, the Postgres server process itself exposes metrics about the underlying system:

CPU utilization — High CPU often correlates with 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; critical for write-heavy workloads
Storage consumption — Table and index size growth, WAL file accumulation
Network I/O — Bytes sent to clients; useful for detecting over-fetching

These metrics typically require OS-level tools (vmstat, iostat, pg_top) or a monitoring agent to collect, since they aren't exposed through SQL catalog views.

aside positive Snowflake Postgres automatically 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

Every Postgres deployment should track these four categories of metrics:

Active Connections

Connection exhaustion is one of the most common causes of Postgres outages. Monitor 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 accessed 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

Query performance is the second pillar of Postgres observability. The pg_stat_statements extension is the single most valuable tool here — it tracks execution statistics for every query your database runs, including total time, average time, number of calls, and rows returned.

Enable the Extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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 that causes the most total time is often not the slowest individual query — it's a moderately slow query that runs thousands of times. pg_stat_statements helps you find both patterns.

What to Look For

High total_exec_time — The biggest overall contributor to database load
High mean_exec_time — Individual queries that are slow (candidates for index optimization)
High calls with moderate time — Frequently executed queries that benefit from even small optimizations
High rows relative to calls — Queries returning more data than the application likely needs

Vacuum and Bloat Monitoring

Postgres uses MVCC (multi-version concurrency control), which means updated and deleted rows aren't immediately removed — they become "dead tuples." The vacuum process reclaims this space. If vacuum falls behind, tables bloat, queries slow down, and in extreme cases you can hit 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% — Vacuum could be falling behind on this table
last_autovacuum is NULL or old — Autovacuum hasn't run recently, investigate why
Large tables with no recent vacuum — These are the most dangerous; bloat grows proportionally to table size

Postgres Logging

Logs are the third pillar — they capture events that metrics alone can't explain, like error messages, lock wait details, and connection lifecycle events. Postgres logging is highly configurable, and these settings give you the data you need for performance analysis without overwhelming your log volume:

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 detect I/O pressure
`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 (indicates queries spilling to disk)

Tuning Tips

Start with log_min_duration_statement = 1000 and lower it as you gain confidence in your log pipeline's capacity
log_connections and log_disconnections together help diagnose connection churn
log_lock_waits is essential for debugging concurrency issues in high-write workloads
log_temp_files = 0 logs every temp file; set it higher (e.g., 10240 for 10MB) if your logs are too noisy

Monitoring Inside Snowflake

Snowflake Postgres provides built-in monitoring through Snowflake Postgres Insights — a set of pre-built dashboards that surface the most important metrics without any configuration.

What Insights Covers

Query performance — Slowest queries, most frequent queries, and query throughput over time
Connections — Active, idle, and idle-in-transaction connection counts
Replication — Lag metrics for read replicas
Resource utilization — CPU, memory, storage, and I/O metrics

aside positive Snowflake Postgres Insights is available out of the box — no agents to install, no dashboards to build, and no third-party tools required.

The Snowflake Event Table

Snowflake Postgres automatically collects both 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 metrics every 5–30 seconds with no configuration required.

You can query these metrics with standard Snowflake SQL, build alerts with Snowflake Tasks, or forward them to external observability platforms.

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

Conclusion

Related Resources

❄️ Snowflake Docs — Snowflake Postgres Metrics

Monitoring features available in Snowflake Postgres.

🐘 PostgreSQL Documentation — Monitoring

Official PostgreSQL docs on the statistics collector and monitoring views.

❄️ Snowflake Guide — Sending Postgres Logs to Datadog

Step-by-step setup for Datadog's Postgres integration with Snowflake Postgres.

❄️ Snowflake Guide — Monitoring Postgres with Grafana

Build custom Grafana dashboards connected to your Snowflake Postgres instance.

❄️ Snowflake Guide — Sending Postgres Logs to Observe

Full-stack observability for Snowflake Postgres with Observe.

Updated 2026-07-08

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances