How do you combine SQL, JSON, vector search, and metadata for AI memory? Put each capability where the memory workflow can query and govern it together. The pieces belong in one architecture when an agent must retrieve semantically related context, filter it by live business state, preserve flexible memory attributes, and enforce the same access policy as the source data — and by 2026 the interesting question is no longer “where do the vectors live?” but whether all of that composes in one query path.
Answer box — the short version
SQL keeps operational facts, joins, and business predicates queryable.
JSON metadata carries flexible facts such as source, scope, category, and retention attributes without forcing a new schema for every memory type.
Vector search retrieves semantically related context; hybrid retrieval combines that context with exact keyword signals.
Governed access applies the same policy to retrieval that applies to the underlying enterprise data.
Why convergence matters: these capabilities share the five guarantees defined in the series anchor: one transaction boundary, one optimizer, one consistency model, one governance domain, and shared access surfaces.
How this was tested
The SQL examples come from the Module 03 lab, validated by GitHub Actions against Oracle AI Database 26ai Free. The long-conversation agent-memory notebook provides the complementary JSON-metadata and layered-memory example.
The architecture question for AI memory
AI memory is more than an embedding column. A useful memory record usually has relational ownership and lifecycle data, flexible JSON attributes, text that must be searched exactly, an embedding that must be searched semantically, and a policy that decides who can retrieve it. The engineering question is whether those operations compose in the same query path.
The series anchor defines a converged database by the guarantees that span data models — not by how many models a product can store. Its companion, Converged Database vs. Multi-Model Database, makes the same distinction directly: multi-model is a storage claim; converged is a guarantees claim. This article applies that framework to AI memory, where SQL, JSON metadata, vector retrieval, text search, and access policy must operate together — and it tests four of the anchor’s guarantees in the retrieval path: one optimizer for vector ranking and joins, one consistency model for read-after-write, one governance domain for tenant filtering, and shared SQL and search access over the same memory data.
The category cooled because the capability spread
In Pavlo’s Databases in 2025 (January 2026), he is blunt: “The buzz around vector databases has muted, and VCs are only writing checks for LLM companies,” and “If 2023 was the year every DBMS added a vector index, then 2025 was the year that every DBMS added support for Anthropic’s Model Context Protocol (MCP).” Vector search became a commodity index — present everywhere, decisive nowhere. By late 2025 the commoditization had reached the storage layer itself: Amazon S3 Vectors — vector indexes native to an object store, at up to two billion vectors per index — went GA in December. And in June 2026 it reached the hyperscale database tier, when Google repositioned Spanner around exactly this argument — the database as a context engine for agents, with vector search it insists is no bolted-on afterthought — a development this article returns to at length below.
The acquisitions tell the same story. The two large data deals of the period were for PostgreSQL companies: Databricks paid roughly $1B for Neon, and Snowflake roughly $250M for Crunchy Data as reported (Snowflake’s own 10-Q books the purchase consideration at $164.5M in cash, closed June 6, 2025) — both framed by observers, and by Pavlo, as Postgres and OLTP plays, not vector-pure-play purchases. Capital flowed toward general-purpose engines that also do vectors.
The academic statement of the same point is Stonebraker and Pavlo’s 2024 survey, which judged vector databases “essentially document-oriented DBMSs with specialized ANN indexes” — those indexes being “a feature, not the foundation of a new system architecture.” And the open-source vector community makes the converged argument in its own words: the pgvector project’s stated rationale is to “store your vectors with the rest of your data,” with “ACID compliance, point-in-time recovery, and JOINs.”
One point of fact requires care, because it is easy to overstate. As of July 2026, Pinecone — the best-funded specialized vector vendor — has not been sold. The Information reported in August 2025 that the company was “exploring a potential sale” and had lost a prominent customer (Notion); its incoming CEO, Ash Ashutosh, subsequently said publicly that a sale was “definitely not on the table right now”; and the 2026 record reads like independent operation, not wind-down: Dedicated Read Nodes went from December-2025 preview to GA in April, and a May launch week introduced native full-text search (preview) and Nexus, a “knowledge engine” in public preview since July 1. The accurate statement is that a sale was rumored and unconfirmed — and the direction of the product pivot is worth noticing: the best-funded vector-database company now markets “knowledge infrastructure,” a layer of context compilation and provenance above similarity search. That similarity alone is not the product is this article’s thesis, in a specialized vendor’s own roadmap.
What dedicated vector stores genuinely do well
This article concedes more than its siblings, and does so deliberately: the thesis is “capability versus architecture,” not “specialized stores are bad.” There are workloads where a dedicated store is the right tool.
- Billion-scale pure similarity serving. Milvus/Zilliz — among the most widely adopted open-source vector engines, Kubernetes-native in its distributed deployment, with multiple ANN index families (IVF, HNSW, DiskANN, GPU-accelerated) — is built for web-scale similarity, scaling to tens of billions of vectors. A single OLTP engine is not trying to win that workload. (Notably, Milvus 3.0 — launched July 16, 2026 — repositions the engine as a lake-native “vector lakebase” querying Iceberg and Lance tables in place: a leading specialized store converging toward the data lake, though still with no SQL joins or shared transactions against operational tables.)
- Fused hybrid ranking as a product. Weaviate ships native dense-plus-keyword fusion with tuned ranking out of the box (relative-score fusion has been its default since v1.24); for teams whose product is best-in-class hybrid retrieval, that specialization is real value. Milvus, too, has carried native BM25 hybrid since 2.5 — fused hybrid is now table stakes among the specialized stores.
- Managed similarity serving at extreme QPS. Pinecone’s serverless architecture and Dedicated Read Nodes — GA since April 2026 — target operational similarity serving as a managed product.
- A specialized-style optimization Oracle also has, stated fairly. Oracle’s own documentation reports that BINARY vectors (one bit per dimension) cut storage roughly 32× and distance computation up to 40× — attributed here as an Oracle-documented claim, not an independent measurement.
The honest decision rule (returned to at the end): if the workload is billions of vectors and pure similarity serving, reach for a specialized store. If retrieval must compose with operational data, governance, and freshness — which describes most enterprise RAG and essentially all agentic systems — the integration is the architecture.
Why AI memory needs more than similarity
Similarity is necessary, not sufficient — and the production record says so plainly. Anthropic’s Contextual Retrieval work (September 2024) reports a chain of gains over embeddings alone: contextual embeddings cut the top-20-chunk retrieval failure rate by 35%; adding contextual BM25 — lexical search — cut it by 49%; adding a reranking step cut it by 67% (5.7% → 1.9%), in Anthropic’s own evaluation. (Those figures are specific to their contextual-retrieval method and their dataset; the structural point — that you want lexical and semantic and reranking, not similarity alone — generalizes; the numbers do not.) Lexical search earns its place because it catches exact identifiers, codes, and acronyms that dense embeddings blur.
Agentic retrieval pushes further still: it decomposes a query into parallel structured and unstructured subqueries and routes each appropriately — retrieval as multi-source orchestration, not a single ANN call. And the framework ecosystem codifies “beyond similarity” directly: LlamaIndex notes that most existing vector stores “do not offer a SQL-like interface; hence they are less suited for queries that involve aggregations, joins, sums, etc.” — its recommended pattern combines text-to-SQL with semantic search.
Five requirements follow, and the rest of this article tests each one — every claim either a runnable assertion in the companion Module 03 lab or a direct vendor-documentation link.
| Requirement | Module-03 proof | The specialized-store cost (vendor-documented) |
|---|---|---|
| Metadata filtering + joins to operational data | 01-filtered-joined-ann.sql |
pgvector: post-index filtering, recall loss; Pinecone: flat-metadata only, documented limits; MongoDB: filter only on indexed fields, “typically slower” |
| Freshness (read-after-write) | 02-vector-read-after-write.sql |
Pinecone: eventually consistent; poll for visibility; MongoDB: no read-after-write guarantee |
| Permission-aware retrieval | 03-permission-aware-retrieval.sql |
Pinecone’s own guide: the store does not enforce permissions; integrate an external authorizer |
| Hybrid keyword + semantic | 04-hybrid-search.sql |
hand-rolled fusion across two systems; or a separate hybrid store with no operational join |
First, though, the embeddings themselves are generated inside the database. Module 03 bakes Oracle’s prebuilt all-MiniLM-L12-v2 ONNX model into the container image and loads it into the engine:
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_MODELS',
file_name => 'all_MiniLM_L12_v2.onnx',
model_name => 'MINILM_L12',
metadata => JSON('{"function":"embedding","embeddingOutput":"embedding","input":{"input":["DATA"]}}'));
All 10,000 support-ticket bodies are then embedded into a real 384-dimensional column by the engine itself — no external embedding service, no copy pipeline:
UPDATE support_tickets SET body_vec = VECTOR_EMBEDDING(MINILM_L12 USING body AS data);
Every proof below runs against those real embeddings.
Oracle AI Database SQL JSON Vector Search for AI Memory in a layered design
AI memory needs a stable core and flexible context. A support decision, user preference, or source document can have relational ownership and timestamps that belong in typed columns, while its evolving attributes — such as source, category, scope, retention, or extraction confidence — fit naturally in JSON metadata. Retrieval then combines those fields with a vector representation of the memory text.
The companion long-conversation agent-memory notebook is the runnable JSON proof for this article. It creates separate tables for conversation turns, structured memory, episodic memory, and retrieval-ready chunks; stores flexible metadata as JSON; and assembles context from the appropriate memory layers. Module 03 supplies the complementary architecture proof: the query can join operational data, apply tenant policy, and rank vector results without moving the memory record into a separate retrieval store.
That split is deliberate. JSON metadata is not a substitute for relational predicates, and vector similarity is not a substitute for either. For AI memory, use SQL when the question is about live business state or a join, JSON when the memory schema must evolve, and vector search when the system needs semantic recall.
Filter and join in one statement
Production retrieval is rarely “find similar documents.” It is “find similar documents for premium customers with a recent shipped order.” Module 03 expresses exactly that as one statement — a natural-language probe embedded in-database, ranked by cosine distance, composed with relational predicates and a join to operational tables:
SELECT t.ticket_id,
c.full_name /* non-key column keeps CUSTOMERS in the plan (defeats join
elimination) so the relational join is visibly costed */,
o.order_id,
VECTOR_DISTANCE(t.body_vec,
VECTOR_EMBEDDING(MINILM_L12 USING 'unable to log in after reset' AS data),
COSINE) AS dist
FROM support_tickets t
JOIN customers c ON c.customer_id = t.customer_id
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.segment IN ('premium','vip')
AND o.status = 'shipped'
AND o.order_ts >= TIMESTAMP '2026-03-01 00:00:00'
ORDER BY dist
FETCH APPROX FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90;
The EXPLAIN PLAN, with its predicate list (DBMS_XPLAN … 'BASIC +PREDICATE', captured from the lab container; the plan hash and the system-generated VECTOR$…$ suffixes vary per build):
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
|* 3 | SORT ORDER BY STOPKEY | | ← final top-10, filtered rows only
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL | ORDERS | ← status/date filter, in the predicate list
|* 6 | HASH JOIN | |
|* 7 | TABLE ACCESS FULL | CUSTOMERS | ← segment filter, in the predicate list
| 8 | NESTED LOOPS | |
| 9 | VIEW | VW_IVENJ_7E289AC7 |
|* 10 | HASH JOIN | |
| 11 | PART JOIN FILTER CREATE | :BF0000 |
| 12 | VIEW | VW_IVCR_B5B87E67 |
|* 13 | COUNT STOPKEY | |
| 14 | VIEW | VW_IVCN_9A1D2119 |
|* 15 | SORT ORDER BY STOPKEY | |
| 16 | TABLE ACCESS FULL | VECTOR$TICKETS_BODYVEC_IVF$…$IVF_FLAT_CENTROIDS | ← the vector index: top centroids
| 17 | PARTITION LIST JOIN-FILTER| |
| 18 | TABLE ACCESS FULL | VECTOR$TICKETS_BODYVEC_IVF$…$IVF_FLAT_CENTROID_PARTITIONS | ← candidate vectors
| 19 | TABLE ACCESS BY USER ROWID | SUPPORT_TICKETS | ← candidates fetched by rowid
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
5 - filter("O"."STATUS"='shipped' AND "O"."ORDER_TS">=TIMESTAMP' 2026-03-01 00:00:00.000000000')
6 - access("C"."CUSTOMER_ID"="T"."CUSTOMER_ID")
7 - filter("C"."SEGMENT"='premium' OR "C"."SEGMENT"='vip')
10 - access("VW_IVCR_B5B87E67"."CENTROID_ID"="VTIX_CNPART"."CENTROID_ID")
13 - filter(ROWNUM<=14)
15 - filter(ROWNUM<=14)
One plan tree — and the vector index is driving it. Read it bottom-up. The IVF index’s own row sources are in the plan (VECTOR$TICKETS_BODYVEC_IVF$…): the optimizer probes the index’s top centroids (ids 12–16; the ROWNUM<=14 stopkey is the accuracy-driven probe count), scans the matching centroid partitions for similarity candidates (17–18), and fetches each candidate’s base row by rowid (19). Then the predicate list — the section the bare BASIC format omits — proves where the filters run: the segment predicate on the CUSTOMERS scan (id 7), the status-and-date predicate on the ORDERS scan (id 5), both below the final top-10 SORT ORDER BY STOPKEY (id 3). The filters apply to the candidate pool before the ranking, so the top-10 is computed over filtered, joined rows. A selective predicate here shrinks the pool the ranking sees — it is never applied after the top-10 is chosen, which is exactly the failure mode pgvector documents for post-index filtering. The module asserts all of it: the VECTOR$…IVF row sources present, each relational predicate attached to its scan, both filter steps below the ranking stopkey, and all three domain tables in one plan tree.
And the choice is honestly cost-based, not hinted. With only 300 tickets in the table the same query full-scans — exact cosine over a few hundred rows is cheaper than probing centroid partitions; on the lab’s 2-core container the crossover to the index arrives by roughly 3,000 rows, and module 03 seeds 10,000 so the index-driven plan has comfortable margin. Oracle documents both pre-filter and post-filter IVF plans; the optimizer prices both and can pick a different plan for a different query. This module proves composition — filter, join, index-driven similarity, and predicate placement in this tested plan — not a universal plan shape or throughput benchmark.
The contrast is the documented filter-versus-ANN tension elsewhere. pgvector applies filters after the index scan, so a selective predicate can starve the result — the README’s own example: with an HNSW index and the default ef_search of 40, a condition matching 10% of rows leaves “only 4 rows … on average,” with iterative_scan and partial indexing offered as manual mitigations. (That cost is pgvector-specific rather than Postgres-architectural — extensions such as VectorChord and pgvectorscale now offer true pre-filtering.) Pinecone’s metadata filtering is real and useful but constrained — flat metadata only, with documented limits on size and structure (“Nested JSON objects are not supported”; 40 KB per record), and filtering applied around the ANN search rather than joined to operational tables. MongoDB’s $vectorSearch stage pre-filters only on fields specifically indexed as filters and its documentation notes that “filtered queries are typically slower”. And none of these stores joins to the orders table at all — that remains an application-side fetch against a second system. (One distributed engine that can make this composition — Google Spanner — is not a vector store at all; it is a SQL engine that added the index.)
Freshness: read-after-write
In a converged engine, a committed change to an embedding is visible to the next query — there is no separate index to fall behind. Module 03 captures a ticket’s distance to a probe, overwrites its embedding, commits, and re-measures:
UPDATE support_tickets
SET body_vec = VECTOR_EMBEDDING(MINILM_L12 USING 'tropical fish aquarium maintenance schedule' AS data)
WHERE ticket_id = 1;
COMMIT;
/* the very next query sees the new vector immediately — no reindex/sleep */
SELECT VECTOR_DISTANCE(body_vec, v_qv, COSINE) INTO v_after_d
FROM support_tickets WHERE ticket_id = 1;
The result, captured from the lab against the live model (the probe — “cannot access my account login error” — is a paraphrase, not a copy, of the ticket’s wording):
cosine distance rank (of 10,000)
BEFORE 0.4820 140
AFTER 1.0621 9,989
RESTORED 0.4820 140
The same query that ranked the ticket 140th of ten thousand drops it to 9,989th the instant the unrelated embedding commits — and the very next query sees it, with no reindex, no polling, and no sleep. Re-embedding the unchanged body restores the distance exactly, to the last decimal. The module asserts the shift and the exact restoration. For approximate search specifically, Oracle documents that HNSW vector-index queries retrieve “transactionally consistent top-K results,” maintained through a per-transaction private journal and SCN-based incremental snapshots — freshness extends to the index, not just the base table.
The documented contrast, as of July 2026: Pinecone is eventually consistent — “there can be a slight delay before new or changed records are visible to queries” — and provides a freshness-check mechanism (an LSN the application polls) precisely because read-after-write is not guaranteed. MongoDB Vector Search, served by the separate mongot process, likewise provides no read-after-write guarantee — its documentation states search “supports eventual consistency and does not provide any stronger consistency guarantees”. Google Spanner draws a subtler line, and it is worth naming precisely: committed vectors are immediately visible to both exact and approximate queries — Google’s own framing is that “the results are always fresh” — but the ScaNN vector index’s tree structure “is optimized for the dataset at the time of creation, and is static thereafter,” so as the data drifts, ANN recall — not visibility — decays until the index is rebuilt. Staleness you can poll for; recall drift arrives silently, which for a retrieval pipeline may be the more dangerous of the two. For an agent that acts on what it retrieves, that staleness window is decision risk, not a tuning detail — the State Vector Dissonance argument developed in the anchor.
Governance: the same query, different rows per tenant
This is the cleanest expression of the whole series’ “one governance domain” guarantee, and the place the architectural difference is starkest. The requirement is documented as a named production problem: permission-aware RAG, where most systems “implicitly assume all retrieved content is equally accessible to any user,” which fails in multi-tenant and role-scoped deployments. The top-k must be access-filtered before it reaches the model. Oracle Virtual Private Database (VPD) is the database control used in this module; hybrid vector indexes and their generated views must be protected consistently when VPD is applied.
The specialized-store position is visible in Pinecone’s own access-control guidance: enforcement lives outside the store. The recommended pattern integrates an external authorizer (the guide uses SpiceDB) to decide what a user may see, copies document identifiers into vector metadata, and carries the authorized-ID list into each query as a filter — a permission model the application must keep synchronized with the source system. That is a consistency boundary by construction: two systems that must agree about who can see what.
In a converged engine, the permission model is the engine. Module 03 attaches a Virtual Private Database policy — available in Oracle AI Database 26ai Free, per the licensing documentation — whose predicate folds into every read of the tickets table, including the vector query:
CREATE FUNCTION m03_tenant_pred(p_schema VARCHAR2, p_object VARCHAR2) RETURN VARCHAR2 IS
BEGIN /* the row-level predicate: a ticket is visible only if its customer's id
parity matches the current tenant (A=even, B=odd). Folded into every SELECT on
support_tickets, including the vector ORDER BY ... FETCH APPROX query. */
RETURN q'[customer_id IN (SELECT customer_id FROM customers
WHERE MOD(customer_id,2) =
CASE WHEN SYS_CONTEXT('M03_TENANT_CTX','TENANT')='A' THEN 0 ELSE 1 END)]';
END;
The module then runs the byte-for-byte identical approximate vector query as two tenants. The results, captured from the lab (probe: “refund for a damaged package”; tenant A is scoped to even-numbered customers, tenant B to odd):
--- Tenant A : identical query, top 10 (first 6) --- --- Tenant B : identical query, top 10 (first 6) ---
ticket customer parity dist ticket customer parity dist
6835 180 even 0.2631 1580 121 odd 0.2715
8285 156 even 0.2631 4292 151 odd 0.2763
4106 16 even 0.2635 4836 189 odd 0.2768
8226 100 even 0.2733 3853 17 odd 0.2775
2084 64 even 0.2800 3961 87 odd 0.2815
4058 80 even 0.2882 4407 11 odd 0.2822
Not one ticket appears in both lists, and every tenant-A row belongs to an even-numbered customer while every tenant-B row belongs to an odd one — the governance predicate, not chance, partitioned them. The application’s query never changed; the engine filtered the unauthorized rows out of the ranking before they could surface. (The module asserts both properties: the two sets are disjoint, and each tenant’s rows match its parity.) No second system, no metadata copy, no synchronization. (The proof toggles tenant identity through an application context within one session — an honest scripting convenience; the predicate enforcement is the engine’s.)
Hybrid keyword and semantic, in one index
Recall Anthropic’s finding that lexical and semantic search are complementary. A converged engine can fuse them in a single index. Module 03 builds Oracle’s native Hybrid Vector Index — one domain index unifying Oracle Text and a vector index over the same column — and queries it with a single call that carries both a semantic probe and a keyword constraint:
v_json := DBMS_HYBRID_VECTOR.SEARCH(
json('{ "hybrid_index_name" : "tickets_hvi",
"vector" : { "search_text" : "the box was crushed and broken in transit" },
"text" : { "contains" : "damaged" },
"return" : { "topN" : 10 } }'));
The model embeds the search_text and runs the semantic search; the contains clause runs the lexical search; one index returns a fused, score-weighted top-N. The result, captured from the lab:
rank ticket fused score vector_score text_score vector_rank text_rank
1 5467 77.40 84.54 6.00 6 1
2 7680 77.31 85.04 0.00 1 40
3 8636 76.99 84.69 0.00 2 40
4 9660 76.95 84.65 0.00 3 40
5 8487 76.94 84.63 0.00 4 40
6 3669 76.88 84.57 0.00 5 40
7 5425 76.85 83.93 6.00 10 1
8 4008 76.57 84.23 0.00 7 40
9 9433 76.50 83.55 6.00 24 1
10 911 76.48 83.53 6.00 25 1
Read the columns: every fused row carries both a vector score and a text score — proof that the single index ran both searches and combined them, with no application-side fusion and no score normalization across systems. And the mix is the argument. Four of the ten were nominated by both arms (text_score 6.00, text_rank 1); the other six came from the vector arm alone (text_score 0.00; text_rank 40 marks rows outside the text arm’s returned set). That is keyword saturation in miniature: hundreds of tickets in this corpus contain “damaged,” and lexical relevance alone cannot say which damaged-package tickets are about a box crushed in transit — the semantic score can, and the fused ranking keeps them. Fusion, not intersection: the lexical arm anchors precision, the semantic arm supplies recall, and one index returns one ranked list. The module asserts the robust properties: ten rows returned, both scores present on every one, and the fused set is not identical to the pure keyword-only top-N.
Packaged call versus plain SELECT. The call above uses the packaged API, DBMS_HYBRID_VECTOR.SEARCH — hand it a semantic probe, a keyword clause, and a top-N, and Oracle runs both searches against the one index and returns a ranked JSON array, performing the score fusion for you. That is a convenience layer, not a separate access path: the same hybrid index is a first-class SQL index. An ordinary SELECT with a CONTAINS predicate is served by it directly —
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | SUPPORT_TICKETS |
| 2 | DOMAIN INDEX | TICKETS_HVI |
— a single DOMAIN INDEX named TICKETS_HVI (the same plan shape, incidentally, that lets module 02’s keyword search work once the hybrid index takes over the body column). So you can let the package fuse for you, or write the keyword and vector clauses yourself in plain SQL — composing CONTAINS with ORDER BY VECTOR_DISTANCE — when you want direct control of the ranking and the freedom to join the result to operational tables. In 26ai the packaged call composes too: DBMS_HYBRID_VECTOR.SEARCH accepts a filter_by clause of ordinary relational predicates alongside the semantic probe and keyword constraint. Review the hybrid-vector-index restrictions and maintenance guidance before adopting the pattern: index status, synchronization, optimization, VPD protection, and model choices matter in production. (The vector and lexical components share one structure because they share the column: when the hybrid index is created, it takes over the body column’s text search, since Oracle’s one-domain-index-per-indextype rule means a standalone text index and the hybrid index’s text component cannot coexist on it — ORA-29879, reproduced in the lab.)
The specialized-stack equivalent of either path is to run two systems and fuse their scores in application code, because lexical and vector scores are not directly comparable; or to adopt a dedicated hybrid store, which fuses well but in a system with no join to operational data. The gap is narrowing from the other side, too — MongoDB shipped native rank-fusion aggregation stages in preview in 8.1/8.2, and Spanner documents the same fusion composed in plain SQL — which rather proves the direction: fusion is moving into the engine.
The scorecard
Capability against store class — every cell from the cited documentation, as of July 2026, to be re-verified at publication:
| Filter + join to operational data | Read-after-write | Permission-aware retrieval | Hybrid keyword + semantic | Billion-scale pure ANN | |
|---|---|---|---|---|---|
| Specialized managed (Pinecone) | flat metadata; no SQL joins | ✗ eventual; poll for visibility | ✗ external authorizer | sparse-dense in one index; native BM25 in preview | ✓ strong |
| Specialized OSS (Milvus, Weaviate) | filters; no SQL joins | tunable — Milvus session/strong opt-in, bounded-staleness default; Weaviate eventual | app-layer row scoping; in-engine RBAC + tenant isolation | ✓ native fusion (Weaviate; Milvus BM25 since 2.5) | ✓ strong |
| Extension (Postgres + pgvector) | joins ✓; ANN post-filter recall loss | ✓ | ✓ (Postgres RLS) | hand-rolled fusion | partitioned, manual |
| Distributed SQL (Google Spanner) | ✓ SQL joins; in-index leaf-level filtering; filtered indexes fixed at creation | ✓ visibility, exact and ANN; ANN recall decays until the static ScaNN tree is rebuilt | partial — roles + column FGAC; no row-level security; views for row scoping | ✓ in-SQL RRF over transactional FTS + ANN (hand-composed) | ✓ 10B+ ANN; partitioned KNN beyond |
| Converged (Oracle 26ai) | ✓ one statement, optimizer-chosen | ✓ transactional | ✓ VPD, query unchanged | ✓ one engine, native HVI | not the target — IVF/HNSW at OLTP scale |
The converged row’s last cell is the honest one: a converged OLTP engine is not where you serve ten billion isolated vectors — that column belongs to the specialized stores, and to Spanner. Everywhere retrieval must compose, the converged engine is.
Spanner: the second opinion that proves the point
If the thesis is that similarity search belongs inside the database that holds everything else, the strongest second opinion arrived on June 29, 2026, when Google published its Spanner “agentic era” positioning. The announcement’s own words could open this article: the database is “no longer a passive repository; it’s a critical context engine,” and Spanner’s vector search “isn’t a bolted-on afterthought” — it is ScaNN, “the exact same state-of-the-art indexing algorithm that powers Google Search and YouTube,” running over the same tables as SQL, graph, and full-text search. The flagship case study is the thesis in production: MakeMyTrip consolidated four specialized engines — MongoDB for documents, Neo4j for graph, Elasticsearch for text, and the dedicated vector store Qdrant — into a single Spanner instance, and reports operational complexity “reduced … by 75%”. A hyperscaler showcasing a customer’s retirement of a dedicated vector database into its multi-model engine is this article’s argument, made with someone else’s workload.
On the five requirements, Spanner scores far closer to the converged column than to the specialized ones. Filters and joins compose in SQL: predicates on columns stored in the vector index are evaluated at the index’s leaf level, a filtered vector index can be built for a fixed predicate, and ANN results join to operational tables in the same statement. Freshness holds up exactly as the read-after-write section noted: full-text indexes are “updated in the same transaction,” and vector visibility is on-commit for exact and ANN alike. Hybrid search is documented as reciprocal-rank fusion composed in plain SQL over transactional full-text search and ANN — fused server-side, no client-side score merging.
The seams that remain are exactly the ones this article has been testing. The ScaNN vector index is the static tree the freshness section described: ANN recall decays as the data drifts, and restoring it is a scheduled rebuild — create a new index, evaluate it, drop the old. That is the price of an index built for ten billion vectors, and at that scale it may be a fair price — but it stands against a converged engine whose IVF index reorganizes itself online and whose HNSW index maintains transactional consistency incrementally. Permission-aware retrieval stops at the column: Spanner’s fine-grained access control offers database roles with table- and column-level privileges but no row-level security, so per-tenant scoping means maintaining definer’s-rights views per audience rather than attaching one policy — the identical-query-different-tenants proof above has no Spanner equivalent. And the embeddings are generated by calling out: ML.PREDICT invokes remote Vertex AI model endpoints — Spanner doesn’t host the models — where module 03’s ONNX model executes inside the engine, beside the data it embeds. Spanner is the strongest evidence yet that the category question is settled in favor of the database. What remains open — the index that maintains itself, the policy that travels with the row, the model that runs where the data lives — is the guarantees question at the centre of this series.
Choosing a vector store as part of an AI memory architecture
A decision rule, not a verdict. Reach for a dedicated vector store when the workload is web-scale similarity serving with little relational context, or when fused-hybrid ranking is itself the product you are shipping. Evaluate the converged engine when AI memory retrieval must be filtered by live business predicates, joined to operational data, carry evolving JSON attributes, be governed by the same access controls as everything else, and stay fresh on commit — which is the shape of most enterprise RAG, and of essentially every agent that takes actions on what it retrieves. Between those poles now sits distributed SQL: when composed retrieval must also run at ten-billion-vector, globally distributed scale, Spanner delivers the composition — filters, joins, freshness on commit — at the cost of the guarantees named above: a periodically rebuilt index, view-based row scoping, remote models. The architecture question is not only “which database has the best ANN index.” Many general-purpose engines now support vector indexes. The question is what the memory query has to compose with.
Next step: Run the long-conversation agent-memory notebook, then adapt the SQL, JSON metadata, vector retrieval, and policy pattern to one production memory workflow.
Oracle AI Database documentation
- Oracle AI Vector Search User’s Guide
- Optimizer plans for vector indexes
- Oracle hybrid search API
- Hybrid vector index maintenance and restrictions
- Oracle VPD data-access controls
- In-database ONNX embedding quick start
FAQ
Do I need a dedicated vector database for AI memory? For web-scale, standalone similarity serving — billions of vectors, pure nearest-neighbor queries, no relational context — a specialized store remains a strong choice. For retrieval that must filter on business data, join to operational tables, enforce per-user permissions, and stay fresh on write, those requirements point toward an engine where vectors live with the rest of the data.
How do SQL, JSON, vector search, and metadata work together for AI memory? Keep business state and relationships in SQL, use JSON metadata for evolving memory attributes, and use vector search for semantic recall. Apply access policy before the retrieved context reaches the model, and use hybrid retrieval when exact terms matter alongside semantic relevance. The notebook linked above demonstrates the layered-memory pattern; module 03 demonstrates the relational, governance, freshness, and hybrid-search behaviors.
Can a relational or converged database do real vector search?
Yes. Oracle AI Database 26ai provides a native VECTOR type, VECTOR_DISTANCE with multiple metrics, IVF and HNSW indexes, in-database embedding generation via ONNX models, and a hybrid keyword-plus-vector index. Stonebraker and Pavlo characterize ANN as “a feature, not the foundation” — a capability general-purpose engines add, which by 2026 they broadly have.
Doesn’t Google Spanner’s vector search settle the question? It settles the category question — Google’s June 2026 “agentic era” positioning argues, as this article does, that retrieval belongs in the multi-model engine beside the data, and its flagship case study retired a dedicated vector store (Qdrant, along with MongoDB, Neo4j, and Elasticsearch) into Spanner. On capability, Spanner delivers SQL-composed filtered ANN at ten-billion-vector scale with transactionally consistent full-text search. Where it stops short of a converged engine is specific: a static ScaNN vector index that is periodically rebuilt as recall drifts, versus self-reorganizing IVF and incrementally maintained HNSW; no row-level security for permission-aware retrieval; and embedding generation through remote Vertex AI endpoints rather than models executing inside the engine.
What does RAG need besides similarity search? In practice: metadata filtering, joins to operational data, freshness (read-after-write), permission-aware retrieval, and hybrid keyword-plus-semantic search, often with reranking. Anthropic’s own results show lexical search and reranking materially reduce retrieval failures over embeddings alone.
When should I still choose a dedicated vector database? When similarity serving is the workload rather than one part of it — extreme scale, extreme query rates, or a product whose value is best-in-class hybrid ranking — and there is little need to compose retrieval with transactional data or in-engine governance.