← projects
$ pgscope
Open-source, read-only monitoring tool for PostgreSQL and MySQL/MariaDB: live dashboard, index/query insights, and replay, without ever executing anything beyond SELECT against system catalogs.
- →Built with a hexagonal Go backend (domain -> application -> infrastructure -> presentation) and a React/TypeScript frontend, enforced via golangci-lint depguard rules so the domain layer can never import infrastructure or pgx directly.
- →Hard architectural boundary: pgscope only ever runs read-only queries against Postgres system catalogs and stat views (pg_stat_*, pg_locks, pg_stat_statements) — it never writes, kills, or resets anything on the monitored database.
- →Live dashboard streams active sessions, lock waits, and blocking chains in real time over SSE, rendered as a graph view that clusters related sessions (hub/fan-in nodes, multi-hop waiting chains) so contention is visible at a glance.
- →Insights panel surfaces actionable findings straight from stats: duplicate indexes, unused indexes (gated on a stats-reset-aware observation window), missing-index candidates on high-write tables, slowest queries via pg_stat_statements, and largest tables.
- →Replay feature snapshots session/lock state over time so a past incident (e.g. a blocking chain that already resolved) can be scrubbed through after the fact, instead of only being visible live.
- →Cookie-based auth (HttpOnly, Secure, SameSite), per-IP rate limiting with configurable budgets, and automatic temporary IP bans after repeated invalid/scanner-style requests, all sitting in front of a single-operator tool with no execution surface to abuse.
- →Shipped with a production docker-compose template and a separate local-dev compose file that joins an existing Postgres via an external Docker network, plus GitHub Actions CI (Go and frontend, path-filtered) and branch protection rulesets.
- →Extended to a second database engine (MySQL/MariaDB) purely by adding a new infrastructure adapter, with zero changes to the domain or application layers, turning the hexagonal boundary from a claim into something proven: every insight that has a real MySQL equivalent, the live session/lock graph, SSE streaming, and history replay all work identically on both engines, selected at startup via an env var.
- →On MySQL, session and lock data comes from performance_schema, sys.innodb_lock_waits, and information_schema, with query text always sourced from the normalized DIGEST_TEXT rather than raw PROCESSLIST_INFO so literal values in a running statement are never exposed; added a MySQL-only lock-wait insight (which session blocks which, and for how long) plus a RECORD vs TABLE lock-severity mapping so a routine row lock is not shown as alarming as a full-table lock.
- →The frontend detects which engine it is connected to and hides advisories that engine cannot produce (vacuum health, checkpoints, replication slots, function and trigger cost tracking), instead of showing a Postgres-only instruction against a MySQL database; documented in the README exactly which Postgres insights have no real MySQL equivalent and why.
GoReactTypeScriptPostgreSQLMySQLHexagonal ArchitectureMonitoringDockerCI/CDSecurity