PATTERN Cited by 1 source

Vector index inside storage engine¶

Problem¶

Vector similarity search is increasingly a first-class feature of production systems (semantic search, RAG, recommendation). The default integration shape is a specialised sidecar store — Pinecone, Weaviate, Milvus, Vectorize, S3 Vectors — that lives next to the relational database and exposes its own API.

Sidecars have three structural costs:

Dual-write consistency. Every mutation has to land in both stores; partial failures leak through.
Transactional semantics breakdown. The vector index is outside the host database's transaction; rollback + crash recovery don't extend to it.
Query composition friction. JOIN between relational data and vector search has to be expressed at the application layer — the optimiser doesn't see both sides.

Solution¶

Put the vector index inside the host database's storage engine, so the index is a first-class durable structure managed by the engine alongside B+tree rows. The index inherits:

MVCC / transactional semantics — mutations commit and rollback with the row change.
Crash recovery — the engine's WAL covers index pages.
Buffer-pool caching — hot index pages are cached alongside row pages.
Sharding — the engine's sharding layer shards the vector index too.
SQL query composition — the optimiser can plan JOIN / WHERE / subquery across relational and vector data inside a single query.

This collapses the sidecar's structural costs into one storage substrate.

Canonical wiki instance: PlanetScale vectors¶

PlanetScale's 2024-10-22 vectors public-beta announcement implements this pattern by integrating a transactional extension of SPFresh — the continuously-updatable descendant of SPANN — inside InnoDB:

"For our implementation, we have extended SPFresh by adding transactional support to all its operations and fully integrating it inside InnoDB, MySQL's default storage engine. … Since the indexes are fully managed and stored on-disk by InnoDB, they are always in-sync with the vector data in your tables, they survive process crashes with strong consistency guarantees, they do not need to be periodically rebuilt, and they scale all the way into terabytes, just like any other MySQL table. Together with Vitess, PlanetScale's sharding layer, this allows the construction and efficient querying of huge vector indexes that are fully integrated with all the relational data in your database and can be used with JOINs and WHERE clauses while the underlying vector data is continuously updated."

—

Structural prerequisites for a hosted ANN algorithm¶

Not every ANN algorithm fits inside a storage engine. The algorithm needs:

SSD-residency / page-oriented layout — so it maps to the engine's page cache ( buffer pool) rather than assuming full-RAM residency (disqualifies HNSW).
Continuously-updatable — so mutations can ride the commit path instead of requiring periodic offline rebuilds (disqualifies stock HNSW and SPANN).
Transactional semantics — or at least a maintenance model that can be retrofitted with transactional semantics without structural redesign (disqualifies DiskANN's incremental-update model per PlanetScale).

SPANN (SSD-resident, posting-list-structured) + SPFresh (continuously-updatable via background maintenance) is the composition that satisfies all three, which is why PlanetScale chose it.

Trade-offs¶

Implementation effort is substantial. PlanetScale's transactional extension of SPFresh and InnoDB integration is a multi-year engineering programme — the beta announcement flags continued performance work up to GA.
The host engine's throughput / storage characteristics bound the vector index. If InnoDB's write path is the bottleneck for relational writes, adding vector mutations is additive pressure.
Not all deployments want coupling. When the vector index is large + mostly read-only + infrequently changed, the dual-write sidecar isn't painful and a specialised vector store may outperform.

patterns/hybrid-tree-graph-ann-index — the algorithmic pattern (SPANN / SPFresh family) that makes this storage-engine integration viable.

Seen in¶

— earliest wiki record of PlanetScale committing to this pattern. Nick Van Wiggeren's 2023-10-03 announcement names the product thesis verbatim: "Soon, you'll be able to use PlanetScale as a vector database for all of your AI needs without needing to adopt a second tool. … Instead of adopting a second database just for vectors, you'll be able to do the same storage and retrieval right in PlanetScale, reducing cost and operational burden significantly." Confirms the pattern's commercial thesis (don't adopt a second database) a year before the mechanism (transactional SPFresh inside InnoDB) is disclosed. Notably this post names HNSW as the intended algorithm — a stated direction that the 2024-10-22 beta announcement explicitly reverses on structural grounds (see concepts/hnsw-index contradiction). Tracks the maturation of the pattern from "aspirational product framing" (2023-10-03) → "algorithm selected and integrated" (2024-10-22) → "GA with hidden-tables mechanism disclosed" (2026-03-25) → "novel-invention engineering deep-dive" (2025-10-01).
— canonical wiki instance. PlanetScale integrates transactional SPFresh inside InnoDB, composed with Vitess sharding, yielding a first-class vector column with full SQL semantics and ACID.
— GA confirmation of the pattern in production. Discloses the integration mechanism: SPANN posting lists are "hidden InnoDB tables". Operational claims added: 2× query perf + 8× memory efficiency vs beta, [[concepts/larger-than-ram-vector-index|6× larger-than- RAM]] working ceiling, fixed/product quantization down to 1 bit/field, full PlanetScale feature composition (branching, deploy requests, reverts, backups, cross- region replicas all work with vector indexes).
sources/2026-04-21-planetscale-larger-than-ram-vector-indexes-for-relational-databases — engineering deep-dive (Vicent Martí, 2025-10-01). Exposes the three novel mechanisms PlanetScale had to invent to make a SPANN-family index actually live inside a B-tree storage engine: (a) the composite-index LSM emulation to avoid catastrophic B-tree blob rewrites on posting-list appends — the alternative would have been forcing users onto MyRocks, which "seemed like an unacceptable barrier to adoption"; (b) [[concepts/vector-versioning-for-deletion|1-byte version counter per vector]] so reassignments and deletions are append-only flag flips on an in-memory versions table; (c) WAL-tied in-memory head-index mutation so the in-memory HNSW centroid graph stays crash-consistent with on-disk posting lists via InnoDB-transaction-coupled journalling. Captures the architectural thesis verbatim: "our guiding light behind this implementation is ensuring that vector indexes in a PlanetScale MySQL database behave like you'd expect any other index to behave" — i.e. the approximate-recall property is strictly orthogonal to the transactional-invariant property.