Spaces:
Running
Running
| <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 <strong>158×</strong></td> | |
| <td class="yes">recommend</td></tr> | |
| <tr><td class="k">users(country)</td> | |
| <td class="k">4,772 → 3,303 1.4×</td> | |
| <td class="no">decline</td></tr> | |
| <tr><td class="k">events(event_type)</td> | |
| <td class="k">35,602 → 27,179 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 & 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> | |