External tools: pganalyze, pgwatch2, and others

Capsule overview

Up to here you have the complete foundation: pg_stat_statements, auto_explain, the slow query log, pg_stat_activity. With those four built-in tools you can diagnose 90% of PostgreSQL performance problems in any deployment.

But at a certain scale, the built-in tools stop scaling. If your company has 30 PostgreSQL instances across different clusters, parsing logs by hand and running psql for each one becomes impractical. If you need historical dashboards over 90 days, pg_stat_statements (which is a cumulative snapshot) doesn't give them to you. If you want to alert your team when a new query enters the top 10, there's no built-in for that.

The industry built a tooling layer on top of the built-in primitives. This capsule gives you the panorama:

• pganalyze (commercial SaaS)
• pgwatch2 + Grafana (open-source self-hosted)
• Datadog Database Monitoring (part of the Datadog APM)
• AWS Performance Insights (managed AWS)
• Simpler solutions (home-made scripts + Prometheus + postgres_exporter)

This capsule teaches you:

• Distinguishing the four categories of external tooling and understanding what each one solves.
• Deciding which (or which ones) your team needs using a concrete decision matrix.
• Recognizing that almost all these tools are built on top of pg_stat_statements and auto_explain — understanding the primitives (capsules 02-06) is what lets you really use the SaaS.
• Anticipating the cost (financial and operational complexity) of each option.

By the end, you'll be able to participate in the "which DB monitoring tool do we choose?" conversation with criteria based on scale, team, and budget, not on the hype of the moment.

Mental model: the tooling pyramid

Imagine DB observability tools as a pyramid:

                      ┌─────────────────┐
                      │     SaaS        │   ← pganalyze, Datadog DBM
                      │   premium       │       ($$$, all set up)
                      ├─────────────────┤
                      │  Self-hosted    │   ← pgwatch2 + Grafana
                      │  integrated     │       ($, 1-2 days setup)
                      ├─────────────────┤
                      │  Managed cloud  │   ← AWS PI, GCP Query Insights
                      │  (limited)      │       ($, comes with the cloud)
                      ├─────────────────┤
                      │  Scripts +      │   ← bash + cron + Prometheus
                      │  exporters      │       ($, requires expertise)
                      ├─────────────────┤
                      │   Built-in      │   ← pg_stat_statements, auto_explain
                      │  (capsules 02-06)│     ($0, the base of EVERYTHING)
                      └─────────────────┘

Each layer is built on the previous one:

• Built-in is the base. Without it, nothing works.
• Scripts + exporters read built-in and expose it to metrics.
• Managed cloud parses logs and built-in views of the managed PostgreSQL.
• Self-hosted centralizes built-in scrapers in an integrated UI.
• SaaS premium adds ML, smart alerts, automatic recommendations.

The important point: climbing the pyramid doesn't give you magical new capabilities — it gives you convenience and abstraction. The primitives are always the same. If you don't understand built-in (capsules 02-06), you don't understand what the SaaS shows you.

The four categories

1. SaaS premium: pganalyze, Datadog DBM

What they are:

SaaS services that connect to your PostgreSQL (via an agent or direct read), ingest pg_stat_statements and auto_explain, and give you an interactive dashboard, alerts, automatic recommendations.

pganalyze:

• Founded by Lukas Fittl (author of several articles referenced in these capsules).
• 100% focused on PostgreSQL.
• Distinctive features: Index Advisor (recommends indexes based on observed queries), Query Insights (plan visualization with history), VACUUM Advisor.
• Pricing: ~$149/month/server up to enterprise plans ($$$$ with many servers).

Datadog Database Monitoring (DBM):

• Add-on to the Datadog APM.
• Integrates with the Datadog stack (logs, traces, infra).
• Covers PostgreSQL, MySQL, SQL Server, Oracle.
• Pricing: per monitored host ($70-200/host/month).

When it's worth it:

• Your team already pays for Datadog/New Relic — adding DBM is trivial.
• Your PostgreSQL is business-critical and downtime costs $$$$/hour.
• Your team doesn't have a dedicated DBA and needs the automatic recommendations.
• You have 5+ PostgreSQL instances and manual pgwatch2 setup would be more expensive in human hours than the SaaS.

When it's NOT worth it:

• You're a startup with 1 small PostgreSQL and nobody will read the dashboards.
• Your team has an expert DBA who prefers their own tooling.
• Compliance/regulation prevents sending query metadata to a third party.

2. Self-hosted integrated: pgwatch2 + Grafana

What it is:

An open-source suite that scrapes PostgreSQL metrics (including pg_stat_statements, pg_stat_activity, pg_stat_database, etc.), stores them in TimescaleDB or InfluxDB, and visualizes them in Grafana with pre-built dashboards.

• pgwatch2 is the scraper.
• Grafana is the UI.
• TimescaleDB/InfluxDB is the time-series storage.

Features:

• Months of history at no extra cost.
• Custom dashboards, alerts (via Grafana or Alertmanager).
• 100% open source. Self-hosted.

When it's worth it:

• Your team already operates Grafana for other things (infra, app metrics).
• You have 5+ PostgreSQL instances and want everything in one dashboard.
• You need months of history for trend analysis.
• You don't want to pay for SaaS but you're willing to pay for 1-2 days of initial setup.

When it's NOT worth it:

• You're a small team (<5 people) without operations expertise.
• You want turnkey solutions with less maintenance.
• Your PostgreSQL is cloud-managed (RDS, Cloud SQL) and you prefer its integrated solution.

Minimal setup (concept):

# 1. Bring up pgwatch2 with docker-compose:
git clone https://github.com/cybertec-postgresql/pgwatch2
cd pgwatch2/docker
docker-compose up -d

# 2. Configure your PostgreSQL so pgwatch2 can read it:
# (in your PostgreSQL, already with pg_stat_statements enabled)
CREATE USER pgwatch2 WITH LOGIN PASSWORD 'xxx';
GRANT pg_monitor TO pgwatch2;

# 3. Add the instance in the pgwatch2 web UI (port 8080).

# 4. Access Grafana (port 3000) and see the pre-built dashboards.

3. Managed cloud: AWS PI, GCP Query Insights, Azure Query Performance Insight

What they are:

Products integrated into the managed PostgreSQL of the big clouds. They come "free" (included in the cost of the managed DB) and give partial visibility without setup.

AWS Performance Insights (PI):

• For RDS and Aurora.
• A "top queries by load" dashboard based on wait events.
• 7 days of history on the free plan, up to 2 years for an extra charge.
• It doesn't use exactly pg_stat_statements — it uses proprietary sampling (lighter but less detail per query).

GCP Query Insights:

• For Cloud SQL PostgreSQL.
• Based on pg_stat_statements.
• A dashboard of top queries, plans, and users.
• Included at no extra cost.

Azure Query Performance Insight:

• For Azure Database for PostgreSQL.
• Similar to GCP's Query Insights.
• Included.

When it's worth it:

• Your PostgreSQL is managed in one of these clouds.
• You want "something better than nothing" without investing in setup.
• The cloud's built-in features cover your need.

When it's NOT worth it:

• Your PostgreSQL is self-hosted or in an unsupported cloud.
• You need features the managed one doesn't have (index recommendations, cross-instance comparison).
• Your team already uses pganalyze or Datadog and duplicating tooling is noise.

4. Home-made scripts + exporters

What it is:

A combination of:

• bash/Python scripts that periodically run queries against pg_stat_statements/pg_stat_activity.
• postgres_exporter (Prometheus) to expose metrics.
• Prometheus + Grafana for storage and visualization.
• Alertmanager for alerts.

When it's worth it:

• Your team already has a Prometheus + Grafana stack.
• Specific needs that no SaaS solves "out of the box".
• A team with expertise in operations and SQL.

When it's NOT worth it:

• You don't have an established operations stack.
• A small team without time to maintain custom tools.

Minimal setup:

# 1. postgres_exporter:
docker run -d --name pg-exporter \
    -e DATA_SOURCE_NAME="postgresql://monitor:xxx@host:5432/postgres?sslmode=disable" \
    -p 9187:9187 \
    quay.io/prometheuscommunity/postgres-exporter

# 2. Prometheus scrape config:
# - job_name: 'postgres'
#   static_configs:
#     - targets: ['pg-exporter:9187']

# 3. Grafana dashboard ID 9628 (PostgreSQL Database) or custom.

Plus custom queries in postgres_exporter for specific things (top by total_exec_time, idle_in_tx > 5min, etc.).

Decision matrix: which one to choose

Guiding questions to decide

1. How many PostgreSQL instances do we operate?

   • 1-2 → built-in + cloud-managed insights is enough.
   • 3-10 → worth investing in something (DBM, pganalyze, pgwatch2).
   • 10+ → you need robust tooling, home-made scripts don't scale.

2. How much does an hour of DB downtime cost?

   • <$100 → basic tooling ok.
   • $1k-10k → invest in proactive alerts (DBM, pganalyze).

   • $10k → top tooling + dedicated DBA.

3. Do we have a DBA or expert?

   • Yes → open-source tooling leverages their expertise.
   • No → premium SaaS with automatic recommendations is worth more.

4. What observability stack do we already have?

   • Datadog → DBM trivial to add.
   • Grafana + Prometheus → pgwatch2 or custom scripts fit.
   • New Relic → its Database integration.
   • Nothing yet → start with cloud-managed (PI, Query Insights).

5. Are there compliance/data-residency restrictions?

   • Yes → self-hosted is mandatory (pgwatch2).
   • No → SaaS is an option.

The most common mistake: buying SaaS without understanding built-in

Many teams buy pganalyze or Datadog DBM and, two months later, extract no real value because nobody on the team understands what the dashboards show.

Symptom:

• "We have pganalyze but the team doesn't open it."
• "Datadog DBM tells us things but we don't know what to do with the information."
• "We bought the most expensive plan and we still have the same incidents."

Root cause:

External tools don't think for you. They show you total_exec_time, captured plans, wait_events — exactly the same primitives you saw in capsules 02-06. If you don't understand what you're seeing, the SaaS is just an expensive dashboard.

Fix:

1. Learn built-in first (capsules 02-06 — you already did that).
2. Operate with built-in until the pain justifies paying for SaaS.
3. When you buy SaaS, identify 1-2 people on the team as "owners" who know how to read and interpret it.
4. Do a weekly or biweekly review of the dashboards (not only when there's an incident).

Rule of thumb: a team that doesn't use pg_stat_statements by hand probably doesn't need pganalyze yet. When they already query pg_stat_statements weekly and get tired of doing manual queries, that's the time to move up to SaaS.

Detailed comparison: features

When "buy nothing" is the correct answer

Sometimes the best tooling is the one you already have (built-in). For small teams just starting out with PostgreSQL in production, this is the recommended minimal setup:

1. pg_stat_statements enabled (capsule 02).
2. auto_explain with threshold = 1s (capsule 04).
3. log_min_duration_statement = 500ms (capsule 05).
4. Weekly cron: run the 4 canonical queries (capsule 03) and send them by email/Slack to the team.
5. Basic alert: idle_in_transaction > 5min (capsule 06).
6. (Optional) postgres_exporter + Grafana if they already use Prometheus.

Cost: $0. Setup time: 2-4 hours.

If this isn't enough for you after 6 months, only then think about an upgrade. Most teams discover that this covers 90% of needs.

Why this matters in real work

1. It avoids unnecessary purchases. DB monitoring SaaS can cost $20k-100k/year in a mid-size company. Knowing that built-in is enough for many cases lets you propose alternatives and save budget the team can use on other things (more servers, infra, etc.).

2. It makes you a real participant in tooling decisions. When your CTO or tech lead asks "should we buy pganalyze?", being able to answer with criteria (based on scale, team, alternatives) is what differentiates you from a dev who just says "yeah, I heard it's good".

3. It prepares you to implement tooling. If the team decides to go with pgwatch2 or Datadog DBM, you're going to be the one who implements it. Understanding what each one does lets you do it right the first time.

4. It gives you vocabulary for interviews. Mid-to-large companies expect senior candidates to know how PostgreSQL is monitored in production beyond pg_stat_statements. Mentioning pganalyze, Datadog DBM, pgwatch2 with criteria (when each one) scores points.

5. It protects you from the hype. Every year a new DB observability tool appears. Knowing what problems the existing tools actually solve makes you immune to "we have to buy the new tool" without justification.

Traps and common mistakes

Mistake 1 (purchasing): choosing SaaS because "it's what company X uses"

Symptom: "Stripe uses pganalyze, so we should too." Without considering that Stripe has 100x your scale, a team dedicated to the DB, and a different budget.

Why it confuses: copying successful companies sounds safe. But the appropriate tooling depends on your context, not theirs.

How to distinguish: how many instances do they have vs you? Do they have a DBA? What's their SLA? If the answers are very different, their tooling doesn't apply.

How to fix it: decide with your decision matrix (above), not with a "the big companies use it" bias.

Mistake 2 (operational): buying SaaS but not integrating it into the workflow

Symptom: "We've had Datadog DBM for 6 months but nobody opens it."

Why it happens: buying the SaaS doesn't guarantee it gets used. Without a defined workflow (who looks, when, what they do with the info), the dashboard is decoration.

How to distinguish: if after 1 month nobody on the team opened the dashboard, you're not extracting value.

How to fix it:

• Assign 1-2 owners.
• Weekly review in the team meeting (15 min reviewing top queries, alerts, trends).
• Turn findings into tickets (not just "look and forget").
• If it doesn't generate actionable tickets after 3 months, consider canceling.

Mistake 3 (technical): assuming the SaaS fixes problems

Symptom: "We bought pganalyze. We expect the slow queries to fix themselves."

Why it happens: SaaS tools show problems and sometimes give recommendations, but they don't apply fixes. Applying fixes is still the team's work.

How to distinguish: after N months with the SaaS, your slow queries are still the same.

How to fix it:

• The SaaS is input for your work, not a substitute.
• Define an internal SLA: "no query in the top 10 can have a mean > 200ms for more than 1 sprint".
• Block time in each sprint to review the dashboard and open tickets.

Mistake 4 (technical): self-hosted without resources to maintain it

Symptom: "Set up pgwatch2 3 months ago. Now the container is down, nobody knows why, the dashboards show old data."

Why it happens: open-source is "free" in license, not in operation. It requires people to maintain the stack.

How to distinguish: who is the owner of pgwatch2 on your team? If that person quits, does anyone else know how to operate it?

How to fix it:

• Assign clear ownership.
• Document setup, restoration, troubleshooting.
• If the team can't maintain it, consider migrating to SaaS even though it costs more.

Mistake 5 (purchasing): paying for features you don't need

Symptom: "We pay for the enterprise Datadog DBM plan with 50 features. We only use 3."

Why it happens: the sales pitch emphasizes advanced features. Teams buy "just in case" without a real need.

How to distinguish: review which features you used in the last 3 months. If they're <30% of the package, you overpaid.

How to fix it:

• Always start with the basic plan.
• Upgrade only when the pain justifies specific features.
• Negotiate pricing by real usage, not by feature lists.

Exercises

Exercise 1: apply the decision matrix to three scenarios

For each scenario, decide what tooling you'd recommend and justify it with 2-3 concrete reasons.

Scenario A: Startup of 8 engineers, 1 PostgreSQL on RDS (db.t3.medium), 50 RPS peak, no DBA. They want monitoring "so they don't fly blind" without spending much.

Scenario B: Company of 80 engineers, 12 self-hosted PostgreSQL on EKS (not managed), they have Datadog for everything else, 1 part-time DBA, a reasonable budget.

Scenario C: A bank with 30 PostgreSQL, on-prem for compliance, a team of 3 full-time DBAs, they can't send query data to SaaS due to regulation.

Exercise 2: assemble the bookstore's minimal setup

For your local bookstore, define the "minimal setup" recommended at the end of the capsule. Document which files you edit, which tools you install, which cron jobs you configure.

shared_preload_libraries = 'pg_stat_statements,auto_explain'

pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000
pg_stat_statements.save = on

auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = 'text'

log_min_duration_statement = '500ms'
log_line_prefix = '%m [%p] %q%u@%d (app=%a) '

#!/bin/bash
# Weekly report of the bookstore's top queries

REPORT=$(docker exec bookstore-pg psql -U bookstore -d bookstore -tA <<'SQL'
\echo '=== Top 10 by total_exec_time ==='
SELECT calls, round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round((100.0 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
       left(query, 200) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC LIMIT 10;
SQL
)

echo "$REPORT" > /tmp/weekly-report.txt

# Send to Slack/email (Slack example):
# curl -X POST $SLACK_WEBHOOK -d "{\"text\": \"$(cat /tmp/weekly-report.txt)\"}"

# For local, just save:
cp /tmp/weekly-report.txt ~/projects/bookstore-baseline/reports/weekly-$(date +%Y-%m-%d).txt

chmod +x scripts/weekly-top-queries.sh

# Crontab (cron Monday 9am):
crontab -e
# Add:
0 9 * * 1 ~/projects/bookstore-baseline/scripts/weekly-top-queries.sh

#!/bin/bash
COUNT=$(docker exec bookstore-pg psql -U bookstore -d bookstore -tA -c "
SELECT count(*) FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - xact_start > interval '5 minutes';
")

if [ "$COUNT" -gt 0 ]; then
    DETAILS=$(docker exec bookstore-pg psql -U bookstore -d bookstore -tA -c "
    SELECT pid || ' | ' || usename || ' | ' || application_name || ' | ' || (now() - xact_start)::text
    FROM pg_stat_activity
    WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
      AND now() - xact_start > interval '5 minutes';
    ")

    # Alert (Slack example):
    # curl -X POST $SLACK_WEBHOOK -d "{\"text\": \"ALERT: $COUNT idle_in_tx > 5min\\n$DETAILS\"}"

    # For local:
    echo "$(date) - ALERT: $COUNT idle in tx" >> ~/projects/bookstore-baseline/alerts.log
    echo "$DETAILS" >> ~/projects/bookstore-baseline/alerts.log
fi

chmod +x scripts/check-idle-tx.sh

# Crontab (every minute):
* * * * * ~/projects/bookstore-baseline/scripts/check-idle-tx.sh

Exercise 3: simulate a purchase decision conversation

Your CTO asks you: "I want to buy pganalyze for our production PostgreSQL. It costs $149/month. Do we approve it?". Your context: 3 PostgreSQL instances, a team of 12 devs, they already use Grafana for infra metrics, no DBA.

Write your answer in 4-6 concrete points.

Exercise 4: compare pganalyze vs pgwatch2 for your case

Research (via their websites, GitHub, blogs) and put together a comparison table specific to your bookstore. Consider: features, cost, setup effort, ongoing maintenance.

# Minimal pgwatch2 docker-compose (only for learning):
git clone https://github.com/cybertec-postgresql/pgwatch2
cd pgwatch2/docker

# Edit pgwatch2.yml to add your bookstore as an instance:
# - unique_name: bookstore-local
#   host: host.docker.internal
#   port: 5432
#   dbname: bookstore
#   user: bookstore
#   password: bookstore
#   preset_metrics: standard

docker-compose up -d
# Access Grafana at http://localhost:3000 (admin/pgwatch2admin)
# Pre-built PostgreSQL dashboards ready.

Exercise 5: detect problems with built-in that a SaaS would also detect

Demonstrate that with built-in (pg_stat_statements + pg_stat_activity) you can detect the same problems pganalyze would detect in its Index Advisor. For a slow query from your bookstore, identify the missing index by hand.

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
  AND calls > 5
ORDER BY mean_exec_time DESC
LIMIT 5;

 calls | mean_ms |                 query
-------+---------+---------------------------------------
    50 |  280.30 | SELECT * FROM reviews WHERE book_id = $1
   ...

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM reviews WHERE book_id = 5;

Seq Scan on reviews  (cost=0.00..14250.00 rows=120 width=85) (actual time=0.020..278.30 rows=120 loops=1)
  Filter: (book_id = 5)
  Rows Removed by Filter: 499880
  Buffers: shared hit=10000 read=4250
Planning Time: 0.150 ms
Execution Time: 278.45 ms

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'reviews';

CREATE INDEX idx_reviews_book_id ON reviews(book_id);

-- Reset stats:
SELECT pg_stat_statements_reset();

-- Re-run query:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM reviews WHERE book_id = 5;

Summary and next step

In this capsule you:

• Distinguished the five categories of tooling: built-in, scripts + exporters, managed cloud, self-hosted (pgwatch2), premium SaaS (pganalyze, Datadog DBM).
• Applied a concrete decision matrix to choose tooling based on scale, team, budget, and compliance.
• Recognized that external tools are built on top of the built-in primitives you learned in capsules 02-06.
• Anticipated the typical mistakes: buying SaaS without understanding built-in, copying tooling from companies with a different scale, not integrating the SaaS into the team's workflow.
• Identified the recommended minimal setup ($0, 2-4 hours) that covers 90% of the needs of small-to-mid teams.

Before moving on, you should be able to:

• Recommend a DB monitoring tool for 3 different scenarios with a data-based justification.
• Explain to a PM the difference between pganalyze and pgwatch2 without falling into marketing.
• Implement the minimal setup in your bookstore (weekly cron + idle_in_tx alert).
• Recognize when built-in is enough vs when it's worth investing in external tooling.

Next capsule — Project: profiling the bookstore in production. The time has come to apply everything in the module to an integrative case. You're going to take the "baseline" bookstore from module 1 (with all the known problems pre-seeded), apply synthetic load with wrk, identify the top problematic queries using the four tools (pg_stat_statements, auto_explain, slow query log, pg_stat_activity), prioritize by impact, and propose a concrete action plan connecting each query with the corresponding module (index → module 3, eager loading → module 4, pool → module 6). It's the dress rehearsal for module 8's final project.

Resources

1. pganalyze — Product page — features, pricing, use cases.
2. pgwatch2 — GitHub repository — code, docs, pre-built dashboards.
3. Datadog Database Monitoring docs — setup, integrations, pricing.
4. AWS RDS Performance Insights overview — official features and limitations.
5. postgres_exporter for Prometheus — the official exporter with built-in and custom queries.
6. Lukas Fittl — "Choosing the right Postgres monitoring tool" — the perspective of pganalyze's creator on the spectrum.
7. GCP Query Insights documentation — features included in Cloud SQL PostgreSQL.

Module 5 — Database Performance & Query Tuning Guide