# PostgreSQL index and worker cluster

Set `storage.postgres` to share the index for a primary Open Knowledge Format bundle. The authoritative source can be a folder, a PostgreSQL table projection, or a SQLite table projection. The index holds source versions, jobs, artifacts, evidence, and retained publications. Workers use the same extraction, chunking, and model pipeline as the embedded SQLite backend.

Build with the `postgres` feature. Add `mcp` for the MCP service. PostgreSQL connections use TLS with a hostname-matching server certificate and an explicit PEM trust root. Use separate logins for the index and any upstream database source, even if both live on one server.

## Initialize and enroll database roles

Copy the [sample index configuration](https://github.com/tsok-org/source-code/blob/main/apps/knowledge/configs/postgres-index.yml) and provide the password through the named environment variable or a private file. Use a migration configuration whose database role owns the `knowledge_index` schema and its objects. The role needs database CREATE privilege on first use. Create separate, unprivileged LOGIN roles for the long-running processes.

```sh
knowledge -c migration.yml cluster init
knowledge -c migration.yml cluster grant-role knowledge_worker
knowledge -c migration.yml cluster grant-role knowledge_reader --read-only
knowledge -c notebook.yml cluster reconciler
knowledge -c notebook.yml cluster worker
```

Role grants apply to the configured bundle. Enroll each login only in its intended bundles. PostgreSQL row-level policies scope table access by the authenticated login. `cluster revoke-role ROLE` removes one bundle authorization. Enroll again after recreating or renaming a login. Restrict direct SQL access to trusted operators. The database reader can read raw retained artifacts in its bundle. Use authenticated HTTP or MCP for path-scoped end users.

To upgrade a v1 index, stop the services, workers, and reconcilers, back up the database, run `cluster init` with the migration role for each bundle using its original processing configuration, and enroll the runtime roles before restarting. The migration retains publication keys and citation IDs. To roll back to v1, restore the pre-migration database backup before starting the old binary. Application release numbers are independent of the PostgreSQL artifact recipe salt. Changing the actual processing recipe remains a separate cutover. See [model changes](https://tsok.org/knowledge/docs/model-changes/) for the implications.

## Reconciliation, workers, and resource limits

Start `cluster worker` in multiple processes or on separate machines. Worker hosts need access to PostgreSQL and their configured models. They can run without mounting the authoritative OKF source. Run `cluster reconciler` where it can read that source. Multiple reconcilers can contend for the database lease. For folder sources, mount the same authoritative folder at the same canonical path on each reconciler host. They share a durable record of observed changes, so changing the reconciler preserves the waiting window.

Set `indexing.change_batching.min_scan_interval_ms`, `quiet_ms`, and `max_delay_ms` to control burst coalescing. The watch loop combines upstream notifications when available with periodic full scans. It drops transient reversions and waits for a quiet version or the maximum delay. It also defers a newer non-deletion while an older job for that path holds a live lease. An incomplete or failed scan preserves the previous publication. Explicit `cluster reconcile` and `index` apply a snapshot immediately. Full scans still read the inventory and obey source budgets.

Set `indexing.resources.max_concurrent_jobs` and local pacing per process. To cap concurrently leased indexing jobs across bundles and hosts that use the same PostgreSQL index database, give those bundles the same `storage.postgres.provider_quota.group` and `max_running`. Register the group with `cluster init`. The database serializes admission for that group and releases capacity on job completion, cancellation, or lease expiry. Use `cluster quota-set GROUP COUNT` as the migration role to change the cap, then align deployment configuration before running `cluster init` again. A lower cap pauses new admissions until the current count falls below it. This limits indexing job concurrency. Provider-side requests per minute, GPU memory, and interactive query calls need separate controls.

Use `index` for a one-shot inventory and job drain, `work` for a one-shot drain, `status` to see job counts, and `retry` to requeue failed jobs for currently desired versions. A drain exits nonzero when work remains queued, running, or failed. Lowering the maximum attempts moves exhausted jobs to failed, including jobs still waiting on a long retry delay.

Claims commit before model inference. Heartbeats renew only the current lease owner and fence. Publication verifies that fence, the expiry, and the desired version, then commits the artifact, citation references, and publication together. A failed worker can cause another attempt at inference. Only the valid attempt can publish. Publication order is per bundle.

## Query through HTTP, MCP, Rust, or CLI

Set `server.watch: false` and use a database role enrolled with `--read-only`. Configure the usual TLS certificates and client authentication. Use `storage.database` for the service's local auth and replay state and `storage.encryption_key` when using SQLCipher. Use a distinct local state file per PostgreSQL bundle. The index itself uses PostgreSQL storage and backup controls.

`knowledge -c reader.yml serve` exposes a backend-specific OpenAPI description at `/openapi.json`. It serves capabilities, status for principals allowed global access, retrieval, context assembly, catalog selection, and retained evidence. `server.mcp.http.enabled` enables Streamable HTTP. `mcp-stdio` serves the local profile. Read tools, evidence resources, catalog resources, prompts, and configured read-side MCP extensions use the same publication and path access rules. Authoring, durable task and event journals, federation, and consolidation remain on SQLite. PostgreSQL configuration rejects incompatible server options at startup.

```sh
knowledge -c notebook.yml search "sensor calibration" --mode lexical
knowledge -c notebook.yml context "What does the procedure require?"
knowledge -c notebook.yml cluster retrieve query.json --prefix public
knowledge -c notebook.yml cluster evidence EVIDENCE_ID --prefix public
```

Rust hosts can use `knowledge::cluster::Cluster` for the shared index. Pass an explicit restricted `Access` to serve untrusted clients. Use `publication_key` or `publication_id` to pin a query and preserve the evidence view. A key and number given together must refer to the same publication.

Exact search reads a repeatable-read snapshot and pushes path scopes into PostgreSQL. BM25 uses only authorized documents. Exact vector ranking, fusion, graph traversal, and reranking use the shared Rust stack. The read transaction closes before model calls. `max_projection_bytes` and retrieval limits bound the amount of data loaded per request.

## Native vector candidates

For an empty bundle, have the DBA install pgvector in the `public` schema. Set `storage.postgres.native_vector.dimensions` to the model dimension and optionally `ef_search` to tune HNSW search. Run `cluster init`, enroll runtime roles, and build the index or import a compatible archive. Keep the dimensions consistent across the deployment. The initial migration of an already populated bundle can preserve exact retrieval. To add HNSW, create a new bundle and rebuild it or import a SQLite archive into it.

HNSW applies to unfiltered, whole-bundle vector retrieval with `graph_hops: 0` against the current publication in the active space. Each bundle has its own table and cosine index. Candidates are checked against the publication in PostgreSQL and reranked with original vectors in Rust. When candidate search returns too few hits, the query falls back to exact retrieval. Scoped and filtered queries, pinned queries, multiple retained embedding spaces, hybrid search, graph search, and context building use exact retrieval. The response includes a warning when approximate candidates were used. Benchmark recall on your corpus before enabling it. [pgvector HNSW reference](https://github.com/pgvector/pgvector/tree/v0.8.7#hnsw)

## Import, backup, and recovery

Create the archive with `knowledge -c sqlite.yml backup ARCHIVE`. Initialize an empty target, then run `knowledge -c migration.yml cluster import-sqlite ARCHIVE`. For an encrypted archive, provide its key with `storage.encryption_key` and use a SQLCipher build. The import checks the SQLite archive, source hashes, and citation IDs. It copies retained source versions, publication keys and numbers, citation URIs, membership, and embedding recipes in one PostgreSQL transaction. It rejects archives with supplemental, federated, curated, derived, or binary-artifact state. Back up the original source separately. To change the source, model, or pipeline after import, validate a shadow bundle first.

Use PostgreSQL's backup and PITR tooling for production. Back up the authoritative OKF source and local service state separately. A logical export can use `pg_dump -Fc --schema=knowledge_index`. Restore into an isolated database, install the same pgvector extension first if the dump contains vector tables, and verify retained pins and citations. For cross-cluster restores, restore as the target migration owner with `--no-owner --no-acl`, clear the restored `knowledge_index.bundle_roles` rows before connecting runtime processes, and re-enroll target logins with `cluster grant-role`. Role authorization binds to the database's role name and OID. Keep the dump and WAL under the same encryption and access policy as the source data.

Use a stable writer endpoint and a PostgreSQL HA system to fence the former primary and promote an eligible standby. Knowledge reconnects to the configured TLS endpoint after a broken session. The recovery exercise uses a primary and streaming standby on one machine. Validate real network partitions, synchronous replication policy, and promotion automation against the production topology before relying on a specific recovery point or time.

## Reproduce the validation

```sh
cargo build --locked --no-default-features --features postgres,mcp
python3 scripts/check-postgres-cluster.py "$(command -v knowledge)" "$(pg_config --bindir)" \
  --http --recovery --benchmark
```

Add `--pgvector` when pgvector is installed for the test server, `--legacy-binary PATH` for an actual v1 binary upgrade, or `--embedding-gguf PATH` on a build with the embedded model feature. The fixture uses disposable PostgreSQL, TLS, and a local HTTP embedding server. The HTTPS fixture uses Node.js for TLS 1.3 support. See the [benchmark results](https://github.com/tsok-org/source-code/blob/main/apps/knowledge/reports/postgres/VALIDATION.md) and [storage design](https://github.com/tsok-org/source-code/blob/main/apps/knowledge/docs/postgres-index-design.md) for scale and feature boundaries.
