Production engineering guide for database monitoring dashboards covering patterns, implementation strategies, and operational best practices.
Database monitoring dashboards are real-time, interactive views of database health, performance, and activity, used to detect anomalies, diagnose issues, and verify operational stability. They matter when a database is the central nervous system of an application—when latency, availability, or data consistency directly impact user experience or business outcomes. They are essential during peak load, after deployments, during outages, and as part of routine operational hygiene.
When query response times rise or throughput drops, a monitoring dashboard must guide engineers from symptom to root cause. The first step is identifying the affected database instance and the nature of the performance problem.
A common signal is a spike in average query duration. This appears as a line chart over time, typically showing 95th percentile latency across all queries. To isolate the cause, the engineer drills down into a query-by-query breakdown, often using a stacked bar chart or a ranked list sorted by total execution time.
SELECT
query,
COUNT(*) AS calls,
AVG(duration) AS avg_duration,
SUM(duration) AS total_duration
FROM query_log
WHERE start_time >= NOW() - INTERVAL '1 hour'
GROUP BY query
ORDER BY total_duration DESC
LIMIT 10;The dashboard visualizes this result, with each query as a row. Hovering over a bar reveals the query text, execution plan, and a histogram of individual execution times. This enables rapid identification of long-running queries—such as a SELECT * FROM orders JOIN customers ON ... WHERE created_at BETWEEN ... that grows from 50ms to 500ms over an hour.
A critical detail is that the query log must include the actual query text, not just a query ID. This is achieved by capturing the query string at execution time, typically via a pg_stat_statements-style collector. The dashboard aggregates this data from multiple instances, showing global performance trends.
If the issue is not a single query but a class of queries, the engineer checks for missing or inefficient indexes. The dashboard displays index usage statistics per table, showing idx_scan, idx_tup_read, and idx_tup_fetch counts. A sudden drop in idx_tup_read suggests that an index is being scanned inefficiently.
The dashboard also surfaces high-load periods through a time-series graph of active connections and concurrent queries. A spike in the number of active connections coincides with a rise in total query duration. This suggests that connection exhaustion is a contributor.
A silent failure is the mismatch between query-level metrics and transaction-level metrics. When a dashboard shows a 200ms average query duration, but end-to-end transaction latency is 600ms, the root cause is often missing or misconfigured instrumentation.
The most common oversight is the lack of a dedicated query_id or trace_id field in the query log. Without it, engineers cannot correlate a single database query with a specific application request. The solution is to inject a trace_id into the query execution context, using SQL commands:
SET application_name = 'user-service';
SET search_path = 'public, api, analytics';
-- In application code:
SELECT set_config('trace_id', 'abc123', true);This allows the dashboard to link each query to a request, enabling cross-service tracing. The dashboard then shows a timeline of query execution within a transaction, with each query as a bar, and color-coded by duration and database connection.
Another failure mode is the misinterpretation of count(*) versus count(1). When a dashboard shows a high number of rows returned per query, but the query is SELECT COUNT(*) FROM large_table, the engineer assumes the query is expensive—but it isn’t. The actual cost lies in the COUNT(*) operation itself, which may be a full table scan. The dashboard must include the EXPLAIN (ANALYZE, BUFFERS) output for each query, shown in a collapsible panel.
When the database slows down, resource pressure is often the culprit. The dashboard must expose CPU usage, memory consumption, disk I/O, and storage metrics.
CPU usage is derived from pg_stat_statements and system-level metrics. The dashboard plots cpu_usage (from top or vmstat) alongside shared_buffers_hit_ratio and wal_write_rate. A drop in shared_buffers_hit_ratio below 90% signals that the database is not caching data effectively.
The dashboard displays a table of buffer usage, showing the most frequently accessed tables and indexes. Each row includes buffers_hit, buffers_read, blks_read, and blks_hit. This allows engineers to identify which tables are causing cache pressure.
Disk I/O is monitored through iostat or df -h, with the dashboard showing read_time, write_time, await, and util metrics for each data volume. A high await (average time to complete I/O) combined with high util (percentage of time the disk is busy) indicates that the storage subsystem is saturated.
A subtle failure mode is the mismatch between fsync frequency and wal_sync_method. The dashboard must show the wal_sync_method setting and its impact on fsync latency. When fsync latency spikes, the dashboard overlays checkpoint_timeout, checkpoint_completion_target, and checkpoint_flush_after to determine whether checkpoints are causing the delay.
Memory pressure is identified through pg_statio_user_tables and pg_buffercache. The dashboard visualizes the buffer_usage as a pie chart, with each slice representing a table or index. The size of the slice is proportional to the number of buffers used.
The dashboard also shows the buffer_hit_ratio over time, with thresholds: < 80% is "high pressure", 80–90% is "moderate", and > 90% is "optimal". A sustained period below 80% triggers an alert.
A common mistake is assuming that shared_buffers size is the only factor in memory efficiency. The dashboard reveals the actual memory usage via pg_buffercache and pg_stat_bgwriter. The buffer_usage column shows how many buffers are in use, and buffer_size is derived from shared_buffers.
When the number of dirty buffers grows rapidly, the dashboard flags the bgwriter_lru_maxpages and bgwriter_lru_multiplier settings. A high bgwriter_lru_maxpages indicates that the background writer is not keeping up with the rate of dirty pages.
The dashboard also plots checkpoint_distance and checkpoint_timeout against the actual time between checkpoints. A discrepancy suggests that checkpointing is not occurring as expected—either due to long-running transactions or high write load.
The health of the database is measured through availability, replication lag, and consistency.
A primary metric is uptime, calculated from pg_is_in_recovery() and pg_last_wal_receive_lsn(). The dashboard shows a continuous line chart of replication_lag—the difference between pg_current_wal_lsn() on the primary and pg_last_wal_receive_lsn() on the replica.
Replication lag is visualized as a time-series graph with two lines: one for primary_lsn, one for replica_lsn, and a third for their difference. A sharp spike in lag indicates that the replica is falling behind, often due to high write load or network latency.
The dashboard includes a table of all replicas, with columns for replication_lag, recovery_state, recovery_start_time, and wal_receive_lsn. When a replica fails to apply WAL records, the dashboard highlights it with a red border and a tooltip showing the last few WAL records.
Another key health signal is checkpoint_interval and checkpoint_timeout. The dashboard plots the number of checkpoints per minute, with the ideal interval derived from checkpoint_timeout. A sudden increase in checkpoint count signals that the database is writing more frequently than expected.
The dashboard integrates with an alerting system that triggers when a metric crosses a threshold. Alerts are defined as SQL queries or expressions over time-series data.
An example alert definition:
SELECT
'high_replication_lag',
1,
NOW(),
'Replication lag exceeds 10 seconds on primary db1',
'SELECT
NOW() AS alert_time,
server_name,
replication_lag
FROM replication_status
WHERE replication_lag > 10000
AND server_name = 'db1'
ORDER BY replication_lag DESC
LIMIT 1'
FROM pg_sleep(1)
WHERE (SELECT COUNT(*) FROM alerts WHERE severity = 'critical' AND resolved = false) = 0;The dashboard displays a list of active alerts, each with a link to the underlying data. Clicking an alert opens a drilldown view showing the full query result, a timeline of the last 60 minutes of replication lag, and a table of recent WAL records.
When an alert is triggered, the dashboard sends a notification via HTTP, typically to a service like alertmanager or webhook. The payload includes:
{
"alert": "high_replication_lag",
"timestamp": "2024-01-15T10:32:15Z",
"labels": {
"instance": "db1",
"database": "orders",
"severity": "critical"
},
"annotations": {
"summary": "Replication lag exceeds 10 seconds",
"description": "Last 10 minutes show lag above 10 seconds. Last received LSN: 1234567890"
}
}The dashboard also supports manual annotations—engineers can add notes, screenshots, or commands to a timeline. These annotations are stored as note entries in a dashboard_notes table and are rendered in the timeline view.
The dashboard includes a view of schema evolution over time. It shows a history of CREATE TABLE, ALTER TABLE, CREATE INDEX, and CREATE FUNCTION statements, grouped by day and enriched with metadata.
The schema changes are derived from pg_stat_statements and a schema_change_log table. The dashboard displays a timeline of schema changes, with each change as a card. Each card includes the SQL statement, execution time, and the user who executed it.
A common failure mode is the lack of a schema_version table. Without it, engineers cannot determine which version of the schema is active across all instances. The dashboard shows a summary of schema_version per instance, with a status indicator: green for up-to-date, yellow for pending, red for outdated.
The dashboard also tracks data changes: inserts, updates, and deletes. It visualizes table_row_counts over time, with a line for each table. A sudden drop in row_count indicates a data loss or truncation.
The dashboard supports filtering by table, date range, and operation type. When a user selects a table, it shows the last 100 INSERT, UPDATE, and DELETE operations, with the full row data in a collapsible panel.
The dashboard includes a view of query patterns—recurring queries, high-frequency queries, and long-running queries. It uses pg_stat_statements to identify the top N queries by execution count and total duration.
The dashboard shows a heatmap of query execution frequency across hours of the day and days of the week. Each cell represents the number of times a query was executed during that period. A query that runs every 15 minutes during business hours appears as a dense vertical band in the heatmap.
The dashboard also identifies query patterns through clustering. It groups similar queries by SQL text, using a fuzzy string comparison algorithm (e.g., Jaccard or cosine similarity). Queries that differ only in literal values are grouped together.
A common oversight is the failure to distinguish between SELECT and SELECT ... FOR UPDATE queries. The dashboard shows a split view: one for SELECT queries, one for SELECT ... FOR UPDATE, each with its own latency, call frequency, and execution plan.
When a query pattern changes—such as a new SELECT ... JOIN ... WHERE query appearing after a deployment—the dashboard highlights it as a "new query" and compares its performance to the previous baseline.
The dashboard supports routine operations: backups, maintenance, and migrations.
A backup status view shows the last 10 backups, with columns for start_time, end_time, duration, size, status, and error_log. The status is derived from a backup_status table with values: started, running, success, failed, partial.
The dashboard displays a timeline of backup activity, with each backup as a bar. Hovering over a bar shows the full pg_dump or pg_basebackup command used, the backup method (e.g., incremental, full, differential), and a log of events.
Maintenance tasks—such as VACUUM, ANALYZE, and REINDEX—are tracked in the dashboard. The dashboard shows the last 20 maintenance runs, with execution time, affected tables, and results.
A key feature is the ability to run ad-hoc maintenance tasks directly from the dashboard. Engineers can click a "Run VACUUM" button, which opens a modal with a form to specify tables, options (FULL, VERBOSE, ANALYZE), and a preview of the generated SQL:
VACUUM (VERBOSE, ANALYZE, FULL)
TABLE orders, customers, products;The dashboard executes the command, shows the output in real time, and updates the maintenance_log table.
This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.
We use cookies for analytics (Google Analytics) and advertising (Google AdSense) to improve your experience and support free content. Privacy Policy