MCP Verify 1.0.636 Database Performance and Partitioning

Date: 2026-08-14

Outcome

This release replaces several request-time catalog and analytics scans with indexed, persisted values and incremental summaries. It also introduces an online monthly partition conversion for analytics_events. Scoring formulas and public trust semantics are unchanged.

Destructive retention is deliberately disabled at release. Enabling it later requires a successful production-like restore and raw-versus-summary parity evidence. The configured policy is:

full validation evidence: 30 days, retaining at least 40 runs per server;
terminal jobs: 14 days;
raw analytics events: 90 days;
compact daily and monthly summaries: indefinite.

validation_runs and jobs remain unpartitioned. Their principal issue is unbounded terminal history, and their current foreign-key/access patterns are better served by summaries, retention, and partial indexes.

Runtime architecture

ingestion / validation
  -> normalized server identity and persisted public score
  -> indexed catalog reads

analytics events
  -> monthly UTC RANGE partitions
  -> incremental daily/session/funnel rollups
  -> bounded admin conversion queries

validation runs / jobs
  -> compact daily/monthly summaries
  -> gated bounded retention batches (default off)

Each Verify process now owns one reusable SQLAlchemy engine and pool. Production budgets are web 10+5, worker 3+2, and migration 2+0; connections carry a service-specific application_name, statement timeout, pool timeout, and pool recycle policy. The web read transaction is released before CPU-heavy admin report assembly.

Schema and query changes

Migration 0029_db_performance adds deterministic query columns to servers:

endpoint host and endpoint fingerprint;
homepage host and repository slug;
server-card URL fingerprint;
generated search document;
canonical server identity;
public display score, algorithm version, and comparable-cohort membership.

The values use the same endpoint classifier as validation, so registry listing URLs do not become endpoint identities. Ingestion updates affected canonical clusters transactionally. Catalog filtering/counting/pagination and persisted score percentile queries execute in SQL when the feature flags are enabled. Search uses a trigram GIN index while retaining existing ordering rules.

Validation history gains compact tools-list status/full-payload markers and a server-version foreign-key index. Latest-completed history uses (server_id, completed_at DESC NULLS LAST, started_at DESC). The duplicate server/started index is removed. Jobs gain partial pending-claim and running- lease indexes plus recent/type history indexes.

Rollups and retention gates

The maintenance job refreshes:

daily analytics dimensions;
30-day session automation and per-server funnel state;
daily and monthly validation counts/status digests;
daily job counts by type and status.

After the first materialization, only the trailing two days are recomputed to capture late events. Admin conversion responses expose the rollup watermark and fall back to raw queries when coverage is insufficient.

Retention requires MCP_VERIFY_DATABASE_RETENTION_ENABLED=true, MCP_VERIFY_DATABASE_RESTORE_GATE_VERIFIED=true, and a recent successful summary watermark. Deletes use bounded SKIP LOCKED batches. Partition removal additionally requires an explicit backup-verified-through date and the watermark to cover the complete partition. The release deployment leaves both gates false and keeps the original analytics table as a rollback backup after cutover. Before an old validation row is removed, any hosted-runtime release that points to it receives a compact evidence record (run/server IDs, status, score, schema, and completion time) in release metadata; the foreign key may then safely become null without erasing the release's evidence lineage.

Analytics partition conversion

The management command implements:

create a shadow RANGE(created_at) parent, current/next-three monthly

partitions, an alerted default partition, local indexes, and an insert mirror;

backfill in bounded batches;
compare count, timestamp bounds, and ID checksum;
lock briefly, catch up, verify again, and atomically rename;
restart all writers, prove old-writer mirroring, then remove the mirror;
retain analytics_events_unpartitioned_backup until the rollback window ends.

An explicit drain-default operation detaches the safety partition inside a transaction, creates the missing month ranges, copies and verifies every row, then reattaches an empty default partition. It never silently leaves out-of- range rows behind.

The partitioned primary key is (created_at, id). Application-generated UUIDs remain unchanged and no table references analytics event IDs.

Observability

PostgreSQL enables pg_stat_statements, track_io_timing, slow-query logging, and large-temporary-file logging. A second postgres-exporter targets the Verify database. Alerts cover connection pressure, dead tuples, temporary writes, rollup lag, and exporter availability.

scripts/report_verify_database_performance.py emits a read-only JSON snapshot of table/index usage, connection/transaction state, temporary I/O, and the top normalized statements. Its estimated_p95_ms is mean + 1.645 * stddev, an operational estimate rather than a sampled percentile; exact query ranking is reviewed after seven representative days.

/version exposes non-secret effective pool, timeout, feature-flag, maintenance, and retention settings.

Safety and rollback

Migration and index creation are online on PostgreSQL.
Partition backfill is dual-written and parity checked twice.
A failed pre-swap stage leaves the original table authoritative.
The old analytics table remains present after finalization.
Retention and expired-partition deletion are disabled by default.
Persisted identity/score flags are enabled only after the backfill succeeds.
Rollback before backup removal is an atomic reverse rename after writers stop.

Verification

Required checks are the Verify suite, root unit suite, make test-suites, the predeployment invariants, PostgreSQL migration through head, online partition cutover, compose validation, and production raw/checksum parity. CI now exercises the PostgreSQL migration and an empty-table version of the complete partition workflow.

Document Content