Enhancement in PostgreSQL Dashboard (ID 9628) with Vacuum, Index Usage, and Connection Pooling Metrics

[romerio66]

Hi everyone,

I’ve been working with the official PostgreSQL Database dashboard (GNet ID 9628) and wanted to share three additional panels I’ve built to deepen our insight into database health and performance.

New Metrics Added:

1. Vacuum Activity: Tracks vacuum operations to maintain database health.

2. Index Usage: Monitors how often indexes are used in queries.

3. Connection Pooling: Metrics related to connection pooling (e.g., active or idle database connections) are specific to database resource management.

In my follow‑up post, I’ll clearly outline the steps taken to create those metrics.

I’d love to hear your thoughts on:

1. Any additional metrics or refinements that could make these panels even more valuable?
2. Best practices for alert thresholds related to vacuum lag or connection saturation.

[infofcc3]

You can try This way to Add New Matrics
Step : 1 Prepare your data souce with Actual/Dummy Data using Postgres
that lookkile this

    Vacuum Activity
    autovacuum_count INTEGER,
	    manual_vacuum_count INTEGER,

    Index Usage
    total_seq_scans BIGINT,
    total_index_scans BIGINT,

    Connection Pooling
    active_connections INTEGER,
    idle_connections INTEGER
);

Step : 2 Add panel for Vaccum Activity and apply below write query

SELECT
  now() as time,
  schemaname || '.' || relname as table,
  last_vacuum,
  last_autovacuum
FROM
  pg_stat_user_tables
ORDER BY
  last_autovacuum DESC

Use Table panel
Optional: Add a threshold or annotation if last_autovacuum is older than N hours/days

Step : 3 Add Panel for Index Usage

SELECT
  now() as time,
  relname as table,
  idx_scan,
  seq_scan,
  CASE
    WHEN (seq_scan + idx_scan) = 0 THEN 0
    ELSE ROUND(100.0 * idx_scan / (seq_scan + idx_scan), 2)
  END AS index_usage_percent
FROM
  pg_stat_user_tables
ORDER BY
  index_usage_percent ASC

Use Bar Chart or Table
Highlight tables with low index usage (<20%)

---

Step : 4 Add Panel for Connection Pooling
Monitor total, active, idle, and waiting connections

SELECT
  now() as time,
  state,
  count(*) as connections
FROM
  pg_stat_activity
GROUP BY
  state

Use Pie chart or Bar graph
Set alerts for:
state = 'active' crossing a threshold (e.g., 80% of max_connections)
Too many idle connections (may indicate leakage)

Step : 5 Set Alert Thresholds (Optional but Recommended)

SELECT
  now() - last_autovacuum AS vacuum_lag
FROM
  pg_stat_user_tables
WHERE
  last_autovacuum IS NOT NULL
ORDER BY
  vacuum_lag DESC
LIMIT 1

Set an alert if lag exceeds a threshold (e.g., 12 hours)

SELECT
  COUNT(*)::float / setting::float * 100 as connection_usage_percent
FROM
  pg_stat_activity,
  pg_settings
WHERE
  name = 'max_connections'

Alert if usage > 80%

FINAL OUTPUT Look like