Hi! I'm one of the authors of this feature. It's something quite novel, because it's not just a HNSW plug-in for MySQL (like e.g. pgvector is for Postgres). It's a fully transactional index, integrated into InnoDB.
We based the implementation on two very new papers from Microsoft Research, SPANN and SPFresh. SPANN is a hybrid graph/tree algorithm that does a fantastic job of scaling larger-than-RAM indexes (https://arxiv.org/abs/2111.08566) and SPFresh expands upon it by defining a set of background operations that can be performed to maintain the index's performance and recall while it's continuously updated in-place (https://arxiv.org/html/2410.14452v1). The novel thing here is designing all the SPANN _and_ SPFresh operations to be transactional, and integrating them in MySQL's default storage engine.
This tight integration fundamentally means that inserting, updating and deleting vector data from MySQL is always reflected immediately in the index as part of committing your transaction. But it also means that the indexes are fully covered by the MySQL binlog; they recover from hard crashes just fine. They're also managed by MySQL's buffer pool, so they scale to terabytes of data, just like any other table. And also crucially, they're fully integrated with the query planner, so they can be used in any query, including JOINs and WHERE clauses (something that many other vector indexes really struggle with).
We plan to release a whitepaper on our transactionality extensions to SPFresh, which I think will be super interesting, but meanwhile please feel free to test the beta and ask me any questions (here, or by emailing PlanetScale support). Thanks!
It's already open source, because GPL requires it. It's unlikely to be accepted as an upstream contribution given that Oracle has their own Vector type that is only available in their MySQL cloud service. Don't expect it to show up in a standard Oracle MySQL community release.
I have the same question. I have a small DB on Planetscale and happily pay 39 for it. IMO, Vector DB's are the next frontier. I can understand the tough decision for Planetscale, given that the cloud services can just offer it as a service and corner all value of an open source project.
very cool stuff! I just read the SPFresh paper a few days ago and was wondering if it's been implemented in industry (e.g. Turbopuffer's implementation of SPANN).
I'd be curious how y'all represent the posting lists for each partition in InnoDB:
- what IDs are you storing in the posting lists?
- how are the posting lists represented on disk? are they using compression and/or some form of skip indexing? the paper seemed to use a pretty simple block-based representation, but I'm curious what works well in practice.
- how do the posting list data structures themselves handle incremental updates and MVCC?
We're storing IDs from a ghost column that is created in the table where you're inserting vector data. This works very well in practice and allows updating the value of the vectors in the table, because they're translated into a delete + insert in the vector index by updating the ghost ID.
We have abstracted away the quantization system from the index; for the initial release, vector data is stored in raw blocks, like in the paper. Query performance is good, but disk usage is high. We're actively testing different quantization algorithms to see which ones we end up offering on GA. We're hoping our beta users will help us guide this choice!
Incremental updates and MVCC are _extremely tricky_, for both correctness and performance. As you've surely noticed, the hard thing here is that the original paper is very focused on LSM trees, because it exploits the fact that LSM trees get compacted lazily to perform incremental updates to the posting lists ('merges'). MySQL (and Postgres, and all relational databases, really) are B-tree based, and in-place updates for B-trees are expensive! I think we came up with very interesting workarounds for the problem, but it's a quite a bit to drill down in a HN comment. Please stay tuned for our whitepaper. :)
I'd be curious if y'all end up supporting adding filter attributes to the inverted index that can then be pushed down into the posting list traversal.
for example, a restaurant search app may have (1) an embedding for each restaurant but also (2) a cuisine. then, if a restaurant has `cuisine = Italian`, we'd also store its ghost ID in a `cuisine:Italian` posting list.
at query time, the query planner could take a query like `SELECT * FROM t1 WHERE cuisine = 'Italian' ORDER BY DISTANCE(..)` and emit a plan that efficiently intersects the `cuisine:Italian` posting list with the union of the partitions' posting lists.
this feels to me like a potential strength of the inverted indexing approach compared to graph-based approaches, which struggle with general filtering (e.g. the Filtered-DiskANN paper).
I'm curious what the use case/customer demand for something like vectors to be transactional is? It strikes me as something fundamentally less about strong consistency than most use cases in a database. Don't get me wrong, if it were free to have transactional, why not, but I can only assume this comes with profound tradeoffs in a workload contention sense.
A good example is TiDB's implementation of vector indexes. TIDB is also a MySQL-compatible distributed transactional database, which separates the storage engine for vector indexes from the transactional store through an extra replica in its OLAP engine (via TiFlash), avoiding resource contention.
Since you're here - why decide to write something from scratch for mysql instead of using/improving postgres? Seems like a very strange decision. MySQL is a poor RDBMS in comparison.
MySQL powers much larger workloads than Postgres as a number of marquee silicon valley companies. Though it's generally used as a sharded key value store in those scenarios
Given that PlanetScale only operates in a handful of AWS and Google regions, isn't the latency brutal for any customer not hosting in 1 of the ~12 datacenter PlanetScale is in.
Will these converge on a common syntax for vector fields, indexes, and comparison functions in the near future? Or will vector implementations just add momentum to the increasing incompatibility in the MySQL-ish ecosystem?
Our vector implementation is fully compatible with the syntax for vector fields and comparison functions in MySQL 9 -- we have carefully backported all the changes to our version of MySQL 8 to ensure full backwards and forwards compatibility. The index syntax is specific to PlanetScale because the open-source MySQL 9 preview does not yet have support for ANN indexes on vector columns, but we're committed to being as compatible as possible in the future and to minimize fragmentation. I do agree that fragmenting the ecosystem is not great!
I have a doubt about vector searches. Does increasing the amount of dimensions always improve performance? Or is there an optimal (application dependant) point where, beyond that, performance starts to degrade?
I'm asking this because most of the vector databases have tight limits on max dimensions and OpenAI's biggest embeddings model has something like 3,000 dimensions. I was wondering if there is something to gain if, for example, OpenAI releases a new embeddings model with 8,000 dimensions or more.
Awesome. Does planetscale vector support multi node indices (like Milvus) and even further blob store index storage which can be separate from compute, allowing to pay for compute on demand when queries are run?
Definitely yes to the first: the index implementation is fully integrated with PlanetScale's sharding layer, so you can scale horizontally as much as you need. This works very well in practice: the sharding layer, Vitess, is extremely well battle tested with petabyte-sized clusters in production, and our vector indexes are like any other table on the cluster, so it really scales horizontally with very predictable performance.
As for separating storage and compute: we don't do that. One of our key sells here is that this is vector data fully integrated into your relational database with ACID semantics. Very hard to do separate storage and compute and keep this behavior!
I work for another db vendor and we also have added vectors support. What’s crazy is that customers were not really asking for- just surfing the hype wave like everyone else. Why would someone store vectors in an SQL db rather than a dedicated vector db with support for native graph / vector semantics?
I'd ask the opposite: why would you use a vector index without the related metadata for filtering and hybrid querying. People don't use B-Tree indexes as a standalone separate database off to the side of their database. So why should they for vectors?
Because you can transact over relational and vector operations. You have to strongly justify why you're not storing data in your dbms because you lose the ability to atomically update them both.
In most cases the justification is performance and that the data is ephemeral but people want to store vectors in lock-step with their data.
b/c you don't want the management or learning curve overhead of a separate database for vectors than the one you're already using for transactions or metadata
Hi! I'm one of the authors of this feature. It's something quite novel, because it's not just a HNSW plug-in for MySQL (like e.g. pgvector is for Postgres). It's a fully transactional index, integrated into InnoDB.
We based the implementation on two very new papers from Microsoft Research, SPANN and SPFresh. SPANN is a hybrid graph/tree algorithm that does a fantastic job of scaling larger-than-RAM indexes (https://arxiv.org/abs/2111.08566) and SPFresh expands upon it by defining a set of background operations that can be performed to maintain the index's performance and recall while it's continuously updated in-place (https://arxiv.org/html/2410.14452v1). The novel thing here is designing all the SPANN _and_ SPFresh operations to be transactional, and integrating them in MySQL's default storage engine.
This tight integration fundamentally means that inserting, updating and deleting vector data from MySQL is always reflected immediately in the index as part of committing your transaction. But it also means that the indexes are fully covered by the MySQL binlog; they recover from hard crashes just fine. They're also managed by MySQL's buffer pool, so they scale to terabytes of data, just like any other table. And also crucially, they're fully integrated with the query planner, so they can be used in any query, including JOINs and WHERE clauses (something that many other vector indexes really struggle with).
We plan to release a whitepaper on our transactionality extensions to SPFresh, which I think will be super interesting, but meanwhile please feel free to test the beta and ask me any questions (here, or by emailing PlanetScale support). Thanks!
Will this be open sourced and be upstreamed to MySQL? Or will it always be Planetscale specific?
It's already open source, because GPL requires it. It's unlikely to be accepted as an upstream contribution given that Oracle has their own Vector type that is only available in their MySQL cloud service. Don't expect it to show up in a standard Oracle MySQL community release.
Is that the case when they're only providing as a service rather than distributing it? And if it is, where's the code?
Well. Let's see how it turns out. I am hoping Vector Type will be available in community release in later 9.x version.
Can it be put into MarianDB? Or has the two project diverged so much that is not possible now?
To be honest using MySQL the fact that it is owned by a corp is quite a concern ATM.
I have the same question. I have a small DB on Planetscale and happily pay 39 for it. IMO, Vector DB's are the next frontier. I can understand the tough decision for Planetscale, given that the cloud services can just offer it as a service and corner all value of an open source project.
very cool stuff! I just read the SPFresh paper a few days ago and was wondering if it's been implemented in industry (e.g. Turbopuffer's implementation of SPANN).
I'd be curious how y'all represent the posting lists for each partition in InnoDB:
- what IDs are you storing in the posting lists?
- how are the posting lists represented on disk? are they using compression and/or some form of skip indexing? the paper seemed to use a pretty simple block-based representation, but I'm curious what works well in practice.
- how do the posting list data structures themselves handle incremental updates and MVCC?
These are very relevant questions! Thank you!
We're storing IDs from a ghost column that is created in the table where you're inserting vector data. This works very well in practice and allows updating the value of the vectors in the table, because they're translated into a delete + insert in the vector index by updating the ghost ID.
We have abstracted away the quantization system from the index; for the initial release, vector data is stored in raw blocks, like in the paper. Query performance is good, but disk usage is high. We're actively testing different quantization algorithms to see which ones we end up offering on GA. We're hoping our beta users will help us guide this choice!
Incremental updates and MVCC are _extremely tricky_, for both correctness and performance. As you've surely noticed, the hard thing here is that the original paper is very focused on LSM trees, because it exploits the fact that LSM trees get compacted lazily to perform incremental updates to the posting lists ('merges'). MySQL (and Postgres, and all relational databases, really) are B-tree based, and in-place updates for B-trees are expensive! I think we came up with very interesting workarounds for the problem, but it's a quite a bit to drill down in a HN comment. Please stay tuned for our whitepaper. :)
looking forward to it!
I'd be curious if y'all end up supporting adding filter attributes to the inverted index that can then be pushed down into the posting list traversal.
for example, a restaurant search app may have (1) an embedding for each restaurant but also (2) a cuisine. then, if a restaurant has `cuisine = Italian`, we'd also store its ghost ID in a `cuisine:Italian` posting list.
at query time, the query planner could take a query like `SELECT * FROM t1 WHERE cuisine = 'Italian' ORDER BY DISTANCE(..)` and emit a plan that efficiently intersects the `cuisine:Italian` posting list with the union of the partitions' posting lists.
this feels to me like a potential strength of the inverted indexing approach compared to graph-based approaches, which struggle with general filtering (e.g. the Filtered-DiskANN paper).
I'm curious what the use case/customer demand for something like vectors to be transactional is? It strikes me as something fundamentally less about strong consistency than most use cases in a database. Don't get me wrong, if it were free to have transactional, why not, but I can only assume this comes with profound tradeoffs in a workload contention sense.
A good example is TiDB's implementation of vector indexes. TIDB is also a MySQL-compatible distributed transactional database, which separates the storage engine for vector indexes from the transactional store through an extra replica in its OLAP engine (via TiFlash), avoiding resource contention.
Since you're here - why decide to write something from scratch for mysql instead of using/improving postgres? Seems like a very strange decision. MySQL is a poor RDBMS in comparison.
https://www.uber.com/blog/postgres-to-mysql-migration/
https://www.mysql.com/customers/
You bring up such a discredited blog post as proof?
PlanetScale is built on Vitess. Vitess was created in 2010 to help scaling MySQL at Youtube.
MySQL powers much larger workloads than Postgres as a number of marquee silicon valley companies. Though it's generally used as a sharded key value store in those scenarios
"... every relational DBMS will have a high-performance implementation of a vector index within the next five years ... " [1]
Andy Pavlo, Jan 4, 2024
P.S.: R.I.P. Ottertune.
---
1: https://archive.is/rzMnq#selection-467.69-467.180
Given that PlanetScale only operates in a handful of AWS and Google regions, isn't the latency brutal for any customer not hosting in 1 of the ~12 datacenter PlanetScale is in.
At the enterprise level my understanding is that PlanetScale can operate within your environment directly instead of as a pure service.
this is correct
I'm very interested to see this alongside MySQL 9 (https://dev.mysql.com/doc/refman/9.0/en/vector-functions.htm...), and MariaDB (https://mariadb.com/kb/en/vector-overview/).
Will these converge on a common syntax for vector fields, indexes, and comparison functions in the near future? Or will vector implementations just add momentum to the increasing incompatibility in the MySQL-ish ecosystem?
Our vector implementation is fully compatible with the syntax for vector fields and comparison functions in MySQL 9 -- we have carefully backported all the changes to our version of MySQL 8 to ensure full backwards and forwards compatibility. The index syntax is specific to PlanetScale because the open-source MySQL 9 preview does not yet have support for ANN indexes on vector columns, but we're committed to being as compatible as possible in the future and to minimize fragmentation. I do agree that fragmenting the ecosystem is not great!
I have a doubt about vector searches. Does increasing the amount of dimensions always improve performance? Or is there an optimal (application dependant) point where, beyond that, performance starts to degrade?
I'm asking this because most of the vector databases have tight limits on max dimensions and OpenAI's biggest embeddings model has something like 3,000 dimensions. I was wondering if there is something to gain if, for example, OpenAI releases a new embeddings model with 8,000 dimensions or more.
Awesome. Does planetscale vector support multi node indices (like Milvus) and even further blob store index storage which can be separate from compute, allowing to pay for compute on demand when queries are run?
Definitely yes to the first: the index implementation is fully integrated with PlanetScale's sharding layer, so you can scale horizontally as much as you need. This works very well in practice: the sharding layer, Vitess, is extremely well battle tested with petabyte-sized clusters in production, and our vector indexes are like any other table on the cluster, so it really scales horizontally with very predictable performance.
As for separating storage and compute: we don't do that. One of our key sells here is that this is vector data fully integrated into your relational database with ACID semantics. Very hard to do separate storage and compute and keep this behavior!
I work for another db vendor and we also have added vectors support. What’s crazy is that customers were not really asking for- just surfing the hype wave like everyone else. Why would someone store vectors in an SQL db rather than a dedicated vector db with support for native graph / vector semantics?
For one thing, combining vector similarity ranking and traditional WHERE clause conditions in the same query is tremendously useful.
“This customer has called our support line 20 times in the past year, pop up the most relevant prior tickets to their current request.”
applying to yc with this idea.
I'd ask the opposite: why would you use a vector index without the related metadata for filtering and hybrid querying. People don't use B-Tree indexes as a standalone separate database off to the side of their database. So why should they for vectors?
Because you can transact over relational and vector operations. You have to strongly justify why you're not storing data in your dbms because you lose the ability to atomically update them both.
In most cases the justification is performance and that the data is ephemeral but people want to store vectors in lock-step with their data.
Are you asking why you did it?
b/c you don't want the management or learning curve overhead of a separate database for vectors than the one you're already using for transactions or metadata
Awesome! Excited to play with this one.