mcp-client-connect.md
# Connecting MCP Clients to the `materialize-developer` Server
This playbook covers **client-side configuration** for connecting an MCP-capable agentic coding tool (Claude Code, Cursor, VS Code, Zed, Continue, Windsurf, Claude Desktop) to a Materialize **`/api/mcp/developer`** endpoint. It does *not* cover authoring or hosting the server.
> **Living info — verify before relying.** MCP client config formats, CLI flags, and config file locations change frequently as products evolve. Each per-client subsection below links to the official docs for that client; **treat the linked doc as the source of truth and the snippets here as a starting point**. Likewise, Materialize's own MCP server docs at <https://materialize.com/docs/integrations/mcp-server/> may have updated guidance that supersedes anything here.
---
## How to use this playbook (instructions for the agent)
This playbook is reference material. **Do not dump it in response to a general question.** The deep snippets, source citations, and per-deployment matrices below are for follow-up answers once the user has narrowed scope.
### Triage flow for a general "how do I connect" question
Examples that count as general: *"how do I connect to the Materialize developer MCP server?"*, *"what do you know about connecting?"*, *"how do I set up the Materialize MCP?"*, *"can you help me configure this?"*.
Keep the initial reply short (target ≤ 30 lines / one screen). Don't paste config snippets, source-code citations, or full deployment tables. Run this flow:
1. **One short sentence** stating what this skill helps with.
2. **List the help topics** as a bullet list — names only, no snippets:
- Picking the right URL for the deployment (Emulator / Cloud / self-managed)
- Building the auth header (Basic vs Bearer; role vs user)
- Generating a config block for a specific MCP client
- Selecting / switching the user or role at runtime
- Verifying the connection (stateless curl probe)
- Troubleshooting connection errors
3. **List the supported clients**, names only, on one line:
*Claude Code · Claude Desktop · Cursor · VS Code (Copilot Chat) · Zed · Continue · Windsurf*
4. **List the three runtime patterns** for selecting the user/role, names only:
1. Env-var rotation (placeholder in config + env var rotation)
2. Multiple registrations (one per identity, each with its own literal `Authorization` header)
3. Direct config edit (swap the literal token in the existing entry)
5. **Detect or ask which client the user wants help with:**
- **If the user named a client in the prompt**, confirm that's the focus.
- **If you can detect your runtime** — e.g. you were invoked via a Claude Code slash command, your environment shows `CLAUDE_*` vars, the working directory has `.claude/` or `claude_desktop_config.json` — name the inferred client (Claude Code or Claude Desktop) and ask the user to confirm. Important: detecting that the *harness running the agent* is Claude Code/Desktop does **not** imply the user wants help configuring that client; they may be using Claude Code to draft setup steps for a teammate using Cursor. Ask, don't assume.
- **Otherwise**, ask the user which of the supported clients they are using (offer the list above).
6. **End with one open question:** *"What would you like help with?"*
### When the user has already narrowed scope
If the user already named a client, deployment, or specific topic (e.g., *"configure Cursor against my Cloud deployment"*, *"what's the URL for self-managed kind"*, *"why is my token rejected with 422"*), skip the triage and answer directly using the relevant section below.
**Special case — first-time setup against the Emulator with Claude Code.** If the user's request matches that pattern, route to the [Walkthrough](#walkthrough--first-time-setup-against-the-emulator-with-claude-code) section below instead of assembling steps from the deep reference content. Trigger phrases:
- *"walk me through first-time setup"*
- *"set up materialize-developer"* (with no other client/deployment named)
- *"connect Claude Code to materialize-developer"* (or *"to the Materialize MCP server"*)
- *"first-time setup"*
- Any prompt that names Claude Code + a local Emulator + the goal of getting connected, without specifying a competing identity pattern.
---
## Walkthrough — first-time setup against the Emulator with Claude Code
The canonical "minimum-drama" path for getting Claude Code talking to a local Materialize Emulator's `/api/mcp/developer` endpoint. **Scope:** Emulator (`http://localhost:6876`), Claude Code, the `my_dev_agent` role, and Pattern A (env-var rotation). For other deployments, clients, or auth patterns, use the deep reference content below this section instead.
### State-detection probes (run before each step; skip the step if state is already in place)
```sh
# 1. Emulator reachable, and is the endpoint actually enabled?
# Must be a POST: GET is answered 405 by the router before the feature flag is
# ever consulted, so it reads "up" whether or not the endpoint is enabled.
curl -sS -o /dev/null -w "%{http_code}\n" -X POST \
http://localhost:6876/api/mcp/developer \
-H 'Content-Type: application/json' \
-d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'
# Expect: 200 (endpoint up and enabled). 503 = enable_mcp_developer is off.
# Connection refused / no response = Emulator not running; stop and direct user to environment-setup.
# 2. my_dev_agent role exists?
psql postgres://materialize@127.0.0.1:6875/materialize -tAc \
"SELECT 1 FROM mz_roles WHERE name='my_dev_agent';"
# 3. materialize-developer registered in Claude Code?
claude mcp list 2>&1 | grep -E '^materialize-developer:'
# 4. ~/.claude.json entry uses the env-var placeholder form?
grep -A 3 '"materialize-developer"' ~/.claude.json | grep -q 'Basic ${MCP_DEV_TOKEN}'
```
### Steps
The walkthrough is **idempotent** — re-running on a partly-configured machine skips finished steps. State each skip out loud so the user sees what was already in place.
1. **Verify the Emulator is up** (probe 1). If it's not, stop with a pointer to `environment-setup/README.md` — the rest of the walkthrough cannot proceed without it.
2. **Create the role if missing** (probe 2 returned no rows):
```sh
psql postgres://materialize@127.0.0.1:6875/materialize -c "CREATE ROLE my_dev_agent;"
```
Expected output: `CREATE ROLE`. Materialize does not support `CREATE ROLE IF NOT EXISTS`, so probe 2 first; don't blindly retry.
3. **Register the MCP server if missing or in the wrong form** (probe 3 / probe 4):
```sh
claude mcp add-json materialize-developer \
'{"type":"http","url":"http://localhost:6876/api/mcp/developer","headers":{"Authorization":"Basic ${MCP_DEV_TOKEN}"}}'
```
If a registration exists but uses a literal token (Pattern C variant), `claude mcp remove materialize-developer` first, then re-add in the placeholder form. The walkthrough is opinionated about Pattern A — the env var is the rotation point.
4. **PAUSE for the user to set the env var.** Show them, verbatim:
```sh
read -s MCP_DEV_PASSWORD # press Enter; Emulator user has no password
export MCP_DEV_TOKEN="$(printf 'my_dev_agent:'"$MCP_DEV_PASSWORD" | base64)"
unset MCP_DEV_PASSWORD
echo "$MCP_DEV_TOKEN" # expect: bXlfZGV2X2FnZW50Og==
```
Wait for the user to confirm the echo matches before continuing. macOS reminder: do **not** add `-w0` to `base64`; that's GNU coreutils only.
5. **PAUSE for the user to restart Claude Code:**
```sh
^C^C
claude --continue
```
The conversation context is preserved. When the user returns ("back" / "ready" / similar), proceed.
6. **Smoke query** to prove the connection is live and identifies the right role:
```
query_system_catalog: SELECT current_role FROM mz_catalog.mz_databases LIMIT 1
```
Expected: `my_dev_agent`. The query needs to reference a system catalog table — a bare `SELECT current_role` is rejected with `Query must reference at least one system catalog table`.
7. **Confirm success** in one short sentence and offer a follow-up. Example: *"You're connected as `my_dev_agent`. Try `/mcp-developer-analysis what's the health of my environment?` next."*
### Failure handling
| Symptom | Likely cause | Fix |
|---|---|---|
| Smoke query returns a role other than `my_dev_agent` | The user's `MCP_DEV_TOKEN` was set to a different role's base64 | Repeat step 4 with the right `read`/`base64` invocation, then restart again. |
| Smoke query returns `anonymous_http_user` | No usable `Authorization` header reached the server: the env var was unset in the shell that launched `claude`, or the token is malformed. The Emulator downgrades both to anonymous rather than rejecting them. | Confirm `echo "$MCP_DEV_TOKEN"` in the launching shell shows `bXlfZGV2X2FnZW50Og==`; if it's empty, `export` was missed. |
| HTTP 422 on the smoke query | The request body failed to deserialize: a malformed JSON-RPC body, not a credential problem. | Check the JSON-RPC body shape, not the token. |
| HTTP 503 on the smoke query | Usually `enable_mcp_developer` is `false` on this Emulator; the server also answers 503 when it cannot fetch a catalog snapshot in time, so retry once. | See the [server config docs](https://materialize.com/docs/integrations/mcp-server/mcp-developer-config/). Not a walkthrough fix. |
| HTTP 403 | The request's `Origin` header is not on the server's allowlist (a browser-based client, or a proxy adding one) | Send the request without an `Origin`, or from an allowed origin. |
| HTTP 200 whose body carries a JSON-RPC `error` instead of a `result` | The request reached the tool; the error's `data.error_type` is `ValidationError` (the statement was refused: not read-only, not a system table, two statements), `ExecutionError` (the SQL ran and failed, read the message), `ToolNotFound` (the `query` tool is disabled), or `InternalError` (a server-side failure). Every tool-level failure looks like this, never like a non-200 status. | Read `error_type` and the message; fix the statement, not the connection. |
| `claude mcp list` shows the server but the smoke query times out | Claude Code wasn't restarted after the env var was set | Step 5 again. |
### What the walkthrough does NOT cover
- **Cloud or self-managed setup** — different URLs, different credentials, possibly Bearer tokens. Use the deep reference content below.
- **Pattern B (multiple registrations)** or **Pattern C (literal-token edit)** — the walkthrough only sets up Pattern A. Cover those if the user explicitly asks to switch identities.
- **Skill installation** — `npx skills add MaterializeInc/agent-skills` is a terminal-side step done before the user can invoke the walkthrough at all. If the slash command isn't found, point the user there first.
- **RBAC tightening** — the walkthrough leaves `my_dev_agent` at `PUBLIC` defaults, which is appropriate for training but not for production. Direct the user to the Materialize RBAC docs for production scoping. RBAC gates data, not the catalog (see the Notes section of `SKILL.md`), so the analysis workflow still works for a narrowly-scoped agent role.
---
## What this connects to
| Deployment | URL pattern | Auth backend |
|---|---|---|
| **Materialize Emulator** (Docker) | `http://localhost:6876/api/mcp/developer` | None — Basic-auth username is the role; password is empty |
| **Materialize Cloud** | `<baseURL>/api/mcp/developer`; copy `<baseURL>` from the Console's Connect modal, MCP Server tab, Developer tab | OAuth through the browser, or Frontegg — Basic with `<email>:<app_password>` (or Bearer JWT) |
| **Self-managed (Password mode)** | `http(s)://<host>:6876/api/mcp/developer`, `<host>` being the balancerd load balancer address, or `localhost` behind `kubectl port-forward svc/<instance>-balancerd 6876:6876 -n materialize-environment` | Internal password hashes — Basic only, with `<role>:<password>` |
| **Self-managed (OIDC mode)** | `https://<host>:6876/api/mcp/developer` | OAuth through the browser (SSO), or External IdP — Bearer JWT; a Basic `<role>:<password>` is also accepted for roles that have a password |
For deployment-specific details (where to find the URL, how to mint app passwords, how to set `enable_mcp_developer`, etc.), see the upstream docs at <https://materialize.com/docs/integrations/mcp-server/>:
- Endpoint overview: <https://materialize.com/docs/integrations/mcp-server/mcp-developer/>
- Server configuration: <https://materialize.com/docs/integrations/mcp-server/mcp-developer-config/>
- Troubleshooting: <https://materialize.com/docs/integrations/mcp-server/mcp-server-troubleshooting/>
---
## OAuth (Cloud from v26.30, self-managed with SSO from v26.31)
The client signs the user in through the browser and holds the token itself,
so the registration carries a URL and no `Authorization` header:
```sh
claude mcp add --transport http materialize-developer <baseURL>/api/mcp/developer
```
A self-managed IdP without dynamic client registration needs a pre-registered
OIDC client: add `--client-id <id> --callback-port <port>`, the port matching
the `http://localhost:<port>/callback` redirect URI registered on the client.
The SSO docs list the other IdP requirements (the authentication claim, for
example `email`, present in access tokens, and a Materialize-specific
audience): <https://materialize.com/docs/security/self-managed/sso/#connecting-mcp-clients>.
The identity is then the signed-in user, so the token patterns below do not
apply. `Incompatible auth server: does not support dynamic client
registration` from the client means the IdP needs that pre-registered client
(Okta and most enterprise IdPs do), or use token-based auth instead. Docs: <https://materialize.com/docs/integrations/mcp-server/mcp-developer/>.
## Authentication header — what to put where (token-based auth)
Which header the server reads depends on the authenticator: password mode
takes Basic only, OIDC takes Bearer and also a Basic `<role>:<password>` for
roles that have one, Cloud takes either:
```
Authorization: Basic <base64(username:password)>
Authorization: Bearer <jwt>
```
The Basic password may be **empty** in the Emulator, but **must be non-empty** in any real deployment.
### Building the Basic token
```sh
# Materialize Emulator (no password)
printf 'my_dev_agent:' | base64
# Self-managed (Password mode)
printf 'my_agent:s3cret' | base64
# Materialize Cloud
printf 'svc-agent@example.com:<app_password>' | base64
```
> The README/docs sometimes show `base64 -w0`. That flag is GNU coreutils only; **macOS BSD `base64` rejects `-w0`** but produces single-line output by default, so just drop the flag.
### Roles vs. users by deployment
- **Emulator** — credentials *are* a role. `CREATE ROLE my_dev_agent` then encode `my_dev_agent:`.
- **Self-managed (Password)** — credentials are a login role with a password: `CREATE ROLE my_agent LOGIN PASSWORD '...'`.
- **Cloud** — credentials are a *user* (email) plus an **app password** generated from the Console (Connect modal → MCP Server tab). The user inherits the union of grants from all roles they're a member of; `SET ROLE` is **not** available through the MCP query tool, so to scope the agent's privileges you typically create a dedicated user with restricted role membership.
---
## Choosing the user/role at runtime — three patterns
These patterns are deployment-agnostic; only the *contents* of the auth token differ by deployment.
### Pattern A — env-var rotation (recommended)
Store the placeholder in the client config; populate the env var in the shell that launches the client:
```jsonc
"headers": { "Authorization": "Basic ${MCP_DEV_TOKEN}" }
```
```sh
# Helper for any deployment:
mcp_as_basic() { export MCP_DEV_TOKEN="$(printf '%s' "$1" | base64)"; }
mcp_as_bearer() { export MCP_DEV_TOKEN="$1"; } # if you also flip the header to Bearer
# Examples:
mcp_as_basic 'my_dev_agent:' # Emulator
mcp_as_basic 'my_agent:s3cret' # Self-managed
mcp_as_basic 'svc-agent@example.com:<app_pw>' # Cloud
```
In Claude Code you can rotate the value without losing your conversation: `^C^C` out, re-export, then `claude --continue`.
### Pattern B — multiple registrations, one per identity
Register the same URL multiple times under different names with literal Authorization headers:
```sh
claude mcp add-json materialize-as-readonly \
'{"type":"http","url":"http://localhost:6876/api/mcp/developer","headers":{"Authorization":"Basic <base64-of-readonly>"}}'
claude mcp add-json materialize-as-admin \
'{"type":"http","url":"http://localhost:6876/api/mcp/developer","headers":{"Authorization":"Basic <base64-of-admin>"}}'
```
Both connect simultaneously; tools from each are namespaced under the registration name. Useful for comparing what different identities can see.
### Pattern C — edit the client config JSON directly
Functionally identical to Pattern B but inside the existing registration's file. Use when you want to flip identity once without re-registering.
### Caveats
- Cloud **app passwords are non-recoverable**. Lost ones must be regenerated from the Console.
- OIDC **Bearer tokens expire**. Pattern A makes refresh trivial (re-export + `claude --continue`); Pattern B/C are awkward for rotating tokens.
- The MCP tools **block `SET` statements**, so post-auth role-switching does not work — pick the right principal at connect time. The cluster is chosen per call instead, through the `query` tool's `cluster` argument.
- In Cloud, the role of record is the *user*, not a SQL role. To scope an agent narrowly, mint a dedicated user with restricted role membership rather than expecting `SET ROLE` to confine it.
---
## Per-client configuration
Each subsection points at the client's official docs. **Verify against the linked page** — these snippets reflect the state of each client at this writing and tend to drift.
### Claude Code
Official docs: <https://code.claude.com/docs/en/mcp>
- Wrapper key: `mcpServers`
- CLI-first; `~/.claude.json` (local/user scope) or `.mcp.json` at project root (project scope)
- Three scopes: `local` (default, per-user-per-project), `project` (committed `.mcp.json`, team-shared), `user` (per-user, all projects)
```sh
claude mcp add-json materialize-developer \
'{"type":"http","url":"http://localhost:6876/api/mcp/developer","headers":{"Authorization":"Basic ${MCP_DEV_TOKEN}"}}'
```
Equivalent project-committed `.mcp.json`:
```json
{
"mcpServers": {
"materialize-developer": {
"type": "http",
"url": "http://localhost:6876/api/mcp/developer",
"headers": { "Authorization": "Basic ${MCP_DEV_TOKEN}" }
}
}
}
```
Tools are discovered at session start. After registering a server or rotating `MCP_DEV_TOKEN`, either restart Claude Code or `^C^C` and `claude --continue` to keep your conversation context.
### Claude Desktop
Official docs: <https://modelcontextprotocol.io/quickstart/user>
- Wrapper key: `mcpServers`
- Config file:
- macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`
- Windows: `%APPDATA%\Claude\claude_desktop_config.json`
- Linux: `~/.config/Claude/claude_desktop_config.json`
```json
{
"mcpServers": {
"materialize-developer": {
"url": "http://localhost:6876/api/mcp/developer",
"headers": { "Authorization": "Basic <base64-token>" }
}
}
}
```
Restart the app after editing. Settings → Developer also exposes a GUI editor.
### Cursor
Official docs: <https://docs.cursor.com/context/model-context-protocol>
- Wrapper key: `mcpServers`
- Config file: `.cursor/mcp.json` (project) or `~/.cursor/mcp.json` (global). Project overrides global on name collision.
```json
{
"mcpServers": {
"materialize-developer": {
"type": "http",
"url": "http://localhost:6876/api/mcp/developer",
"headers": { "Authorization": "Basic <base64-token>" }
}
}
}
```
**Cursor reloads MCP config only on startup** — fully quit (including the menu-bar item, not just the window) and relaunch after editing. No first-class secret store for remote servers; the token sits in the config file.
### VS Code (GitHub Copilot Chat)
Official docs:
- <https://code.visualstudio.com/docs/copilot/customization/mcp-servers>
- Config reference: <https://code.visualstudio.com/docs/copilot/reference/mcp-configuration>
- Wrapper key: **`servers`** (not `mcpServers` — direct paste from other clients won't work)
- Config file: `.vscode/mcp.json` (workspace) or user profile (synced via Settings Sync)
```json
{
"servers": {
"materialize-developer": {
"type": "http",
"url": "http://localhost:6876/api/mcp/developer",
"headers": { "Authorization": "Basic ${input:mz-dev-token}" }
}
},
"inputs": [
{
"type": "promptString",
"id": "mz-dev-token",
"description": "base64 of <user>:<app_password> (or <role>: for Emulator)",
"password": true
}
]
}
```
`${input:...}` prompts on first use and stores the value in the OS keychain — preferred over inlining the token. VS Code also supports a per-server `sandbox` block (filesystem/network restrictions); see the config reference.
### Zed
Official docs: <https://zed.dev/docs/ai/mcp>
- Wrapper key: **`context_servers`** (not `mcpServers`)
- Config file: `.zed/settings.json` (project) or `~/.config/zed/settings.json` (global)
```json
{
"context_servers": {
"materialize-developer": {
"url": "http://localhost:6876/api/mcp/developer",
"headers": { "Authorization": "Basic <base64-token>" }
}
}
}
```
Zed supports MCP **Tools** and **Prompts** only; Discovery, Sampling, and Elicitation are not implemented as of late 2025 — verify current status against the linked doc. Tool permission default is set via `agent.tool_permissions.default` (`confirm` / `allow` / `deny`).
### Continue
Official docs: <https://docs.continue.dev/customization/mcp-tools>
- Wrapper key: `mcpServers` — but **as files in a directory**, one per server, each containing a JSON object with a `name` field
- Config location: `.continue/mcpServers/`
`.continue/mcpServers/materialize-developer.json`:
```json
{
"name": "materialize-developer",
"type": "http",
"url": "http://localhost:6876/api/mcp/developer",
"headers": { "Authorization": "Basic ${{ secrets.MZ_DEV_TOKEN }}" }
}
```
Use `${{ secrets.NAME }}` for credentials; secrets are stored separately from the config file. Continue auto-detects drops of Claude Desktop / Cursor / Cline configs into `.continue/mcpServers/` — no manual conversion needed.
### Windsurf
Official docs: <https://docs.windsurf.com/windsurf/mcp>
- Wrapper key: `mcpServers`
- Config file: `~/.codeium/windsurf/mcp_config.json`
- URL field: **`serverUrl`** (not `url`)
```json
{
"mcpServers": {
"materialize-developer": {
"serverUrl": "http://localhost:6876/api/mcp/developer",
"headers": { "Authorization": "Basic <base64-token>" }
}
}
}
```
The Cascade UI has a "Refresh" button that reloads MCP config without a full restart.
---
## Cross-client cheat sheet
| Client | Wrapper key | Primary config file | URL field |
|---|---|---|---|
| Claude Code | `mcpServers` | `~/.claude.json` (local/user), `.mcp.json` (project) | `url` |
| Claude Desktop | `mcpServers` | `claude_desktop_config.json` (per-OS path) | `url` |
| Cursor | `mcpServers` | `.cursor/mcp.json` or `~/.cursor/mcp.json` | `url` |
| VS Code | `servers` | `.vscode/mcp.json` | `url` |
| Zed | `context_servers` | `.zed/settings.json` or `~/.config/zed/settings.json` | `url` |
| Continue | `mcpServers` (files in dir) | `.continue/mcpServers/<name>.json` | `url` |
| Windsurf | `mcpServers` | `~/.codeium/windsurf/mcp_config.json` | `serverUrl` |
---
## Verifying the connection
Before launching the client, you can prove the URL + auth header pair work with a stateless JSON-RPC POST. The Materialize MCP server responds without requiring a session handshake.
```sh
# tools/list — proves auth + transport
curl -sS -X POST <baseURL>/api/mcp/developer \
-H "Authorization: Basic <base64-token>" \
-H "Content-Type: application/json" \
-H "Accept: application/json, text/event-stream" \
-d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'
# Identify the connected role/user — proves which principal the token resolves to
curl -sS -X POST <baseURL>/api/mcp/developer \
-H "Authorization: Basic <base64-token>" \
-H "Content-Type: application/json" \
-H "Accept: application/json, text/event-stream" \
-d '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"query_system_catalog","arguments":{"sql_query":"SELECT current_role FROM mz_catalog.mz_databases LIMIT 1"}}}'
```
Note: `query_system_catalog` requires the SQL to **reference at least one `mz_*` / `pg_catalog` / `information_schema` table** — a bare `SELECT current_role` is rejected with `Query must reference at least one system catalog table`.
`tools/list` takes no `params`: sending `"params":{}` is a type error and comes back as HTTP 422 `Failed to deserialize the JSON body ...`.
A 422 with a `Failed to deserialize` body is a malformed request, not a credential problem. Bad or missing credentials (a typo in the role name, a wrong app password, an expired JWT) are HTTP 401 on Cloud and self-managed deployments, for any method, so an unauthenticated GET is 401 there rather than the Emulator's 405; the Emulator never answers that way: an unknown role name creates that role, and a missing or malformed `Authorization` header downgrades to `anonymous_http_user`, neither of which is a rejection. So on the Emulator, read the *principal the smoke query reports*, not the status code. HTTP 503 usually means the `enable_mcp_developer` system parameter is `false`, but the server also answers 503 when it cannot fetch a catalog snapshot in time, so retry once before concluding the flag is off — see the [server config docs](https://materialize.com/docs/integrations/mcp-server/mcp-developer-config/).
---
## Common gotchas
- **Wrong wrapper key.** A Claude Code config pasted into VS Code (`mcpServers` vs `servers`) silently produces zero tools. Same for Zed (`context_servers`).
- **Wrong URL field.** Windsurf uses `serverUrl`, not `url`. Easy to miss when copying from another client.
- **Continue's directory format.** One JSON file per server inside `.continue/mcpServers/`, each with a `name` field — not a single config file with an array or object.
- **Cursor's startup-only loading.** Edits during a session don't take effect; users assume the config is wrong when it's just stale.
- **`base64 -w0` on macOS.** Drop the flag on Darwin.
- **Empty password is Emulator-only.** Real deployments reject `<role>:` (empty password) with HTTP 401.
- **TLS redirect on Cloud / TLS-enabled self-managed.** Hitting plain `http://` returns a redirect to `https://`. Some clients follow it; some don't. Specify `https://` in the URL up front.
- **`SET` is blocked.** You cannot switch role after auth, so pick the right principal in the auth header. Cluster is per call: the `query` tool takes a required `cluster` argument (and `cluster_replica` from v26.33); `query_system_catalog` always uses auto-routing plus the session default.
- **Project-vs-global precedence.** Each client has its own rule; verify in the linked doc when configs collide.
---
## References
**Materialize:**
- MCP server overview — <https://materialize.com/docs/integrations/mcp-server/>
- `materialize-developer` endpoint — <https://materialize.com/docs/integrations/mcp-server/mcp-developer/>
- Developer endpoint configuration — <https://materialize.com/docs/integrations/mcp-server/mcp-developer-config/>
- MCP server troubleshooting — <https://materialize.com/docs/integrations/mcp-server/mcp-server-troubleshooting/>
**Per-client (official docs only):**
- Claude Code MCP — <https://code.claude.com/docs/en/mcp>
- Claude Desktop quickstart — <https://modelcontextprotocol.io/quickstart/user>
- Cursor MCP — <https://docs.cursor.com/context/model-context-protocol>
- VS Code MCP servers — <https://code.visualstudio.com/docs/copilot/customization/mcp-servers>
- VS Code MCP configuration reference — <https://code.visualstudio.com/docs/copilot/reference/mcp-configuration>
- Zed MCP — <https://zed.dev/docs/ai/mcp>
- Continue MCP — <https://docs.continue.dev/customization/mcp-tools>
- Windsurf MCP — <https://docs.windsurf.com/windsurf/mcp>
**Protocol:**
- Model Context Protocol specification — <https://modelcontextprotocol.io/specification>
README.md
# MCP Developer Analysis
A skill for analyzing Materialize environments via the built-in
[MCP developer endpoint](https://materialize.com/docs/integrations/mcp-server/mcp-developer/)
(`/api/mcp/developer`).
## What it does
Connect an MCP-compatible AI agent (Claude Code, Claude Desktop, Cursor) to your
Materialize environment and ask natural language questions like:
- "Why is my materialized view stale?"
- "Why is my cluster running out of memory?"
- "What can I optimize to save costs?"
- "What's the health of my environment?"
The skill provides the AI agent with:
- **Verified diagnostic queries** against the system catalog
- **Client setup** for Claude Code, Claude Desktop, Cursor, VS Code, Zed, Continue, and Windsurf (`mcp-client-connect.md`)
- **Diagnostic workflows** for common troubleshooting scenarios
- **Remediation runbooks** with copy-pasteable SQL commands
- **Guardrails** against known pitfalls (cluster-scoped introspection reads, per-process replica relations, clusters without replicas)
## Setup
1. Install the skill:
```bash
npx skills add MaterializeInc/agent-skills
```
2. Connect your MCP client to the Materialize developer endpoint. See the
[MCP Server for Developers](https://materialize.com/docs/integrations/mcp-server/mcp-developer/)
documentation for connection instructions.
references/queries.md
# Diagnostic Queries Reference
All queries in this file target system catalog tables and run through the MCP
`query_system_catalog` tool, except where a heading says `query`. Use the
`query` tool for cluster-bound operations: `EXPLAIN ANALYZE` on a materialized
view or index, reading user data, and every `mz_introspection` relation. The
`query` tool exists from Materialize v26.30 and takes a required `cluster`
argument; from v26.33 it also takes `cluster_replica`, required for any
`mz_introspection` read on a cluster with more than one replica.
`SKILL.md` states the tool constraints in full. Three of them shape every query
below: rows come back with no column names, so every `AS` alias here is
discarded and you map columns positionally (and `timestamp` columns arrive as
epoch milliseconds unless cast to `text`, which the queries below do); a
response is capped at 1 MB, so
narrow or `LIMIT` anything that could enumerate a whole large catalog; and a
`cluster` argument passed to `query_system_catalog` is silently ignored, so a
cluster-scoped query there answers about the session's default cluster
instead, quietly when that cluster has one replica and with an error otherwise
(`SKILL.md`, Critical Rules). Runtime relations such as `mz_frontiers` and
`mz_compute_exports` are keyed by GlobalId, which equals `mz_objects.id` for
every object as created and for a materialized view after an applied
replacement; a table altered with `ADD COLUMN` keeps its id and gains a
GlobalId per version (`mz_internal.mz_object_global_ids`), so the direct
joins below show its first version, which shares the table's persist shard
and frontier. Joining through `mz_object_global_ids` instead lists retired
versions too.
**Important column name notes:**
- `mz_source_statuses` and `mz_sink_statuses` use `last_status_change_at` (NOT `updated_at`)
- `mz_cluster_replica_statuses` uses `updated_at`
- `mz_cluster_replica_utilization` carries no cluster or replica *name* — JOIN
`mz_cluster_replicas` and `mz_clusters` to get them. It is keyed by
`(replica_id, process_id)`, so aggregate or filter by process on
multi-process sizes.
- When unsure, run `SHOW COLUMNS FROM <table>` first
---
## Environment Overview
### Version
`query_system_catalog` rejects a `SELECT` that references no system catalog
table, so the version probe needs a `FROM`.
```sql
SELECT mz_version() FROM mz_catalog.mz_databases LIMIT 1
```
### Clusters and Replicas
LEFT JOIN, not JOIN: a cluster without replicas has no replica rows, and an
inner join makes it invisible. Count the replica rows rather than reading
`replication_factor`, which is NULL on unmanaged clusters and counts only
billed replicas.
```sql
SELECT
c.name AS cluster_name,
c.managed,
c.size,
c.replication_factor,
r.name AS replica_name,
r.size AS replica_size,
(i.id IS NOT NULL) AS unbilled
FROM mz_catalog.mz_clusters c
LEFT JOIN mz_catalog.mz_cluster_replicas r ON c.id = r.cluster_id
LEFT JOIN mz_internal.mz_internal_cluster_replicas i ON i.id = r.id
ORDER BY c.name, r.name
```
### Cluster Topology
One row per cluster, zero-replica clusters included, with the replica count,
the replica sizes (an unmanaged cluster has NULL `size` and can mix sizes),
the unbilled support replicas, and the highest current replica memory percent
that the report template's Cluster Topology table asks for. Judge zero
replicas on user clusters (`c.id LIKE
'u%'`): system clusters can legitimately have no replicas (the Emulator
ships every one but `mz_catalog_server` that way), and none of them takes a
`cluster` argument usefully: `mz_system` refuses queries, `mz_probe`,
`mz_support` and `mz_analytics` deny `USAGE`, and `mz_catalog_server` refuses
user objects.
```sql
SELECT
c.name AS cluster_name,
c.managed,
c.size,
string_agg(DISTINCT r.size, ', ') AS replica_sizes,
c.replication_factor,
count(DISTINCT r.id) AS replicas,
count(DISTINCT i.id) AS unbilled_replicas,
max(u.memory_percent) AS max_memory_percent
FROM mz_catalog.mz_clusters c
LEFT JOIN mz_catalog.mz_cluster_replicas r ON r.cluster_id = c.id
LEFT JOIN mz_internal.mz_internal_cluster_replicas i ON i.id = r.id
LEFT JOIN mz_internal.mz_cluster_replica_utilization u ON u.replica_id = r.id
GROUP BY c.name, c.managed, c.size, c.replication_factor
ORDER BY c.name
```
### Schemas
```sql
SELECT
d.name AS database_name,
s.name AS schema_name
FROM mz_catalog.mz_schemas s
JOIN mz_catalog.mz_databases d ON s.database_id = d.id
WHERE s.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY d.name, s.name
```
---
## Object Inventory
### Sources
`mz_sources` has a row for every subsource and `progress` collection too, so
this returns many more rows than the user would call sources. Add
`AND s.type NOT IN ('subsource', 'progress')` before reporting a count.
```sql
SELECT
s.name AS source_name,
sc.name AS schema_name,
s.type AS source_type,
c.name AS cluster_name
FROM mz_catalog.mz_sources s
JOIN mz_catalog.mz_schemas sc ON s.schema_id = sc.id
LEFT JOIN mz_catalog.mz_clusters c ON s.cluster_id = c.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY s.name
```
### Materialized Views
```sql
SELECT
mv.name AS mv_name,
sc.name AS schema_name,
c.name AS cluster_name
FROM mz_catalog.mz_materialized_views mv
JOIN mz_catalog.mz_schemas sc ON mv.schema_id = sc.id
JOIN mz_catalog.mz_clusters c ON mv.cluster_id = c.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY mv.name
```
### Views (non-materialized)
```sql
SELECT
v.name AS view_name,
sc.name AS schema_name
FROM mz_catalog.mz_views v
JOIN mz_catalog.mz_schemas sc ON v.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY v.name
```
### Sinks
```sql
SELECT
sk.name AS sink_name,
sc.name AS schema_name,
sk.type AS sink_type,
c.name AS cluster_name
FROM mz_catalog.mz_sinks sk
JOIN mz_catalog.mz_schemas sc ON sk.schema_id = sc.id
LEFT JOIN mz_catalog.mz_clusters c ON sk.cluster_id = c.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY sk.name
```
### Indexes
```sql
SELECT
i.name AS index_name,
o.name AS on_object,
sc.name AS schema_name,
c.name AS cluster_name
FROM mz_catalog.mz_indexes i
JOIN mz_catalog.mz_objects o ON i.on_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
JOIN mz_catalog.mz_clusters c ON i.cluster_id = c.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY i.name
```
### Connections
```sql
SELECT
conn.name AS connection_name,
sc.name AS schema_name,
conn.type AS connection_type
FROM mz_catalog.mz_connections conn
JOIN mz_catalog.mz_schemas sc ON conn.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY conn.name
```
---
## Object Definitions
Pull the actual SQL definitions to analyze query patterns, join strategies, and
optimization opportunities.
### Materialized View Definitions
```sql
SELECT
sc.name AS schema_name,
mv.name AS mv_name,
mv.definition
FROM mz_catalog.mz_materialized_views mv
JOIN mz_catalog.mz_schemas sc ON mv.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY sc.name, mv.name
```
### View Definitions (non-materialized)
```sql
SELECT
sc.name AS schema_name,
v.name AS view_name,
v.definition
FROM mz_catalog.mz_views v
JOIN mz_catalog.mz_schemas sc ON v.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY sc.name, v.name
```
### Index Definitions
Note: `mz_indexes` does not have a `definition` column — use `create_sql` for
the full DDL. `mz_sources` is the same. `mz_views` and `mz_materialized_views`
*do* have `definition`.
```sql
SELECT
sc.name AS schema_name,
i.name AS index_name,
o.name AS on_object,
o.type AS on_type,
c.name AS cluster_name,
i.create_sql
FROM mz_catalog.mz_indexes i
JOIN mz_catalog.mz_objects o ON i.on_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
JOIN mz_catalog.mz_clusters c ON i.cluster_id = c.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY sc.name, i.name
```
### Source Definitions
```sql
SELECT
sc.name AS schema_name,
s.name AS source_name,
s.type AS source_type,
s.create_sql
FROM mz_catalog.mz_sources s
JOIN mz_catalog.mz_schemas sc ON s.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY sc.name, s.name
```
---
## Index Advice and Query Activity
### Index Advice (built-in optimizer)
```sql
SELECT
o.name AS object_name,
o.type AS object_type,
sc.name AS schema_name,
ia.hint,
ia.details
FROM mz_internal.mz_index_advice ia
JOIN mz_catalog.mz_objects o ON ia.object_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY ia.hint, o.name
```
Hint types — these six are the whole set, so do not filter on a subset:
- `keep` — the object is needed as-is
- `drop unless queried directly` — fewer than two downstream dependencies (or
none at all, or an index on a source); only useful if SELECT queries hit it
- `convert to a view` — MV can be dematerialized entirely, saving its
arrangement memory
- `convert to a view with an index` — MV can be a view, but keep its indexes
- `convert to materialized view` — a view with indexes on more than one cluster
should be an MV instead
- `add index` — object would benefit from an index
### Summary by Hint Type
```sql
SELECT
ia.hint,
COUNT(*) AS object_count
FROM mz_internal.mz_index_advice ia
JOIN mz_catalog.mz_objects o ON ia.object_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
GROUP BY ia.hint
ORDER BY object_count DESC
```
---
## Cost Analysis
### Credit Rates per Cluster Size
```sql
SELECT size, credits_per_hour
FROM mz_catalog.mz_cluster_replica_sizes
ORDER BY credits_per_hour
```
### Current Compute Cost per Cluster
Monthly credits per cluster projected from the replicas running right now
(730 hours/month), not a past month's spend.
Price the replicas, not the cluster: each replica row carries its own size,
so summing `credits_per_hour` over `mz_cluster_replicas` covers managed and
unmanaged clusters alike, mixed sizes included, with no `replication_factor`
(NULL on unmanaged clusters; multiplying by it on top of a replica join counts
a cluster twice). Support-created replicas
(`mz_internal.mz_internal_cluster_replicas`) are unbilled, so they are
excluded here, though they still run, still show in utilization, and still
count toward the replica rules; `replica_sizes` lists every replica,
`billed_replicas` and the credits only the billed ones. The LEFT JOIN keeps
zero-replica clusters at 0. System clusters (`s` ids: `mz_system`, `mz_catalog_server`, `mz_probe`,
`mz_support`, `mz_analytics`) are not billed, so both queries keep user
clusters only; `quickstart` is a user cluster. Credits are a Cloud billing
unit; on self-managed the shipped sizes carry Cloud's numbers and
operator-defined sizes carry whatever the operator set (the Helm template
defaults to 0.0), so treat them as relative weights at best and say so when
they read 0.
```sql
SELECT
c.name AS cluster_name,
c.managed,
string_agg(DISTINCT r.size, ', ') AS replica_sizes,
count(r.id) FILTER (WHERE i.id IS NULL) AS billed_replicas,
coalesce(sum(s.credits_per_hour), 0) AS cluster_credits_per_hour,
(coalesce(sum(s.credits_per_hour), 0) * 730)::numeric(10,1) AS monthly_credits
FROM mz_catalog.mz_clusters c
LEFT JOIN mz_catalog.mz_cluster_replicas r ON r.cluster_id = c.id
LEFT JOIN mz_internal.mz_internal_cluster_replicas i ON i.id = r.id
LEFT JOIN mz_catalog.mz_cluster_replica_sizes s ON s.size = r.size AND i.id IS NULL
WHERE c.id LIKE 'u%'
GROUP BY c.name, c.managed
ORDER BY cluster_credits_per_hour DESC
```
### Total Monthly Compute Cost
A projection from the replicas running right now (current rate times 730
hours), not the spend of any past month.
```sql
SELECT
coalesce(sum(s.credits_per_hour), 0)::numeric(10,2) AS total_credits_per_hour,
(coalesce(sum(s.credits_per_hour), 0) * 730)::numeric(10,1) AS total_monthly_credits
FROM mz_catalog.mz_clusters c
JOIN mz_catalog.mz_cluster_replicas r ON r.cluster_id = c.id
LEFT JOIN mz_internal.mz_internal_cluster_replicas i ON i.id = r.id
JOIN mz_catalog.mz_cluster_replica_sizes s ON s.size = r.size
WHERE c.id LIKE 'u%' AND i.id IS NULL
```
---
## Performance: Freshness / Lag
### Materialization Lag
`local_lag` and `global_lag` are plain `interval`s, so they sort and compare
directly. `slowest_global_input_id` names the input that is holding the object
back.
```sql
SELECT
o.name AS object_name,
o.type AS object_type,
sc.name AS schema_name,
l.local_lag,
l.global_lag,
si.name AS slowest_global_input
FROM mz_internal.mz_materialization_lag l
JOIN mz_catalog.mz_objects o ON l.object_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
LEFT JOIN mz_catalog.mz_objects si ON l.slowest_global_input_id = si.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY l.global_lag DESC NULLS LAST
LIMIT 30
```
### Write Frontiers (sorted oldest first)
`write_frontier` lives on `mz_internal.mz_frontiers` and is of type
`mz_timestamp` (milliseconds since epoch). You cannot subtract it from `now()`,
and `::bigint` fails with `CAST does not support casting from mz_timestamp to
bigint`; it casts to `text`, `timestamp` and `timestamptz`, so
`write_frontier::timestamptz::text` is the readable form (without the `::text`
the tools return it as epoch milliseconds again) and `write_frontier::text` the
raw millisecond number. Sort ascending to
find the most-lagging objects. A frontier of `0` means the object has never
been written, not that it was written in 1970.
```sql
SELECT
o.name AS object_name,
o.type AS object_type,
sc.name AS schema_name,
f.write_frontier::text AS write_frontier,
f.write_frontier::timestamptz::text AS frontier_time
FROM mz_internal.mz_frontiers f
JOIN mz_catalog.mz_objects o ON f.object_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
AND f.write_frontier IS NOT NULL
ORDER BY f.write_frontier ASC
LIMIT 30
```
### Source Status and Freshness
Driven from `mz_source_statuses`, which carries a row per source-fed table
(`type = 'table'`) as well as per source and subsource; a join from
`mz_sources` would hide every `CREATE TABLE ... FROM SOURCE` table. The view
reports webhook and `progress` rows as `running` unconditionally, and
`created` for a source with no status recorded yet.
```sql
SELECT
ss.name AS source_name,
ss.type AS source_type,
ss.status,
ss.error,
sc.name AS schema_name,
f.write_frontier::text AS write_frontier,
f.write_frontier::timestamptz::text AS frontier_time
FROM mz_internal.mz_source_statuses ss
LEFT JOIN mz_internal.mz_frontiers f ON ss.id = f.object_id
LEFT JOIN mz_catalog.mz_objects o ON ss.id = o.id
LEFT JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
WHERE sc.name IS NULL OR sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY f.write_frontier ASC NULLS FIRST
```
---
## Performance: Hydration
### Non-Hydrated Objects
Returns only objects that are NOT fully hydrated. The replica joins have to be
LEFT JOINs: `mz_hydration_statuses` records `replica_id IS NULL` when there is
no replica to hydrate on, so inner joins drop every object on a zero-replica
cluster. A NULL replica in the result means there is no replica to hydrate on;
confirm with the Clusters and Replicas query for that object's cluster.
```sql
SELECT
o.name AS object_name,
o.type AS object_type,
sc.name AS schema_name,
c.name AS cluster_name,
r.name AS replica_name,
h.hydrated
FROM mz_internal.mz_hydration_statuses h
JOIN mz_catalog.mz_objects o ON h.object_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
LEFT JOIN mz_catalog.mz_cluster_replicas r ON h.replica_id = r.id
LEFT JOIN mz_catalog.mz_clusters c ON r.cluster_id = c.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
AND h.hydrated = false
ORDER BY o.name
```
---
## Performance: Memory and Resource Usage
### Cluster Replica Utilization
One row per (replica, process); `process_id` tells them apart on multi-process
sizes (`mz_cluster_replica_sizes.processes` says how many a size has).
`mz_cluster_replica_metrics` is keyed the same way, so sum its `memory_bytes`
across processes for a replica total. `heap_percent` is RAM plus swap against
the limit, the reading the stale-MV runbook pairs with `memory_percent`.
```sql
SELECT
c.name AS cluster_name,
r.name AS replica_name,
r.size,
u.process_id,
u.cpu_percent,
u.memory_percent,
u.heap_percent,
u.disk_percent
FROM mz_internal.mz_cluster_replica_utilization u
JOIN mz_catalog.mz_cluster_replicas r ON u.replica_id = r.id
JOIN mz_catalog.mz_clusters c ON r.cluster_id = c.id
ORDER BY u.memory_percent DESC NULLS LAST
```
### Replica Restarts (OOM loop)
The current status and a utilization sample often look healthy between two
kills; the status history is what shows a loop. Replicas that were dropped or
replaced since keep their history rows, hence the LEFT JOIN. The reason
`oom-killed` covers the cgroup OOM killer, the replica's own heap limiter and
a full lgalloc spill disk alike, so it says memory (or spill disk), not which
limit. The emulator's process
orchestrator records no reason at all, so there only `offline_events` counts.
```sql
SELECT
c.name AS cluster_name,
r.name AS replica_name,
h.replica_id,
h.process_id,
count(*) FILTER (WHERE h.status = 'offline') AS offline_events,
count(*) FILTER (WHERE h.reason = 'oom-killed') AS oom_kills,
max(h.occurred_at) FILTER (WHERE h.reason = 'oom-killed')::text AS last_oom
FROM mz_internal.mz_cluster_replica_status_history h
LEFT JOIN mz_catalog.mz_cluster_replicas r ON h.replica_id = r.id
LEFT JOIN mz_catalog.mz_clusters c ON r.cluster_id = c.id
WHERE h.occurred_at > now() - INTERVAL '24 hours'
GROUP BY 1, 2, 3, 4
HAVING count(*) FILTER (WHERE h.status = 'offline') > 0
ORDER BY oom_kills DESC, offline_events DESC
```
### Cluster Replica Statuses
Driven from `mz_clusters` so that a cluster with no replicas still shows up,
with a NULL replica name and status; otherwise one row per replica process.
```sql
SELECT
c.name AS cluster_name,
c.replication_factor,
r.name AS replica_name,
rs.process_id,
rs.status,
rs.reason,
rs.updated_at::text
FROM mz_catalog.mz_clusters c
LEFT JOIN mz_catalog.mz_cluster_replicas r ON r.cluster_id = c.id
LEFT JOIN mz_internal.mz_cluster_replica_statuses rs ON r.id = rs.replica_id
ORDER BY c.name, r.name
```
### Arrangement Sizes (`query` tool)
`mz_introspection.mz_dataflow_arrangement_sizes` is cluster-scoped: run this
through `query` with the cluster to inspect, plus `cluster_replica` if it has
more than one replica. Its `id` is a dataflow id (`uint8`), never
`mz_objects.id` (`text`), so the catalog is reached through
`mz_compute_exports`. A NULL object is a dataflow that exports nothing in
`mz_objects`: an internal introspection subscribe
(`Dataflow: introspection-subscribe-...`), a user or Console `SUBSCRIBE`, a
running `SELECT` (`Dataflow: oneshot-select-...`), or the retired version of a
materialized view after `APPLY REPLACEMENT`, which keeps the view's old name
until it is dropped; those still count toward the cluster's memory. A
dataflow is named after the object as created, so a replaced view's live
dataflow carries the replacement's name. The query groups per dataflow
and lists what each one exports, so a dataflow with several exports appears
once and `exported_objects` 0 is a NULL-object dataflow. For one object's
per-operator memory, run `EXPLAIN ANALYZE MEMORY FOR MATERIALIZED VIEW
<schema>.<mv>` (or `FOR INDEX <name>`) on the same cluster.
```sql
SELECT
s.name AS dataflow_name,
s.records,
s.size,
count(o.id) AS exported_objects,
string_agg(o.type || ' ' || o.name, ', ' ORDER BY o.name) AS objects
FROM mz_introspection.mz_dataflow_arrangement_sizes s
LEFT JOIN mz_introspection.mz_compute_exports e ON e.dataflow_id = s.id
LEFT JOIN mz_catalog.mz_objects o ON o.id = e.export_id
GROUP BY s.id, s.name, s.records, s.size
ORDER BY s.size DESC NULLS LAST
LIMIT 30
```
---
## Object Dependencies
### Dependency Graph
Objects also depend on the builtin types and functions their definitions
reference, one row each, so the parent side excludes types and functions; system relations a view reads (say
`mz_catalog.mz_tables`) stay, since that is a real dependency. Still
unbounded: add a `LIMIT`, or narrow to the objects under investigation, before
running it into the 1 MB response cap.
```sql
SELECT
parent.name AS parent_name,
parent.type AS parent_type,
child.name AS child_name,
child.type AS child_type
FROM mz_internal.mz_object_dependencies d
JOIN mz_catalog.mz_objects parent ON d.referenced_object_id = parent.id
JOIN mz_catalog.mz_objects child ON d.object_id = child.id
JOIN mz_catalog.mz_schemas sc ON child.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
AND parent.type NOT IN ('type', 'function')
ORDER BY parent.name, child.name
```
---
## Health: Source and Sink Statuses
### Source Statuses
Driven from `mz_source_statuses` so that source-fed tables (`type = 'table'`)
are included; see Source Status and Freshness.
```sql
SELECT
ss.name AS source_name,
ss.type AS source_type,
ss.status,
ss.error,
ss.last_status_change_at::text,
sc.name AS schema_name
FROM mz_internal.mz_source_statuses ss
LEFT JOIN mz_catalog.mz_objects o ON ss.id = o.id
LEFT JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
WHERE sc.name IS NULL OR sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY ss.status DESC, ss.name
```
### Sink Statuses
```sql
SELECT
sk.name AS sink_name,
sk.type AS sink_type,
ss.status,
ss.error,
ss.last_status_change_at::text,
sc.name AS schema_name
FROM mz_catalog.mz_sinks sk
JOIN mz_internal.mz_sink_statuses ss ON sk.id = ss.id
JOIN mz_catalog.mz_schemas sc ON sk.schema_id = sc.id
WHERE sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY ss.status DESC, sk.name
```
### Source Statistics
`mz_source_statistics` is keyed on (source, replica), which is not one row per
replica: Kafka and load-generator sources run on every replica and get one row
each with independent counters (never sum across rows), while PostgreSQL,
MySQL and SQL Server sources always run on one replica and have one row.
Rows outlive their replica: after a pause, resize or replica change the old
row stays for a day, stale, next to a fresh one for the new replica id whose
counters restart at zero, so judge by `replica_exists`, not by row count.
Webhook sources run on no replica and carry a NULL `replica_id` (even on a
cluster without replicas). An ingestion that never ran, because its cluster
has no replicas or no table is attached to it, has no row, so its
`snapshot_committed` reads as missing rather than `false`. Statistics are
written once a minute and the first row for a (source, replica) is all
zeros, so for about two minutes after a source or replica starts the counters
read 0 and `snapshot_committed` reads `false` whatever the truth. `progress`
collections have no row. Source-fed tables and subsources have their own rows
and the parent source's row is the SUM of them (`offset_known` the MAX,
`offset_committed` the MIN), so never add a source row to its tables' rows;
the query joins `mz_objects` because `id` may be a table.
```sql
SELECT
o.name AS source_name,
o.type AS object_type,
st.replica_id,
(r.id IS NOT NULL) AS replica_exists,
st.snapshot_committed,
st.messages_received,
st.bytes_received,
st.updates_staged,
st.updates_committed
FROM mz_internal.mz_source_statistics st
LEFT JOIN mz_catalog.mz_objects o ON st.id = o.id
LEFT JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
LEFT JOIN mz_catalog.mz_cluster_replicas r ON r.id = st.replica_id
WHERE sc.name IS NULL OR sc.name NOT IN ('mz_catalog', 'mz_internal', 'pg_catalog', 'information_schema', 'mz_introspection')
ORDER BY o.name, st.replica_id
```
references/report-template.md
# Report Template
The shape of the analysis report SKILL.md's Step 4 produces. Fill every
section from the queries in `queries.md`; the Cluster Topology table comes
from the `Cluster Topology` query there plus the credit columns of
`Current Compute Cost per Cluster`.
```markdown
# Environment Analysis
**Date**: <date>
**Materialize Version**: <version>
## Executive Summary
<2-3 paragraph high-level assessment>
## Cluster Topology
| Cluster | Size | Replicas | Credits/Hr | Monthly Credits | Utilization |
## Deployed Objects
### Sources (<count>)
### Materialized Views (<count>)
### Sinks (<count>)
### Indexes (<count>)
## Performance Analysis
### Freshness
### Hydration
### Cluster Utilization
### Worker Skew
### Source and Sink Health
## Cost Analysis (if requested)
## Index Advice Summary
## SQL-Level Analysis
### Materialized View Definitions
### Index Analysis
## Optimization Recommendations
<numbered list with specific SQL for each>
```
SKILL.md
---
name: mcp-developer-analysis
description: 'Analyze a Materialize environment via the MCP Developer endpoint, and/or configure an MCP client (Claude Code, Cursor, VS Code, Zed, Continue, Windsurf, Claude Desktop) to connect to the materialize-developer server. For analysis: check environment health, investigate performance, troubleshoot stale materialized views, diagnose memory pressure, audit resource utilization, run EXPLAIN ANALYZE on user objects, get optimization recommendations. For client connection: configure/connect/set-up an MCP client to materialize-developer (Emulator, Cloud, or self-managed), control which user/role is used, switch between identities. Trigger even if user just says "check my environment", "why is my MV stale", "why is my cluster slow", "what can I optimize", "explain analyze my materialized view", "how do I connect Claude Code to materialize-developer", or "configure Cursor for the Materialize MCP".'
---
# Materialize Developer Analysis
Analyze a Materialize environment via the MCP Developer endpoint and produce a
structured report with health status, performance findings, and optimization
recommendations. Assumes Materialize v26.24 or later.
The developer endpoint exposes two read-only tools:
- **`query_system_catalog`** — `SELECT`/`SHOW`/`EXPLAIN` restricted to system
catalog tables (`mz_*`, `pg_catalog`, `information_schema`). Takes no cluster
argument, and one passed anyway is silently ignored rather than rejected.
Catalog reads are auto-routed to the catalog server cluster
(`mz_catalog_server`) while `auto_route_catalog_queries` is on (the
default); anything the router cannot serve there — notably every
`mz_introspection` relation — runs on the session's default cluster (the
role's `cluster` default if set, else the system default, `quickstart`
unless the operator changed it). Use for most catalog lookups.
- **`query`** (added in Materialize v26.30) — `SELECT`/`SHOW`/`EXPLAIN` against
any object the role can access, on the cluster named by its required
`cluster` argument (plus, from v26.33, an optional `cluster_replica`).
Required for `EXPLAIN ANALYZE` (it must run on the
MV/index's cluster) and for reading user data directly. May be hidden when
the operator has disabled `enable_mcp_developer_query_tool`.
## Connecting an MCP client to `materialize-developer`
If the user is asking how to **configure**, **connect**, or **set up** an MCP
client (Claude Code, Cursor, VS Code, Zed, Continue, Windsurf, Claude Desktop)
to talk to the `materialize-developer` server — including how to control which
user or role the connection uses, or how to switch between identities — see
[`mcp-client-connect.md`](mcp-client-connect.md). It covers the Emulator,
Materialize Cloud, and self-managed deployments, with per-client snippets,
authentication patterns, and verification recipes.
The rest of this `SKILL.md` covers what to do *once you are connected* and want
to analyze the environment.
## Discovering Tables and Columns
**Do NOT guess column names.** System catalog relations are a mix of tables,
views, materialized views and sources, so no single `SHOW TABLES` or
`SHOW VIEWS` lists them all; use `SHOW OBJECTS FROM <schema>`. The ontology
views in `mz_internal` describe the catalog; use them to discover the correct
tables, columns, join paths, and ID types:
| Table | What it tells you |
|-------|-------------------|
| `mz_internal.mz_ontology_entity_types` | What catalog entities exist and which `mz_*` table they map to. |
| `mz_internal.mz_ontology_link_types` | Relationships between entities (foreign keys, metrics, etc.). |
| `mz_internal.mz_ontology_properties` | Column names, types, and descriptions for each entity. |
| `mz_internal.mz_ontology_semantic_types` | Typed ID domains (CatalogItemId, ReplicaId, etc.). |
Example queries:
```sql
-- Find the right table for an entity
SELECT name, relation, description
FROM mz_internal.mz_ontology_entity_types
WHERE name LIKE '%source%'
-- Find join paths between entities
SELECT name, source_entity, target_entity, properties, description
FROM mz_internal.mz_ontology_link_types
WHERE source_entity = 'source' OR target_entity = 'source'
-- Find columns for a table
SELECT column_name, semantic_type, description
FROM mz_internal.mz_ontology_properties
WHERE entity_type = 'source_status'
```
Verify column names with `SHOW COLUMNS FROM <schema>.<table>` before querying.
## Critical Rules
The server's own `initialize` instructions carry the catalog gotchas: column
names such as `last_status_change_at`, the replica-to-cluster JOIN for
`mz_cluster_replica_utilization`, the ontology views, and from v26.40 the
`mz_introspection` routing and id types. Most clients forward them; if yours
does not, `SHOW COLUMNS` is the fallback.
### `mz_introspection` relations need the `query` tool
Every `mz_introspection` relation, `mz_dataflow_arrangement_sizes` included,
is cluster-scoped, and `query_system_catalog` cannot target a cluster, so
through that tool the read lands on the session's default cluster: with
exactly one replica there you get its numbers, or an empty result, with no
error; with several replicas the read fails with `log source reads must
target a replica`, with none with `has no replicas available to service
request`. Read them through `query` with the `cluster` argument, plus
`cluster_replica` (from v26.33) on a cluster with more than one replica
(unbilled ones count), which is what the first error asks for; nothing fixes
the second.
Servers before v26.40 say in their `initialize` instructions never
to query `mz_dataflow_arrangement_sizes`; that rule predates the `query`
tool, and this supersedes it. The query, its join path to the catalog, and
its caveats are under Arrangement Sizes in `references/queries.md`. Without
the `query` tool, fall back to the relations under Memory and Resource
Consumption below.
## Workflow Overview
1. **Connect** — Verify the MCP Developer tools are available
2. **Discover** — Use the ontology + catalog queries to inventory all deployed objects
3. **Analyze** — Assess performance metrics: freshness, hydration, memory, utilization
4. **Report** — Produce a structured markdown report with findings and recommendations
## Step 1: Verify MCP Connection
Confirm you have access to the `query_system_catalog` tool. Run a quick test:
```
query_system_catalog: SELECT mz_version() FROM mz_catalog.mz_databases LIMIT 1
```
`query_system_catalog` rejects a `SELECT` that references no system catalog
table, so a bare `SELECT mz_version()` fails with `Query must reference at
least one system catalog table`, which reads like a broken connection.
Check whether a `query` tool is among the tools your client exposes. With it
you can also run `EXPLAIN ANALYZE` and queries against user objects on a named
cluster; without it (operator disabled it, or a pre-v26.30 build), fall back
to `query_system_catalog` for everything that fits.
If `query_system_catalog` fails, check:
- The MCP server is registered in your client's MCP configuration (the
file and key differ per client, see `mcp-client-connect.md`)
- The `enable_mcp_developer` feature flag is enabled on the environment
- Your authentication credentials are valid
### Running Queries
Both tools take one read-only statement per call, no `SET`, and no trailing
semicolon (one is tolerated; two statements are rejected).
Results come back as a bare array of rows with no column names, and every `AS`
alias is discarded, so map columns positionally and keep the `SELECT` list
short and in the order you intend to read it. Numbers arrive as strings,
booleans as JSON `true`/`false`, NULL as `null`; `timestamp` and `timestamptz`
values arrive as millisecond-since-epoch strings (`1787687415336.000`), so cast
them to `text` where you need a readable time. Qualify object names with the database when the object is not in the
session's database (`SHOW database`), or the statement fails with `unknown
schema`. A response is capped (1 MB by
default) and a request timed out (60 seconds by default), both configurable by
the operator and both surfacing as errors. A `LIMIT` keeps a catalog
enumeration under the cap, and shortens the work only for a plain read of one
indexed relation, not for a join. A timeout releases the call but sometimes
not the query, so do not retry a timed-out statement as it was.
`query_system_catalog` checks its allowlist by walking FROM-clause table
references, so statements without one are not checked at all: `SHOW TABLES`,
`SHOW COLUMNS`, the `SHOW CREATE` forms and object-level `EXPLAIN ANALYZE`
pass there and can name user objects (which is why the latter answers empty,
see Worker Skew). Treat that as a fallback for when `query` is missing; use
`query` for user objects.
When filtering out system schemas, always exclude: `mz_catalog`, `mz_internal`,
`pg_catalog`, `information_schema`, and `mz_introspection`. `SHOW SCHEMAS` also
lists `mz_catalog_unstable` and `mz_unsafe`, which hold no relations.
## Step 2: Discover — Inventory the Environment
Run the discovery queries to understand what is deployed. See
`references/queries.md` for the full query set. The discovery phase covers:
### Environment Overview
- Materialize version
- Clusters and replicas — names, `managed`, replica counts and sizes. Start
from `mz_clusters` and LEFT JOIN the replicas, or a cluster without
replicas disappears from the inventory. An unmanaged cluster has NULL
`size` and `replication_factor`; unbilled support replicas are listed in
`mz_internal.mz_internal_cluster_replicas` and count like any other.
- Schemas in use
### Deployed Objects Inventory
- **Sources**: type (Kafka, Postgres, MySQL, Webhook, etc.), cluster
assignment, status. `mz_sources` also has a row for every subsource and
`progress` collection, so filter `type NOT IN ('subsource', 'progress')`
before reporting a source count.
- **Materialized Views**: cluster assignment, indexes, dependencies
- **Views**: (non-materialized) and their usage patterns
- **Sinks**: type, destination, cluster assignment
- **Indexes**: what they're on, cluster assignment
- **Connections**: external system connections configured
### Object Definitions
Retrieve SQL definitions for materialized views, views, indexes, and sources
using `references/queries.md`. This is critical for optimization analysis —
the SQL definitions tell you *how* things are computed:
- Join patterns and join order
- Filter predicates (or lack thereof — missing temporal filters are a common issue)
- Aggregation strategies
- Whether MVs duplicate logic that could be shared
## Step 3: Analyze — Performance and Resource Metrics
### Freshness (Lag Analysis)
Query `mz_internal.mz_materialization_lag` for per-object lag. Its `local_lag`
and `global_lag` columns are plain `interval`s, so they compare and sort
directly, and `slowest_global_input_id` names the input holding the object
back. Both lags are measured against the inputs' frontiers, not the wall
clock: a pipeline whose source is paused shows zero lag while growing
arbitrarily stale, so also compare `mz_frontiers.write_frontier` with `now()`
(the Write Frontiers query).
`write_frontier` is not on this relation. It lives on
`mz_internal.mz_frontiers` as an `mz_timestamp`, which casts to `timestamptz`
for a readable time and to `text` (then `bigint`) for the raw number, never
directly to `bigint`. `references/queries.md` has the queries.
### Hydration Status
Query `mz_internal.mz_hydration_statuses` to check whether all dataflows are
hydrated. Non-hydrated objects after initial startup may indicate resource
pressure or configuration issues. LEFT JOIN the replica columns: `replica_id`
is NULL with no replica to hydrate on, and an inner join hides those objects.
### Memory and Resource Consumption
- `mz_internal.mz_cluster_replica_utilization` for memory/CPU percentage per replica process
- `mz_internal.mz_cluster_replica_metrics` for raw memory metrics, also one
row per (replica, process)
- `mz_internal.mz_index_advice` to identify which MVs/indexes can be optimized
### Worker Skew (CPU imbalance across workers)
Use `WITH SKEW` to find operators where one worker does disproportionate
CPU/memory work.
**Run these through the `query` tool**, not `query_system_catalog`:
`EXPLAIN ANALYZE` executes on the cluster you pass as the `cluster` argument,
and for the object-level commands that must be the cluster the MV/index lives
on. Getting it wrong does not reliably error: on another single-replica
cluster, or through `query_system_catalog` when the default cluster has one
replica, the object-level commands return an empty result, which reads like
"no skew"; with several replicas the read fails with `log source reads must
target a replica`, with none with `has no replicas available to service
request`. Without `query`, this section is not actionable.
**Cluster-level (run on the cluster you want to inspect):**
```sql
EXPLAIN ANALYZE CLUSTER CPU WITH SKEW
```
**Object-level (run for both the MV and its indexes):**
```sql
EXPLAIN ANALYZE CPU WITH SKEW FOR MATERIALIZED VIEW <schema>.<mv_name>
```
```sql
EXPLAIN ANALYZE CPU WITH SKEW FOR INDEX <schema>.<index_name>
```
If skew is present: identify the skewed operator (often TopK/window/agg/join/distinct), then inspect definitions (`SHOW CREATE ...`) and recommend a concrete SQL change (remove/adjust hints, refactor keys/partitioning, or rewrite the MV).
### Index Advice
Query `mz_internal.mz_index_advice`, Materialize's built-in advisor. It emits
exactly six hints: `keep`, `drop unless queried directly`, `convert to a view`,
`convert to a view with an index`, `convert to materialized view`, `add index`.
Filtering on a subset silently drops real recommendations;
`references/queries.md` says what each one means.
### Cost Analysis (optional)
Use the Cost Analysis queries in `references/queries.md` (per-replica pricing).
When writing recommendations, **always quantify the credit impact** (credits
are a Cloud billing unit; on self-managed the shipped sizes carry Cloud's
numbers and operator-defined sizes whatever the operator set, 0 by default,
so present them as relative weights at best).
### Object Dependencies
Query `mz_internal.mz_object_dependencies` to understand the dependency graph.
## Step 4: Report — Generate the Analysis
Produce a structured markdown report following
`references/report-template.md`: executive summary, cluster topology,
deployed objects, performance analysis (freshness, hydration, utilization,
worker skew, source and sink health), cost analysis, index advice, SQL-level
analysis, and numbered recommendations with specific SQL.
The Cluster Topology table comes from the `Cluster Topology` query in
`references/queries.md` plus the credit columns of `Current Compute Cost per
Cluster` there.
### Writing Recommendations
**Always include specific SQL commands.** For example:
Good:
> **Recommendation:** Dematerialize `my_schema.unused_mv` to save memory.
> ```sql
> SHOW CREATE MATERIALIZED VIEW my_schema.unused_mv
> DROP MATERIALIZED VIEW my_schema.unused_mv
> CREATE VIEW my_schema.unused_mv AS <definition>
> ```
Bad:
> **Recommendation:** Consider dematerializing `my_schema.unused_mv`.
## Troubleshooting Runbooks
**Always end with specific SQL commands to fix the issue**, and never apply
one without the user's yes: scaling up or adding replicas costs money, and
dropping or altering objects loses state. Every fix below is DDL, which both
tools reject, so hand the commands to the user.
### "Why is my materialized view stale?"
**Diagnostic steps:**
1. Check whether the MV's cluster has any replica first: `mz_clusters` LEFT
JOIN `mz_cluster_replicas` (the Clusters and Replicas query), not
`replication_factor`, which is NULL on unmanaged clusters and blind to
unbilled replicas. A cluster with no replicas runs nothing, and the steps
below don't work well.
2. Check `mz_internal.mz_hydration_statuses` — is the MV hydrated, and is
everything else on its cluster (the Non-Hydrated Objects query, restricted
to that cluster)?
3. Check `mz_internal.mz_cluster_replica_status_history` for recent `offline`
events with reason `oom-killed`, which covers the cgroup OOM killer, the
heap limiter and a full lgalloc spill disk alike (the Replica Restarts
query; the emulator records no reason, so there judge by the `offline`
count): an OOM loop often reads `online` in the current status and modest
in a utilization sample. Most OOMs happen during hydration, so the loop is
over when step 2 shows everything hydrated and the latest history row per
process is `online`; kills that recur after hydration completes are a
steady-state loop, which only a size change or less on the cluster ends.
4. Check `mz_internal.mz_cluster_replica_utilization`: `memory_percent` near
100 means RAM is full and further growth lands on swap where there is
swap, felt as slow hydration or lag; `heap_percent` (RAM plus swap against
the limit) climbing while `memory_percent` stays put is that swap in use,
and near 100 the next spike kills the replica. Either way: scale up, move
the MV, or shrink what the cluster holds (the fixes below, and step 7).
5. Check `mz_internal.mz_source_statuses` — upstream source errors?
6. Check `mz_internal.mz_materialization_lag` for the MV's lag (see Freshness)
7. If the `query` tool is available, run
`EXPLAIN ANALYZE MEMORY FOR MATERIALIZED VIEW <schema>.<mv>` on the MV's
cluster to see per-operator memory and spot expensive shapes (large
arrangements, joins without indexes, missing temporal filters).
**Common fixes:**
*If the MV's cluster has no replicas:*
```sql
ALTER CLUSTER <cluster_name> SET (REPLICATION FACTOR 1)
```
```sql
-- Unmanaged cluster: add a replica directly
CREATE CLUSTER REPLICA <cluster_name>.r1 (SIZE = '<size>')
```
*If the cluster is overloaded (high memory/CPU):*
```sql
-- Option A: Scale up the cluster (unmanaged: DROP and CREATE its replicas
-- at the bigger size instead)
ALTER CLUSTER <cluster_name> SET (SIZE = '<next_size_up>')
```
```sql
-- Option B: Move the MV to a different cluster
SHOW CREATE MATERIALIZED VIEW <schema>.<mv_name>
DROP MATERIALIZED VIEW <schema>.<mv_name>
CREATE MATERIALIZED VIEW <schema>.<mv_name> IN CLUSTER <new_cluster> AS <definition>
```
*If an upstream source is not running:*
```sql
SELECT name, status, error, last_status_change_at
FROM mz_internal.mz_source_statuses
WHERE status != 'running'
```
Not every non-`running` status is a fault, and only `stalled` carries an
`error`; "Are my sources healthy?" below lists what each status means.
Fix the upstream source issue first. MV freshness depends on source health.
### "Why is my cluster running out of memory?"
**Diagnostic steps:**
1. Check `mz_internal.mz_cluster_replica_utilization` for memory percentage and
`mz_cluster_replica_status_history` for `oom-killed` events (Replica Restarts)
2. Check `mz_internal.mz_index_advice` for MVs that can be dematerialized
3. Check MV definitions for missing temporal filters
4. Check for redundant indexes
5. If the `query` tool is available, run
`EXPLAIN ANALYZE MEMORY FOR MATERIALIZED VIEW <schema>.<mv>` (or
`FOR INDEX <name>`) on the suspect object's cluster to see which operators
hold the most memory — the most direct way to confirm which arrangement is
responsible.
**Common fixes:**
*Dematerialize MVs that don't need to be materialized:*
```sql
SELECT o.name, o.type, sc.name AS schema_name, ia.hint, ia.details
FROM mz_internal.mz_index_advice ia
JOIN mz_catalog.mz_objects o ON ia.object_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
WHERE ia.hint = 'convert to a view'
```
`convert to a view with an index` and `convert to materialized view` are memory
recommendations too, with different remediations, so read the whole advice set
(the Index Advice query in `references/queries.md`) before acting on this one.
```sql
-- For each candidate:
SHOW CREATE MATERIALIZED VIEW <schema>.<mv_name>
DROP MATERIALIZED VIEW <schema>.<mv_name>
CREATE VIEW <schema>.<mv_name> AS <definition>
```
*Drop unused indexes:*
```sql
SELECT o.name, o.type, sc.name AS schema_name, ia.hint, ia.details
FROM mz_internal.mz_index_advice ia
JOIN mz_catalog.mz_objects o ON ia.object_id = o.id
JOIN mz_catalog.mz_schemas sc ON o.schema_id = sc.id
WHERE ia.hint = 'drop unless queried directly'
```
```sql
-- Verify with the user before dropping
DROP INDEX <schema>.<index_name>
```
*Scale up the cluster (unmanaged: DROP and CREATE its replicas at the bigger
size instead):*
```sql
ALTER CLUSTER <cluster_name> SET (SIZE = '<next_size_up>')
```
### "Are my sources healthy? / Has my source finished snapshotting?"
**Diagnostic steps:**
1. Check `mz_internal.mz_source_statuses` for source errors
2. Check `mz_internal.mz_source_statistics` for ingestion progress
3. Check `mz_internal.mz_materialization_lag` for end-to-end lag
**Common fixes:**
*If a source is not running:*
```sql
SELECT name, status, error
FROM mz_internal.mz_source_statuses
WHERE status != 'running'
```
The status is one of `created` (no status recorded yet), `starting`,
`running`, `paused`, `stalled` or `dropped`; the docs also list `failed`,
which the code never produces. Only `stalled` carries an `error`. Webhook and
`progress` rows always read `running`. `created` and `starting` are transient
and normal; `paused` means the source's cluster has no replicas, which no
amount of waiting fixes:
```sql
-- paused: give the source's cluster a replica
ALTER CLUSTER <cluster_name> SET (REPLICATION FACTOR 1)
-- unmanaged cluster: CREATE CLUSTER REPLICA <cluster_name>.r1 (SIZE = '<size>')
```
```sql
-- stalled/failed on bad credentials:
ALTER SECRET <secret_name> AS '<new_value>'
```
If `snapshot_committed` is `false`, the source is still loading its initial
snapshot. This is normal for large sources — wait for it to complete; it also
reads `false` with zero counters for about two minutes after a source or
replica starts. A source with no `mz_source_statistics` row at all never ran,
so check its status and whether its cluster has any replica instead of
waiting; a paused source keeps a stale row, so read `status` first.
### "What's the health of my environment?"
Run these checks in order:
1. `mz_internal.mz_cluster_replica_statuses` — all replicas of user clusters
ready, and no recent `oom-killed` in `mz_cluster_replica_status_history`?
2. `mz_internal.mz_source_statuses` — all sources running?
3. `mz_internal.mz_sink_statuses` — all sinks running?
4. `mz_internal.mz_cluster_replica_utilization` — resource pressure?
### "What can I optimize to save costs?"
1. Check `mz_internal.mz_index_advice` for optimization candidates
2. Check cluster utilization — are clusters over-provisioned?
3. Check credit rates from `mz_catalog.mz_cluster_replica_sizes`
## Notes
- Access is governed by RBAC, so you only see the *data* your credentials have
access to. The catalog is not gated the same way: a role with no object
grants still reads the full object inventory, every view and MV `definition`,
and all of `mz_index_advice`. Where RBAC is on, `query` and every read that
is not auto-routed also need `USAGE` on the cluster they run on
(`mz_catalog_server` grants it to `PUBLIC`). Whether RBAC checks are on
depends on the deployment and its configuration; `SHOW enable_rbac_checks`
and `SHOW enable_session_rbac_checks` tell (either `on` enables them).
- Freshness numbers are point-in-time snapshots. Re-run to check if lag is stable or growing.