---

name: db-performance description: Debug and troubleshoot database performance in ettametta. Use when investigating slow queries, connection pool issues, migration conflicts, N+1 patterns, or schema drift.

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.