fix(db): raise pgxpool MaxConns + PG max_connections + set PG resources - #1221

## 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]>

…1225)

## Summary

Follow-up to #1221. That PR doubled pgxpool `MaxConns` from 30 to 60 per
pod without bumping the registry pod memory limit. pgxpool keeps
per-connection state in the Go process (prepared statement cache,
scratch buffers, row iteration state), so memory grew roughly
proportionally — peak per-pod usage shifted from ~330 MB to ~450 MB
against a 512 MiB limit.

## What surfaced it

Pod `tx49f` was OOMKilled at 17:01 UTC on 2026-04-29 (`exitCode 137`,
`reason: OOMKilled`). Memory history from Cloud Monitoring confirms the
regression:

| Pods | Peak 30-min memory |
|------|-------------------:|
| Pre-#1221 (`MaxConns=30`) | 178–333 MB |
| **Post-#1221** (`MaxConns=60`) | **437–449 MB** |
| Old limit | **512 MiB** ← right at the edge |

Other pod was at 394 MB at the time of inspection — could OOM again.

## Fix

`deploy/pkg/k8s/registry.go:217-225`:

| | Before | After |
|---|-------:|------:|
| `requests.memory` | 256 MiB | **512 MiB** |
| `limits.memory` | 512 MiB | **1 GiB** |

1 GiB is ~2× the current peak under load — gives room for traffic spikes
without immediate OOM risk. Either node has 4–5 GiB free allocatable
memory, so the new request fits comfortably.

## Test plan

- [x] `go build ./...` clean
- [x] `golangci-lint run` clean
- [ ] After release + prod promotion: pod memory stabilises around 450
MB peak with no OOMKills
- [ ] After release + prod promotion: tx49f restartCount stops climbing
(currently at 5)

## Deployment

Zero downtime — Deployment template change only, rolling update with
`maxSurge: 1, maxUnavailable: 0`. **No PG involvement** (no
`max_connections` change, no shared_preload_libraries change). Same
shape as a normal v1.7.x image promotion.

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

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

## Summary

Promotes
[v1.7.4](https://github.com/modelcontextprotocol/registry/releases/tag/v1.7.4)
(#1225) to production: registry pod memory request `256Mi → 512Mi`,
limit `512Mi → 1Gi`.

## Why

#1221 doubled pgxpool `MaxConns` 30→60 per pod without bumping the
registry pod memory limit. pgxpool keeps per-connection state in the Go
process (prepared statement cache, scratch buffers, row iteration state)
so peak per-pod memory shifted from ~330 MB to ~450 MB against the 512
MiB limit. Pod `tx49f` was OOMKilled at 17:01 UTC on 2026-04-29.

## Validation so far

- Staging deployed cleanly via `deploy-staging.yml` after #1225 merged
- Both staging pods running with `req=512Mi limit=1Gi` ✓
- Rolling update was clean — no failed health checks during the
transition
- Both pods spread across nodes (q716 + 7exo)

## Deployment

**Zero downtime** — Deployment template change only, rolling update with
`maxSurge: 1, maxUnavailable: 0`. No PG involvement (no
`max_connections` change, no shared_preload_libraries change).

## Test plan

- [x] v1.7.4 release built and pushed
(`ghcr.io/modelcontextprotocol/registry:1.7.4`)
- [x] Staging deployed cleanly with new limits
- [ ] Prod Pulumi run applies cleanly; rolling update completes
- [ ] Prod pods come up with `req=512Mi limit=1Gi`
- [ ] After 24h: no OOMKills on either pod, peak memory stays under ~600
MB

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

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