/database-optimization

name: database-optimization
description: "Schema design, index strategy, migration safety, and query analysis. TRIGGER when: designing tables or indexes, writing a migration, or diagnosing a slow query. SKIP: writing ORM model code (use python-patterns); generic backend patterns (use python-patterns)."

Schema Design Principles

| Form | Use When | |------|----------| | 1NF | Always (atomic values) | | 2NF | Most tables | | 3NF | Transactional data | | Denormalized | Read-heavy, reporting |

Index Strategy

| Type | Use Case | |------|----------| | B-Tree | Default, range queries | | Hash | Exact match only | | GIN (Postgres) | Full-text, JSONB, arrays | | Partial | Subset of rows | | Composite | Multi-column queries |

> Index type names vary by engine (e.g. GIN/GiST/BRIN are Postgres-specific; > MySQL/SQLite expose a different set). Treat engine-specific rows as examples.

When to Index

• Primary keys (automatic)
• Foreign keys
• WHERE clause columns
• ORDER BY columns
• JOIN columns

When NOT to Index

• Low cardinality columns
• Frequently updated columns
• Small tables (< 1000 rows)

Migration Safety

Safe Operations

• ADD COLUMN (nullable)
• ADD INDEX CONCURRENTLY
• CREATE TABLE
• ADD CONSTRAINT (with validation)

Dangerous Operations

• DROP COLUMN
• RENAME COLUMN
• ALTER COLUMN TYPE
• DROP TABLE

Migration Checklist

• [ ] Tested on production-like data
• [ ] Rollback script ready
• [ ] Estimated execution time
• [ ] Lock impact assessed
• [ ] Application compatibility verified

Query Analysis

Common Issues

| Issue | Symptom | Solution | |-------|---------|----------| | Missing index | Sequential scan | Add index | | N+1 queries | Many similar queries | Eager loading | | Over-fetching | SELECT * | Select specific columns | | No pagination | Large result sets | Add LIMIT/OFFSET | | Cartesian join | Exploding rows | Fix JOIN conditions |

Analysis Commands

Frontend

• `npm run build -- --analyze` - Bundle analysis
• `lighthouse` - Performance audit
• Browser DevTools Performance tab

Backend (Python)

• `py-spy` - CPU profiling
• `memory_profiler` - Memory analysis
• `EXPLAIN ANALYZE` - Query analysis

Best Practices

DO

• Measure before optimizing
• Design database for future scale
• Document schema decisions
• Use foreign keys and index them
• Plan migrations carefully
• Test with production-like data
• Focus on user-impacting metrics

DON'T

• Optimize prematurely
• Guess at bottlenecks
• Use SELECT *
• Skip foreign keys
• Over-index
• Modify released migrations
• Ignore query plans

Transaction Guidelines

• Keep transactions short; avoid I/O inside transactions
• Handle deadlocks with retry logic
• Default isolation: Read Committed (usually sufficient)
• Use Serializable only when phantom reads are unacceptable

Connection Pooling

| Setting | Development | Production | |---------|-------------|------------| | Min connections | 1 | 5 | | Max connections | 5 | 20-50 | | Idle timeout | 30s | 300s | | Max lifetime | 1800s | 3600s |

Pool size formula: `(cores * 2) + disk_spindles`. Always use pooling in production.

Naming Conventions

| Element | Convention | Example | |---------|------------|---------| | Tables | snake_case, plural | `users`, `order_items` | | Columns | snake_case | `first_name`, `created_at` | | Primary Key | `id` | `id` | | Foreign Key | `{table}_id` | `user_id`, `order_id` | | Indexes | `ix_{table}_{columns}` | `ix_users_email` |

---

Example: Async SQLAlchemy schema patterns (illustrative)

> Illustrative — this is one team's SQLAlchemy/Postgres setup shown as a concrete > example. Substitute your ORM, driver, and schema conventions. The schema-design, > indexing, and query-analysis guidance above is the engine-agnostic, reusable part.

Async SQLAlchemy Setup (`<your-database-module>.py`)

from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

engine = create_async_engine(
    DATABASE_URL,           # postgresql+asyncpg://...
    echo=False, future=True,
    pool_size=5, max_overflow=10,
    pool_recycle=3600,      # Recycle connections after 1 hour
    pool_pre_ping=True,     # Detect stale connections
)
async_session_maker = async_sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False
)

**Session dependency** (commit-on-success, rollback-on-error):

async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise

Model Conventions

All models inherit from the shared declarative `Base` and follow these patterns:

| Convention | Pattern | Example | |-----------|---------|---------| | Primary key | `String(36)`, UUID as string | `id: Mapped[str] = mapped_column(String(36), primary_key=True)` | | Timestamps | `DateTime(timezone=True)` + `utc_now` | `created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)` | | Foreign keys | Explicit `ondelete` policy | `ForeignKey("projects.id", ondelete="CASCADE")` | | Nullable FK | `ondelete="SET NULL"` | `ForeignKey("users.id", ondelete="SET NULL")` | | Indexes | On FKs and query columns | `index=True` on project_id, created_at, github_id | | Type hints | `Mapped[]` with `mapped_column` | SQLAlchemy 2.0 declarative style | | Relationships | `TYPE_CHECKING` guard for imports | Avoids circular imports between modules |

Models Overview

| Table | Model | Key Fields | |-------|-------|-----------| | `projects` | `Project` | id, path (unique), name, created_at | | `task_refs` | `TaskRef` | id, project_id (FK), conversation_id, session_id, created_by (FK) | | `users` | `User` | id (uuid4), github_id (unique), github_login, avatar_url | | `user_projects` | `Us