Semantic reranking

Provider-neutral semantic reranking is Experimental. Its PostgreSQL 17 and 18 in-server, backup/restore, and one-million-row certification gates pass locally. Stable promotion still requires retained hosted build and smoke evidence for pgcontext-worker on Darwin and Linux, each on arm64 and x86_64.

pgContext can release a bounded set of currently authorized source texts to an external reranker, then treat the returned scores as untrusted ordering input. The model process never receives database credentials or authority. PostgreSQL rechecks every final row under the caller’s current ACL, RLS, filter, deletion, source-version, and content-hash state.

This is a detached two-step API. An application prepares an envelope, sends it to its chosen worker, and finalizes the worker response in PostgreSQL.

Prepared requests are transaction- and WAL-durable on the live cluster for their bounded lifetime, but they are intentionally absent from logical extension dumps. Source registrations are restored; applications must prepare a new request after logical backup and restore.

Register a source

The source table must already back a collection. It needs a single-column primary key named id, one text column, and one positive bigint source version column.

SELECT pgcontext.register_semantic_rerank_source(
    'articles',
    'body',
    'body_text',
    'source_version'
);

-- Metadata may use only registered fields, and its value must match the row.
SELECT pgcontext.register_filter_column('articles', 'kind', 'kind');

Registration records relation, key, text, version, type, collation, and content hash identity. Each prepared request also freezes a digest of every filter and metadata binding it used, including relation OID, attnum, column name, type OID, type modifier, collation OID, and JSON path. Only a collection owner can register or replace a binding. Missing objects, incompatible types, lost privileges, re-registration, and catalog drift fail closed.

Prepare an authorized envelope

Candidate identities normally come from query_multi_model or another bounded retrieval stage. Each occurrence includes its logical point, fused rank and score, and rank-fusion contributions.

SELECT pgcontext.prepare_semantic_rerank(
    'articles',
    'body',
    'postgres storage internals',
    '[
      {
        "occurrence_id": 11,
        "point_id": 42,
        "fused_rank": 1,
        "fused_score": 0.0164,
        "contributions": [
          {
            "profile": "dense_v2",
            "rank": 1,
            "native_score": 0.12,
            "weight": 1.0,
            "contribution": 0.0164
          }
        ],
        "metadata": [{"key": "kind", "value": "article"}]
      }
    ]'::jsonb,
    'my-cross-encoder',
    7,
    5000,
    'require_reranker',
    '{"must":[{"key":"tenant","match":"acme"}]}'::jsonb,
    false
);

Preparation validates the complete request before creating private request state. It then resolves only the declared point IDs, applies the registered filter with bound parameters, reads the source under current MVCC and RLS, and returns a versioned envelope containing the authorized text and SHA-256 content digest. Private candidate rows retain identity, version, digest, fusion provenance, and allow-listed metadata, but do not duplicate source text or source keys.

Every metadata key must name a registered filter field. Preparation compares the supplied value with the authoritative row, and finalization repeats that comparison so metadata drift cannot survive an otherwise unchanged text hash.

The limits are:

  • 512 unique occurrences and point IDs;
  • 32 KiB of text per candidate;
  • 64 KiB for the query;
  • 4 MiB for the complete envelope projection;
  • 6 MiB for one encoded newline-delimited worker frame;
  • eight metadata pairs per candidate;
  • 127 fusion contributions per candidate;
  • an expiry from 1 to 60,000 milliseconds.

PostgreSQL admits JSONB before converting it into Rust values. Candidate input is limited to 4 MiB, 100,000 iterator tokens, and nesting depth 64. A filter is limited to 256 KiB, 1,024 tokens, and the same depth. A worker response is limited to 256 KiB, 4,096 tokens, and the same depth. These structural limits apply even when every individual field is otherwise valid.

Finalize untrusted output

The response must echo envelope version 3, request ID, model name, and model revision. Scores must be finite, unique, and refer only to released occurrence IDs.

SELECT pgcontext.finalize_semantic_rerank(
    123,
    '{
      "version": 3,
      "request_id": 123,
      "model": "my-cross-encoder",
      "model_revision": 7,
      "scores": [{"occurrence_id": 11, "score": 0.91}]
    }'::jsonb,
    NULL
);

allow_partial = false requires exactly one score for every released candidate. With allow_partial = true, omitted or newly invisible rows produce partial_reranked; they never masquerade as a complete result. Ties break by ascending occurrence ID. Returned rows include point and occurrence identity, the reranker score, fused score/rank, source version, contribution provenance, and allow-listed metadata. They never include the released text.

An identical replay is idempotent, but it still rechecks current PostgreSQL authorization and source state before returning rows. A different replay for the same request is rejected.

Failure policy

require_reranker fails when the worker is unavailable, times out, crashes, expires, or returns partial output. allow_fused_fallback may return the stored fused order for the operational reasons unavailable, timeout, crash, partial_output, or expired:

SELECT pgcontext.finalize_semantic_rerank(123, NULL, 'timeout');

Fallback is visible as status = "degraded_reranker" and includes a bounded degraded_reason. Cancellation, malformed or injected output, permission loss, RLS/filter changes, source edits, registration drift, and model/request identity mismatch never use a permissive fallback.

Expired or finalized requests can be removed in bounded batches:

SELECT pgcontext.cleanup_semantic_rerank_requests(1000);

Worker deployment

pgcontext-worker is a separate Rust binary; it is not loaded into a PostgreSQL backend. The certified linear_pair_v1 adapter is a private contract fixture, not a claim of general transformer compatibility. It loads only an operator-provided artifact whose exact byte length and SHA-256 digest match an immutable manifest. The manifest also fixes model/tokenizer revision, score contract, candidate/token/time limits, retry count, circuit-breaker threshold and cooldown, platforms, SPDX identifier, license URL, and operator_provided_only distribution.

Token ceilings are adapter-manifest specific. The certified linear_pair_v1 fixture accepts at most eight query tokens and sixteen document tokens; a future adapter must declare and certify its own values without weakening the PostgreSQL envelope and JSONB limits above.

No weights are bundled or downloaded. The worker has no network adapter or default egress path. It uses a current-thread Tokio runtime, a tracked blocking scorer, cooperative cancellation, bounded retries, and one persistent circuit breaker for the lifetime of the process.

Run pgcontext-worker score --manifest PATH as a newline-delimited service. Write one rerank_envelope_v3 JSON object per line. A successful line produces one rerank_response_v3 line with the same request ID. An operational failure produces a bounded rerank_failure_v1 line:

{"version":1,"request_id":123,"error":"timeout","failure_reason":"timeout"}

Route a success object to finalize_semantic_rerank(request_id, response, NULL). Route an operational failure to finalize_semantic_rerank(request_id, NULL, failure_reason). Invalid input is terminal and content-free. Write the reserved line shutdown to request cooperative cancellation; the process joins any active scorer before exiting. The input reader stops at the 6 MiB frame ceiling even if a sender never writes a newline. CRLF input is accepted by removing one trailing carriage return.

Default diagnostics are content-free: do not add query, source, tenant, metadata values, or provider payloads to worker or PostgreSQL logs. The runtime and direct-license selection record is in design/p12-worker-runtime-spike.md.