fix(list): use row-constructor cursor + restore per-phase publish timings + enable pg_stat_statements - #1215

## Summary

Promotes
[v1.7.2](https://github.com/modelcontextprotocol/registry/releases/tag/v1.7.2)
(PR #1215) to production:

- **Row-constructor cursor pagination** — fixes the 760ms list query
that was the root cause of today's pool-exhaustion alerts. Local
benchmark shows /v0/servers paginated requests dropping from ~30ms (cold
cache, 100K rows) to ~0.05ms.
- **Per-phase publish slog** — `publish complete` events now include
validate / lock / remotes / version_checks / unmark / db_create timings
so the next slow publish is self-diagnostic.
- **pg_stat_statements** — aggregate query visibility we didn't have
during today's incident.

## Deployment caveat

The CNPG cluster spec change for `pg_stat_statements` triggers an in-pod
postgres restart on the next prod Pulumi run. Staging took **~45–70s of
registry unavailability** during the equivalent restart. The DB
retry-with-backoff in v1.7.1 covers most of it, but registry pods may
bounce once before they reconnect (one staging pod hit `Failed to
connect after 8 attempts` then recovered on its next restart).

**Time the merge for a low-traffic UTC window** — the alert history
suggests very early UTC is quietest.

## Post-merge action

Once the prod Pulumi run completes, run once on the existing cluster:

```bash
PATH=/opt/homebrew/share/google-cloud-sdk/bin:$PATH
kubectl exec -i registry-pg-1 -c postgres \
  --context gke_mcp-registry-prod_us-central1-b_mcp-registry-prod \
  -- psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"
```

Verify:

```bash
kubectl exec -i registry-pg-1 -c postgres \
  --context gke_mcp-registry-prod_us-central1-b_mcp-registry-prod \
  -- psql -U postgres -d app -c "
    SELECT round(mean_exec_time::numeric, 2) AS mean_ms, calls,
           substring(query, 1, 80) AS q
    FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"
```

## Test plan

- [x] v1.7.2 release built and pushed
(`ghcr.io/modelcontextprotocol/registry:1.7.2`)
- [x] Staging deployed cleanly; `pg_stat_statements` is collecting on
staging
- [ ] Prod Pulumi run applies cleanly; brief PG restart
- [ ] Run `CREATE EXTENSION` on prod
- [ ] Confirm `publish complete` events contain per-phase timings on the
next publish

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-authored-by: Claude Opus 4.7 (1M context) <[email protected]>

…#1220)

## Summary

Same shape of bug as #1215's cursor fix, different filter. The RemoteURL
filter built its WHERE predicate using `jsonb_array_elements` + `->>`
extraction, which the planner can't translate into a GIN search.
Replaced with a JSONB containment predicate `@>` that uses
`idx_servers_json_remotes`.

## Diagnosis (from the per-phase publish slog #1215 added)

Today's first publish-latency alert at 17:08 UTC pointed straight at
this:

```
publish complete server_name=dev.storage/mcp version=1.10.1 total_ms=14822
  validate_ms=0 lock_ms=238 remotes_ms=10980 version_checks_ms=535 unmark_ms=3014 create_ms=53
```

`remotes_ms=10,980` on a server with **one** remote URL pointed at
`validateNoDuplicateRemoteURLs`, which runs the RemoteURL filter against
the database. EXPLAIN ANALYZE on prod confirmed:

```
Filter: (status != 'deleted') AND EXISTS(SubPlan 1)
Rows Removed by Filter: 21,092       ← scans the whole table
Buffers: shared hit=20,028
Execution Time: 10,005 ms
```

## Fix

`internal/database/postgres.go:99` rewritten:

```sql
-- before  (planner can't use GIN index for this)
EXISTS (SELECT 1 FROM jsonb_array_elements(value->'remotes') AS remote
        WHERE remote->>'url' = $1)

-- after   (GIN-indexable)
value -> 'remotes' @> jsonb_build_array(jsonb_build_object('url', $1::text))
```

Semantically equivalent for our schema (`url` is always a string, NOT
NULL).

## Local benchmark — 20K rows, prod-shaped JSONB (~1KB/row)

| Form | Buffer hits | Index used | Time |
|------|------------:|------------|-----:|
| OLD | 40,398 | none — full scan | 17.0 ms |
| NEW | **294** | **`idx_servers_json_remotes` GIN bitmap** | **1.6 ms**
|

Maps to prod's 10,005 ms cold-cache observation — same shape of speedup
we got from the cursor fix.

## Scope honesty

This PR addresses the **publish slowness path**
(`validateNoDuplicateRemoteURLs` in the publish transaction). It
directly resolves today's 17:08 UTC `Publish Endpoint Latency` alert.

It does **not** address:

- Today's 17:35–17:40 UTC `Availability dropped below 95%` alert.
Per-investigation: only 4 publishes happened in the entire 17:00–17:50
window and zero during the burst minutes — so my initial "concurrent
slow publishes starved the pool" hypothesis was wrong. The actual cause
was scraper-driven concurrency (4,500+ requests in 5 min) on plain
cursor pagination + ILIKE substring search, where individual queries are
fast (mean 42 ms) but tail latency under concurrency reaches 10 s
server-side and 20–35 s nginx-level due to pool queueing.
- The 18:17 UTC `Publish Endpoint Latency` re-fire (same scraper-driven
concurrency).

A follow-up PR will raise pool size + PG `max_connections` + add
explicit PG resource limits to absorb concurrency without queue blowup.
That fix has a different shape (config) and a different risk profile (PG
restart for `max_connections`); kept separate so this surgical SQL
change can ship cleanly.

## Test plan

- [x] `go build ./...` clean
- [x] `make lint` clean
- [x] `go test -race ./internal/database/... ./internal/service/...`
green — existing `TestPostgreSQL_ListServers/filter by remote URL`
covers semantic correctness
- [x] EXPLAIN ANALYZE comparison locally on 20K rows + prod-shaped JSONB
- [ ] After merge + deploy: verify on prod via `EXPLAIN ANALYZE`;
confirm `remotes_ms` in `publish complete` slog drops to single-digit ms

## Out of scope (separate follow-ups)

- pgxpool `MaxConns` / `MinConns` bump + PG `max_connections` raise + PG
`resources` limits — addresses the cursor-tail and ILIKE-search
concurrency that this PR doesn't touch
- Per-IP rate limiting at nginx — defends against scraper retry storms
regardless of query speed
- `Cache-Control` headers on `/v0/servers` — let nginx absorb scraper
repeats
- Pre-existing `superfluous WriteHeader` warnings — separate Huma
framework issue
- ILIKE substring search on `server_name` is unindexable — could be
replaced with `pg_trgm` GIN index or full-text search

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-authored-by: Claude Opus 4.7 (1M context) <[email protected]>

…es (#1221)

## Summary

Follow-up to #1215 and #1220. Both of those addressed *individual* slow
queries; this PR addresses *concurrency* — under today's scraper load
the cursor query is fast on average (mean 42ms) but the connection pool
saturates and queue depth blows up at the Go HTTP layer.

## Diagnosis

`pg_stat_statements` (added in #1215) made this possible to see:

```
max_ms   mean_ms  calls    template
10,755     41.8   94,009   plain cursor pagination, no filter
 7,277    214.7   14,770   ILIKE substring filter
 7,638    330.8    7,656   ILIKE substring + is_latest filter
```

Mean times are healthy. But `max_exec_time` of 7–10s on the cursor
query, combined with sustained ~15 req/s from scrapers (ServiceNow's
148.139.x.x range, anonymous `node` user-agent, etc) saturates
`MaxConns=30 × 2 pods = 60`. New requests queue at the Go HTTP layer;
nginx-side p99 hits 35s; eventually scrapers time out at 60s, retry, and
amplify the queue.

Today's two ongoing alerts are both this pattern:
- 17:35–17:40 UTC `Availability dropped below 95%` — 4,526 GET requests
in 5 min, hundreds of 504s
- 18:17 UTC `Publish Endpoint Latency` re-fire — same scraper
concurrency dragging the publish path

Critically, the symptoms today were also visible during yesterday's
incident, but yesterday's broken cursor (#1215) was the dominant cause.
After #1215 the cursor is fast individually; concurrency now becomes the
next bottleneck.

## Changes

### pgxpool (`internal/database/postgres.go`)

| Setting | Before | After |
|---------|-------:|------:|
| `MaxConns` | 30 | **60** per pod |
| `MinConns` | 5 | **10** per pod |
| `MaxConnIdleTime` | 30 min | (unchanged) |
| `MaxConnLifetime` | 2 h | (unchanged) |

2 pods × 60 = 120 total app connections. Cuts queue depth roughly in
half at current scraper load.

### PG cluster (`deploy/pkg/k8s/postgres.go`)

- `max_connections: 100 → 200` — required to support the larger pool.
120 (app) + ~10 (PG internals: autovacuum, replication, admin) + 70
headroom.
- Added explicit `resources:` block — previously the pod had no resource
limits, making node-level OOM behaviour unpredictable.

| | Request | Limit |
|---|--------:|------:|
| memory | 512Mi | **4Gi** |
| cpu | 200m | 1500m |

## Resource budget

| Node | Now | After |
|------|----:|------:|
| dy89 (PG node) | 39% mem (~2.4 GiB / 6 GiB) | ~65% mem worst-case
(~3.9 GiB) |
| 2yxm | 36% mem | unchanged |

Both nodes well within capacity. CPU usage <50% on both, plenty of
headroom.

PG worst-case memory math:
- 200 conns × ~15 MB per backend = 3 GiB
- + `shared_buffers` 128 MiB
- + `maintenance_work_mem`, OS overhead, etc.
- ≈ 3–4 GiB total

## Deployment caveat

`max_connections` is a postmaster-level setting → CNPG triggers a PG
restart on the change. Same in-pod restart shape as the
pg_stat_statements deploy yesterday — registry pods see ~30s of DB
unavailability, covered by v1.7.1's retry-with-backoff (8 attempts, 1→8s
capped). One registry pod may bounce once before recovering, like
yesterday.

**Time the merge for a low-traffic UTC window.**

Order of operations matters within the deploy itself:
1. Pulumi applies the new CNPG spec → PG restarts with
`max_connections=200`
2. Rolling deploy of registry pods picks up `MaxConns=60` config
3. New conns are accepted up to 200 limit

Pulumi's standard ordering does step 1 before step 2 in this scenario
(Pulumi resource graph: CNPG cluster precedes Deployment). If for any
reason it doesn't, the worst case is `too many connections` errors
during a small window — Self-correcting once the rollout completes.

## Test plan

- [x] `go build ./...` clean for app + deploy
- [x] `make lint` clean
- [x] `go test -race ./internal/database/...` green
- [ ] On merge: staging deploy applies the spec change cleanly; PG
restarts; pool reaches 60 max
- [ ] On prod deploy: same; verify no `too many connections` errors
during the rollout window
- [ ] After deploy: query `SHOW max_connections` returns 200; query
pg_stat_statements after a scraper burst, confirm `max_exec_time` for
cursor query no longer hits 10s

## Out of scope

- **ILIKE substring search** (`server_name ILIKE '%foo%'`) is
unindexable. Three pg_stat_statements variants run with means 141–333ms
and max 4–7s. Worth replacing with a `pg_trgm` GIN index or full-text
search in a separate PR.
- **Per-IP nginx rate limiting** — defends against scraper retry storms
regardless of pool size.
- **`Cache-Control` on `/v0/servers`** — let nginx absorb
scraper-repeated cursors.
- **Pre-existing `superfluous WriteHeader` warnings** — separate Huma
framework issue.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-authored-by: Claude Opus 4.7 (1M context) <[email protected]>

## Summary

Promotes
[v1.7.3](https://github.com/modelcontextprotocol/registry/releases/tag/v1.7.3)
to production. Contents:

- **#1220** — `RemoteURL` filter SQL rewritten to use JSONB containment
(`@>`) instead of `EXISTS jsonb_array_elements`. The new form is
GIN-indexable; the old form forced a full table scan. Local benchmark:
40,398 → 294 buffer reads, 17 ms → 1.6 ms. Maps to prod's 10,005 ms
cold-cache observation.
- **#1221** — pgxpool `MaxConns 30→60`, `MinConns 5→10`. PG
`max_connections 100→200`. Explicit PG `resources:` block (was unset).

## What this addresses

- **#1220**: yesterday's 17:08 UTC `Publish Endpoint Latency` alert
(`dev.storage/mcp` publish took 14.8s with `remotes_ms=10980` —
pinpointed by the per-phase slog from #1215).
- **#1221**: yesterday's 17:35–17:40 UTC `Availability dropped below
95%` alert and 18:17 UTC `Publish Endpoint Latency` re-fire. Both were
scraper-driven concurrency on `/v0/servers` (~15 req/s sustained from
ServiceNow + others). With the bumped pool, the queue at the Go HTTP
layer should clear faster instead of blowing up to 20–35s nginx-level
latencies.

## Deployment caveat

The PG `max_connections` change is a postmaster-level setting → CNPG
triggers a PG restart on the next prod Pulumi run. With `instances: 1`
this is brief downtime — staging took **~30s** during the equivalent
restart, with **one registry pod bouncing once** on its 8-attempt
DB-retry budget before recovering on the next kubelet restart.

**Time the merge for a low-traffic UTC window.** Alert history suggests
very early UTC (02:00–04:00) is quietest.

## Resource impact

PG node memory is currently 39% (~2.4 GiB / 6 GiB allocatable).
Worst-case PG memory growth with `max_connections=200` lands around 3–4
GiB, putting the node at ~65% — fits with headroom. Empirically, prod PG
has peaked at **413 MiB** in the last 30h of incident data, so the
proposed 4 GiB limit is ~10× the historical max — guardrail not
constraint.

## Post-merge

CNPG handles `pg_stat_statements` extension creation automatically (no
manual `CREATE EXTENSION` step needed — it was already done in v1.7.2's
deploy).

Verify after deploy:

```bash
PATH=/opt/homebrew/share/google-cloud-sdk/bin:$PATH

# max_connections actually changed
kubectl exec -i registry-pg-1 -c postgres \
  --context gke_mcp-registry-prod_us-central1-b_mcp-registry-prod \
  -- psql -U postgres -tAc "SHOW max_connections"  # expect: 200

# resources block applied
kubectl get pod registry-pg-1 \
  --context gke_mcp-registry-prod_us-central1-b_mcp-registry-prod \
  -o jsonpath='{.spec.containers[?(@.name=="postgres")].resources}{"\n"}'

# pgxpool MaxConns reflected (registry app uses 60 per pod after restart)
kubectl exec -i registry-pg-1 -c postgres \
  --context gke_mcp-registry-prod_us-central1-b_mcp-registry-prod \
  -- psql -U postgres -tAc "SELECT count(*) FROM pg_stat_activity WHERE datname='app'"
```

## Test plan

- [x] v1.7.3 release built and pushed
(`ghcr.io/modelcontextprotocol/registry:1.7.3`)
- [x] Staging deployed cleanly; PG restarted and came back with
`max_connections=200`; one staging pod bounced as expected
- [ ] Prod Pulumi run applies cleanly; brief PG restart
- [ ] Confirm `SHOW max_connections` returns 200 on prod
- [ ] Confirm `publish complete` events show `remotes_ms` < 10ms
- [ ] Watch for any "too many connections" errors during the rollout
window (none expected — Pulumi orders CNPG cluster before Deployment, so
PG accepts the new conn limit before pgxpool tries to use it)

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-authored-by: Claude Opus 4.7 (1M context) <[email protected]>