SingleStore vector indexes: columnstore only, one column, VECTOR F32

SingleStore vector indexes: columnstore only, one column, VECTOR F32

TL;DR

If CREATE VECTOR INDEX fails on SingleStore, check three things: the table must be a columnstore table (rowstore tables cannot carry vector indexes), the index goes on exactly one column, and the column must be VECTOR(N, F32), not BLOB or JSON. You can create the index inline in CREATE TABLE or later with ALTER TABLE ... ADD VECTOR INDEX.

Use this when you run into the situation in the title.

When not to use this skill: unrelated tasks. It covers only the procedure above.

Compatibility

The steps above apply to the commands named in them. This skill does not pin a version, so if a flag looks different on your machine, check your installed version's docs first.

Details

If you need a different similarity metric later, you can even keep multiple vector indexes on the same column; pick between them at query time with the USE INDEX ([name]) hint, or USE INDEX () to force exact kNN.

Context: Official docs (SingleStore Helios vector indexing): documents index-creation rules that trip agents used to other vector DBs. Vector indexes can only be built on columnstore tables, on a single column, and the column type is restricted to VECTOR([N], F32); F32 is currently the only supported element type. Multiple vector indexes may exist on the same column, but each of these rules is hard.

Source: Official docs (SingleStore Helios vector indexing): documents index-creation rules that trip agents used to other vector DBs. Vector indexes can only be built on columnstore tables, on a single column, and the column type is restricted to VECTOR([N], F32); F32 is currently the only supported element type. Multiple vector indexes may exist on the same column, but each of these rules is hard.

Verified resolution: If CREATE VECTOR INDEX fails on SingleStore, check three things: the table must be a columnstore table (rowstore tables cannot carry vector indexes), the index goes on exactly one column, and the column must be VECTOR(N, F32), not BLOB or JSON. You can create the index inline in CREATE TABLE or later with ALTER TABLE ... ADD VECTOR INDEX. If you need a different similarity metric later, you can even keep multiple vector indexes on the same column; pick between them at query time with the USE INDEX ([name]) hint, or USE INDEX () to force exact kNN.

Source URL: https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/vector-indexing/