Working with Vector Data

A vector is a list of numbers that places content in space so that distance means similarity. That is the whole idea -- types, operators and indexes exist to make it fast at scale.

We start with four hand-written vectors you can check with a calculator, then load 10,000 real customer questions, then run a search that finds the right answer using none of the words you typed.

What's in this notebook:

1. What a vector actually is.

2. Measuring similarity, correctly.

3. Turning real text into vectors.

4. Loading 10,000 real questions.

5. Where keyword search falls down.

6. Searching at scale with a vector index.

7. Vectors are just another column.

8. Clean up.

Questions?

Reach out to us through our forum.

Create a database for the examples below.

1. What a vector actually is.

Let's describe four drinks with three numbers each -- caffeine, sweetness, fizz, on a 0-1 scale. They are hand-written so you can check every result below yourself.

The column type is VECTOR(3): a first-class type, not a BLOB. Values go in as plain JSON arrays.

Now search. An iced coffee is caffeinated, slightly sweet, not fizzy: [0.7, 0.3, 0.0]. Two operators compare it against every row:

• <-> is EUCLIDEAN_DISTANCE -- straight-line distance. Lower is more similar.

• <*> is DOT_PRODUCT -- higher is more similar.

Note the shape: no special syntax, just ORDER BY ... LIMIT. Vector search is ordinary SQL.

2. Measuring similarity, correctly.

Look at those two columns -- they disagree.

<-> ranks latte closest (0.1414 vs 0.2828), but <*> scores espresso highest (0.66 vs 0.54). Same data, same query, different winner.

Espresso wins on dot product because it is longer, not because it points in a more similar direction -- magnitude 0.9055 against latte's 0.7211, and dot product rewards length. For search we want direction, not size.

The fix is normalization: scale every vector to length 1. The docs are explicit -- "vectors must be normalized to length 1 before using the DOT_PRODUCT function to obtain the cosine similarity metric" -- and recommend doing it before the data is saved.

Now <*> agrees with <->: latte 0.9833, espresso 0.9570. With both sides at unit length, dot product is cosine similarity, 1 for identical direction, -1 for opposite.

So normalize once, at write time. <*> then gives you cosine similarity for free on every query, and it is the cheaper operator. This one step prevents the most common surprise in vector search: scores that rank by document size instead of meaning.

3. Turning real text into vectors.

Hand-written numbers made the geometry visible, but nobody scores documents by hand. An embedding model does it: text in, a fixed-length list of numbers out, trained so that similar meaning lands in a similar direction.

We will use all-MiniLM-L6-v2 -- 384 dimensions, runs locally on this workspace. No API key, and no text leaves your deployment.

Here is what one sentence looks like as a vector. Note normalize_embeddings=True -- that is section 2's lesson applied at write time, so every vector leaves the model at length 1.

Those 384 numbers mean nothing one at a time -- there is no "caffeine" dimension to point at. What matters is that the whole arrangement puts this sentence near others about using a card abroad, and far from ones about forgotten passcodes.

4. Loading 10,000 real questions.

Four hand-written rows were enough to see the geometry, but four rows never needed a database — you could rank them with a calculator. Vector search earns its keep at a scale you cannot eyeball, so here are 10,000 genuine customer-support questions sent to a bank, each labelled with the intent behind it — real phrasing, real typos.

VECTOR(384) stores each embedding beside the text it came from and its category -- one row, one table, no second system to keep in sync. SORT KEY makes it columnstore, which section 6 requires: vector indexes can only be built on columnstore tables.

Rows go in batched, each vector a JSON array string that SingleStore parses straight into the VECTOR column.

5. Where keyword search falls down.

A customer types "can I take this overseas". Nobody in this dataset phrased it that way. What can keyword search do with it?

First the literal phrase, then its one distinctive word overseas, grouped by what those rows are actually about.

The phrase matches nothing. The word overseas matches 13 rows -- but look at what they are: mostly transfer_fee_charged and wrong_exchange_rate_for_cash_withdrawal. Those are complaints about fees, not questions about whether the card works abroad. Keyword search found the word and missed the question.

Now the same query as a vector. This helper embeds the search text and passes it to SQL as a VECTOR(384) parameter; we reuse it for the rest of the notebook.

This is the whole point of vector search.

The top result is "Can I use this all over the world?" -- it shares not one word with "can I take this overseas". Neither does the second or the third. No amount of stemming or synonym tuning reaches those rows, because there is no lexical overlap to match on. The model matched the meaning.

And the one genuinely relevant keyword row, "Can I use my card while on vacation overseas?", is still here -- ranked fourth, below three better answers. Nothing was lost; word overlap simply stopped being the definition of relevance.

6. Searching at scale with a vector index.

That query used no index, so SingleStore compared against all 10,000 rows. Exact k-nearest-neighbour is correct but linear: ten times the data, ten times the work.

A vector index trades a little accuracy for a lot of speed by searching approximately (ANN). We use HNSW_FLAT, one of the two types SingleStore recommends.

The index is used automatically. USE INDEX () with an empty index list disables it and forces the exact scan, so we can run the identical query both ways and compare.

Two details matter for an honest comparison. Embedding the query takes far longer than the search does, so the text is encoded before the clock starts -- otherwise we would be timing MiniLM, not SingleStore. And a single query is mostly connection overhead, so we reuse one connection and run a batch.

Approximate search is only worth it if the answers stay good. That is recall: of the rows exact search would have returned, how many did ANN actually find? Measure it rather than assume it -- same ten queries, top 10 each, both ways.

Read those two cells together, and be suspicious of the obvious conclusion.

At 10,000 rows the exact scan is already fast -- 3.8 million multiply-adds is nothing -- so the two timings may land close together, or ANN may even come out slower, because walking a graph has its own cost.

Recall is the number worth staring at. Ours came out around 99%: across ten queries the exact scan returned 100 rows, and the approximate search found 99 of them. That miss is not a bug, it is the definition of approximate -- and it is almost certainly a row near the bottom of a ranking where the scores were nearly tied, the kind of difference no user would ever notice. Expect your own figure to move by a point either way, since graph construction is not deterministic.

Put the two cells together and this dataset makes an awkward argument: you gave up about 1% of your answers and got no speed for it. On 10,000 rows, indexing is the wrong call, and a notebook that claimed otherwise would be lying to you. The value is asymptotic -- exact search is linear, so 100x the rows is 100x the work, while a graph index grows far slower -- and the crossover depends on your row count, dimensionality and recall target. That is why the real advice is to measure it on your own data rather than trust anyone's benchmark, and why both cells above are worth keeping around: switching between the two is one hint in the ORDER BY.

7. Vectors are just another column.

This is the part a bolt-on vector store cannot do.

The vectors live in a normal table, so they compose with everything else SQL gives you. Here is a reference table mapping each intent to its owning team and response-time target -- ordinary relational data, sitting beside the embeddings.

Now one statement that does semantic search, a metadata filter, and a join at the same time: find the questions most similar to this text, but only ones owned by Onboarding, and tell me the SLA.

One query, one database. The filter, the join and the similarity ranking were planned and executed together.

Do that across a standalone vector store and it becomes a distributed systems problem: fetch candidate IDs from the vector service, ship them to your database, join there, and hope the two copies agree. Every filter is a round trip; every write lands in two places. Here the optimizer just sees one table with an extra column type.

That is why vectors belong next to your operational data, not in a service beside it.

8. Clean up.

Drops the tables this notebook created, so you can re-run from the top.

What to explore next

• Hybrid full-text and vector search -- blend MATCH ... AGAINST and vector scores in one ranked query, for when exact terms do matter.

• Vector indexing -- IVF_PQFS, quantization, and tuning recall against latency.