Building Real-Time Dashboards with Grafana and PostgreSQL

Why Grafana + PostgreSQL?

You do not need a specialized time-series database for most dashboard use cases. PostgreSQL handles time-series queries remarkably well, and you probably already have it running. Grafana connects to PostgreSQL natively with a powerful query editor.

<div style="margin:2.5rem auto;max-width:600px;width:100%;text-align:center;"><svg viewBox="0 0 600 200" xmlns="http://www.w3.org/2000/svg" style="width:100%;height:auto;"><rect width="600" height="200" rx="12" fill="#1a1a2e"/><rect x="15" y="10" width="570" height="25" rx="6" fill="#6366f1" opacity="0.3"/><circle cx="30" cy="22" r="4" fill="#ef4444"/><circle cx="42" cy="22" r="4" fill="#f59e0b"/><circle cx="54" cy="22" r="4" fill="#2dd4bf"/><text x="300" y="27" text-anchor="middle" fill="#ffffff" font-size="10" font-family="system-ui">Monitoring Dashboard</text><rect x="20" y="45" width="130" height="55" rx="6" fill="#6366f1" opacity="0.2"/><text x="85" y="65" text-anchor="middle" fill="#94a3b8" font-size="9" font-family="system-ui">CPU Usage</text><text x="85" y="88" text-anchor="middle" fill="#2dd4bf" font-size="18" font-family="system-ui" font-weight="bold">23%</text><rect x="160" y="45" width="130" height="55" rx="6" fill="#6366f1" opacity="0.2"/><text x="225" y="65" text-anchor="middle" fill="#94a3b8" font-size="9" font-family="system-ui">Memory</text><text x="225" y="88" text-anchor="middle" fill="#f59e0b" font-size="18" font-family="system-ui" font-weight="bold">6.2 GB</text><rect x="300" y="45" width="130" height="55" rx="6" fill="#6366f1" opacity="0.2"/><text x="365" y="65" text-anchor="middle" fill="#94a3b8" font-size="9" font-family="system-ui">Requests/s</text><text x="365" y="88" text-anchor="middle" fill="#6366f1" font-size="18" font-family="system-ui" font-weight="bold">1.2K</text><rect x="440" y="45" width="140" height="55" rx="6" fill="#6366f1" opacity="0.2"/><text x="510" y="65" text-anchor="middle" fill="#94a3b8" font-size="9" font-family="system-ui">Uptime</text><text x="510" y="88" text-anchor="middle" fill="#2dd4bf" font-size="18" font-family="system-ui" font-weight="bold">99.9%</text><rect x="20" y="110" width="560" height="80" rx="6" fill="#6366f1" opacity="0.1"/><text x="45" y="125" fill="#94a3b8" font-size="8" font-family="system-ui">Response Time (ms)</text><polyline points="40,170 80,155 120,160 160,140 200,145 240,135 280,150 320,130 360,125 400,140 440,120 480,115 520,125 560,110" fill="none" stroke="#6366f1" stroke-width="2"/><polyline points="40,170 80,155 120,160 160,140 200,145 240,135 280,150 320,130 360,125 400,140 440,120 480,115 520,125 560,110" fill="url(#chartGrad)" stroke="none" opacity="0.3"/><defs><linearGradient id="chartGrad" x1="0" y1="0" x2="0" y2="1"><stop offset="0%" stop-color="#6366f1"/><stop offset="100%" stop-color="transparent"/></linearGradient></defs><line x1="40" y1="130" x2="560" y2="130" stroke="#e2e8f0" stroke-width="0.3" opacity="0.2"/><line x1="40" y1="150" x2="560" y2="150" stroke="#e2e8f0" stroke-width="0.3" opacity="0.2"/><line x1="40" y1="170" x2="560" y2="170" stroke="#e2e8f0" stroke-width="0.3" opacity="0.2"/></svg><p style="margin-top:0.75rem;font-size:0.85rem;color:#94a3b8;font-style:italic;line-height:1.4;">Real-time monitoring dashboard showing CPU, memory, request rate, and response time trends.</p></div>

At TechSaaS, our monitoring stack runs Grafana backed by PostgreSQL and Loki. It tracks everything from Docker container health to business metrics — all on a single self-hosted server.

Setting Up

If you are using Docker Compose:

services:
  grafana:
    image: grafana/grafana:11.0.0
    container_name: grafana
    restart: unless-stopped
    ports:
      - "3000:3000"
    environment:
      GF_SECURITY_ADMIN_USER: admin
      GF_SECURITY_ADMIN_PASSWORD: ${GRAFANA_PASSWORD}
      GF_INSTALL_PLUGINS: grafana-clock-panel,grafana-piechart-panel
    volumes:
      - grafana_data:/var/lib/grafana
    networks:
      - padc-net

Data Modeling for Dashboards

Structure your PostgreSQL tables with dashboards in mind:

-- Time-series events table
CREATE TABLE app_events (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    user_id UUID,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create a time-based index for fast range queries
CREATE INDEX idx_events_created_at ON app_events
    USING BRIN (created_at);

-- Partitioned table for high-volume data
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    name VARCHAR(100) NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    labels JSONB DEFAULT '{}'
) PARTITION BY RANGE (time);

-- Create monthly partitions
CREATE TABLE metrics_2025_11 PARTITION OF metrics
    FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE metrics_2025_12 PARTITION OF metrics
    FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

Connecting Grafana to PostgreSQL

1. Navigate to Connections > Data Sources > Add data source 2. Select PostgreSQL 3. Enter connection details: - Host: postgres:5432 (or your service name) - Database: analytics - User: grafana_reader (always use a read-only user) - TLS/SSL Mode: disable (internal Docker network) 4. Click Save & Test

Create a read-only database user:

CREATE USER grafana_reader WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE analytics TO grafana_reader;
GRANT USAGE ON SCHEMA public TO grafana_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO grafana_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO grafana_reader;

Building Dashboard Panels

Panel 1: Requests Per Minute (Time Series)

SELECT
  date_trunc('minute', created_at) AS time,
  event_type,
  COUNT(*) AS requests
FROM app_events
WHERE
  created_at >= $__timeFrom()
  AND created_at <= $__timeTo()
GROUP BY 1, 2
ORDER BY 1

Grafana's $__timeFrom() and $__timeTo() macros automatically map to the dashboard time picker.

Panel 2: Active Users (Stat Panel)

SELECT COUNT(DISTINCT user_id) AS "Active Users"
FROM app_events
WHERE created_at >= NOW() - INTERVAL '24 hours'

Panel 3: Revenue by Day (Bar Chart)

SELECT
  date_trunc('day', created_at) AS time,
  SUM((metadata->>'amount')::numeric) AS revenue
FROM app_events
WHERE
  event_type = 'purchase'
  AND created_at >= $__timeFrom()
  AND created_at <= $__timeTo()
GROUP BY 1
ORDER BY 1

<div style="margin:2.5rem auto;max-width:600px;width:100%;text-align:center;"><svg viewBox="0 0 600 180" xmlns="http://www.w3.org/2000/svg" style="width:100%;height:auto;"><rect width="600" height="180" rx="12" fill="#1a1a2e"/><ellipse cx="150" cy="55" rx="60" ry="18" fill="#6366f1" opacity="0.8"/><rect x="90" y="55" width="120" height="50" fill="#6366f1" opacity="0.8"/><ellipse cx="150" cy="105" rx="60" ry="18" fill="#6366f1" opacity="0.9"/><text x="150" y="85" text-anchor="middle" fill="#ffffff" font-size="12" font-family="system-ui" font-weight="bold">Primary</text><text x="150" y="140" text-anchor="middle" fill="#94a3b8" font-size="10" font-family="system-ui">Read + Write</text><ellipse cx="400" cy="30" rx="50" ry="14" fill="#a855f7" opacity="0.7"/><rect x="350" y="30" width="100" height="35" fill="#a855f7" opacity="0.7"/><ellipse cx="400" cy="65" rx="50" ry="14" fill="#a855f7" opacity="0.8"/><text x="400" y="52" text-anchor="middle" fill="#ffffff" font-size="10" font-family="system-ui">Replica 1</text><ellipse cx="400" cy="110" rx="50" ry="14" fill="#a855f7" opacity="0.7"/><rect x="350" y="110" width="100" height="35" fill="#a855f7" opacity="0.7"/><ellipse cx="400" cy="145" rx="50" ry="14" fill="#a855f7" opacity="0.8"/><text x="400" y="132" text-anchor="middle" fill="#ffffff" font-size="10" font-family="system-ui">Replica 2</text><defs><marker id="arrow8" markerWidth="8" markerHeight="6" refX="8" refY="3" orient="auto"><path d="M0,0 L8,3 L0,6" fill="#2dd4bf"/></marker></defs><path d="M212,65 Q280,30 348,48" stroke="#2dd4bf" stroke-width="1.5" fill="none" marker-end="url(#arrow8)"/><path d="M212,90 Q280,130 348,128" stroke="#2dd4bf" stroke-width="1.5" fill="none" marker-end="url(#arrow8)"/><text x="280" y="55" text-anchor="middle" fill="#2dd4bf" font-size="9" font-family="system-ui">WAL stream</text><text x="280" y="130" text-anchor="middle" fill="#2dd4bf" font-size="9" font-family="system-ui">WAL stream</text><text x="500" y="52" text-anchor="start" fill="#94a3b8" font-size="9" font-family="system-ui">Read-only</text><text x="500" y="132" text-anchor="start" fill="#94a3b8" font-size="9" font-family="system-ui">Read-only</text></svg><p style="margin-top:0.75rem;font-size:0.85rem;color:#94a3b8;font-style:italic;line-height:1.4;">Database replication: the primary handles writes while replicas serve read queries via WAL streaming.</p></div>

Panel 4: Top Endpoints (Table)

SELECT
  metadata->>'endpoint' AS endpoint,
  COUNT(*) AS hits,
  ROUND(AVG((metadata->>'latency_ms')::numeric), 1) AS avg_latency_ms,
  PERCENTILE_CONT(0.95) WITHIN GROUP (
    ORDER BY (metadata->>'latency_ms')::numeric
  ) AS p95_latency_ms
FROM app_events
WHERE
  event_type = 'api_request'
  AND created_at >= $__timeFrom()
  AND created_at <= $__timeTo()
GROUP BY 1
ORDER BY hits DESC
LIMIT 20

Panel 5: Error Rate (Gauge)

SELECT
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE (metadata->>'status_code')::int >= 500)
    / NULLIF(COUNT(*), 0),
    2
  ) AS "Error Rate %"
FROM app_events
WHERE
  event_type = 'api_request'
  AND created_at >= NOW() - INTERVAL '1 hour'

Setting Up Alerts

Grafana can alert you when metrics cross thresholds:

1. Edit a panel and go to the Alert tab 2. Set conditions:

WHEN avg() OF query(A) IS ABOVE 5.0
FOR 5 minutes

Configure notification channels in Alerting > Contact points:

Performance Optimization

Materialized Views for Heavy Queries

Pre-compute expensive aggregations:

CREATE MATERIALIZED VIEW hourly_stats AS
SELECT
  date_trunc('hour', created_at) AS hour,
  event_type,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS unique_users
FROM app_events
GROUP BY 1, 2;

CREATE UNIQUE INDEX idx_hourly_stats ON hourly_stats (hour, event_type);

-- Refresh every hour via cron or pg_cron
REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_stats;

Query Caching

In Grafana panel settings, set the Min interval to reduce query frequency:

Connection Pooling

Use PgBouncer between Grafana and PostgreSQL to prevent connection exhaustion:

# pgbouncer.ini
[databases]
analytics = host=postgres port=5432 dbname=analytics

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

<div style="margin:2.5rem auto;max-width:600px;width:100%;text-align:center;"><svg viewBox="0 0 600 200" xmlns="http://www.w3.org/2000/svg" style="width:100%;height:auto;"><rect width="600" height="200" rx="12" fill="#1a1a2e"/><rect x="0" y="0" width="600" height="28" rx="12" fill="#2d2d44"/><rect x="0" y="12" width="600" height="16" fill="#2d2d44"/><circle cx="18" cy="14" r="5" fill="#ef4444"/><circle cx="34" cy="14" r="5" fill="#f59e0b"/><circle cx="50" cy="14" r="5" fill="#2dd4bf"/><text x="300" y="18" text-anchor="middle" fill="#94a3b8" font-size="10" font-family="system-ui">docker-compose.yml</text><rect x="0" y="28" width="35" height="172" fill="#1e1e32"/><text x="25" y="48" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">1</text><text x="25" y="66" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">2</text><text x="25" y="84" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">3</text><text x="25" y="102" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">4</text><text x="25" y="120" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">5</text><text x="25" y="138" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">6</text><text x="25" y="156" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">7</text><text x="25" y="174" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">8</text><text x="25" y="192" text-anchor="end" fill="#94a3b8" font-size="10" font-family="monospace" opacity="0.5">9</text><text x="45" y="48" fill="#a855f7" font-size="11" font-family="monospace">version</text><text x="100" y="48" fill="#e2e8f0" font-size="11" font-family="monospace">: &quot;3.8&quot;</text><text x="45" y="66" fill="#a855f7" font-size="11" font-family="monospace">services</text><text x="105" y="66" fill="#e2e8f0" font-size="11" font-family="monospace">:</text><text x="55" y="84" fill="#3b82f6" font-size="11" font-family="monospace"> web</text><text x="80" y="84" fill="#e2e8f0" font-size="11" font-family="monospace">:</text><text x="55" y="102" fill="#2dd4bf" font-size="11" font-family="monospace"> image</text><text x="110" y="102" fill="#e2e8f0" font-size="11" font-family="monospace">: nginx:alpine</text><text x="55" y="120" fill="#2dd4bf" font-size="11" font-family="monospace"> ports</text><text x="102" y="120" fill="#e2e8f0" font-size="11" font-family="monospace">:</text><text x="55" y="138" fill="#e2e8f0" font-size="11" font-family="monospace"> - &quot;80:80&quot;</text><text x="55" y="156" fill="#2dd4bf" font-size="11" font-family="monospace"> volumes</text><text x="118" y="156" fill="#e2e8f0" font-size="11" font-family="monospace">:</text><text x="55" y="174" fill="#e2e8f0" font-size="11" font-family="monospace"> - ./html:/usr/share/nginx</text><rect x="365" y="164" width="2" height="14" fill="#6366f1" opacity="0.8"/></svg><p style="margin-top:0.75rem;font-size:0.85rem;color:#94a3b8;font-style:italic;line-height:1.4;">A well-structured configuration file is the foundation of reproducible infrastructure.</p></div>

Dashboard Design Tips

1. Top row: KPI stat panels (big numbers) — active users, revenue, error rate 2. Second row: Time-series graphs — requests/minute, latency, throughput 3. Third row: Tables and heatmaps — top endpoints, error breakdown 4. Use variables: Template variables for filtering by environment, service, region 5. Color thresholds: Green < 1% error rate, yellow < 5%, red > 5%

Grafana with PostgreSQL is a powerful combination that covers 90% of dashboard needs without additional infrastructure. At TechSaaS, it is the backbone of our monitoring for every client deployment.