Vacuum Laravel Package

Getting Started

heyosseus/vacuum is a Postgres-native monitoring dashboard for Laravel: it surfaces database size, largest tables, and vitals (cache hit ratio, active sessions) plus query statistics from pg_stat_statements.

1. composer require heyosseus/vacuum
2. Publish the config (php artisan vendor:publish) and open it first — the connection name, route/prefix, and authorization callback all live there.
3. Point the package at a read-only Postgres connection. The package runs its statistics reads through a read-only executor, so give it a role like pg_monitor / pg_read_all_stats rather than your app's write credentials.
4. Ensure the prerequisites: PostgreSQL (not MySQL/SQLite), pg_stat_statements in shared_preload_libraries (needs a restart), and a sane statement_timeout on the connection the dashboard uses.
5. Hit the dashboard route and open the Overview.

Your first real use case is incident triage: something is slow, you open the Overview, check the vitals card (cache hit ratio tells you memory vs. disk pressure; active sessions tell you whether you're saturated), then look at Largest tables to see who owns the bytes, then use the query statistics to find the statements actually costing time. Do this on staging first so you can confirm the extension and grants are right before you need it at 3am.

Implementation Patterns

Separate the monitoring connection. Define a dedicated pgsql-monitoring connection with its own credentials and register it in the package config. This keeps the dashboard from ever holding a write-capable handle, lets you set a different statement_timeout than your app, and means a saturated app pool doesn't starve the dashboard (or vice versa).

Gate it like an admin tool. The dashboard exposes table names, sizes, and query text — treat it as sensitive. Wire the authorization callback to a policy or an Gate::define on an ops/admin role, and additionally restrict by environment or IP in production. Prefer read-only visibility over "hidden route", since routes are enumerable.

Use it as an investigation entry point, not a dashboard to stare at. The natural loop is: Overview → Largest tables → query statistics for the top offending table → decide between indexing, archiving, or VACUUM tuning. Keep the package UI as the discovery layer and do the fixing in migrations.

Reuse the vitals numbers in your own health checks. Cache hit ratio and active sessions are the two figures worth alerting on. Rather than scraping the HTML, read them from the same views the package reads (pg_stat_database, pg_stat_activity) in a scheduled command, and keep the UI for humans.

Version 1.2.3 makes the Overview safe to land on. Database size now comes from pg_database_size() (one call) instead of sizing every relation, and Largest tables no longer polls every five seconds. On large clusters this is the difference between a page that loads and a worker pinned for a minute — so you can now make the Overview your default landing page and link to it from alert notifications. No config, migration, or API changes are needed to upgrade; it's a plain composer update.

Scale expectations by cluster size. Under a few hundred tables the whole package is unremarkable. At ~500 tables / 100GB+ the Overview is now cheap but the Tables list still sorts by size on every load (tracked upstream as #31) — so avoid auto-refresh loops or embedding that page in a wall display on very large databases.

Gotchas and Tips

• Postgres only. Every meaningful call is a pg_* function. If your test suite runs on SQLite, anything that touches the dashboard route will fail — guard it behind a driver check in your test setup.
• pg_stat_statements requires a restart to add to shared_preload_libraries, and its counters reset on restart or pg_stat_statements_reset(). If query stats look empty, check the extension before you check the package.
• Missing grants fail silently. Insufficient privileges usually produce blank cards or zeroed charts rather than an error. If a panel is empty, verify the role has pg_read_all_stats/pg_monitor and can see other sessions in pg_stat_activity (row-level visibility is filtered by role).
• Database size now includes system catalogs. pg_database_size() is about 0.03% larger than the sum of relation sizes. Don't try to reconcile it exactly against per-table numbers, and expect a small step change in any alert thresholds or graphs you built from the old figure.
• The statement_timeout is a feature, not an obstacle. On a database too large to size in time you get an empty chart instead of a stalled worker. Tighten the timeout for the monitoring connection rather than raising it — a monitoring query should never hold a request open.
• Largest tables no longer live-updates. It was polling every five seconds; now it doesn't. If you were relying on it to watch a table grow during a bulk load, refresh manually.
• Keep the remaining size-sort cost in mind. The Tables list still sorts by size per load on huge schemas. Don't put it behind a hot link or an aggressive client-side refresh; watch upstream #31 for the fix.
• Extension points to look at before forking: the config's connection and authorization callback cover most customization; the views are publishable if you need to relabel or embed cards elsewhere. Resist patching vendor code for chart tweaks.