--- name: postgres-performance-tuning description: MUST USE when diagnosing or fixing slow queries and performance problems on a ClickHouse-managed Postgres service (high latency, high CPU, low throughput, cache thrash, lock waits). Drives the private MCP tools to rank the slowest query patterns, runs EXPLAIN on the worst offenders to read their plans, cross-checks service metrics for resource pressure, and produces ranked, concrete fixes (indexes, query rewrites, type or schema changes, config, scaling). Recommend-only by default: it never runs DDL or DML against the service. license: Apache-2.0 metadata: author: ClickHouse Inc version: "0.1.0" --- # Postgres Performance Tuning This skill turns a vague "Postgres is slow" report into an evidence-backed fix. The loop is: find the slow query patterns that dominate the service, drill into a pattern's recent executions, run `EXPLAIN` on the actual query to read its plan, cross-check the service metrics for resource pressure, then recommend a ranked set of concrete fixes. Unlike a pure metrics-only RCA, this skill reads real plan trees: it runs `EXPLAIN` itself through the read-only query tool, so it can point at the exact sequential scan, missing index, expensive sort, or bad row estimate rather than guessing from IO counters alone. > **Sources of truth, in priority order:** > 1. The plan you read from `EXPLAIN` on the live service. A real plan beats any heuristic. > 2. The aggregate IO and timing signal from the Slow Query Patterns tools. > 3. Official Postgres docs on [planner / `EXPLAIN`](https://www.postgresql.org/docs/current/using-explain.html), [indexes](https://www.postgresql.org/docs/current/indexes.html), and [routine vacuuming](https://www.postgresql.org/docs/current/routine-vacuuming.html). > > Never invent Postgres behavior. If a recommendation is not backed by a plan you read or the docs, say so and verify. ## Core principles - **Read the plan, do not guess it.** This surface lets you run `EXPLAIN`. Use it. A blocks-per-row ratio suggests a full scan; the plan confirms it and names the node. - **Find the dominant cost first.** One or two patterns usually own most of the total execution time. Fix those, not the longest single query you happen to notice. - **Cross-check before blaming the query.** A slow pattern during a CPU or memory saturation window may be a victim, not the cause. Pull metrics for the same window. - **Recommend, do not apply.** Write the exact SQL or change, explain why, and let a human run it. The query tool is read-only by contract and will reject writes anyway. ## What you have access to (private MCP tools) All tools are read-only and scoped to the caller's organization. Pass the Postgres `serviceId` returned by `get_services_list`. - **`get_services_list`**: lists every ClickHouse and Managed Postgres service in the org (id, name, state, region, version). Filter to `serviceType: "postgres"` to resolve the service when the user does not give you an id. - **`list_postgres_slow_query_patterns`**: ranks the slowest query patterns over a time window. Per pattern: `callCount`, `errorCount`, `totalDurationUs`, `avgDurationUs`, `maxDurationUs`, `p50DurationUs`, `p95DurationUs`, `p99DurationUs`, `totalRows`, `totalSharedBlksRead` (cache misses), `totalSharedBlksHit` (cache hits), `totalCpuTimeUs`, `totalWalBytes`. Durations are microseconds. `queryText` is normalized with `$1`-style placeholders. Filterable by `dbName`, `dbUser`, `dbOperation`, `app`. - **`get_postgres_slow_query_pattern_details`**: for one chosen pattern, returns the aggregate metrics plus up to 10 of the most recent individual executions (each with duration, rows, shared and local and temp buffer counters, WAL, JIT, CPU, parallel workers, plus `errMessage` and `errSqlstate` if it failed). This is where you get representative literal values and per-execution buffer detail. - **`get_postgres_metrics`**: bucketed time-series service metrics over a window: CPU usage, memory usage, disk usage, disk I/O, network traffic, connection count, cache hit ratio, operation throughput, deadlocks, database size, transactions. Each metric has a `key`, `name`, `unit`, `description`, and one `series` per label dimension; timestamps are Unix seconds at the bucket start. - **`run_postgres_select_query`**: runs a read-only `SELECT` against the service through the Postgres query endpoint with the read-only role. **Use this to run `EXPLAIN`,** and to inspect the catalog (`pg_indexes`, `pg_stats`, `information_schema`). ### What this surface does NOT give you directly - Server logs (so `deadlock detected` line items and the exact conflicting statements live in the Postgres logs, not here; the `deadlocks` metric tells you it happened, not which statements). - The ability to mutate the service. No `CREATE INDEX`, no `VACUUM`, no `ANALYZE`, no killing backends. You recommend; a human applies. ## Tool argument conventions Both insights tools require a UTC time window with millisecond precision (`...:00.000Z`). A default 1-hour or 24-hour lookback is usually right; widen it if the user reports an intermittent problem. - `fromDate` / `toDate`: e.g. `2026-06-29T00:00:00.000Z`. Plain second-precision strings are rejected. - When drilling from a list result into `get_postgres_slow_query_pattern_details`, pass `queryId`, `dbName`, `dbUser`, `dbOperation`, and `app` **exactly as returned** by the list. Pass `app` as an empty string when the pattern has no `application_name`; omit `app` only when you intentionally want to match across all applications. ## Workflow Six phases, in order. Do not jump to recommending an index before you have read a plan. 1. Resolve the service 2. Find the slow query patterns 3. Drill into a pattern's recent executions 4. Run and read EXPLAIN 5. Cross-check service metrics for resource pressure 6. Produce ranked recommendations A consolidated checklist sits at the end. Use it as the definition of done. --- ### Phase 1: Resolve the service Goal: get the right Postgres `serviceId`. If the user gave you a service id or name, you can usually skip ahead. Otherwise call `get_services_list`, filter to `serviceType === 'postgres'`, and match on `name`. If more than one Postgres service matches, list the candidates (name, region, state) and ask which one. Confirm the service `state` is running before pulling insights; a stopped or hibernated service will not have current data. Record: the resolved `serviceId`, and the `databaseName` if the user mentioned one (you will need it for `run_postgres_select_query`, which defaults to the `postgres` database). --- ### Phase 2: Find the slow query patterns Goal: rank where execution time actually goes, so you fix the dominant cost rather than a query someone happened to notice. Call `list_postgres_slow_query_patterns` for the window with `sortBy: "total_duration"` (the default) and a `limit` of 10 to 25. `total_duration` ranks by aggregate impact, which is what you want: a 5 ms query called a million times outranks a 2 s query called twice. Re-sort to expose different shapes: - `sortBy: "avg_duration"` or `"max_duration"` finds individually slow queries (full-scan and bad-plan candidates). - `sortBy: "call_count"` finds hot loops and N+1 patterns. - `sortBy: "total_blks_read"` finds the most disk-IO-heavy patterns (cache misses). - `sortBy: "total_wal_bytes"` finds the heaviest writers. - `sortBy: "error_count"` surfaces failing patterns (often serialization conflicts or statement timeouts). Read the shape of the top patterns. For each, look at the relationship between `callCount`, `avgDurationUs`, `totalRows`, and the two buffer counters: | Pattern shape | Likely cause | Where Phase 4 / 6 goes | |---|---|---| | One pattern dominates `total_duration`; high blocks touched per row; many disk reads | Full / large scan: missing or unused index | `EXPLAIN` it, expect a `Seq Scan`; recommend an index | | Huge `callCount`, tiny `avgDurationUs`, large `totalDurationUs` | N+1 / hot loop in the app | Confirm the query is cheap per call; recommend batching in the app | | High `avgDurationUs`, low blocks read and low blocks hit per call | Likely lock or IO waits, not CPU or scan | `EXPLAIN` may look cheap; surface and ask the user to check `pg_stat_activity` / logs | | Many patterns slow at once; low cache hit ratio across them | Capacity or cache thrash | Phase 5 metrics confirm; recommend scaling, not a per-query fix | | Top patterns are INSERT/UPDATE/DELETE with high `totalWalBytes` | Write-path congestion | Look at batching, vacuum, WAL pressure; not an index | Derive a useful ratio per candidate read pattern: ``` blocks_touched_per_row = (totalSharedBlksHit + totalSharedBlksRead) / max(totalRows, 1) ``` Use blocks **touched** (hit plus read), not just disk reads: a table fully resident in cache still scans every block per call, which is a full scan that disk-only thinking would miss. A ratio in the hundreds or thousands per returned row is a strong full-scan signal, to be confirmed by the plan in Phase 4. Note: ClickHouse Cloud runs internal monitoring probes (`SELECT pg_current_wal_lsn()`, `SELECT pg_is_in_recovery()`, and similar) against the instance. They show high `callCount` but near-zero `totalDurationUs` and zero IO. Skip patterns whose `totalDurationUs` is trivially small; they are not the user's workload. Pick the top one to three patterns to investigate. --- ### Phase 3: Drill into a pattern's recent executions Goal: turn a normalized pattern into a concrete query you can `EXPLAIN`, and read per-execution detail the aggregate hides. Call `get_postgres_slow_query_pattern_details` with the pattern's `queryId`, `dbName`, `dbUser`, `dbOperation`, and `app` exactly as returned. From the result: - **Pull representative literal values.** `queryText` is normalized to `$1`-style placeholders, which `EXPLAIN` cannot run as-is. The `recentExecutions` give you real executions; use their values to substitute realistic literals into the placeholders. Choose values that look typical, not edge cases, so the plan reflects normal behavior. - **Read the per-execution counters the aggregate flattens.** Large `tempBlksRead` / `tempBlksWritten` on an execution means the query spilled sorts or hashes to disk (a `work_mem` signal). High `sharedBlksRead` versus `sharedBlksHit` confirms cache misses. Non-zero `errMessage` / `errSqlstate` tells you the pattern is failing, not just slow (e.g. `40001` serialization failure, `57014` statement timeout, `53200` out of memory). - **Note `dbName`.** You need it as the `database` argument for `run_postgres_select_query` in Phase 4. If `recentExecutions` is empty (executions older than the detail window's 24-hour reach), reconstruct plausible literals from the `queryText` shape and the column types, and say in the report that you used synthetic literals. --- ### Phase 4: Run and read EXPLAIN Goal: get the planner's own account of the query, then interpret it. This is the heart of the skill. Substitute representative literals (from Phase 3) into the normalized `queryText`, then run `EXPLAIN` via `run_postgres_select_query`, passing the pattern's `database`. #### Which EXPLAIN variant to run - **Default to plain `EXPLAIN`.** It only plans the statement, never executes it, so it is safe for any statement under the read-only role and returns instantly. It gives you node types, estimated costs, and estimated row counts. ```sql EXPLAIN SELECT ... ; -- planner's estimate, no execution EXPLAIN (FORMAT JSON) SELECT ... ; -- same, machine-readable; easier to parse node by node ``` - **`EXPLAIN ANALYZE` and `EXPLAIN (ANALYZE, BUFFERS)` actually execute the statement.** For a `SELECT` that is read-only, but it really runs the query and consumes CPU, memory, and IO, so use it deliberately (not on a query you suspect is already saturating the box) and say in the report that you ran it. `BUFFERS` adds real shared/local/temp buffer hit and read counts per node, which is the best confirmation of a full scan and of disk spills. `ANALYZE` is the only way to see **actual vs estimated** rows, which is how you catch bad row estimates. ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- executes; gives actual rows, timing, and per-node buffers ``` - **Never run `EXPLAIN ANALYZE` (or `EXPLAIN` with `ANALYZE`) on a writing statement.** `ANALYZE` executes the statement, and executing an `INSERT` / `UPDATE` / `DELETE` mutates data. For a write pattern, run plain `EXPLAIN` only (it plans without executing), or wrap a manual investigation in a transaction that rolls back. The read-only role will reject a write either way, but do not even attempt `ANALYZE` on a write. #### Reading the plan Plans read inside-out and bottom-up: the most indented node runs first, feeding its parent. Walk it for these signatures: - **`Seq Scan` on a large table with a selective filter** is the classic missing-index signal, especially when the node's estimated rows are far below the table's row count (Postgres is reading everything to return a little). With `BUFFERS`, a large `shared read`/`shared hit` here confirms the scan cost. - **`Filter:` removing most rows** (`Rows Removed by Filter` is large under `ANALYZE`) means the predicate is not served by an index. Index the filtered column(s). - **A `Sort` node with `Sort Method: external merge Disk:`** means the sort spilled to disk because it exceeded `work_mem`. Either add an index that provides the order, reduce the rows sorted, or raise `work_mem` for that workload. - **Expensive joins:** a `Nested Loop` with a large outer row count multiplies a per-row inner lookup and can dominate; a `Hash Join` building a huge hash, or a `Merge Join` forcing big sorts, can too. Check whether the join keys are indexed and whether a different join order would filter earlier. - **Bad row estimates:** under `ANALYZE`, a node where estimated rows and actual rows differ by one or more orders of magnitude means the planner is working from stale or insufficient statistics. The fix is usually to `ANALYZE` the table (refresh stats), sometimes to raise the column's statistics target or add extended statistics for correlated columns. A bad estimate often explains a "why did it pick a `Seq Scan` when an index exists" mystery. - **Index present but unused:** if you know an index covers the predicate yet the plan does not use it, suspect a function wrapped around the indexed column (`WHERE lower(email) = $1` cannot use a plain index on `email`), a type mismatch in the comparison (`bigint` column vs `text` literal), a non-sargable predicate (`col + 0 = $1`, leading-wildcard `LIKE '%x'`), or stale stats making the scan look cheaper. `EXPLAIN` is how you tell "no index exists" apart from "index exists but is ignored," which lead to different fixes. Confirm the column to index by checking the catalog (also read-only, also via `run_postgres_select_query`): ```sql -- Existing indexes on the table SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'orders'; -- Column statistics the planner uses (n_distinct, null_frac, correlation) SELECT attname, n_distinct, null_frac, correlation FROM pg_stats WHERE schemaname = 'public' AND tablename = 'orders'; ``` --- ### Phase 5: Cross-check service metrics for resource pressure Goal: decide whether the slow pattern is the cause or a victim, and catch system-level problems a per-query view cannot show. Call `get_postgres_metrics` for the **same window** as the slow-query pull. Read: - **CPU usage** near saturation during the window means queries are CPU-bound; a full scan or a hot loop fits, and so does simple under-provisioning. - **Cache hit ratio** well below the high-90s percent on a workload that should fit in RAM means cache thrash: the working set is larger than memory, so even indexed queries reread from disk. This is a memory-sizing signal, not a per-query bug. - **Memory usage** high alongside the temp-buffer spills you saw in Phase 3 / 4 points at `work_mem` pressure or an oversized working set. - **Connection count** climbing toward a known ceiling means client fan-out or stuck queries; pair with the high-`callCount` hot-loop shape from Phase 2. - **Disk I/O** saturated corroborates a read- or write-heavy pattern; **disk usage** near full is a separate operational risk to flag. - **Deadlocks** non-zero means lock-order conflicts; the metric proves it happened but the conflicting statements are only in the server logs, so surface the count and ask the user to check logs. - **Transactions** (commit vs rollback) with a high rollback share corroborates the error-count signal from Phase 2 (serialization conflicts, app exceptions mid-transaction). If every metric is healthy and one pattern still dominates, the problem is the query or its plan: trust Phase 4. If many patterns are slow and the cache hit ratio is low or CPU is pinned, the problem is capacity: recommend scaling or memory, not a single index. --- ### Phase 6: Produce ranked recommendations Goal: hand back concrete, ordered fixes, each tied to the evidence you gathered. Rank by impact and confidence: a missing index confirmed by a `Seq Scan` in the plan outranks a speculative config tweak. For each recommendation give the exact change and one sentence on why it addresses the diagnosed cause. The common fix families: - **Add an index** (the most common read-path fix). Always `CONCURRENTLY` on a live service so it does not block writes: ```sql CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC); ``` Lead with the equality / most-selective predicate column; append a column that serves the query's `ORDER BY` (in the matching direction) so the index can satisfy the sort and skip a `Sort` node. Use a **partial index** (`... WHERE status = 'open'`) when the hot predicate is highly selective, and an **expression index** (`... ((lower(email)))`) when the query wraps the column in a function. Do not over-index a write-heavy table; every index slows inserts. - **Rewrite the query** when the plan shows a non-sargable predicate or a needless full scan: drop the function around the indexed column, fix a type mismatch so the comparison is sargable, replace a leading-wildcard `LIKE` with a trigram index or full-text search, or pull a correlated subquery into a join. For an N+1 hot loop the rewrite is in the app: batch many single-row reads into one `WHERE id = ANY($1)`, or many single-row writes into a multi-row `INSERT ... VALUES (...), (...)` or `COPY`. - **Fix statistics** when `ANALYZE` shows estimates far off actuals: run `ANALYZE ` to refresh stats, raise a column's `STATISTICS` target for a skewed column, or add `CREATE STATISTICS` for correlated columns the planner is mis-estimating. - **Type or schema changes** when a column type forces casts that defeat indexes, when a wide row should split TOAST-eligible columns into a side table, or when a frequently filtered JSON field should be promoted to a typed, indexed column. - **Config and operational tuning**, with the tradeoff stated and left to the user: raise `work_mem` for a workload spilling sorts to disk (per-connection memory, so size it carefully); tune **autovacuum** to run more aggressively on a bloated, high-churn table (`autovacuum_vacuum_scale_factor`) when bloat is degrading scans; adjust `synchronous_commit` / `commit_delay` for WAL-flush-bound writes (durability tradeoff). Flag bloat as a hypothesis only if the plan or the IO supports it; this surface does not expose bloat directly. - **Scale the service** when Phase 5 shows the box is the bottleneck (CPU pinned across patterns, working set larger than RAM driving a low cache hit ratio). Recommend more memory or compute rather than a per-query change, and say so explicitly. Use the output template below. State what you did and did not do. --- ## Output template ```markdown ## Summary ## Evidence - Slow query pattern (from list_postgres_slow_query_patterns), trimmed to the fields that matter: callCount, totalDurationUs, avgDurationUs, totalRows, totalSharedBlksHit, totalSharedBlksRead, queryText - Derived: blocks_touched_per_row = ; share of total duration = % - EXPLAIN plan (the node that dominates, quoted): Seq Scan on orders (cost=... rows=... ) Filter: (customer_id = 4210) Rows Removed by Filter: 1998734 -- if ANALYZE was run - Metrics cross-check (from get_postgres_metrics, same window): CPU %, cache hit ratio %, ... ## Diagnosis ## Recommendations (ranked) 1. 2. 3. ## What I did NOT do - I did not run any DDL or DML; I ran EXPLAIN ANALYZE, which executed the read-only SELECT to gather actual-row and buffer numbers. - A human should review and apply the changes above. ``` --- ## Worked example A user reports the orders dashboard is slow. 1. **Resolve:** `get_services_list` returns one Postgres service; use its `serviceId`. 2. **Find patterns:** `list_postgres_slow_query_patterns` (last 1 hour, `sortBy: total_duration`) returns a dominant pattern: ```json { "queryId": "a1b2c3", "queryText": "SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2", "dbName": "appdb", "dbUser": "app", "dbOperation": "SELECT", "app": "dashboard", "callCount": 5120, "avgDurationUs": 412000, "totalDurationUs": 2109440000, "totalRows": 51200, "totalSharedBlksHit": 240000, "totalSharedBlksRead": 81200000 } ``` `blocks_touched_per_row = (240000 + 81200000) / 51200 ≈ 1591` blocks per returned row, with most blocks read from disk. Strong full-scan signal. 3. **Drill in:** `get_postgres_slow_query_pattern_details` returns recent executions; a typical one has `customer_id = 4210`, `LIMIT 50`, and a large `sharedBlksRead`. Database is `appdb`. 4. **EXPLAIN:** run via `run_postgres_select_query` (database `appdb`): ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4210 ORDER BY created_at DESC LIMIT 50; ``` ``` Limit (cost=98234.1..98234.2 rows=50 ...) (actual rows=50 ...) -> Sort (cost=98234.1..98239.1 rows=2000 ...) (actual rows=50 ...) Sort Key: created_at DESC Sort Method: top-N heapsort Memory: 64kB -> Seq Scan on orders (cost=0.00..98180.0 rows=2000 ...) (actual rows=1843 ...) Filter: (customer_id = 4210) Rows Removed by Filter: 1998157 Buffers: shared hit=312 read=81200 ``` The plan confirms a `Seq Scan` reading ~2M rows to keep ~1.8k, with `created_at` sorted afterward. No index on `customer_id`. 5. **Cross-check metrics:** `get_postgres_metrics` for the window shows cache hit ratio dipping to the low 80s percent and disk I/O elevated, consistent with repeated full scans, but CPU not pinned. So this is a query/index problem, not a capacity problem. 6. **Recommend:** ```sql CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC); ``` The leading `customer_id` makes the equality predicate an index seek instead of a 2M-row scan, and the trailing `created_at DESC` lets the same index satisfy the `ORDER BY ... LIMIT` without a separate `Sort`. Built `CONCURRENTLY` so it does not block writes. Follow-up: re-run the same `list_postgres_slow_query_patterns` after the index is live to confirm `totalDurationUs` and disk reads dropped. --- ## Recommend-only boundary The query tool is read-only and runs under a read-only role; it will reject `INSERT`, `UPDATE`, `DELETE`, and DDL. Do not try to apply fixes through it. Write the exact SQL or app change, explain why, and let a human run it. `EXPLAIN ANALYZE` on a `SELECT` is allowed (it executes a read-only statement to gather actual numbers); never put `ANALYZE` on a writing statement. If the user asks you to apply a fix, decline, and offer to re-run the slow-query and metrics pulls to confirm the fix worked after they apply it. --- ## Checklist (definition of done) **Resolve** - [ ] Postgres `serviceId` resolved and confirmed running **Find patterns** - [ ] Slow query patterns ranked by `total_duration` over the window - [ ] Re-sorted by `avg_duration` / `call_count` / `total_blks_read` / `total_wal_bytes` as the shape warranted - [ ] Internal probe patterns (near-zero duration) excluded - [ ] `blocks_touched_per_row` derived for read candidates; top one to three patterns chosen **Drill in** - [ ] Recent executions pulled; representative literal values extracted from them - [ ] Per-execution temp-spill, cache-miss, and error counters read; `dbName` noted **EXPLAIN** - [ ] Plain `EXPLAIN` (or `FORMAT JSON`) run on the worst pattern - [ ] `EXPLAIN (ANALYZE, BUFFERS)` run deliberately on a `SELECT` where actual-vs-estimated rows or per-node buffers were needed (never on a write) - [ ] Plan interpreted: scan type, filter removal, sorts/spills, join cost, row-estimate accuracy, index-present-but-unused **Cross-check** - [ ] `get_postgres_metrics` pulled for the same window: CPU, memory, cache hit ratio, connections, disk I/O, deadlocks, transactions - [ ] Decided whether the pattern is cause or victim (per-query fix vs capacity) **Recommend** - [ ] Ranked recommendations, each tied to plan or metric evidence with exact SQL or app change - [ ] Index recommendations use `CONCURRENTLY`; partial/expression index used where the predicate warranted - [ ] Stated what was and was not done; no DDL or DML executed against the service ```