Lesson 24: Performance#

Three real, code-verified performance costs — not hypothetical scaling advice.

1. Document Upload: N Sequential Network Calls#

Already the subject of Lesson 18 from a background-processing angle; here's the raw performance number. split_text_into_chunks at defaults (800 chars, 100 overlap) produces roughly one chunk per 700 net characters. add_chunk_to_vector_store is called once per chunk, serially, each call making one OpenAI embeddings HTTP round-trip:

At even a fast ~150ms per embedding call, a 1MB document blocks its upload request for well over three minutes. The fix isn't algorithmic — it's using OpenAI's batch embeddings endpoint (embed all of a document's chunks in one API call instead of one call each) combined with the background-processing change from Lesson 18.

2. The Dashboard Endpoint: Aggregation in Python, Not SQL#

tasks = db.query(models.Task).filter(models.Task.agent_id == agent_id).all()
tool_calls = db.query(models.ToolCall).filter(models.ToolCall.agent_id == agent_id).all()
workflow_runs = db.query(models.WorkflowRun).filter(models.WorkflowRun.agent_id == agent_id).all()
 
total_tasks = len(tasks)
open_tasks = len([task for task in tasks if task.status == "open"])
...
average_latency_ms = sum(latencies) / len(latencies)

GET /{agent_id}/dashboard (Lesson 09a) pulls every Task, ToolCall, and WorkflowRun row for the agent — full ORM objects, every column — into Python memory, purely to compute counts and an average. For an agent with 50,000 historical tool calls, this endpoint transfers 50,000 full rows from SQLite, materializes 50,000 Python objects, just to produce a handful of integers and one float. The SQL equivalent does the counting inside the database engine and returns only the aggregates:

from sqlalchemy import func, case
 
tool_call_stats = (
    db.query(
        func.count(models.ToolCall.id),
        func.sum(case((models.ToolCall.success == "true", 1), else_=0)),
        func.avg(models.ToolCall.latency_ms),
    )
    .filter(models.ToolCall.agent_id == agent_id)
    .first()
)

3. No Indexes Beyond Primary Keys#

Every model in models.py declares id = Column(Integer, primary_key=True, index=True) — SQLite automatically indexes primary keys, so this is mostly redundant (it's implicit), but it also means it's the only indexed column anywhere. Every foreign key used in a .filter() clause — Task.agent_id, ToolCall.agent_id, WorkflowRun.agent_id, Message.agent_id, DocumentChunk.agent_id — has no explicit index. SQLite can still filter on these columns, but it does a full table scan to do it rather than an index lookup. At the row counts a development database has (dozens to low hundreds), this is imperceptible; at the row counts implied by "tens of thousands of tool calls" above, it's the difference between a query that returns in milliseconds and one that scans the entire table.

agent_id = Column(Integer, ForeignKey("agents.id"), nullable=False, index=True)

is the one-line fix, repeated across each foreign-key column that's actually queried by — every single list/dashboard endpoint in routers/agents.py and routers/documents.py filters by agent_id.

A Fourth, Smaller One: run_startup_migrations() Runs on Every Boot#

def run_startup_migrations():
    if not DATABASE_URL.startswith("sqlite"):
        return
    with engine.begin() as connection:
        existing_columns = {row[1] for row in connection.execute(text("PRAGMA table_info(tool_calls)"))}
        for column_name, column_type in tool_call_columns.items():
            if column_name not in existing_columns:
                connection.execute(text(f"ALTER TABLE tool_calls ADD COLUMN {column_name} {column_type}"))

Runs at every server startup (main.py calls it unconditionally, right after Base.metadata.create_all), not just on the first boot after a schema change. It's cheap (one PRAGMA query plus, at most, three conditional ALTER TABLEs) so this isn't a real cost today — but it's worth noticing as the shape of a problem that gets worse the more migrations accumulate this way, since Lesson 27 covers why a real migration tool would scale better than this ad-hoc approach.

What This Codebase Gets Right, Performance-Wise#

Worth balancing the ledger: SessionLocal's connection pooling defaults are untouched (fine for SQLite, which doesn't benefit from large pools the way a networked database does), and the corrective RAG graph's empty-retrieval short-circuit (Lesson 08e) actively saves an LLM call rather than costing one — not everything here is a performance problem.

Common Mistakes#

• Benchmarking the dashboard endpoint against a development database with a handful of rows and concluding it's fast. The Python-side aggregation cost scales with row count; a dev database won't reveal it.
• Adding indexes to every column "just in case." Indexes cost write performance and storage — only the foreign keys actually used in WHERE/.filter() clauses (all five agent_id columns above) are justified by the current query patterns.

Exercises#

1. Rewrite get_agent_dashboard's task-counting section to use one grouped SQL query (func.count + case, or group_by(models.Task.status)) instead of loading every Task row.
2. Add index=True to the five agent_id foreign-key columns identified above, and explain (in terms of run_startup_migrations's pattern) how you'd apply this change to an already-running SQLite database without losing data.
3. Measure, using Python's time module and a script that inserts 10,000 fake ToolCall rows, the wall-clock difference between the current dashboard implementation and the aggregate-query rewrite from exercise 1.

Key Takeaways#

🎯 Document upload makes one sequential network call per chunk — no batching, no concurrency. 🎯 The dashboard endpoint loads full row objects to compute aggregates that SQL could compute directly. 🎯 No foreign-key column used in .filter() clauses is explicitly indexed. 🎯 These are read directly from the source, not generic "best practices" — each one has a concrete before/after fix.

Next Steps#

👉 Lesson 25: Security →

Module 6 · Production Mastery