Okay, this is going to sound cheap, because it is: my entire Postgres monitoring stack costs zero dollars and runs off a systemd timer. No Datadog agent, no Grafana Cloud trial that quietly becomes a credit card charge, no agent eating 300MB of RAM on a 4GB VPS to tell me the database is, in fact, up. I’ve been running pg_statviz for this, and version 1.2 just landed with PostgreSQL 19 support and a genuinely useful new trick, so it’s a good moment to write up the setup.
If you’ve never heard of it: pg_statviz is a minimalist pair of things, a tiny Postgres extension that snapshots the database’s own internal statistics into a few tables, and a Python utility that turns those snapshots into time series charts. That’s the whole product. The 1.2 announcement on postgresql.org covers the new bits, and I’ll get to my favorite one, but first I want to make the case for this whole category of tool, because I think most small teams are monitoring their databases wrong.
The problem with most postgresql monitoring tools
The standard advice for Postgres observability assumes you have a platform team. Install an exporter, run Prometheus, run Grafana, maintain dashboards, configure alerting, and now you’re maintaining five services to watch one. The SaaS route trades that maintenance for money and a data egress question you’ll answer awkwardly in a compliance review someday.
Here’s what I actually need for the client databases I look after: when something feels slow on Thursday, I want to know what changed since Monday. That’s it. That’s 90% of real-world database investigation on small systems. It’s a question about history, and Postgres, out of the box, barely keeps any. The stats collector’s cumulative counters tell you totals since the last reset, which is like asking how your car is running and being told the odometer reading.
Postgres 19 actually acknowledges this gap: the stats system keeps getting richer (more on wal_fpi_bytes below), but the history problem remains yours to solve. Snapshots solve it. pg_statviz just takes them for you, inside the database, with no external infrastructure.
Setup in four commands
The extension is in PGDG repos, so on Debian-flavored systems:
sudo apt install postgresql-17-statviz
sudo -u postgres psql -c "CREATE EXTENSION pg_statviz;"
pipx install pg_statviz
sudo -u postgres psql -c "SELECT pgstatviz.snapshot();"
That last command takes one snapshot. The tables live in the pgstatviz schema and storage is deliberately light, so taking snapshots every 15 minutes costs you approximately nothing.
For scheduling, the README suggests cron, but I run it from a systemd timer for the same reasons I moved my cron jobs to timers across the board: logged output in journald, no silent mail-to-nowhere failures, and systemctl list-timers shows me the next run at a glance.
# /etc/systemd/system/pgstatviz-snapshot.service
[Unit]
Description=pg_statviz snapshot
[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/psql -c "SELECT pgstatviz.snapshot();"
# /etc/systemd/system/pgstatviz-snapshot.timer
[Unit]
Description=pg_statviz snapshot every 15 minutes
[Timer]
OnCalendar=*:00/15
Persistent=true
[Install]
WantedBy=timers.target
When you want charts, the Python side pulls the snapshots and renders them:
pg_statviz --host localhost -U postgres -d myapp analyze
You get PNGs of WAL generation, buffer activity, connection counts, transaction rates, and so on, over whatever window your snapshots cover. It isn’t pretty in the Grafana sense. It answers questions, which I’ve come to prefer.
What 1.2 adds, and why the locks module is the headline
The PG19 support is table stakes (it’s been tested across the whole 13-to-19 range, which is a nice compatibility promise for a small project). Two additions stand out.
First, WAL visibility got sharper. The extension now captures the new wal_fpi_bytes counter from pg_stat_wal on 19, so you can finally see how much of your WAL volume is full-page images rather than actual change records. If your WAL spikes after every checkpoint and you’ve never known why, that’s the counter that explains it, and it’s the kind of thing that turns “storage bills feel high” into a specific tuning conversation about checkpoint spacing.
Second, and this is the one I’ve wanted for years: a blocking locks analysis module. Each snapshot now records how many sessions were blocked and blocking, broken down by lock type. Crucially, detection is built on pg_blocking_pids(), so it counts soft blocks too, sessions waiting in the lock queue behind another waiter, not just direct hard conflicts. Lock contention is the classic invisible problem: it never shows up when you go looking, because looking means running a query at 2pm and the pileup happened at 9:15am. Having it snapshotted every 15 minutes means Thursday-me can see exactly which morning the trouble started and what kind of lock it was.
The announcement also mentions that sustained blocking can never be reported as healthy thanks to a deterministic severity floor on the module’s verdicts, which is a small design decision I appreciate. Monitoring tools that grade on a curve train you to ignore them.
Where it fits next to pg_stat_statements
To be clear about scope: pg_statviz tells you what the server was doing over time. It won’t tell you which query is responsible. For that you still want pg_stat_statements, and the two compose nicely: statviz narrows down when things went sideways and in which subsystem, then the four pg_stat_statements queries I run before adding any index identify the guilty SQL. Time dimension from one, query dimension from the other. Between them you’ve covered most of what a paid APM gives you for a database this size.
What this stack won’t do is page you at 3am. There’s no alerting here, and I don’t pretend otherwise. For the client work I do through my agency practice, I pair it with a dumb uptime check and a disk space alert, on the theory that a small system’s genuine emergencies are few and boring, while its performance mysteries are frequent and historical. The snapshots are for the mysteries.
Set it up before you need it
The uncomfortable truth about snapshot-based monitoring: it only helps if it was already running before the problem started. You cannot retroactively collect history, and the day you wish you had it is by definition too late. So here’s the concrete move for this week: pick your most important Postgres box, install the extension, set the timer for every 15 minutes, and forget about it. The whole exercise takes ten minutes. Next month, when someone says “the app felt slow yesterday afternoon,” you’ll run one command and have charts instead of vibes.
