What you are building#

This article is the second part of the Compiled Knowledge sub-series. The first part, From Agentic RAG to Compiled Knowledge, made the architectural case: four independent teams converged in spring 2026 on the same idea. Stop making the agent rediscover knowledge on every question. Compute the synthesis once at ingest time. Store the result in a queryable form. Pinecone calls the result an artifact. Karpathy calls it a wiki page. Microsoft and Google call it an ontology or a catalog.

What you are building here is an artifacts table that does the same job for one team without buying any of those products. An artifact is not a chunk of text. It is a typed, pre-synthesized record that answers a known class of questions for a specific agent persona. “Total revenue of Allstate Corp for fiscal year 2024” is an artifact. “A 500-word PDF fragment that mentions Allstate Corp three times” is a chunk. The artifact carries the exact number, the source citation, and any metadata the agent needs to render an audit-ready answer in one query.

One row equals one artifact equals one pre-computed answer for one persona. If the financial analyst and the compliance officer need the same source document in different shapes, that produces two rows. The table is denormalized by design.

The payoff is that the agent’s read path collapses to a single SQL statement. No retrieval loop. No vector search returning ten chunks the model has to reconcile. No 49,000-token rediscovery on every request (the agentic RAG average from Pinecone’s KRAFTBench benchmark). The agent issues one structured query, receives one structured row, and exits.

The schema#

The whole pattern lives in one CREATE TABLE plus three CREATE INDEX statements. Read it once, then we’ll walk the columns.

1
CREATE EXTENSION IF NOT EXISTS pgcrypto;
2
CREATE EXTENSION IF NOT EXISTS vector;
3

4
CREATE TABLE artifacts (
5
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
6
  source_id           uuid NOT NULL REFERENCES sources(id),
7
  artifact_type       text NOT NULL,
8
  persona_tag         text,
9
  payload             jsonb NOT NULL,
10
  payload_embedding   vector(1536),
11
  payload_tsv         tsvector,
12
  eval_set_version    text,
13
  compiled_at         timestamptz NOT NULL DEFAULT now(),
14
  source_version_hash text NOT NULL,
15
  schema_version      smallint NOT NULL DEFAULT 1
16
);
17

18
CREATE INDEX artifacts_payload_gin
19
  ON artifacts USING GIN (payload jsonb_path_ops);
20

21
CREATE INDEX artifacts_payload_tsv_gin
22
  ON artifacts USING GIN (payload_tsv);
23

24
CREATE INDEX artifacts_payload_hnsw
25
  ON artifacts USING HNSW (payload_embedding vector_cosine_ops);

Eleven columns and three indexes. Every column earns its keep.

id is a UUID primary key. gen_random_uuid() ships with pgcrypto, available everywhere. UUIDs let you mint artifact identifiers on the application side without round-tripping the database when the compiler fans out work.

source_id is the foreign key to the source-of-truth document. Every artifact must trace back to a real source. This is the audit trail your governance team will ask for the first time a compliance officer wants to know where a number came from.

artifact_type is the machine-readable shape: 'company_fact_sheet', 'risk_summary', 'segment_revenue'. The planner uses it to prune large tables fast.

persona_tag is which agent persona this row was compiled for. The same source document might need three persona shapes, which gives you three rows, each filterable by tag. This is the column that lets you scale to many agents without polluting any one of them with another’s data.

payload is the JSONB column. Everything typed and queryable about the artifact lives here: the exact numbers, the cited entity names, the field-level provenance, the synthesized prose. This column carries the weight.

payload_embedding is a vector(1536) for semantic similarity. 1,536 dimensions is a common default for current OpenAI embedding models; adjust to match whichever embedding model you actually use.

payload_tsv is a tsvector for Postgres full-text search, populated from a synthesized prose version of the payload. This powers BM25-style keyword retrieval when the structured filters cannot catch the question.

eval_set_version records which eval set compiled the artifact ('eval-2026-05-10-v3'). When you bump the eval set, the artifact becomes a candidate for recompile.

compiled_at is a timestamp. Useful for time-windowed cache invalidation and for showing the user how fresh the answer is.

source_version_hash is the SHA-256 of the source document the compiler read. When the underlying source changes, the hashes diverge, and that mismatch is the signal that drives delta updates. This is the column that makes Pattern 3 below possible.

schema_version is an integer the compiler bumps when the JSONB shape changes. Old rows recompile lazily on the next pass. You will be glad you have this field the first time you decide to add a new sub-field to the payload.

The three indexes correspond to the three query surfaces. The JSONB index uses jsonb_path_ops because the dominant query is containment, not key-exists; the next section explains why. The HNSW vector index uses cosine distance because that is the right default for normalized embeddings from OpenAI or Cohere. The tsvector index is the standard GIN choice.

KEY INSIGHT: If you don’t put source_version_hash and eval_set_version in the schema on day one, you will rebuild the table on day ninety. Every column on this list is the one you wish you had added before you needed it.

Figure 2 - Eleven Columns and Three Indexes: The complete schema fits on one diagram. Every column maps to a specific operational concern: governance (source_id, source_version_hash), persona routing (persona_tag, artifact_type), the typed payload (payload), the three query surfaces (payload, payload_tsv, payload_embedding), reproducibility (eval_set_version, schema_version), and freshness (compiled_at). The three GIN and HNSW indexes serve the three query surfaces.

The query surface, part one: JSONB#

JSONB is the part of Postgres most teams underuse. The official docs page ships fourteen operators on the JSONB type plus five jsonb_path_* functions. Twelve of the operators shipped in PostgreSQL 9.4 (December 2014); the last two (@? and @@) and the five jsonb_path_* functions arrived in PostgreSQL 12 (October 2019). All of them apply to current PostgreSQL (versions 14 through 18). You will use most of those fourteen to filter the artifacts table at query time.

The fourteen operators:

The five jsonb_path_* functions (added in Postgres 12) give you SQL/JSON path expressions like $.officers[*] ? (@.role == "CFO") for predicates the operator surface cannot express in one shot. Useful for nested-array filters inside a payload.

For artifact filtering, the operators that earn most of the rent are @> (containment), ->> (extract as text), and the two key-exists operators when you need them. A realistic query:

1
SELECT id, payload, compiled_at
2
FROM artifacts
3
WHERE persona_tag = 'financial_analyst'
4
  AND artifact_type = 'segment_revenue'
5
  AND payload @> '{"ticker":"AAPL","fiscal_year":2024}'::jsonb
6
  AND payload_tsv @@ plainto_tsquery('english', 'services revenue')
7
ORDER BY compiled_at DESC
8
LIMIT 1;

That single statement uses the JSONB GIN index for the containment predicate, the tsvector GIN index for full-text, and the equality predicates on persona_tag and artifact_type to prune the table first. No vector search yet; we have not needed it. The agent sees one row come back.

Figure 3 - All Fourteen JSONB Operators: The complete reference. Containment (@>, <@), key existence (?, ?|, ?&), field extraction (->, ->>, #>, #>>), mutation (||, -, #-), and the jsonpath pair (@?, @@) added in PostgreSQL 12. Almost every artifact-table query you will write is a combination of containment and one or two field extractions. The mutation operators are for the compile-time code path, not query time.

The query surface, part two: pgvector#

pgvector is the extension that gives Postgres a vector type and the index machinery to query vectors at scale. The current release is v0.8.2, and it compiles against PostgreSQL 13 and newer. You install it once per database with CREATE EXTENSION vector, and from that point on the vector type behaves like any other Postgres type.

Six distance operators ship with v0.8.2:

Two index types: HNSW and IVFFlat. HNSW is the production read default because it gives better recall and query speed. It builds slower and uses more memory than IVFFlat. IVFFlat is the right choice when memory is the constraint or when HNSW build time becomes impractical. It also needs training data to build, so it is less convenient on a cold table.

The dimension limits matter and they get misremembered. On the standard vector type, HNSW indexes a maximum of 2,000 dimensions. On halfvec, the limit is 4,000. On bit, the limit is 64,000. OpenAI’s text-embedding-3-small at 1,536 dimensions sits comfortably inside the vector ceiling. The limit only bites if you reach for an unusual high-dimensional model.

The HNSW index for the artifacts table is the one CREATE INDEX line in the schema:

1
CREATE INDEX artifacts_payload_hnsw
2
  ON artifacts USING HNSW (payload_embedding vector_cosine_ops);

vector_cosine_ops is the opclass that pairs with the <=> operator. If you switch to L2 or inner product later, swap to vector_l2_ops or vector_ip_ops and rebuild the index. Cosine is the right default for the embeddings most teams will use.

A query that combines the JSONB filter with vector similarity:

1
SELECT id, payload,
2
       payload_embedding <=> $1 AS distance
3
FROM artifacts
4
WHERE persona_tag = 'financial_analyst'
5
  AND artifact_type = 'risk_summary'
6
  AND payload @> '{"fiscal_year":2024}'::jsonb
7
ORDER BY payload_embedding <=> $1
8
LIMIT 5;

The structured filters prune the candidate set first. The HNSW index re-orders the survivors by semantic similarity. The agent receives five candidates, each carrying its full structured payload, and picks the one whose JSONB fields match the question precisely. This is hybrid search on a single row.

Figure 5 - pgvector v0.8.2 At A Glance: Six distance operators, two index types, three dimension ceilings. Default to HNSW with cosine distance for production reads. Use IVFFlat when memory is tight or build time matters more than recall. Use halfvec if you need more than 2,000 dimensions and can accept half-precision. Use bit if you’re running binary-quantized embeddings and need extreme density. The verified facts come from the pgvector v0.8.2 README.

The compile workflow#

The schema is half the system. The other half is the code that populates it. Pinecone calls this the Context Compiler. Karpathy’s wiki calls it the ingest workflow. Same three primitives in both.

First, the eval set. A JSONL file of representative tasks with known right answers. Pinecone’s KRAFTBench example used 150 hard questions across nine sectors and ten financial topics on the S&P 500 10-K filings, split into three difficulty categories [7],[8]. Your eval set will be smaller and more specific to your domain, but the shape is the same: questions, ground-truth answers, optional tags that label which artifact type the answer should come from.

Second, the skills library. A library of small functions that the compiler composes to build artifacts. Chunk a PDF. Extract entities. Pull metrics from a table. Run a structured-extraction prompt. Generate an embedding. Compute a tsvector. In the DIY pattern, the skills library is roughly ten Python functions in a skills/ directory. Pinecone does not publish the system prompt of its Context Compiler or the exact skills in its library; the DIY pattern below reproduces the workflow structurally, not byte-for-byte.

Third, the compile loop. A script that walks the source documents, calls the skills to produce artifacts, writes them to the table, runs the eval set, and either accepts the result or iterates on prompts and skills until the evals pass. In Pinecone Nexus this is an autonomous coding agent. In the DIY pattern, you can run it as a Python script under your normal Anthropic SDK harness.

The directory structure looks like this:

1
compile/
2
  eval_set.jsonl          # 50-200 questions with ground-truth answers
3
  skills/
4
    chunk_pdf.py
5
    extract_entities.py
6
    extract_metrics.py
7
    synthesize_summary.py
8
    embed_payload.py
9
    build_tsv.py
10
    persona_route.py
11
    hash_source.py
12
    write_artifact.py
13
    run_evals.py
14
  compile_loop.py         # the agent that calls the skills and writes rows

A minimal compile_loop.py lands at around 150-200 lines. It walks through sources, picks the right skills for each source type, builds the JSONB payload, computes the embedding and tsvector, writes the artifact row inside a transaction, and tags it with the current eval_set_version. The agent edits prompts and recombines skills as it learns from eval failures. No magic. No proprietary algorithm. One autonomous loop you fully control.

Figure 6 - The Eval-Driven Compile Loop: Three primitives. The eval set defines what good looks like. The skills library is the building blocks the compiler composes. The compile loop is the agent that runs the build, runs the evals, learns from failures, and iterates. Pinecone Nexus runs this loop on their infrastructure with an opaque skill library. The DIY pattern reproduces the workflow structurally, with a transparent skills library you maintain yourself.

KEY INSIGHT: The compile workflow is the part most teams will get wrong the first time. The eval set is not optional, the eval set is not “we’ll add it later,” the eval set is the entire reason the compiler can be autonomous. Build the eval set first.

The delta-update pattern#

Sources change. A 10-K gets amended. A product spec gets revised. A customer record gets updated. Artifacts that depend on a stale source are now wrong, and an agent reading from them is serving stale answers with high confidence. The artifact table needs to know when to recompile.

Three patterns handle this, in order of operational maturity.

1
CREATE TABLE recompile_queue (
2
  id          bigserial PRIMARY KEY,
3
  source_id   uuid NOT NULL,
4
  enqueued_at timestamptz DEFAULT now(),
5
  attempts    smallint DEFAULT 0,
6
  locked_at   timestamptz
7
);
8

9
CREATE OR REPLACE FUNCTION enqueue_recompile() RETURNS trigger AS $$
10
BEGIN
11
  IF OLD.content_hash IS DISTINCT FROM NEW.content_hash THEN
12
    INSERT INTO recompile_queue (source_id) VALUES (NEW.id);
13
  END IF;
14
  RETURN NEW;
15
END $$ LANGUAGE plpgsql;
16

17
CREATE TRIGGER sources_after_update
18
  AFTER UPDATE ON sources
19
  FOR EACH ROW
20
  EXECUTE FUNCTION enqueue_recompile();

1
WITH next_batch AS (
2
  SELECT id, source_id
3
  FROM recompile_queue
4
  WHERE locked_at IS NULL
5
  ORDER BY enqueued_at
6
  FOR UPDATE SKIP LOCKED
7
  LIMIT 10
8
)
9
UPDATE recompile_queue q
10
   SET locked_at = now(), attempts = q.attempts + 1
11
  FROM next_batch
12
 WHERE q.id = next_batch.id
13
RETURNING q.id, q.source_id;

1
INSERT INTO recompile_queue (source_id)
2
SELECT s.id
3
FROM sources s
4
JOIN artifacts a ON a.source_id = s.id
5
WHERE a.source_version_hash IS DISTINCT FROM s.content_hash
6
GROUP BY s.id;

Build versus buy#

The honest version of the build-versus-buy conversation goes like this.

Buy Pinecone Nexus when you have a multi-thousand-question enterprise eval set, no internal Postgres expertise to call on, a procurement process that prefers a vendor contract over an internal build, and you are comfortable with an Early Access product. As of May 2026, Pinecone Nexus is in early access, and Pinecone does not publish the Context Compiler’s system prompt or the skills it composes. None of that is a slight. It is the honest description of where the product is in its lifecycle, and the price you pay for someone else operating the autonomous compile loop on infrastructure you do not have to staff.

Buy a Microsoft Fabric IQ Ontology engagement when you are already deep in the Microsoft Fabric stack, your governance team wants a vendor-supported semantic layer over existing data sources rather than a synthesis layer, and you are comfortable with a public-preview product. Microsoft Fabric IQ Ontology is in public preview as of May 2026, and the MCP-server surface for Fabric IQ ontologies is also in preview. Different bet from Pinecone (a view on the source rather than a synthesized artifact), similar maturity stage.

Build the Postgres pattern yourself when you have an existing Postgres footprint, one engineer who knows JSONB and is willing to learn pgvector, an eval set in the hundreds rather than the thousands, and a preference for transparent code you can read in a pull request. This is most teams. The pattern in this article is around 200 lines of Python, eleven columns of schema, three indexes, and three delta-update patterns. No vendor product. No licensing cost. No procurement cycle.

Figure 8 - Three Options, One Decision: Pinecone Nexus is in early access with an opaque skill library and the operational platform run on Pinecone’s infrastructure. Microsoft Fabric IQ Ontology is in public preview with a different bet (semantic view, not synthesized artifact). The DIY Postgres pattern uses the database you already run, an eval set you maintain, and a skills library you can read in a pull request. The right answer depends on whether your team already has Postgres expertise and whether you can write your own eval set.

KEY INSIGHT: The hard part of compiled knowledge is not the storage layer. The hard part is the eval set and the compile loop. Buying Nexus does not skip those steps, it just rents you someone else’s operational platform for them. If you can write a JSONL eval set, you can build the rest yourself.

The Servoy bridge#

If your team is running a Servoy stack, this whole pattern lands almost without modification on the Postgres database Servoy already ships with. Servoy 2025.12 and later runs PostgreSQL 17.6 with pgvector enabled by default; the JSONB and tsvector types are already there. The artifact table can live alongside your normal application tables, and a Servoy headless client can take the role of the recompile worker.

The Servoy-side integration of this pattern is the subject of an upcoming Tier 6 PostgreSQL series, starting with tutorial #30 (“FOR UPDATE SKIP LOCKED”) and tutorial #31 (“pgvector for Servoy”). Both tutorials are in draft and have not shipped yet; we’ll link them here when they do. The pattern in this article does not need a separate database, a separate worker runtime, or a separate ops process for a Servoy shop. It runs on the database you already have.

That is the pattern. The next time someone on your team asks whether to buy Pinecone Nexus, you have the alternative on the table in concrete terms. One artifacts table, three indexes, fourteen JSONB operators, six pgvector distance operators, a tsvector column for full-text, and three delta-update patterns. Put it in Postgres.

The Series#

This is Part 2 of the three-part Compiled Knowledge sub-series:

1. From Agentic RAG to Compiled Knowledge: Why Karpathy’s Wiki Idea Is Spreading — the architectural case for moving from query-time retrieval loops to compile-time synthesis, and the four-team convergence in spring 2026
2. Build Your Own Compiled Knowledge Engine in Postgres (this article) — the practical walkthrough: one artifacts table, three indexes, JSONB plus pgvector plus tsvector, no vendor product required
3. Memory and Dreaming: How Anthropic Just Shipped the Karpathy Wiki Pattern — the production primitive in Anthropic’s Managed Agents API and what it means for teams already running on the platform

References#

[1] PostgreSQL Global Development Group, “JSON Functions and Operators,” PostgreSQL Documentation, 2026. https://www.postgresql.org/docs/current/functions-json.html

[2] PostgreSQL Global Development Group, “JSON Types,” PostgreSQL Documentation, 2026. https://www.postgresql.org/docs/current/datatype-json.html

[3] PostgreSQL Global Development Group, “NOTIFY,” PostgreSQL Documentation, 2026. https://www.postgresql.org/docs/current/sql-notify.html

[4] PostgreSQL Global Development Group, “CREATE TRIGGER,” PostgreSQL Documentation, 2026. https://www.postgresql.org/docs/current/sql-createtrigger.html

[5] A. Kane, “pgvector: Open-source vector similarity search for Postgres,” GitHub Repository, 2026. https://github.com/pgvector/pgvector

[6] Citus Data, “pg_cron: Run periodic jobs in PostgreSQL,” GitHub Repository, 2026. https://github.com/citusdata/pg_cron

[7] Pinecone, “Knowledge infrastructure for agents,” Pinecone Engineering Blog, May 2026. https://www.pinecone.io/learn/knowledge-infrastructure-for-agents/

[8] Pinecone, “Better models won’t save your agent,” Pinecone Engineering Blog, May 2026. https://www.pinecone.io/learn/better-models-wont-save-your-agent/

[9] A. Karpathy, “LLM Wiki,” GitHub Gist, Apr 2026. https://gist.github.com/karpathy/

[10] Microsoft, “What is Fabric IQ?”, Microsoft Fabric Documentation, 2026. https://learn.microsoft.com/en-us/fabric/iq/overview

[11] Google Cloud, “Knowledge Catalog (formerly Dataplex),” Google Cloud Documentation, 2026. https://cloud.google.com/products/knowledge-catalog