How to Build a Live KPI Dashboard from Postgres (No Tableau Required)

Your Postgres database already has everything you need for a KPI dashboard.

Revenue, signups, churn, active users, conversion rates — it's all sitting in tables right now. The question isn't whether you have the data. The question is whether you build your dashboard in Grafana, Metabase, a Python app, or something that lets you ask questions in plain English.

I've built KPI dashboards all three ways (plus a few cursed approaches I won't mention), and they each make different tradeoffs. Here's what each approach actually looks like — with real SQL you can steal.

The KPIs most teams track from Postgres

Before we get into tools, let's agree on what we're building. Most B2B SaaS teams track some variation of these five metrics, all queryable directly from Postgres:

1. MRR (Monthly Recurring Revenue) — how much predictable revenue comes in each month
2. DAU/MAU ratio — daily vs monthly active users (engagement health)
3. Signup-to-activation rate — what percentage of signups actually do the thing that matters
4. Revenue per user (ARPU) — are you attracting high-value or low-value customers
5. Churn rate — how many paying customers you lose per period

If you're not a SaaS company, swap in your equivalents: order volume, average basket size, repeat purchase rate, etc. The SQL patterns are the same.

Real SQL examples (Postgres-specific)

These queries assume a typical schema with users, subscriptions, and events tables. Adapt the table/column names to your setup.

MRR over time

select
  date_trunc('month', period_start) as month,
  sum(amount_cents) / 100.0 as mrr
from subscriptions
where status = 'active'
  and period_start >= now() - interval '12 months'
group by 1
order by 1;

DAU/MAU ratio (rolling 30 days)

with daily_active as (
  select
    date_trunc('day', created_at) as day,
    count(distinct user_id) as dau
  from events
  where created_at >= now() - interval '30 days'
  group by 1
),
monthly_active as (
  select count(distinct user_id) as mau
  from events
  where created_at >= now() - interval '30 days'
)
select
  d.day,
  d.dau,
  m.mau,
  round(d.dau::numeric / nullif(m.mau, 0), 3) as dau_mau_ratio
from daily_active d
cross join monthly_active m
order by d.day;

Signup-to-activation rate by cohort

with cohorts as (
  select
    date_trunc('week', created_at) as cohort_week,
    id as user_id
  from users
  where created_at >= now() - interval '8 weeks'
),
activated as (
  select distinct user_id
  from events
  where event_name = 'activation_complete'
)
select
  c.cohort_week,
  count(c.user_id) as signups,
  count(a.user_id) as activated,
  round(count(a.user_id)::numeric / nullif(count(c.user_id), 0) * 100, 1) as activation_pct
from cohorts c
left join activated a on a.user_id = c.user_id
group by 1
order by 1;

Revenue per user (ARPU)

select
  date_trunc('month', s.period_start) as month,
  sum(s.amount_cents) / 100.0 as total_revenue,
  count(distinct s.user_id) as paying_users,
  round(sum(s.amount_cents) / 100.0 / nullif(count(distinct s.user_id), 0), 2) as arpu
from subscriptions s
where s.status = 'active'
  and s.period_start >= now() - interval '6 months'
group by 1
order by 1;

Monthly churn rate

with months as (
  select generate_series(
    date_trunc('month', now() - interval '6 months'),
    date_trunc('month', now()),
    interval '1 month'
  ) as month
),
active_start as (
  select
    m.month,
    count(distinct s.user_id) as users_at_start
  from months m
  join subscriptions s
    on s.period_start < m.month
    and (s.canceled_at is null or s.canceled_at >= m.month)
  group by 1
),
churned as (
  select
    date_trunc('month', canceled_at) as month,
    count(distinct user_id) as churned_users
  from subscriptions
  where canceled_at >= now() - interval '6 months'
  group by 1
)
select
  a.month,
  a.users_at_start,
  coalesce(c.churned_users, 0) as churned,
  round(coalesce(c.churned_users, 0)::numeric / nullif(a.users_at_start, 0) * 100, 2) as churn_pct
from active_start a
left join churned c on c.month = a.month
order by a.month;

Three approaches to building the dashboard

Now that you have the queries, you need somewhere to run them and display the results. Here are three real options, with honest tradeoffs.

Approach 1: Grafana

Grafana connects directly to Postgres and auto-refreshes dashboards on a configurable interval (every 10 seconds, every minute, whatever you want). It's free, open-source, and widely deployed.

Setup: Install Grafana (Docker or native), add Postgres as a data source, paste your SQL queries into panels, set refresh interval.

What it's good at:

• Auto-refresh without any custom code
• Built-in alerting (threshold-based: "alert me if churn > 5%")
• Battle-tested at scale — handles large result sets well
• Good for ops teams who want dashboards on a wall-mounted TV

Where it struggles:

• The SQL editor is limited — no CTEs in older versions, weird variable syntax
• Visualization options are time-series focused. Bar charts and tables exist but feel like afterthoughts
• No natural language queries — you write SQL or you don't get answers
• Dashboard-as-code (JSON provisioning) is verbose and painful to maintain

Best for: Teams that already run Grafana for infrastructure monitoring and want to add business KPIs to the same tool.

Approach 2: Metabase

Metabase is open-source and explicitly designed for business analytics (unlike Grafana, which started as an ops tool). It has a visual query builder for non-SQL users AND a native SQL editor for those who prefer it.

Setup: Docker container, point at your Postgres, create "Questions" (their term for saved queries), arrange into dashboards.

What it's good at:

• Non-technical users can build queries without SQL (visual point-and-click)
• "Saved Questions" are reusable across dashboards
• Embedding: you can embed charts in your own app with signed URLs
• Automatic query suggestions based on your schema
• Clean, modern UI that non-engineers actually enjoy using

Where it struggles:

• Auto-refresh is limited — dashboards refresh on page load, not continuously
• Alerting exists but is basic (email when a number crosses a threshold)
• Performance with complex queries on large datasets can be slow without caching
• The visual query builder can't express everything SQL can

Best for: Teams where non-technical stakeholders need self-serve access to data. See our Metabase alternatives comparison for a deeper breakdown.

Approach 3: Fastero (NL-to-SQL + AI analysis)

Fastero connects to your Postgres database and lets you ask questions in plain English. "What's our MRR trend?" generates the SQL, runs it, and returns a chart. You can also paste SQL directly if you prefer.

Setup: Connect your Postgres (connection string or SSH tunnel), ask questions or paste SQL, pin results to a dashboard.

What it's good at:

• Natural language to SQL — non-technical users ask questions without learning SQL
• AI-generated insights ("MRR grew 12% but new customer revenue declined — growth is coming from expansions")
• Smart alerts ("notify me when churn rate exceeds its 30-day average by 2x")
• No infrastructure to manage — hosted service, not a Docker container to maintain
• Combines querying, visualization, and alerting in one tool

Where it struggles:

• Newer product — fewer integrations than Grafana's massive plugin ecosystem
• NL-to-SQL works best with clean schemas (good column names, not col_a, col_b)
• If you need pixel-perfect custom visualizations, a code-first approach gives more control

Best for: Teams that want answers from their data without maintaining BI infrastructure. Especially useful when the people asking questions aren't the people who write SQL. More detail on the NL-to-SQL product page.

Adding live refresh and alerts

A dashboard that only updates when someone clicks refresh is just a prettier spreadsheet. Here's how each approach handles "keep this current":

Grafana: Native auto-refresh. Set it to 30 seconds and forget it. Alerts fire when a query result crosses a threshold you define. This is Grafana's core strength.

Metabase: Limited auto-refresh. You can set cache invalidation policies, but there's no "refresh this dashboard every 30 seconds" mode built in. Alerts are email-based and trigger on saved question results.

Fastero: Alert-driven. Instead of polling on a timer, you set conditions: "alert me when ARPU drops below $45" or "notify me when weekly signups are 20% below the 4-week average." The system checks on its own schedule and notifies via Slack, email, or webhook.

The mental model shift: Grafana assumes you're watching the dashboard. Fastero assumes you're not — and tells you when something needs attention.

Quick comparison table

Which approach to pick

You want a live wall dashboard that auto-refreshes: Grafana. It's literally built for this. Paste your SQL, set 30-second refresh, put it on a TV.

You want non-technical teammates to explore data themselves: Metabase. The visual query builder means your marketing lead can answer their own questions without filing a Jira ticket.

You want to ask questions and get alerted when things change: Fastero. Skip the dashboard-building entirely — ask questions when you have them, get notified when something needs attention.

You want all three and have engineering bandwidth: Self-host Grafana + Metabase, maintain both, and accept that you're now running two pieces of infrastructure that need upgrades, backups, and access management.

The point is: your Postgres database is already the hard part. You've collected the data, modeled the schema, kept it running. The dashboard layer should be the easy part — pick the tool that matches how your team actually works, not the one with the most features on a comparison page.

Related reading:

• Postgres integration details
• Natural language to SQL — how it works
• Metabase alternatives
• Best BI tools compared
• How to automate SQL reports without a BI tool