db-health
Skill0 starsChanged 6 months agoMITView on GitHub
DB Health Agent — PostgreSQL diagnostics. TRIGGER when: база данных, database, БД здоровье, db health, медленные запросы, slow queries, таблицы раздулись, bloat, индексы, indexes, бэкап базы, database backup, pg_stat, вакуум, vacuum, размер базы, database size, database check, диагностика БД, db diagnostics, оптимизация запросов, query optimization. Contains: pg_stat analysis, bloat detection, index recommendations, backup check, slow query analysis.
- Reads credentials
2 other projects use a version of this.
Adopted by
Most recently taken up by
- shim369/Python-Smart-Health-Intelligence-Platformtheir own version, first seen Sep 23, 2026
- freskhu/genesistheir own version, first seen May 8, 2026
Other versions2 projects use a version of this.
Word for word · 0
None found.
Edited · 0
None found.
Wrote their own version · 2
- freskhu/genesis★ 5same name
- shim369/Python-Smart-Health-Intelligence-Platform★ 0same name
How matches are found. Word for word: the exact same text. Edited: shares passages with this file (“99% same” means 99% of its five-word phrases also appear here; 10% or more counts). Their own version: similar meaning, or, weakest, the same name. We can’t tell which project had it first. Projects are counted among the 200 most-starred candidates, and mentions are links to the repository we found, up to 50 per site, so both counts are a floor.
In this repository15 other files it gives its agents.
- CLAUDE.mdCLAUDE.md
- api-scaffoldSkill
- content-agentSkill
- debug-serverSkill
- deploySkill
- git-workflowSkill
- log-monitorSkill
- max-apiSkill
- new-featureSkill
- notebooklmSkill
- postgresqlSkill
- qa-agentSkill
- security-checkSkill
- ui-ux-pro-maxSkill
- web-proSkill
The file
We split this skill into the rules it gives an agent
7 rules. About 0 are common, 2 are distinctive, and 5 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈2
Distinctive · estimate
This author's own choices. Worth a look.
5
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 32
Use the obtained values as $PGPASSWORD, $DB_USER, $DB_NAME in the commands below.
- Very specific: line 68
If dead_pct > 20% — VACUUM is needed
- Very specific: line 69
If last_autovacuum is NULL — autovacuum is not running (CRITICAL)
- Distinctive: line 135
If no backups exist — this is CRITICAL.
- Very specific: line 175
Write to Agent-Notes/db-health.md (overwrite — only the latest is needed).
- Distinctive: line 179
Do NOT run VACUUM or ALTER yourself — only recommend
- Very specific: line 226
After each diagnostic — append to Agent-Notes/db-health.md:
≈ “Common” is an estimate: at least 1 in 5 of the 372,429 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
DB Health Agent — PostgreSQL Diagnostics
Role
You are the DB Health Agent. You perform full PostgreSQL diagnostics, find performance issues, recommend optimizations, and check backups.
Connections
| DB | Server | IP | SSH |
| fujidb | Beget | 155.212.210.165 | ssh -i ~/.ssh/id_rsa root@155.212.210.165 |
| plusone | Beget | 31.128.42.61 | ssh -i ~/.ssh/id_rsa root@31.128.42.61 |
NEVER hardcode passwords. Get them from
.envon the server:ssh -i ~/.ssh/id_rsa root@IP "grep DB_PASS /var/www/*/\.env 2>/dev/null || grep PGPASSWORD /var/www/*/\.env 2>/dev/null | head -3"When new DBs appear — add a row and run all 9 checks for each.
Step 0 — Get Credentials from Server
# For each server — find .env and extract DB credentials
ssh -i ~/.ssh/id_rsa root@155.212.210.165 "cat /var/www/fuji-routes/.env 2>/dev/null | grep -E 'DB_|PG' | head -5"
ssh -i ~/.ssh/id_rsa root@31.128.42.61 "cat /var/www/*/.env 2>/dev/null | grep -E 'DB_|PG' | head -5"Use the obtained values as $PGPASSWORD, $DB_USER, $DB_NAME in the commands below.
Diagnostics Algorithm
All commands below use a template. Substitute real credentials from Step 0. Template:
ssh -i ~/.ssh/id_rsa root@IP "PGPASSWORD=FROM_ENV psql -U USER -h 127.0.0.1 -d DBNAME -c 'SQL'"
1. Database and Table Sizes
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT pg_size_pretty(pg_database_size('$DB')) AS db_size;
\""
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;
\""2. Bloat (Table Bloat)
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT schemaname, relname, n_live_tup, n_dead_tup,
CASE WHEN n_live_tup > 0 THEN round(n_dead_tup::numeric / n_live_tup * 100, 1) ELSE 0 END AS dead_pct,
last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;
\""- If
dead_pct> 20% — VACUUM is needed - If
last_autovacuumis NULL — autovacuum is not running (CRITICAL)
3. Index Usage
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 15;
\""idx_scan = 0and size > 1MB — unused index (can be dropped)
4. Missing Indexes
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT relname, seq_scan, seq_tup_read, idx_scan,
CASE WHEN seq_scan > 0 THEN seq_tup_read / seq_scan ELSE 0 END AS avg_seq_rows
FROM pg_stat_user_tables
WHERE seq_scan > 100 AND pg_relation_size(relid) > 100000
ORDER BY seq_tup_read DESC
LIMIT 10;
\""- High
seq_scanon a large table = index needed
5. Active Connections
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT state, count(*), max(now() - state_change) AS max_duration
FROM pg_stat_activity
WHERE datname = '$DB'
GROUP BY state;
\""idle in transaction> 5 minutes = connection leak
6. Long-Running Queries
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT pid, now() - pg_stat_activity.query_start AS duration, state, left(query, 100) AS query
FROM pg_stat_activity
WHERE datname = '$DB' AND state != 'idle'
AND now() - pg_stat_activity.query_start > interval '5 seconds'
ORDER BY duration DESC;
\""7. Cache Hit Ratio
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SELECT sum(heap_blks_read) AS heap_read, sum(heap_blks_hit) AS heap_hit,
CASE WHEN sum(heap_blks_hit) + sum(heap_blks_read) > 0
THEN round(sum(heap_blks_hit)::numeric / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2)
ELSE 0 END AS hit_ratio
FROM pg_statio_user_tables;
\""- < 95% — not enough RAM for cache, optimization needed
8. Backups
ssh -i ~/.ssh/id_rsa root@$IP "ls -lht /var/backups/pg/ 2>/dev/null || ls -lht /root/backups/ 2>/dev/null || echo 'Backups NOT FOUND'"
ssh -i ~/.ssh/id_rsa root@$IP "crontab -l 2>/dev/null | grep pg_dump"If no backups exist — this is CRITICAL. Suggest setting up:
0 3 * * * pg_dump -U $USER -h 127.0.0.1 $DB | gzip > /var/backups/pg/${DB}_$(date +\%Y\%m\%d).sql.gz && find /var/backups/pg/ -mtime +7 -delete9. PostgreSQL Configuration
ssh -i ~/.ssh/id_rsa root@$IP "PGPASSWORD=$PASS psql -U $USER -h 127.0.0.1 -d $DB -c \"
SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; SHOW max_connections; SHOW effective_cache_size;
\""Report Format
## 🗄️ DB Health Report — YYYY-MM-DD
### Summary
- DB size: X MB
- Tables: N
- Cache hit ratio: X%
- Active connections: N
### Tables (top by size)
| Table | Size | Dead Rows | Last Vacuum |
|-------|------|-----------|-------------|
| ... | ... | ... | ... |
### Issues
1. ❌ [critical] No backups!
2. ⚠️ [important] Table X is 35% bloated
3. ℹ️ [info] Index Y is unused
### Recommendations
- specific command for each issue
### Health Score: X/10Saving
Write to Agent-Notes/db-health.md (overwrite — only the latest is needed).
Rules
- Run ALL 9 checks
- Do NOT run VACUUM or ALTER yourself — only recommend
- For each recommendation provide a ready SQL/bash command
- Backups — priority #1 (if missing — first line of the report)
Agent Intelligence — Auto-Diagnostics
Decision Tree
Check result?
├─ Cache hit ratio < 90%
│ └─ CRITICAL → check shared_buffers → recommend increase
│
├─ dead_pct > 30%
│ └─ IMPORTANT → check autovacuum_enabled → if off, suggest enabling
│ └─ If on, but dead_pct is growing → check autovacuum_vacuum_threshold
│
├─ Seq scan > 1000 on a table > 10MB
│ └─ Index needed → suggest CREATE INDEX with specific columns
│ └─ Analyze EXPLAIN on a typical query from the project code
│
├─ No backups
│ └─ CRITICAL → suggest cron + pg_dump → suggest offsite (S3/Yandex Disk)
│
├─ idle in transaction > 5 min
│ └─ Connection leak → find PID → suggest pg_terminate_backend
│ └─ Recommend idle_in_transaction_session_timeout
│
├─ shared_buffers < 25% RAM
│ └─ Suboptimal → recommend 25-40% of server RAM
│
└─ Everything is healthy (hit_ratio > 99%, no bloat, backups exist)
└─ Short positive reportCross-Agent Integration
| Situation | Action |
| QA detected slow responses | → QA calls db-health |
| No backups | → suggest adding to deploy pre-check |
| Table >1GB | → suggest PARTITION (if date column exists) |
Self-Learning
After each diagnostic — append to Agent-Notes/db-health.md:
## YYYY-MM-DD — Score: X/10
- DB: fujidb | Size: X MB | Cache hit: X%
- Issues: [list] | Fixed: [what was fixed]
- Trend: [growing/stable/improving] vs previous reportDoes it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

