PostgreSQL Database Health

The PostgreSQL Database Health page is a real-time monitoring panel for the application's primary PostgreSQL database, which stores inventory, topology, alarms, settings, and other console data. Use it to tell whether the database is the cause when the console is slow or a page times out.

The page shows server uptime and size, connection pool pressure, cache efficiency, per-table bloat, running queries, and lock contention. It is read-only and does not modify the database.

Overview

The page refreshes automatically every 15 seconds. A line under the title reads Collected Ns ago so you can see how fresh the data is.

Header

The header shows the page title, the Collected Ns ago counter, and two pills:

• Uptime — how long the PostgreSQL server has been running.
• DB size — the total on-disk size of the database.

The controls on the right are:

• Pause / Resume — stops or restarts the 15-second refresh. While paused, a Polling paused chip is shown.
• Refresh — fetches a fresh snapshot immediately. The icon spins while the request is in progress.

Summary cards

Five cards give the headline health numbers. Each card is green, amber, or red according to the thresholds below.

Card	Shows	Green	Amber	Red
DB Size	Total database size, with the table count underneath	—	—	—
Connections	Active connections, with utilization as a percentage of max_connections	Below 50%	50–80%	Above 80%
Cache Hit Ratio	Percentage of reads served from the buffer cache rather than disk, with the index-cache hit ratio underneath	95% or higher	90–95%	Below 90%
Active Queries	Queries running now, with the duration of the longest (or None running)	—	Any query running longer than 30 seconds	—
Dead Tuples	Total dead rows across all tables, with the sub-line Bloat healthy or Consider VACUUM	Below 10,000	10,000–100,000	Above 100,000

A low cache hit ratio means the working set no longer fits in memory, so queries read from disk. Dead tuples are rows that were updated or deleted and not yet reclaimed by autovacuum.

Connection Pool

A horizontal bar breaks the connections down by state:

• Active — connections currently running a query.
• Idle — open connections that are idle.
• Idle in Tx — connections that opened a transaction and are idle inside it. A persistent count is a warning sign, because these connections hold locks and block autovacuum.
• Aborted — idle in transaction (aborted) connections. Shown only when the count is above zero.

The section header shows two limits:

• HikariCP max — the configured maximum size of the console's client-side connection pool. This is the most connections the console opens.
• PG connections — the current total over the server's max_connections, with utilization as a percentage of that maximum.

Comparing the two shows which limit you are near. At the client limit, the application queues for a connection. At the server limit, PostgreSQL refuses new connections.

Table Health

This table shows one row per database table.

Column	Meaning
Table	Table name
Live Rows	Current row count
Dead Rows	Dead tuples awaiting vacuum. Highlighted amber above 1,000.
Bloat %	Estimated wasted space. Grey below 5%, amber 5–20%, red above 20%.
Size	On-disk size of the table
Last Vacuum	When autovacuum last ran on the table (— if never)
Last Analyze	When autoanalyze last updated the table's statistics (— if never)

Use the table to find the tables behind a high Dead Tuples value. A table with high bloat and an old or empty Last Vacuum is the usual cause and may need a manual VACUUM.

Active Queries

This table lists every query currently running, one row per query. When none are running, the section reads No active queries.

Column	Meaning
PID	Backend process ID. Use it to terminate a query with pg_terminate_backend if needed.
User	Database user running the query
State	active (green), idle in transaction (amber), or other (grey)
Duration	How long the query has been running. Amber above 5 seconds, red above 30 seconds.
Wait	The wait event the query is blocked on, or — if it is not waiting
Query	A truncated preview of the SQL

Click a query preview to expand the full SQL in a code panel below the table. Click it again, or click ✕ close, to collapse it.

Lock Waits

This section lists pairs of queries where one query blocks another. When lock waits exist, an amber warning banner also appears near the top of the page. The banner shows the count and advises you to investigate or terminate the long-running blocking transaction.

Column	Meaning
Blocking PID	The process holding the lock
Blocked PID	The process waiting for the lock
Wait	How long the blocked query has waited
Blocking Query / Blocked Query	Truncated previews of both queries

When there is no contention, the section shows No lock contention.

Server info

A footer line shows the PostgreSQL version, the database name, and the server start time.

Workflows

Check whether the database is slowing the console

1. Open the page and review the five summary cards.
2. If Connections is red, the pool is saturated. Check the Connection Pool bar to see whether the limit is the HikariCP (client) maximum or max_connections (server), and look for a high Idle in Tx count holding connections open.
3. If Cache Hit Ratio is amber or red, the working set no longer fits in memory.
4. If Active Queries is amber, find the long-running query in the Active Queries table, click it to read the full SQL, and note its PID.

Find and clear a blocking lock

1. Check for the amber lock wait banner at the top of the page.
2. In Lock Waits, note the Blocking PID and read its blocking query.
3. If it is a stuck transaction, terminate that backend with SELECT pg_terminate_backend(<blocking_pid>).
4. Confirm the banner clears on the next refresh.

Tips

• Use Pause only to hold a snapshot still while you read it. During a fast-moving incident, let the page refresh on its own.
• A high Dead Tuples count together with high Bloat % on one table usually means autovacuum is falling behind on that table.
• A growing Idle in Tx count usually indicates an application bug: a transaction was opened and never committed. These connections hold locks and prevent autovacuum from reclaiming dead tuples.
• A red error banner means the health snapshot request failed. The database may be unreachable, which is itself a useful diagnosis.