Monitor PostgreSQL by collecting session counts from pg_stat_activity, cache counters from pg_stat_database, and standby status and WAL positions from pg_stat_replication. Put those metrics beside connection capacity, application latency, and collector freshness; page on sustained conditions that threaten service, not on a single cache percentage or a replication-lag field by itself.
The practical goal is to spot exhaustion or degraded replication while there is still time to respond. PostgreSQL supplies the underlying statistics, but it does not prescribe a universal safe connection percentage, cache-hit target, or lag threshold. Set alert limits around your workload, recovery needs, and on-call response.
As an Amazon Associate I earn from qualifying purchases.
What to collect and where
PostgreSQL’s built-in statistics views are the foundation whether you query them with a scheduled collector or feed them into an observability platform. The official PostgreSQL 18 monitoring documentation describes the monitoring context and current released version; the detailed statistics semantics cited below are in the PostgreSQL 19 development documentation. Because PostgreSQL 19 is still a development version, check view columns and behavior against the major version you run and any managed-service restrictions.
Recommended Free Tools
| Question | View or signal | What it tells you |
|---|---|---|
| How many sessions are consuming capacity? | pg_stat_activity |
Live server processes and session details, including database, state, user, and application where visible. |
| How are database block hits and reads trending? | pg_stat_database |
Per-database backend counts and accumulated block-hit and block-read counters. |
| Are directly connected replicas receiving and replaying WAL? | pg_stat_replication |
Primary-side rows for connected standbys, with state, WAL positions, timing fields, and reply time. |
| Is the database I/O affecting users? | Application latency and operating-system I/O alongside PostgreSQL counters | Whether database-level counters correspond to a user-visible performance or storage symptom. |
Statistics are not an instantaneous event stream. A quiet graph can also mean a broken collector or missing permissions, so track scrape time and collector health alongside the database metrics.
#1 Best Overall
How to monitor connection use
Trend total sessions and group them by database, state, user, and application. Keep distinct counts for active, idle, and idle-in-transaction sessions: a rising total has different operational meaning depending on which services and states account for it.
Query sessions by database and state
SELECT datname, state, count(*) AS sessions
FROM pg_stat_activity
GROUP BY datname, state
ORDER BY datname, state;For a dashboard, add application-level breakdowns and a time series for server session use. Compare client-session consumption with the configured max_connections and show remaining headroom. If a pooler is in use, distinguish its client-side pool metrics from PostgreSQL server backends; they describe different parts of the connection path.
Set a useful connection alert
There is no universal safe percentage of max_connections. Reserve capacity for maintenance, failover, migrations, and operator access, then base warnings and pages on measured peak demand and how long it takes your team to add capacity or shed load.
- Warn when use approaches your locally defined operating limit.
- Page when remaining headroom is low enough to threaten the time required to scale, shed load, or recover.
- Alert on sustained active-session growth, prolonged idle-in-transaction sessions, or a sudden rise in connection attempts if your collection setup exposes attempts.
- Show the applications and states driving the count so the responder has a starting point.
Use both a sustained threshold and a growth signal. A short-lived spike may not require a page; a steady climb can forecast exhaustion before the hard limit is reached.
Rank #2
How to calculate and interpret cache hit ratio
A conventional per-database shared-buffer hit ratio is the percentage of counted block accesses that PostgreSQL found in its buffer cache rather than counted as reads:
SELECT datname,
100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0) AS cache_hit_pct,
stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL;NULLIF prevents division by zero when there have been no counted hits or reads. The result is a clue about PostgreSQL shared-buffer behavior, not a pass/fail score for the entire storage stack. PostgreSQL’s I/O counters do not distinguish data fetched from disk from data already available in the operating system’s kernel page cache. Pair the database counters with operating-system I/O and application latency to understand whether reads are causing a service problem.
Use the ratio as a trend, not a universal target
pg_stat_database counters are accumulated and their population can change after a statistics reset or restart. Include stats_reset on the dashboard, and track counter deltas or rates as well as the ratio. A ratio over a long interval can conceal a recent workload change, while a reset can make a new interval look unlike the old one.
Do not page solely because a cache-hit percentage falls below a generic benchmark. The useful baseline depends on the workload, working set, query patterns, and latency objective. Page on a user-facing latency or I/O objective breach, using cache and read counters to help explain the symptom.
How to monitor replication lag
On the primary, inspect pg_stat_replication by standby or application. Its rows describe WAL senders connected to directly attached standbys; a primary’s rows do not show downstream replicas behind another standby.
Track state, WAL positions, and timing
Where the deployed version exposes them, collect state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, and reply_time. The positions help show progress through the WAL pipeline; the state and reply time help distinguish a progressing replica from one that is disconnected or stale.
The time-based lag columns report recent write, flush, or replay intervals. They are not estimates of how long a lagging standby will take to catch up. For an asynchronous replica, replay_lag can approximate how long recent transactions took to become visible on the standby, but it is not a catch-up-time forecast.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Handle idle replicas and missing telemetry deliberately
When a standby has caught up and WAL activity stops, recent time-based measurements may persist briefly and then become NULL. Decide whether your dashboard represents NULL as missing, zero, or last-known value; do not treat an idle, caught-up NULL as proof of failure. Separately alert if an expected replica disappears, is no longer streaming, stops reporting, or exceeds the service’s measured read-consistency or recovery tolerance for a sustained interval.
There is no one-size-fits-all replication-lag threshold in the cited PostgreSQL documentation. Choose it from the application’s read-after-write expectations, recovery objectives, and the time the on-call responder has to act. Combine lag or WAL-distance signals with replica state and telemetry freshness.
Build alerts that lead to action
A useful page signals a condition that threatens the service and gives the responder enough context to act. Define warning periods for sustained degradation and faster paging for immediate outage risk.
- Connection headroom: warn as use approaches the operating limit; page when the reserve may run out before the team can respond.
- Connection pressure: detect sustained active-session growth, long-lived idle-in-transaction sessions, or a sharp rise in connection attempts where available.
- Cache and I/O symptoms: trend hits and reads beside operating-system I/O and latency; page on the service objective breach, not the ratio alone.
- Replica health: check expected replica presence and streaming state, then combine WAL progress, lag indicators, and telemetry freshness.
- Monitoring failure: alert when the collector cannot connect, required statistics are invisible, a metric stops updating, or scrape timestamps become stale.
Choose thresholds from peak load, reserved connection capacity, read-after-write needs, recovery point and recovery time objectives, and the actions available to on-call staff. Keep the alert’s measurement window and required duration explicit so a transient sample does not trigger the same response as sustained risk.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Permissions, freshness, and version compatibility
Ordinary roles can see full details for their own sessions, while details about other sessions may be hidden. Superusers and roles granted pg_read_all_stats can see full session information, according to PostgreSQL’s cumulative statistics documentation. Give the collector only the access needed for its queries, and test those queries using the actual production monitoring role; otherwise, an incomplete view can look like a healthy database.
Cumulative statistics may not update immediately: PostgreSQL accumulates statistics locally in processes and flushes them to shared memory at intervals. A transaction can also retain a statistics snapshot or cached values. Keep monitoring queries short and interpret totals as cumulative measurements, not real-time event records.
At the time of the PostgreSQL 18 documentation consulted here, PostgreSQL 18 was the current release and versions 17, 16, 15, and 14 were listed as supported. Confirm field availability and permissions for your deployed major version. Managed database providers may expose or restrict statistics differently; the PostgreSQL documentation does not establish provider-specific behavior.
Choose a collection approach
You can query the built-in views with a scheduled collector or use a monitoring platform that collects and routes the metrics. The underlying measurements are the same; the operational trade-offs are in coverage, compatibility, alerting, retention, and maintenance.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11| Consideration | Scheduled SQL collector | Monitoring or observability platform |
|---|---|---|
| Metric coverage | Direct access to the views you choose to query. | Check that it collects the required views and application or operating-system signals. |
| Version and provider compatibility | Validate SQL, privileges, and field availability for each deployed version and provider. | Verify supported PostgreSQL versions, managed services, and any provider-specific restrictions. |
| Dashboards, retention, and routing | You operate the storage, visualization, retention, and page routing. | Confirm the platform’s dashboard, retention, and alert-routing features meet your needs. |
| Permissions and operational burden | You control the collector role and maintain query, scheduling, and delivery reliability. | Review the required database privileges and the work needed to configure and maintain collection. |
| Cost | Account for the infrastructure and staff time needed to operate the collection path. | Evaluate cost against the retention, coverage, and operational capabilities required. |
Whichever route you use, make collector connectivity, permissions, and metric freshness observable. Monitoring that silently stops is not evidence that the database is healthy.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
