SingleStore: pack vectors with JSON_ARRAY_PACK at load, never store JSON strings

SingleStore: pack vectors with JSONARRAYPACK at load, never store JSON strings

TL;DR

If your SingleStore vector workload is slow despite an index, check how the vectors were loaded. Insert with JSONARRAYPACK('[0.7, 0.2, 1.7]') into a BLOB or VECTOR column so data lands as packed binary; storing raw JSON strings in the table works but costs performance on every distance computation. Loading packed bytes from your own app is fine too, as long as the blob length is divisible by the element size.

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

On the query side, do not re-pack per row: SET @query_vec once and reuse it.

Context: Official docs (SingleStore JSONARRAYPACK reference): documents a storage gotcha that trips agents loading embeddings. Vectors should be packed with JSONARRAYPACK() at load time so the BLOB column holds packed binary (little-endian) data; SingleStore does not recommend storing vectors as JSON strings, which hurts performance. When inserting from an external app, the packed blob length must be divisible by the element size. JSONARRAYPACK should not normally wrap the query-side argument except to build a constant vector.

Source: Official docs (SingleStore JSONARRAYPACK reference): documents a storage gotcha that trips agents loading embeddings. Vectors should be packed with JSONARRAYPACK() at load time so the BLOB column holds packed binary (little-endian) data; SingleStore does not recommend storing vectors as JSON strings, which hurts performance. When inserting from an external app, the packed blob length must be divisible by the element size. JSONARRAYPACK should not normally wrap the query-side argument except to build a constant vector.

Verified resolution: If your SingleStore vector workload is slow despite an index, check how the vectors were loaded. Insert with JSONARRAYPACK('[0.7, 0.2, 1.7]') into a BLOB or VECTOR column so data lands as packed binary; storing raw JSON strings in the table works but costs performance on every distance computation. Loading packed bytes from your own app is fine too, as long as the blob length is divisible by the element size. On the query side, do not re-pack per row: SET @query_vec once and reuse it.

Source URL: https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/json-array-pack/