Health screen
The Health screen is a one-shot DB checkup. Ten indicators on one screen show where to save space, speed up queries, and spot stale stats. Instead of jumping between pgAdmin → Statistics → Slow queries → Bloat → ..., everything lives in one place.
Open
- ⌘+Shift+H
- The ❤ icon in the tab header
The Health tab is bound to the current connection.
What's shown
1. Database and table sizes
Total DB size and top-10 tables by size. The Open in editor button on any row opens the table in Schema Browser.
2. Bloat
Tables and indexes with bloated empty pages (typical after mass UPDATE or DELETE). Computed via the standard query against pg_stat_user_tables + pg_stats.
Columns:
bloat_pct — % of empty space in the table datawasted_mb — megabytes that can be reclaimed
A REINDEX or VACUUM FULL button appears on a row when bloat > 30%. Before running, ide99 shows the SQL and warns about locking:
VACUUM FULL takes an AccessExclusiveLock. For prod use pg_repack.
3. Unused indexes
Indexes with idx_scan = 0 (zero usage since stats reset). Hint: if pg_stat_reset() ran long ago, then 0 truly means "never used".
Columns:
index_nametable_namesize_mblast_used (if pg_stat_user_indexes is enabled)
The DROP INDEX button shows the SQL and requires confirmation by typing the index name.
4. Slow queries
If pg_stat_statements is installed — top-10 queries by total_exec_time. Columns: query, calls, mean_time, total_time. Click — opens a tab with pg_stat_statements (see that page for details).
If the extension isn't installed — Health shows instructions to enable it:
CREATE EXTENSION pg_stat_statements;
-- + add to shared_preload_libraries and restart
5. Cache hit ratio
How many reads went into shared buffers vs disk. Healthy prod: > 99% buffer hits. < 95% means too little shared_buffers or active set bigger than RAM.
6. Active connections
How many connections are open, what % of max_connections. > 80% — warning. The Open Live Ops button takes you to the live sessions panel.
7. Vacuum / Autovacuum
Last successful VACUUM and AUTOVACUUM timestamps for top tables. If long ago — bloat grows. The VACUUM button runs manually (with locking warning).
8. Replication lag
If you have replicas (pg_stat_replication) — lag in bytes and seconds for each. Highlighted red if lag exceeds threshold (default 10 seconds). Configurable in Settings → Health → Replication threshold.
9. Stats hotspots
Tables where last_analyze > 7 days or n_dead_tup / n_live_tup > 20%. This means the planner is working on stale data and may pick bad plans. The ANALYZE button refreshes stats.
10. Foreign keys without an index
Columns referenced by a foreign key but missing an index. This makes JOIN and DELETE CASCADE slow. The CREATE INDEX button shows CREATE INDEX CONCURRENTLY (prod-safe).
Confirming changes on prod
Any action button (REINDEX, VACUUM, DROP, CREATE INDEX) on a prod connection requires typing the table or index name:
Confirm REINDEX "orders"
Type the table name:
[ ]
After running, a live progress (read from pg_stat_progress_*) shows percentage done.
Cadence
Health doesn't run in the background. It's a "right now" snapshot. Good habit — open weekly on dev and stage, less often on prod (long queries can load the database).
All Health queries are read-only. They only read system tables, never modify data. Safe to run on prod.
Next