agentleFS
3akat-system3akat-system/nexus-starter/.claude/skills/db-health/SKILL.md

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

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
  1. Very specific:

    Use the obtained values as $PGPASSWORD, $DB_USER, $DB_NAME in the commands below.

    line 32
  2. Very specific:

    If dead_pct > 20% — VACUUM is needed

    line 68
  3. Very specific:

    If last_autovacuum is NULL — autovacuum is not running (CRITICAL)

    line 69
  4. Distinctive:

    If no backups exist — this is CRITICAL.

    line 135
  5. Very specific:

    Write to Agent-Notes/db-health.md (overwrite — only the latest is needed).

    line 175
  6. Distinctive:

    Do NOT run VACUUM or ALTER yourself — only recommend

    line 179
  7. Very specific:

    After each diagnostic — append to Agent-Notes/db-health.md:

    line 226

≈ “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.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

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

DBServerIPSSH
fujidbBeget155.212.210.165ssh -i ~/.ssh/id_rsa root@155.212.210.165
plusoneBeget31.128.42.61ssh -i ~/.ssh/id_rsa root@31.128.42.61

NEVER hardcode passwords. Get them from .env on 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_autovacuum is 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 = 0 and 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_scan on 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 -delete

9. 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/10

Saving

Write to Agent-Notes/db-health.md (overwrite — only the latest is needed).

Rules

  1. Run ALL 9 checks
  2. Do NOT run VACUUM or ALTER yourself — only recommend
  3. For each recommendation provide a ready SQL/bash command
  4. 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 report

Cross-Agent Integration

SituationAction
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 report

Download now (ZIP)

Does 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
What did you use it for? e.g. Reviewing pull requests in a Rails app

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.