database-optimization

Schema Design Principles

Index Strategy

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

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

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

Naming Conventions

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:

Models Overview

Relationship Patterns

# Parent side - cascade delete orphans
tasks: Mapped[list["TaskRef"]] = relationship(
    "TaskRef", back_populates="project", cascade="all, delete-orphan"
)
# Child side
project: Mapped["Project"] = relationship("Project", back_populates="tasks")

Alembic Migration Conventions

• Sync driver: Alembic uses psycopg2 (strips +asyncpg from URL)
• Advisory locks: pg_advisory_lock(1573678) prevents concurrent migrations
• Schema validation: main.py validates ORM vs DB schema on startup, logs warnings
• Migration file naming: {hash}_{description}.py with upgrade() and downgrade()
• All models imported in env.py: Required for autogenerate support
• Safe pattern: Always include both upgrade() and downgrade() functions