Spaces:

yashMaini
/

pg-reliability-agent

Running

File size: 14,719 Bytes

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Postgres Reliability Agent</title>
<style>
  :root {
    --bg: #0d1117; --panel: #161b22; --line: #262d36;
    --fg: #e6edf3; --muted: #8b949e; --dim: #6e7681;
    --accent: #58a6ff; --good: #3fb950; --warn: #d29922; --bad: #f85149;
    --mono: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, monospace;
  }
  * { box-sizing: border-box; }
  body {
    margin: 0; background: var(--bg); color: var(--fg);
    font: 16px/1.65 -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
    -webkit-font-smoothing: antialiased;
  }
  .wrap { max-width: 860px; margin: 0 auto; padding: 56px 20px 96px; }
  h1 { font-size: 2.1rem; line-height: 1.2; margin: 0 0 12px; letter-spacing: -.02em; }
  h2 { font-size: 1.25rem; margin: 56px 0 16px; letter-spacing: -.01em; }
  h2::before { content: "§ "; color: var(--dim); font-weight: 400; }
  p { margin: 0 0 16px; }
  a { color: var(--accent); }
  .lede { font-size: 1.1rem; color: var(--muted); margin-bottom: 28px; }
  code, .mono { font-family: var(--mono); font-size: .875em; }
  code { background: var(--panel); padding: .15em .4em; border-radius: 4px; }

  .scores { display: flex; flex-wrap: wrap; gap: 12px; margin: 28px 0 8px; }
  .score {
    flex: 1 1 150px; background: var(--panel); border: 1px solid var(--line);
    border-radius: 10px; padding: 16px 18px;
  }
  .score b { display: block; font-family: var(--mono); font-size: 1.6rem; color: var(--good); }
  .score span { color: var(--muted); font-size: .82rem; }

  .tablewrap { overflow-x: auto; margin: 0 0 16px; }
  table { border-collapse: collapse; width: 100%; font-size: .9rem; min-width: 560px; }
  th, td { text-align: left; padding: 10px 12px; border-bottom: 1px solid var(--line); vertical-align: top; }
  th { color: var(--muted); font-weight: 600; font-size: .78rem; text-transform: uppercase; letter-spacing: .05em; }
  td.k { font-family: var(--mono); font-size: .84rem; white-space: nowrap; }
  .no { color: var(--bad); } .yes { color: var(--good); }

  .finding {
    border: 1px solid var(--line); border-left: 3px solid var(--warn);
    border-radius: 8px; background: var(--panel); padding: 18px 20px; margin: 0 0 14px;
  }
  .finding.high { border-left-color: var(--bad); }
  .finding .tag {
    font-family: var(--mono); font-size: .72rem; color: var(--muted);
    text-transform: uppercase; letter-spacing: .06em;
  }
  .finding h3 { margin: 6px 0 10px; font-size: 1rem; }
  .finding p { margin: 0 0 12px; color: var(--muted); font-size: .92rem; }
  .fix {
    font-family: var(--mono); font-size: .82rem; background: #0b0f14;
    border: 1px solid var(--line); border-radius: 6px; padding: 10px 12px;
    color: var(--good); overflow-x: auto; white-space: pre;
  }
  .ev { margin-top: 12px; font-family: var(--mono); font-size: .78rem; color: var(--dim); }
  .ev span { display: inline-block; margin-right: 14px; }

  .flow {
    font-family: var(--mono); font-size: .82rem; background: var(--panel);
    border: 1px solid var(--line); border-radius: 8px; padding: 18px 20px;
    color: var(--muted); overflow-x: auto; white-space: pre; line-height: 1.7;
  }
  .note {
    border-left: 3px solid var(--line); padding: 2px 0 2px 16px;
    color: var(--muted); font-size: .92rem; margin: 20px 0;
  }
  .links { display: flex; flex-wrap: wrap; gap: 10px; margin-top: 12px; }
  .links a {
    background: var(--panel); border: 1px solid var(--line); border-radius: 8px;
    padding: 10px 16px; text-decoration: none; font-size: .9rem;
  }
  .links a:hover { border-color: var(--accent); }
  footer { margin-top: 64px; color: var(--dim); font-size: .84rem; border-top: 1px solid var(--line); padding-top: 20px; }
  @media (max-width: 600px) { .wrap { padding: 36px 16px 64px; } h1 { font-size: 1.7rem; } }
</style>
</head>
<body>
<div class="wrap">

  <h1>Postgres Reliability Agent</h1>
  <p class="lede">
    Diagnoses Postgres incidents from the stats views. <strong>No model decides
    what's wrong</strong> — deterministic rules and <code>EXPLAIN (GENERIC_PLAN)</code> do.
  </p>

  <div class="scores">
    <div class="score"><b>5/5</b><span>faults detected</span></div>
    <div class="score"><b>5/5</b><span>ranked first</span></div>
    <div class="score"><b>0</b><span>findings when healthy</span></div>
  </div>
  <p class="note">
    That last number is the one that matters. A tool that invents problems on a
    working database is worse than one that misses them — nobody reads its
    alerts by week three.
  </p>

  <h2>Why not just ask an LLM</h2>
  <p>
    Ask a model <em>"what's wrong with this database?"</em> and it will tell you
    something, because it is trained to be helpful rather than to say
    <em>"nothing."</em> Detection has to be deterministic; the model belongs
    above that layer, explaining and fixing, not deciding.
  </p>
  <p>
    The scenario set is built to punish the alternative. On <strong>four of the
    five faults the obvious answer is wrong</strong>:
  </p>

  <div class="tablewrap">
  <table>
    <thead><tr><th>fault</th><th>pattern-matching says</th><th>actually correct</th></tr></thead>
    <tbody>
      <tr><td class="k">missing_index</td><td>add an index</td><td class="yes">✓ add an index</td></tr>
      <tr><td class="k">plan_regression</td><td>add an index</td><td class="no">✗ run ANALYZE — the schema is fine</td></tr>
      <tr><td class="k">bloat</td><td>the table is just large</td><td class="no">✗ dead tuples, autovacuum is off</td></tr>
      <tr><td class="k">lock_contention</td><td>kill the slow queries</td><td class="no">✗ they're victims; one holder is at fault</td></tr>
      <tr><td class="k">n_plus_1</td><td>nothing is slow, it's healthy</td><td class="no">✗ 1000 calls at 0.04 ms each</td></tr>
      <tr><td class="k">healthy</td><td>find something anyway</td><td class="no">✗ the answer is no finding</td></tr>
    </tbody>
  </table>
  </div>
  <p>
    Two naive baselines, scored against the same six cases rather than asserted
    — <code>python -m evals.baselines</code> reproduces this:
  </p>
  <div class="tablewrap">
  <table>
    <thead><tr><th>approach</th><th>score</th></tr></thead>
    <tbody>
      <tr>
        <td>always name something — for a database, usually an index</td>
        <td class="k no">1 / 6</td>
      </tr>
      <tr>
        <td>slowest statement over 10 ms <em>and</em> a large table → missing index</td>
        <td class="k no">2 / 6</td>
      </tr>
      <tr>
        <td>deterministic detectors</td>
        <td class="k yes">6 / 6</td>
      </tr>
    </tbody>
  </table>
  </div>
  <p class="note">
    The second baseline is right twice: it names the one genuine index problem,
    and it stays quiet on the healthy database. On the other four it returns
    <strong>nothing at all</strong> — none of those faults presents as a slow
    query. Stale statistics, bloat, a lock holder and an N+1 loop are invisible
    to any heuristic that ranks by duration, which is what most dashboards do.
  </p>

  <h2>The pair that proves it works</h2>
  <p>
    Both of these are a slow query against a multi-million-row table. They need
    opposite fixes, and the agent separates them from the stats alone. Real
    output, unedited:
  </p>

  <div class="finding high">
    <div class="tag">high · missing_index</div>
    <h3>Sequential scan on order_items — index verified to fix it</h3>
    <p>order_items has 2,500,000 rows and the planner has no index for this
       predicate, so it scans the whole table on every call. Creating product_id
       as a hypothetical index and re-planning drops the estimated cost from
       32,591 to 645 (51×), and the planner chooses it.</p>
    <div class="fix">CREATE INDEX CONCURRENTLY ON order_items (product_id);</div>
    <div class="ev">
      <span>calls: 150</span><span>mean: 79.42 ms</span>
      <span>filter: (product_id = $1)</span>
      <span>cost_before: 32590</span><span>cost_after: 645</span>
      <span>planner_uses_index: true</span>
    </div>
  </div>

  <div class="finding high">
    <div class="tag">high · stale_stats</div>
    <h3>Stale planner statistics on orders</h3>
    <p>300,000 rows in orders have changed since the last ANALYZE (23% of the
       table). autovacuum is disabled on this table, so nothing will refresh
       them on its own.</p>
    <div class="fix">ANALYZE orders;
ALTER TABLE orders SET (autovacuum_enabled = true);</div>
    <div class="ev">
      <span>n_mod_since_analyze: 300000</span><span>live_tuples: 1300000</span>
      <span>autovacuum_enabled: false</span>
    </div>
  </div>

  <p class="note">
    An index on the second one would cost write throughput and fix nothing.
    <code>missing_index</code> stays silent there because it requires
    <em>slow</em> <strong>and</strong> a confirmed sequential scan on a large
    table — not slowness alone.
  </p>

  <h2>And the one nothing else catches</h2>
  <div class="finding">
    <div class="tag">medium · n_plus_1</div>
    <h3>Statement called 1,000 times returning 2.6 rows each</h3>
    <p>Called 1,000 times at 0.044 ms per call. Each call is fast; the cost is
       the round trips. This is an application loop issuing one query per parent
       row.</p>
    <div class="fix">Batch the lookup — WHERE fk = ANY($1), or eager-load in the ORM.
No database change needed.</div>
    <div class="ev"><span>total: 43.9 ms</span><span>rows_per_call: 2.64</span></div>
  </div>
  <p>
    The slowest query in that database is <strong>0.044 ms</strong>. Every
    dashboard sorted by duration shows a clean bill of health.
  </p>

  <h2>How it works</h2>
  <div class="flow">collect      one read-only snapshot — pg_stat_statements, pg_stat_user_tables,
             pg_stat_activity, the blocking graph, pg_settings
   ↓
detectors    deterministic rules. no model involved.
   ↓
explain      EXPLAIN (GENERIC_PLAN) on suspect statements
   ↓
findings     root cause + fix + the numbers each rule fired on</div>
  <p>
    <code>pg_stat_statements</code> normalizes queries to
    <code>WHERE product_id = $1</code>, so a plain <code>EXPLAIN</code> can't run
    them. <code>GENERIC_PLAN</code> (PG16+) plans them anyway — so the agent reads
    the planner's real choice instead of guessing from timings, and never replays
    anything against your data.
  </p>
  <p>
    Every finding carries the evidence it fired on, so it can be checked rather
    than trusted. The connection is read-only with a statement timeout, so it's
    safe to point at a production replica.
  </p>

  <h2>It proves the index before recommending it</h2>
  <p>
    Anything can print <em>“add an index on product_id.”</em> Before it says
    that, this builds the candidate as a <strong>hypothetical</strong> index —
    no disk write, no lock, no <code>ACCESS EXCLUSIVE</code> on a 2.5M row table
    — re-plans the query, and reads what the planner actually decides.
  </p>
  <div class="tablewrap">
  <table>
    <thead><tr><th>candidate</th><th>estimated cost</th><th>verdict</th></tr></thead>
    <tbody>
      <tr><td class="k">order_items(product_id)</td>
          <td class="k">32,409 → 205 &nbsp;<strong>158×</strong></td>
          <td class="yes">recommend</td></tr>
      <tr><td class="k">users(country)</td>
          <td class="k">4,772 → 3,303 &nbsp;1.4×</td>
          <td class="no">decline</td></tr>
      <tr><td class="k">events(event_type)</td>
          <td class="k">35,602 → 27,179 &nbsp;1.3×</td>
          <td class="no">decline</td></tr>
    </tbody>
  </table>
  </div>
  <p class="note">
    The useful surprise: <em>“does the planner use it”</em> is a weak test. The
    planner adopted <strong>all three</strong> — it will take almost any index
    that shaves a little cost. But an index is paid for on every INSERT and
    UPDATE forever, so the margin decides, not adoption. The bar is 3×.
  </p>
  <p>
    So a suggestion has four possible outcomes rather than one: verified (with
    the delta), marginal (adopted, not worth building), refused (the planner
    won't use it, so an index is the wrong answer), and unverified — labelled
    as such rather than quietly implied.
  </p>

  <h2>What it does on a database that isn't mine</h2>
  <p>
    Most Postgres instances don't have <code>pg_stat_statements</code> installed
    and most logins aren't superuser. Every detector declares what it needs and
    is <strong>skipped with a reason</strong> rather than silently returning
    nothing — because “no findings” and “couldn't look” are different answers
    and a tool that conflates them is worse than useless.
  </p>
  <div class="flow">$ pgra --dsn postgresql://reader@replica/app capabilities

  pg_stat_statements   no    <span style="color:#8b949e">query-level detectors unavailable</span>
  generic_plan         yes
  read_all_stats       no    <span style="color:#8b949e">other users' query text is hidden</span></div>
  <p>
    Run that first against anything unfamiliar and you know which detectors are
    live before you trust a clean report. The three catalog-only detectors —
    stale statistics, bloat, lock contention — work on any Postgres with no
    extensions at all.
  </p>

  <h2>Limitations</h2>
  <p>
    Five failure modes, not a complete taxonomy — real Postgres also fails
    through connection exhaustion, transaction wraparound, replication lag and
    bad migrations. Detection is read-only and advisory: it proposes fixes and
    never applies them. The sandbox is a synthetic e-commerce schema on a single
    Postgres 16 instance, so absolute timings reflect the machine that produced
    them; the ratios are the signal, not the milliseconds.
  </p>

  <h2>Try it</h2>
  <p>
    This page is a static snapshot — Hugging Face reserves Docker Spaces for PRO
    accounts. The interactive version and the full scenario sandbox run locally
    in two commands:
  </p>
  <div class="flow">docker compose up -d --build
python -m sandbox.cli inject plan_regression
pgra --dsn postgresql://dbra:dbra@localhost:5433/shopdb diagnose</div>

  <div class="links">
    <a href="https://github.com/Yashmaini30/pg-reliability-agent">GitHub — code &amp; eval harness</a>
    <a href="https://huggingface.co/datasets/yashMaini/postgres-incident-diagnosis">Dataset — the benchmark</a>
  </div>

  <footer>
    Postgres 16 · deterministic detectors · 48 unit tests · CI runs the scored
    eval on every push. Built by <a href="https://github.com/Yashmaini30">Yash Maini</a>.
  </footer>

</div>
</body>
</html>