Vector search and storage

Welcome to the PlanetScale vectors beta! The goal of this private beta period is to get the product in the hands of our customers so you can build alongside us while we continue to improve the feature — with your feedback.

If at any point you experience issues with vectors while using the beta, we highly encourage you to get in touch. Your feedback is extremely valuable during this beta period, so don’t hesitate to reach out. You can submit a support ticket to relay any feedback or issues. We also have a private Discord channel for the vectors beta. If you'd like to added, fill out our contact form.

This documentation outlines how to get started with vectors, known issues and limitations, some example usage, and how to share feedback.

Known issues and limitations

• Building a one-shot index (an index built in bulk on an existing set of vectors) requires enough RAM to fit all of the vectors in memory simultaneously. This limitation will be lifted by the time the beta is complete.
• Incremental indexes (indexes that begin empty and update as new vectors are added) function correctly, but are significantly slower to build compared to a one-shot index. Disk usage is much higher due to potentially very high InnoDB blob fragmentation issues, so it's much easier to run out of disk space.
• Online DDL and deploy requests do not work well yet, because they build incremental indexes. Please use direct DDL for now. We plan to improve this significantly during the beta.
• Since this is a beta, there may be bugs, performance, and security issues that have not yet been uncovered. We also may need to change query or DDL syntax before the feature is generally available. Don’t run this on a production database.
• Once you opt a branch into the vectors feature, that branch must continue to run a vectors-enabled version of MySQL. You can remove your vector columns/tables, but you cannot downgrade that branch to its prior version of MySQL.

Overview

Vectors are a data structure that captures opaque semantic meaning about something and allows a database to search for resources by similarity based on this opaque meaning. As a data type, a vector is just an array of floating-point numbers. Those numbers are generated by submitting some resource — a word, a string, a document, an image, audio, etc — to an embedding¹, which converts the resource to a vector.

A vector database stores those vector embeddings alongside other relational data. In practice, that might look like a table with columns for ID (a primary key), content (as a BLOB or VARCHAR), and a vector. Then it becomes possible to perform queries that find content similar to a search query, like so:

SELECT id
  FROM t1
  ORDER BY DISTANCE(TO_VECTOR('[1.2, 3.4, 5.6]'), embedding, 'L2_squared')
  LIMIT 10;

Possible applications include recommendation engines that show products similar to a user's purchase history, or search engines that find documents or other resources based on natural-language queries.

PlanetScale has added support for vector columns, vector distance functions, and vector indexes, as described below.

[¹]: PlanetScale does not currently provide an embedding service. You can find several good cloud-based options like OpenAI or AWS Titan, or local options like Python sentence_transformers.

Enrolling in the PlanetScale vectors beta

This is currently a closed beta. To access the beta, you must have received an invite. If you did not receive an invite and wish to join, or you would like to enroll a different organization, please fill out our contact form.

PlanetScale has a series of MySQL images that have been extended with vector support. Vector support can be enabled on a per-branch basis, however, you have to first opt-in to the beta from your database settings page. After that, you will choose which branch(es) you’d like to opt-in to the vectors beta. The branch will be updated to the vectors-enabled version of MySQL at the time of opting the branch in.
To enable the vector support on a branch:

1. Click on the database that you’d like to enroll in the vectors beta.
2. Click “Settings” in the left nav.
3. Click “Beta features”.
4. Click “Enroll” next to “Vectors”.
5. Next, navigate to the “Branches” tab, and click on the branch that you would like to enable vector support on.
6. Click on the small gear icon underneath the “Connect” button on the right.
7. Click the toggle next to “Enable vectors”.
8. Click “Save branch settings”.
9. The branch will upgrade asynchronously to the correct version of MySQL, which may take 30-60 minutes. You can confirm when this process is complete by executing a “SELECT @@version” query. The vector-enabled version is 8.0.37.

Adding vector columns

As a first step, create one or more columns with the VECTOR type and then insert some vectors. Here’s an example:

CREATE TABLE t1(id INT PRIMARY KEY auto_increment, embedding VECTOR(4));
INSERT INTO t1(embedding) VALUES (TO_VECTOR('[1, 2, 3, 4]')),
                                 (TO_VECTOR('[5, 6, 7, 8]'));

At some point, you’ll want to build an index on this vector column to facilitate fast similarity searches. Here’s how to create one:

CREATE /*vt+ QUERY_TIMEOUT_MS=0 */
  VECTOR INDEX embedding_index ON t1(embedding);

The QUERY_TIMEOUT_MS comment is optional, but important for tables over 100,000 rows. Vector indexes take a significant amount of time to build, and the comment allows the CREATE statement to bypass Vitess’s usual timeout.

Then, you can perform similarity searches like so:

SELECT id, DISTANCE(TO_VECTOR('[3, 3, 3, 3]'), embedding, 'L2_SQUARED') AS d
  FROM t1
  ORDER BY d
  LIMIT 10;

Use an EXPLAIN query to confirm that the query uses the new index. This query actually won’t use the index until the table has around 50 rows in it.

Note that vector indexes provide approximate results. An unindexed query with LIMIT 100 returns exactly the 100 rows closest to the reference vector, after performing a full table scan and a sort. An indexed query returns, on average, about 100 of the top 105 (around 95%) of the rows closest to the reference vector, but much faster than a full table scan. This is expected, because all efficient vector indexes, including PlanetScale’s vector indexes, perform approximate nearest neighbor (ANN) searches.

If you are adding vectors from an app, you may want to use prepared statements, although we do not recommend it. TO_VECTOR works in that setting, but serializing the vectors on the client side and uploading them as binary is faster. The serialized format is IEEE-754 32-bit floats, which you can serialize with code like this:

• Python: struct.pack('ffff', *float_array)
• Ruby: float_array.pack(“ffff”)
• Rust: float_array.map(|f| f.to_ne_bytes()).flatten().collect()

You can use the resulting blob (which will be 4 bytes times the number of dimensions in the vector) in an INSERT statement like this:

INSERT INTO t1(embedding) VALUES
  (CAST(? AS CHAR CHARACTER SET binary));

Vector index parameters

Statements that create a vector index take optional parameters, which can be specified as JSON key-value pairs, like so:

CREATE VECTOR INDEX embedding_index ON t1(embedding)
  SECONDARY_ENGINE_ATTRIBUTE='{"type":"spann", "distance":"cosine"}';

The type attribute specifies the algorithm used to build and query the vector index. It is optional, and the only valid value is spann, which is the default. The distance attribute specifies the distance metric that queries will use, and can be any of the following:

• dot for the dot product
• cosine for the cosine of the angle between the two vectors, which is the same as the dot product divided by the magnitude of the two vectors
• l2 or euclidean for the length of a line between the ends of the vectors
• l2_squared or euclidean_squared for the square of the Euclidean distance. This is the default.

The distance metric specified at index creation time must match the distance metric used at query time, or the index cannot be used, and MySQL will perform a full-table scan instead.

Filtered vector queries

Vector data is stored in tables alongside any other relational data, and sometimes applications need to query, join, or filter based on that relational data. In particular, queries with WHERE clauses work, and MySQL chooses the index or indexes that allow the query to complete the fastest. For example, in a table of products for sale containing indexed columns for price and seller ID, here are how two queries might be executed:

SELECT id,price,seller_id
  FROM products
  WHERE price < 20.0
  ORDER BY DISTANCE(TO_VECTOR('[1.2, 3.4, 5.6]'), embedding, 'L2_squared')
  LIMIT 10;

This query selects the ten products with a price under $20 closest to some reference vector. If a non-trivial fraction of products cost less than $20, then MySQL will use the vector index to produce more than ten results, and filter them down to exactly ten results that meet the price constraint.

SELECT id,price,seller_id
  FROM products
  WHERE seller_id=789
  ORDER BY DISTANCE(TO_VECTOR('[1.2, 3.4, 5.6]'), embedding, 'L2_squared')
  LIMIT 10;

This query selects the ten products from a given seller that are closest to some reference vector. If that seller’s products are a small enough fraction of the rows in the table, then MySQL will use the index on seller_id to find all products from that seller, and will sort those products by vector distance and return the top ten.

The MySQL query planner chooses whether to use the vector index or some other index automatically based on the query and based on the contents of the table, to maximize query performance. Use EXPLAIN on any given query to see how it will execute.

As part of the private beta, we’re looking for feedback on how well MySQL plans vector queries. If you believe you’ve hit an edge case or something looks wrong, please open a support ticket and let us know.

Vector function reference

TO_VECTOR(string)
Converts a text string to a binary vector value. The text string is an array of floating point numbers in JSON format.

• alias STRING_TO_VECTOR(string)

• Example: SELECT TO_VECTOR('[1, 2.78, 3.14]');

  -> 0x0000803F85EB3140C3F54840

FROM_VECTOR(string)
Converts a binary vector to a human-readable string.

• alias VECTOR_TO_STRING(vector)

• Example: SELECT FROM_VECTOR(0x0000803F85EB3140C3F54840);

  -> [1.00000e+00,2.78000e+00,3.14000e+00]

VECTOR_DIM(string)
Calculates the dimension of a vector

• Example: SELECT VECTOR_DIM(TO_VECTOR('[1,2,3]')); -> 3

DISTANCE(vector1, vector2, [metric])
Calculates the distance between two vectors. The optional third parameter specifies which distance metric is to be used: DOT, COSINE, L2 (EUCLIDEAN), or L2_SQUARED (EUCLIDEAN_SQUARED). If the distance metric is omitted, it defaults to DOT.

• DOT means the dot product. Example:

  SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'DOT');

  -> 13

• COSINE means the cosine of the angle between the two vectors, which is the same as the dot product divided by the magnitude of the two vectors. Example:

  SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'COSINE');

  -> 0.09204061549954834

• L2 (or EUCLIDEAN) means the length of a line between the ends of the vectors. Example:

  SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'L2');

  -> 4.47213595499958

• L2_SQUARED (or EUCLIDEAN_SQUARED) is the square of the Euclidean distance

  SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'L2_SQUARED');

  -> 20

DISTANCE_DOT(vector1, vector2)
Is the same as DISTANCE(vector1, vector2, 'DOT')

DISTANCE_COSINE(vector1, vector2)
Is the same as DISTANCE(vector1, vector2, 'COSINE')

DISTANCE_L2(vector1, vector2)
Is the same as DISTANCE(vector1, vector2, 'L2')

• alias: DISTANCE_EUCLIDEAN(vector1, vector2)

DISTANCE_L2_SQUARED(vector1, vector2)
Is the same as DISTANCE(vector1, vector2, 'L2_SQUARED')

• alias: DISTANCE_EUCLIDEAN_SQUARED(vector1, vector2)

Feedback

We want to make our vectors offering as reliable, fast, and feature-rich as possible. Feedback from our early users will help make this possible. If you encounter any issues, crashes, unexpected errors or poor performance, please submit a support ticket. You are also welcome to reach out with general feedback and suggestions.

We also have a private Discord channel for the vectors beta where you can ask questions, share feedback, and discuss what you’re working on. If you'd like to added, please fill out the contact form.

Need help?

Get help from the PlanetScale support team, or join our GitHub discussion board to see how others are using PlanetScale.