Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

2.7 KB
Raw Blame

query_performance_ai

Local-only coordinator that hands prod ClickHouse slow queries to a sandboxed agent and asks it to optimise them.

The agent is pi-coding-agent running the pi-autoresearch extension under our bundled clickhouse-autoresearch-campaign skill (sandboxed_autoresearch_agent/pi_plugin/).

Prereqs

  • macOS — the coordinator hard-fails on other platforms.
  • A docker daemon (Orbstack etc).
  • ANTHROPIC_API_KEY in your .env.

The python -m query_performance_ai.… commands below need this bundle on PYTHONPATH (the inner query_performance_ai package imports products.tasks from the repo root, so both must be importable). Run from the repo root with PYTHONPATH=.:tools/query-performance-ai, or cd tools/query-performance-ai with the repo root on the path.

What happens when you run it

  1. Fetch slow queries from Metabase's prod system.query_log (filtered hard to ai_data_processing_approved = true — we only ever feed the agent queries that the customer explicitly approved for AI analysis).
  2. Open a token-gated HTTP server on localhost.
  3. Spawn one Docker sandbox per query. Each sandbox clones this repo's branch from origin, locks down its network with iptables, and runs pi-coding-agent.
  4. The agent submits candidate SQL through the coordinator's /v1/run; the coordinator forwards each query to the configured backend.
  5. Artifacts (best.sql, metrics, lane / hypothesis notes) are harvested to data/runs/<query_id>_<run_id>/ and the sandbox is destroyed.

Backends

# Replay candidates against a Metabase-fronted test cluster:
python -m query_performance_ai.orchestrator.coordinator \
    --target test_cluster --metabase-region us \
    --query-log-database-id 142 --test-cluster-database-id 146 \
    --team-id 2 --max-queries 5
# Requires `hogli metabase:login --region us` first.

# Replay candidates against your local dev ClickHouse:
python -m query_performance_ai.orchestrator.coordinator --target local

--target decides where candidate SQL runs. Slow queries are always sourced from Metabase regardless; only the execution destination differs.

Smoke test

python -m query_performance_ai.orchestrator.coordinator --target local --test-query

--test-query skips Metabase and feeds SELECT 1, sleep(1) as the campaign SQL. End-to-end smoke — the agent should drop the sleep within a couple of iterations.

Privacy

The Metabase fetch in orchestrator/slow_queries.py filters to ai_data_processing_approved = true in log_comment. This is a hard SQL predicate, not a runtime check; no flag bypasses it. --test-query runs a synthetic constant, not a real customer query.