Database Performance Optimization for AI Agents: Indexing, Query Tuning, and Caching

Performance Bottlenecks in Agent Systems

AI agent databases face a distinctive load pattern. Reads are heavy during context retrieval — loading conversation history, fetching knowledge base articles, and querying user preferences. Writes are bursty during agent execution — inserting messages, recording tool calls, and updating metrics. The latency budget is tight because every millisecond of database overhead adds directly to the user's perceived response time.

The three most common performance problems in agent databases are missing indexes on conversation lookups, N+1 queries when loading message histories, and unbounded queries that fetch entire conversation threads instead of recent messages.

EXPLAIN ANALYZE: Your Primary Diagnostic Tool

Before optimizing anything, measure. PostgreSQL's EXPLAIN ANALYZE shows exactly how a query executes:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT m.id, m.role, m.content, m.created_at
FROM messages m
WHERE m.conversation_id = '550e8400-e29b-41d4-a716-446655440000'
ORDER BY m.created_at;

The output tells you everything:

Sort  (cost=1250.32..1256.45 rows=2453 width=312) (actual time=45.2..47.1 rows=2453 loops=1)
  Sort Key: created_at
  Sort Method: external merge  Disk: 1024kB
  Buffers: shared hit=12 read=234
  ->  Seq Scan on messages m  (cost=0.00..1120.00 rows=2453 width=312) (actual time=0.05..32.4 rows=2453 loops=1)
        Filter: (conversation_id = '550e8400...'::uuid)
        Rows Removed by Filter: 47547
        Buffers: shared hit=12 read=234
Planning Time: 0.15 ms
Execution Time: 48.3 ms

Red flags in this output: Seq Scan (no index used), Rows Removed by Filter: 47547 (scanning the entire table), external merge Disk (sort spilled to disk). This query needs an index.

Strategic Index Design

Create compound indexes that serve both the filter and sort:

-- This single index eliminates the Seq Scan AND the Sort step
CREATE INDEX idx_messages_conv_created
    ON messages(conversation_id, created_at);

After adding the index, the same query plan becomes:

Index Scan using idx_messages_conv_created on messages m
  (actual time=0.03..0.45 rows=2453 loops=1)
  Index Cond: (conversation_id = '550e8400...'::uuid)
  Buffers: shared hit=28
Planning Time: 0.12 ms
Execution Time: 0.68 ms

From 48ms to 0.68ms — a 70x improvement from one index.

Index design principles for agent databases:

-- Conversation list: filter by user, sort by recency
CREATE INDEX idx_conv_user_updated
    ON conversations(user_id, updated_at DESC);

-- Active conversations only (partial index)
CREATE INDEX idx_conv_active_user
    ON conversations(user_id, updated_at DESC)
    WHERE status = 'active' AND deleted_at IS NULL;

-- Tool analytics: filter by tool name and time range
CREATE INDEX idx_tool_exec_name_time
    ON tool_executions(tool_name, created_at DESC);

-- Covering index: includes content to avoid table lookup
CREATE INDEX idx_messages_conv_covering
    ON messages(conversation_id, created_at)
    INCLUDE (role, content);

The covering index with INCLUDE avoids fetching the actual table row when the query only needs the included columns. This is particularly effective for conversation loading where you select role and content.

Eliminating N+1 Queries

The classic N+1 problem in agent systems: loading a conversation list with the latest message from each:

# BAD: N+1 queries
conversations = await session.execute(
    select(Conversation).where(Conversation.user_id == user_id)
)
for conv in conversations:
    # This triggers a separate query per conversation
    latest_msg = conv.messages[-1]

Fix with a lateral join or subquery:

-- Single query: conversations with their latest message
SELECT c.id, c.title, c.updated_at,
    lm.role AS last_role,
    lm.content AS last_content
FROM conversations c
LEFT JOIN LATERAL (
    SELECT role, content
    FROM messages
    WHERE conversation_id = c.id
    ORDER BY created_at DESC
    LIMIT 1
) lm ON true
WHERE c.user_id = $1
  AND c.deleted_at IS NULL
ORDER BY c.updated_at DESC
LIMIT 20;

In SQLAlchemy:

from sqlalchemy import select, lateral, func

latest_msg = (
    select(Message.role, Message.content)
    .where(Message.conversation_id == Conversation.id)
    .order_by(Message.created_at.desc())
    .limit(1)
    .lateral("latest_message")
)

stmt = (
    select(
        Conversation.id,
        Conversation.title,
        latest_msg.c.role,
        latest_msg.c.content,
    )
    .outerjoin(latest_msg, literal(True))
    .where(Conversation.user_id == user_id)
    .where(Conversation.deleted_at.is_(None))
    .order_by(Conversation.updated_at.desc())
    .limit(20)
)

Materialized Views for Analytics

Agent dashboards that aggregate data across thousands of conversations should not query raw tables on every page load:

CREATE MATERIALIZED VIEW conversation_stats AS
SELECT
    agent_id,
    date_trunc('day', c.created_at) AS day,
    count(*) AS total_conversations,
    avg(msg_counts.msg_count) AS avg_messages_per_conv,
    sum(msg_counts.total_tokens) AS total_tokens
FROM conversations c
JOIN LATERAL (
    SELECT
        count(*) AS msg_count,
        coalesce(sum(token_count), 0) AS total_tokens
    FROM messages
    WHERE conversation_id = c.id
) msg_counts ON true
WHERE c.created_at > now() - INTERVAL '90 days'
GROUP BY agent_id, day;

CREATE UNIQUE INDEX idx_conv_stats_agent_day
    ON conversation_stats(agent_id, day);

Refresh the materialized view on a schedule:

-- Concurrent refresh allows reads during the refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY conversation_stats;

Application-Level Caching with Redis

Cache frequently accessed, rarely changing data like agent configurations and user preferences:

import json
import redis.asyncio as redis

cache = redis.from_url("redis://localhost:6379/0")


async def get_agent_config(agent_id: str) -> dict:
    cache_key = f"agent:config:{agent_id}"

    # Check cache first
    cached = await cache.get(cache_key)
    if cached:
        return json.loads(cached)

    # Cache miss: query database
    async with get_session() as session:
        agent = await session.get(Agent, agent_id)
        if not agent:
            raise ValueError(f"Agent {agent_id} not found")

        config = {
            "id": agent.id,
            "name": agent.name,
            "model": agent.model,
            "instructions": agent.instructions,
            "config": agent.config_,
        }

    # Cache for 5 minutes
    await cache.setex(cache_key, 300, json.dumps(config))
    return config


async def invalidate_agent_config(agent_id: str):
    await cache.delete(f"agent:config:{agent_id}")

Do not cache conversation messages — they change with every interaction. Cache agent configs, system prompts, knowledge base articles, and aggregated statistics.

Monitoring Query Performance

Set up continuous monitoring to catch regressions:

-- Find the slowest queries in the last hour
SELECT
    round(mean_exec_time::numeric, 2) AS avg_ms,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    query
FROM pg_stat_statements
WHERE mean_exec_time > 50  -- Queries averaging over 50ms
ORDER BY mean_exec_time DESC
LIMIT 20;

Enable pg_stat_statements in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Review this weekly. A query that was fast with 10,000 rows may degrade at 1 million rows if an index is missing.

FAQ

How do I decide between adding an index and rewriting a query?

Run EXPLAIN ANALYZE on the query first. If the plan shows a Seq Scan on a large table with a selective WHERE clause, an index will help. If the plan shows an efficient index scan but the query returns too many rows or does unnecessary joins, rewrite the query. Often you need both — a better query structure paired with the right index.

When should I use materialized views versus application caching?

Use materialized views for complex aggregations that multiple parts of your application need — they stay in PostgreSQL and benefit from SQL query composition. Use application caching (Redis) for simple key-value lookups, session data, and cases where you need sub-millisecond latency. Materialized views refresh on a schedule; application caches can invalidate immediately on writes.

How do I prevent a single slow agent query from affecting other users?

Use statement_timeout at the session level: SET statement_timeout = '5s' cancels any query running longer than 5 seconds. Configure per-role timeouts for different query types — agent context loading might allow 5 seconds while dashboard analytics allow 30 seconds. Also use connection pooling (PgBouncer in transaction mode) to prevent one slow query from exhausting all connections.

---

#Performance #PostgreSQL #Indexing #QueryOptimization #Caching #AIAgents #AgenticAI #LearnAI #AIEngineering