PostgreSQL Integration

Monitor PostgreSQL connections, query performance, cache behaviour, lock contention, table bloat, autovacuum, WAL and checkpoint activity, and replication health from a single Watchlog agent.

Supported Platforms

• Ubuntu
• Windows
• Source Code
• Docker Container
• Kubernetes

Supported Versions

PostgreSQL 9.6 through 18. Version differences are detected, not assumed — the agent builds its projection per server version and aliases everything to one stable shape, so no parser branches on version.

The boundaries that matter:

Required Privileges

From PostgreSQL 10 onward, the built-in pg_monitor role covers everything:

CREATE USER watchlog_monitor WITH PASSWORD 'your_password';
GRANT pg_monitor TO watchlog_monitor;

On 9.6, grant the underlying roles instead:

GRANT pg_read_all_stats TO watchlog_monitor;

For per-table and per-index detail the user also needs CONNECT on each database you want covered — those views only ever describe the database you are connected to:

GRANT CONNECT ON DATABASE myapp TO watchlog_monitor;

A database the user cannot reach simply does not appear. It never breaks the rest.

pg_stat_statements

Query-level analysis needs the pg_stat_statements extension. It is not installed by default, and Watchlog will never install it — creating an extension changes your database, and that is your decision.

-- postgresql.conf, then restart
shared_preload_libraries = 'pg_stat_statements'

-- once, per database
CREATE EXTENSION pg_stat_statements;

Installed is not the same as readable: the role still needs SELECT, and without pg_read_all_stats it sees <insufficient privilege> instead of the query text. pg_monitor covers both.

When the extension is absent, the Queries and Slow Queries tabs are hidden and a capability note explains why — rather than showing two empty tabs.

What Watchlog Collects

Connections and activity Total, active, idle and idle-in-transaction sessions against max_connections, waiting sessions, longest running query, longest open transaction, and longest idle-in-transaction — the one that quietly holds back vacuum cluster-wide.

Watchlog excludes its own backend from every connection count, so it never reports itself.

Databases Per database: commits and rollbacks with rollback ratio, blocks hit against blocks read, interval cache hit ratio, rows returned / fetched / inserted / updated / deleted, temp files and bytes, deadlocks, conflicts, and database size.

Queries (needs pg_stat_statements) Per statement: calls, total and mean execution time, rows, shared blocks hit and read, temp blocks, WAL bytes, and planning time where available — ranked by impact (calls in the interval × interval mean duration), which is the database time actually consumed.

Locks Lock counts by mode, waiting versus granted, and blocking pairs with both statements.

Tables Live and dead tuples with dead-tuple ratio, sequential versus index scans, rows inserted / updated / deleted / HOT-updated, total, data and index size, heap and index cache hit ratios, and last vacuum / autovacuum timestamps.

Indexes Scans, tuples read and fetched, size, and blocks hit/read, with unused candidates flagged. Primary key, unique and constraint-backed indexes are never removal candidates — they enforce correctness, not performance.

Vacuum Tables needing attention, total dead tuples, tables never vacuumed, vacuums in progress, and whether autovacuum is enabled at all.

WAL and checkpoints WAL records, bytes and full-page images per interval; checkpoints timed versus requested; buffers written by the checkpointer versus by backends.

Replication Per replica: write, flush and replay lag, WAL lag in bytes, state and sync state. On a replica, its own replay position and lag.

Health Score and Insights

A 0–100 score computed server-side, with a written reason on every deduction. Several signals are deliberately context-sensitive, because the naive versions are wrong often enough to be useless:

• A sequential scan is not a defect. PostgreSQL correctly prefers a scan on a small table. Only a large table where scans outnumber index lookups is flagged.
• A high dead-tuple ratio on 30 rows is noise. Both a ratio and an absolute count are required.
• A low cache hit ratio over 200 block accesses means nothing. Volume is required before the score judges — and an analytics workload legitimately reads from disk.
• An old replay timestamp on an idle primary is not lag. The byte gap is the honest signal.
• A long query may be a legitimate report. A long transaction is different — it pins the snapshot and blocks vacuum from reclaiming anything it might still see.
• Rollbacks are an application signal, not a server fault, and are worded neutrally.

Cache hit ratio is computed over the interval, not since the last stats reset. A database up for months reports a lifetime ratio that cannot move; the interval ratio is what reveals a working set falling out of shared_buffers.

Slow Queries

There is no log file to configure. Watchlog reads pg_stat_statements, so query shapes, timings and IO are available as soon as the extension is installed.

slowQuery.thresholdMs (default 100) decides what the dashboard calls slow. The agent, the API and the UI all use that one number so they cannot disagree.

Query text is already normalized by PostgreSQL — literals are $1, $2. Parameter values never leave your host.

Collection Intervals

Per-database views describe only the connected database, so each configured database needs its own connection — which is why storage is throttled well below the 60s tick.

Safety

Watchlog is read-only. It never runs any of the following:

CREATE EXTENSION · ALTER SYSTEM · SET · VACUUM / VACUUM FULL · ANALYZE · REINDEX · CLUSTER · pg_stat_statements_reset() · pg_cancel_backend() · pg_terminate_backend() · any write to any table

Every monitoring statement carries a statement_timeout, so it can never be the thing that hangs a busy server. Index advice is advisory only — no "Drop Index" action is ever offered.

Advanced Options

{
  "service": "postgresql",
  "monitor": true,
  "host": "localhost",
  "port": "5432",
  "username": "watchlog_monitor",
  "password": "your_password",
  "database": ["myapp", "analytics"],
  "ssl": false,

  "advanced": {
    "enabled": true,
    "queries": true,
    "activity": true,
    "locks": true,
    "storage": true,
    "indexes": true,
    "vacuum": true,
    "wal": true,
    "replication": true,
    "maxStatements": 200,
    "maxTables": 300,
    "maxIndexes": 500,
    "maxActivity": 100,
    "maxBlocking": 50,
    "storageIntervalSeconds": 300
  },

  "slowQuery": {
    "thresholdMs": 100
  },

  "activity": {
    "longQuerySeconds": 30,
    "longTransactionSeconds": 60,
    "idleTransactionSeconds": 300
  }
}

The activity thresholds are the ones worth tuning to your workload. A reporting database with five-minute queries should raise longQuerySeconds; a pure OLTP database should lower it.

Multiple Instances

Add one entry per instance. Each is identified by its host:port:

[
  {
    "service": "postgresql",
    "monitor": true,
    "host": "127.0.0.1",
    "port": "5432",
    "username": "watchlog_monitor",
    "password": "your_password",
    "database": ["myapp"]
  },
  {
    "service": "postgresql",
    "monitor": true,
    "host": "127.0.0.1",
    "port": "5433",
    "username": "watchlog_monitor",
    "password": "your_password",
    "database": ["analytics"]
  }
]

Troubleshooting