db-performance
Diagnoses database performance issues, query latency, and migration conflicts in the ettametta project.
Install
mkdir -p .claude/skills/db-performance && curl -L -o skill.zip "https://agentskills.codes/api/skills/download/19016" && unzip -o skill.zip -d .claude/skills/db-performance && rm skill.zipInstalls to .claude/skills/db-performance
Activation
This is the description your AI agent reads to decide when to run this skill — the better it matches your request, the more reliably it fires.
Debug and troubleshoot database performance in ettametta. Use when investigating slow queries, connection pool issues, migration conflicts, N+1 patterns, or schema drift.Key capabilities
- →Monitor database connection count
- →Identify active queries and their durations
- →Determine table sizes
- →Check Alembic migration state
- →Diagnose connection pool exhaustion
How it works
The skill executes `psql` and `alembic` commands to gather diagnostic data on connection counts, active queries, table sizes, and migration status for the `ettametta` database.
Inputs & outputs
When to use db-performance
- →Investigate slow queries
- →Check database connection pool
- →Verify migration state
- →Optimize ORM models
About this skill
Database Performance Debugging
Quick Diagnostics
# Connection count
docker compose exec db psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"
# Active queries
docker compose exec db psql -U postgres -c "SELECT pid, now()-query_start AS duration, query FROM pg_stat_activity WHERE state='active' ORDER BY duration DESC;"
# Table sizes
docker compose exec db psql -U postgres -c "SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"
# Migration state
alembic heads
alembic current
alembic check
Connection Configuration
Async engine (FastAPI): pool_pre_ping=True, defaults pool_size=5, max_overflow=10
Sync engine (Celery): Default QueuePool, pool_size=5, max_overflow=10
Celery task-scoped: pool_size=2, max_overflow=0 (tight isolation)
NullPool for Celery workers: Fresh engine per call, disposed after use. Expensive but avoids event-loop conflicts.
Default DATABASE_URL: sqlite:///./data/db/ettametta.db. Production uses PostgreSQL.
Session Management
- Pattern A (routes):
get_db()dependency with rollback on exception - Pattern B (services):
async_session_factory()inline - Pattern C (Celery):
get_async_session()with NullPool
All use expire_on_commit=False, autocommit=False, autoflush=False.
Models
35 ORM models. Zero relationship() declarations — all cross-table access via explicit joins.
Key models: UserDB (7 unique indexes), ContentCandidateDB (external_id unique, niche, region), VideoJobDB, PublishedContentDB, ScheduledPostDB, SocialAccount.
Missing indexes (potential)
- video_jobs.user_id
- audit_logs.user_id
- scheduled_posts.user_id
- nexus_jobs.user_id
No composite indexes declared.
Migrations
20 migration files. Current HEAD: d410fb0d40a9. 2 merge migrations exist.
CI/CD bypasses Alembic: Deployment uses create_all() not alembic upgrade head.
Query Patterns
N+1 risks
- Nexus stats: 4 separate COUNT(*) queries (lines 482-506 in routes/nexus.py). Fix: single GROUP BY.
- Settings: 2 separate queries for system + user settings. Fix: join.
In-memory pagination
Discovery routes use paginate_list() — loads all then slices. Use SQL .offset().limit().
DateTime Monkeypatch
database.py lines 12-24: strips timezone info from DateTime. Workaround for SQLite/PG compat.
Common Issues
Connection pool exhaustion
docker compose exec db psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"
Stale connections
Async engine has pool_pre_ping=True. Sync engine does not.
NullPool performance
get_async_session() creates/disposes full engine per call. Expensive for high-frequency ops.
Missing composite indexes
Queries on multiple columns do sequential scans. Add Index() declarations.
expire_on_commit=False
Correct for async but means stale data if objects reused after commit without refresh.
When not to use it
- →When the database is not `ettametta`
- →When the issue is not related to slow queries, connection pools, migrations, N+1 patterns, or schema drift
- →When the database is not PostgreSQL or SQLite
Limitations
- →Requires `docker compose exec db psql` access to the database.
- →Requires `alembic` to be installed and configured.
- →CI/CD bypasses Alembic, using `create_all()` instead of `alembic upgrade head`.
How it compares
This skill provides specific commands and insights for diagnosing `ettametta` database performance issues, offering targeted troubleshooting instead of general database monitoring.
Compared to similar skills
db-performance side by side with the closest alternatives in the catalog.
| Skill | Installs | Updated | Safety | Difficulty |
|---|---|---|---|---|
| db-performance (this skill) | 0 | 1mo | Review | Intermediate |
| monitoring-database-transactions | 1 | 10d | Review | Advanced |
| postgres-pro | 0 | 2mo | No flags | Advanced |
| supabase-observability | 0 | 10d | Review | Intermediate |
Try saying
Example prompts that trigger this skill in your AI assistant.
You might also like
monitoring-database-transactions
jeremylongshore
Monitor use when you need to work with monitoring and observability. This skill provides health monitoring and alerting with comprehensive guidance and automation. Trigger with phrases like "monitor system health", "set up alerts", or "track metrics".
postgres-pro
Jeffallan
Use when optimizing PostgreSQL queries, configuring replication, or implementing advanced database features. Invoke for EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning, performance monitoring.
supabase-observability
jeremylongshore
Execute set up comprehensive observability for Supabase integrations with metrics, traces, and alerts. Use when implementing monitoring for Supabase operations, setting up dashboards, or configuring alerting for Supabase integration health. Trigger with phrases like "supabase monitoring", "supabase metrics", "supabase observability", "monitor supabase", "supabase alerts", "supabase tracing".
transaction-correctness
tursodatabase
How WAL mechanics, checkpointing, concurrency rules, recovery work in tursodb
audit
senda-labs
Run complete system health audit of DQIII8 — checks DB integrity, agent performance, pipeline connections, error log, and services. Produces a scored Markdown report.
altinity-expert-clickhouse-metrics
ntk148v
Real-time monitoring of ClickHouse metrics, events, and asynchronous metrics. Use for load average, connections, queue monitoring, and resource saturation.