Four kinds of search, from exact match to meaning

Most applications end up running several search systems. The database answers exact lookups, a search cluster handles words in documents, and a vector store handles meaning. Each one keeps its own copy of the data, and a sync job has to keep every copy in step with the records and with who is allowed to see them.

In InventDB the four kinds of search run inside the engine that stores the records. They read the same segments, use indexes the engine maintains as part of writing, and return rows through the same entry point that applies each person's row rules. Nothing is copied to a second system, so there is nothing to fall out of step.

This article takes the four in turn: exact, fuzzy, full-text and semantic. For each one it covers what you write, what the engine does with it, what it costs and where it stops being the right tool.

Exact matches come from the property index

Every field of every type has a property index. It is three B-link trees kept in step on each write: one maps a value to the ids of the records that hold it, one maps a value to a count and a sum for grouping, and one maps a record id back to its value. An equality, a range, an IN list or a BETWEEN is a seek on the first tree, which costs O(log n + k) for k matches. Each segment also records the smallest and largest value of every field, so a range query skips segments that cannot contain a match.

SELECT unit, tenant, rent FROM pms.leases
WHERE status = 'Active' AND rent BETWEEN 1500 AND 2500

String comparisons in a WHERE clause ignore case, so 'active' finds Active.

LIKE uses the same index. A pattern with a fixed prefix, such as LIKE 'Main%', is a seek to the first key with that prefix. A pattern that starts with a wildcard, such as LIKE '%leak%', cannot seek, so the engine scans the index's keys in parallel instead of reading the records. Index keys hold values up to 1,024 bytes. A longer value is stored in the index as a SHA-256 hash, and a pattern match fetches those records and tests the real text, so a long description is never skipped because it was too large to index.

Fuzzy matches: edit distance in a WHERE clause

People misspell names, and typed data carries typos. LEVENSTEINDISTANCE(column, 'text') returns the number of single-character insertions, deletions and substitutions that turn one string into the other, ignoring case. The function name drops the h from Levenshtein, so type it as shown. With a threshold it finds near misses:

SELECT firstname, lastname FROM ECommerce.Customer
WHERE firstname = 'xavier'
  AND LEVENSTEINDISTANCE(lastname, 'rodriguz') <= 1

This returns Xavier Rodriguez, one insertion away from the typo, and it is one of the queries in our test suite.

No index can answer an edit distance, because the distance depends on the string you compare against. The planner therefore treats the function as a filter applied to candidate rows. On its own, that means computing the distance for every row of the type. In an AND with a predicate the index can answer, as in the example, the indexed part supplies the candidates and the distance is computed only for them. You can also put the function in the SELECT list to show how close each match was.

Full-text search: BM25 over the words in your files

Full-text search answers a different question: which documents contain these words, ranked by how well they match. In InventDB it serves file search, where the text comes from PDFs, office documents, email and OCR of images. How that text is produced is covered in the article on files and OCR.

The index is an inverted index kept per segment. Each document's text is split on anything that is not a letter or a digit, lowercased and passed through a conservative English stemmer, so detectors becomes detector, tested and testing become test, and policies becomes policy. Words shorter than four letters are left alone, so gas and bus stay as they are. The index maps each stem to a postings list of (document, term frequency) pairs and stores every document's length.

Results are scored with Okapi BM25, with k1 = 1.2 and b = 0.75. Rare terms count for more than common ones, a repeated term counts with diminishing returns, and a long document does not win merely by being long. A query does not have to match every word: "smoke detector compliance" still ranks an inspection report about smoke detectors that never uses the word compliance.

Indexing happens when a checkpoint persists a write, which the server does every five seconds. Splitting and stemming need no model, so the work is done inside that step rather than queued for later. When a segment fills and is sealed, its index is compacted and written as an immutable, encrypted file next to the segment's data. A query searches every sealed segment's index in parallel along with the in-memory index of the newest segment, merges the best results, and drops any hit whose record was updated or deleted after its segment sealed. The index returns ids only; the engine then fetches the rows, so row rules apply.

POST /attach/pms/properties/search
Authorization: Bearer <token>
Content-Type: application/json

{ "query": "smoke detector compliance", "search_type": "fulltext", "limit": 10 }

Semantic search: MEANING() in SQL

The fourth kind finds rows that mean what you asked, even when they share none of its words. A work order that says "water coming through the bedroom ceiling" should answer a search for "leaky roof". InventDB exposes this as a SQL function:

SELECT _id, notes FROM pms.workorders
WHERE MEANING(notes) LIKE 'leaky roof'
LIMIT 20

MEANING() turns text into a vector of 384 numbers with a sentence-embedding model compiled into the engine, and compares vectors by cosine similarity. MEANING(col) = 'text' matches when the similarity is above 0.5. MEANING(col) LIKE 'text' is looser and matches above 0.3. No language model is called and nothing leaves the instance: the embedding model is a fixed numeric function from text to vectors.

For a record column, the engine embeds each distinct value once rather than each row. An administrator builds the semantic index for a column. The engine reads the column's distinct values from the property index, embeds them, and builds an HNSW graph over the vectors. A query embeds the search text once, walks the graph to the nearest distinct values, keeps the ones inside the threshold, and looks up their records through the property index. A category column with a handful of distinct values across millions of rows needs a handful of embeddings.

Queries that contain MEANING() leave the columnar pipeline and run in the document executor, which is the part of the engine that can embed text. The model, the caches and the index files are described in detail in Embeddings computed inside the database.

Choosing between them

The four answer different questions, and the cost of each follows from the structure behind it:

File search can also combine them. Its default mode runs a keyword leg over file names and descriptions, a full-text leg and a semantic leg, and adds their scores with weights of 0.2, 0.3 and 0.5, which a request can change. A semantic hit below 0.62 cosine similarity is never returned in any mode. That floor keeps a file whose only text is a stray OCR fragment from matching every query, while the same file still ranks first for a search that genuinely matches it.

Where each one stops

• Fuzzy on its own scans the type. Pair it with a predicate the index can answer, such as a first name, a city or a date range.
• Full-text sees writes up to the last checkpoint. A file becomes searchable once its text has been extracted and the next checkpoint has run.
• Full-text ranks by terms rather than phrases. There are no phrase or boolean operators, the stemmer is English, and inverse document frequency is computed per segment rather than over the whole collection. With the segment counts we see in practice, that last point makes little difference to ranking.
• A semantic index is built on request. It is not updated on every write. Values added after the last build are not found by meaning until the next build, which embeds only the new values. If a column's index has never been built, MEANING() falls back to a case-insensitive substring match.
• Semantic results are approximate. HNSW returns approximate nearest neighbours, and the model reads at most 256 tokens of any value.

Trying it

All four kinds of search run on InventDB Serverless and InventDB SOAR, because both products run the same engine. Send SQL to POST /sql with a bearer token, and use the routes under /attach for file search. Every instance serves its live API reference at /api/docs, which lists each parameter.

POST /sql
Authorization: Bearer <token>
Content-Type: application/json

{ "sql": "SELECT _id, notes FROM pms.workorders WHERE MEANING(notes) LIKE 'leaky roof' LIMIT 20" }

A practical order of work: put exact predicates on the fields people filter by, add edit distance where names are typed by hand, rely on full-text for the contents of files, and build semantic indexes on the free-text columns where people describe things in their own words.