The Slow Query Sentinel: A Zero-Dollar Server That Explains Your Database's Worst SQL

Описание и применение

Сентинель автоматически выявляет самые медленные запросы в PostgreSQL, отправляет их в бесплатную LLM и получает простые объяснения и рекомендации, ускоряя диагностику проблем с производительностью.

Задача

On-call engineers often spend long time diagnosing slow queries after a sudden performance drop, typically by manually inspecting EXPLAIN output and identifying root causes such as index regressions.

Результат

The sentinel reduces the time from noticing a slow query to understanding its root cause from hours to minutes, providing clear, high-impact fixes such as index suggestions.

Рабочий процесс

• 1. Prepare a free server database: enable pg_stat_statements and create a query_advice table.
• 2. Write a collector that queries pg_stat_statements for top queries, fetches EXPLAIN (FORMAT JSON) for each, and stages the data.
• 3. Implement a model caller that sends query text and plan JSON to a free LLM endpoint, requesting a structured JSON answer with advice and impact.
• 4. Parse the model response and insert advice into query_advice table.
• 5. Query the table to rank recommendations by impact, presenting the highest-value fixes first.

Доказательства

The sentinel I describe below pulls the slowest queries from pg_stat_statements, formats them with their execution plans, and asks a free model to explain the root cause in plain language.

Источник

A typical response looks like this: the model points out a sequential scan on a large table, suggests a composite index, and marks the impact as high.

Источник