Datadog pings. 11:47 PM. API latency triples on checkout. You’re SSH’d into the bastion, fingers flying over that eternal query.
SELECT pid, state, EXTRACT(EPOCH FROM (now() - query_start)) AS seconds, query FROM pg_stat_activity WHERE state != ‘idle’ ORDER BY query_start;
Run it. Again. Tabs multiply. pg_locks joins the party. Three terminals. One bleary-eyed human.
Sound familiar?
Every Postgres wrangler’s nightmare. Those golden system views—pg_stat_activity, pg_locks, pg_statio_user_tables—buried in a shell. At midnight. Brutal productivity tax.
Enter data-peek’s Connection Health Monitor. Not some modal nag. A full tab. Refreshes every 2, 5, 10, or 30 seconds—you pick. Four panels. No fluff.
Why Stuff pg_stat_activity Into Your SQL Client?
Active Queries: Non-idle stuff from pg_stat_activity. Duration. Wait events. And—bam—a kill button per row.
Table Sizes: Top 50 by heap plus indexes. Live tuples estimated.
Cache Hit Ratios: Buffer and index percentages. Per-table seq-scan vs. index-scan drama.
Locks & Blocking: The blocked/blocking join you love to hate. Queries side-by-side.
That kill button? Fires pg_cancel_backend(pid). No “Are you sure?” popup. Accident? Query fails, rerun it. Perfect for 3 AM.
“The kill button calls pg_cancel_backend(pid). No confirmation dialog. If you hit it by accident, the worst that happens is a query fails and you run it again. That is the right tradeoff at midnight.”
Share buttons everywhere. Clean screenshots for Slack. No more terminal cropping marathons.
Boring? Damn right. And brilliant.
The Guts: No Smoke, Just SQL
React frontend. IPC handlers. Postgres adapter. Wafer-thin.
IPC’s ten lines: Grab config, hit adapter, wrap in IpcResponse. Failures sandboxed—pg_locks chokes? Active Queries sails on.
The SQL? Gold. Active Queries panel:
SELECT pid, usename AS user, datname AS database, state, COALESCE(EXTRACT(EPOCH FROM (now() - query_start))::text || ‘s’, ‘0s’) AS duration, … FROM pg_stat_activity WHERE state != ‘idle’ AND pid != pg_backend_pid() AND query NOT LIKE ‘%pg_stat_activity%’ ORDER BY query_start ASC NULLS LAST;
pid != pg_backend_pid()? Ditches the monitor itself. No infinite “why is this query here?” loop.
query NOT LIKE ‘%pg_stat_activity%’? Catches other pollers. Self-monitoring self-sabotage, fixed.
Cache hits? That copy-paste trap:
SELECT CASE WHEN SUM(heap_blks_hit) + SUM(heap_blks_read) = 0 THEN 0 ELSE ROUND(SUM(heap_blks_hit)::numeric / (SUM(heap_blks_hit) + SUM(heap_blks_read)) * 100, 2) END AS buffer_cache_hit_ratio, … FROM pg_statio_user_tables;
CASE? No div-by-zero on cold starts. Pros don’t skip that.
Is This Just Hype—or Does It Kill Midnight SSH Forever?
Here’s my take: This echoes the pgBadger era. Remember? Pre-2010, Postgres ops meant hand-rolled scripts, check_postgres.pl hacks. Clunky. Then pgBadger parsed logs into visuals. Revolution.
But still external. data-peek bakes it in. No context-switch tax. My bold prediction: In two years, every SQL client apes this. pgAdmin? TablePlus? They’ll scramble. SSH at midnight? Dead relic. Like floppy disks.
Corporate spin? None here. Solo dev. Honest pipeline. No VC vaporware.
Skeptical? Test it. Fire a runaway query. Watch the kill button. Bliss.
But wait—tradeoffs. pg_cancel_backend cancels, doesn’t terminate. Stuck backends? pg_terminate_backend. Riskier—connection drops. Dev chose safe. Smart.
Wait events? Visible. But drill-down? Missing. Future candy.
Panels independent. One flakes, others roll. Resilience baked in.
Share screenshots? Incident gold. Slack warrior’s dream.
Why Does This Matter for Postgres DevOps?
Postgres ops: 80% firefighting. This? Shifts to prevention. Spot cache misses early. Kill blockers pre-meltdown.
Dev.to skips SQL details. Here? Front and center. Copy-paste ready.
Unique insight: It’s the anti-Observability bloat. Datadog, New Relic? Billions for graphs. This? Free tab in your client. Razor-focused. Reminds me of Unix—do one thing, superbly.
Datadog alerts? Still ping. But now, fix in-client. No bastion hop.
Tired of shell grinds? Install data-peek. Feel the lift.
🧬 Related Insights
- Read more: 5 Open-Source Saviors That Could Slash Your SaaS Bills by 80%
- Read more: Nissan’s PSR Simulator: Crunching the Numbers on a Desperate Revival Gambit
Frequently Asked Questions
What is data-peek’s Connection Health Monitor?
It’s a SQL client tab pulling pg_stat_activity, locks, cache hits, table sizes—with kill buttons and share screenshots. Refreshes automatically.
Does the kill button work on production Postgres?
Yes, via pg_cancel_backend(pid). Safe cancel—no connection kill. No confirmations for speed.
Is data-peek free and open source?
Check the repo—solo dev tool, likely OSS. Focused on Postgres adapters.
