Skip to main content

Search records

Database includes three search modes for relational flat tables:

ModeBest forQuery interfaces
Full textWords and phrases across selected text fieldsJavaScript, Python, Refine, REST
VectorSemantic similarity against one embedding fieldPython, REST
HybridCombining semantic and keyword relevancePython, REST

Whole-table JSONB storage does not provide vector or hybrid search. Review storage compatibility before applying a search workflow to an existing table.

Prepare a searchable table​

Prepare the table before your application uses search:

  1. Choose the text fields or embedding field that represents the searchable content.
  2. Add the matching search configuration to the table's schema — see Plan schema changes.
  3. Apply and verify the generated search or vector index.
  4. Store representative records and run a bounded test query.
  5. Confirm the expected results, stable ordering, and application policy with production-equivalent roles.

Configure full-text search​

A table with full-text search stores a generated search_vector column and a GIN index. Its search configuration chooses the searchable text fields and language configuration. Application clients send only the search term and normal query controls.

search_fields contains between one and ten distinct fields. Each field must have the Frictionless type string or text and may carry a weight from A (highest) through D (lowest):

{
  "search_fields": [
    {"field": "title", "weight": "A"},
    {"field": "summary", "weight": "B"}
  ],
  "search_config": "english"
}

When a weight is omitted, Database assigns A, B, C, then D to successive unweighted entries and uses D for any remaining entries. The weights affect the text rank consumed by hybrid search; the ordinary full-text query below still uses the explicit application ordering.

Query the table through the interface your application already uses.

await database
  .from('documents')
  .search('release planning')
  .sort('id', 'asc')
  .page(1)
  .pageSize(20)
  .execute();

The search convenience parameter is applied only when the table contains the generated search vector. On a table without search configured it has no effect, so only show search in your UI for tables that have it.

Full-text search on this route filters matching records; it does not expose a relevance score or apply relevance ordering. Supply a stable order whenever you paginate. The examples use the unique id field.

A vector field uses Frictionless type: "any" with vector metadata. TaruviBase derives its HNSW index from the same metadata:

{
  "name": "embedding",
  "type": "any",
  "x-vector-metadata": {
    "dimensions": 1536,
    "distance": "cosine",
    "index_type": "hnsw",
    "storage_type": "vector"
  }
}

A table supports one vector field. Its distance must be cosine, l2, or ip, and its storage must be vector or halfvec. HNSW vector fields allow up to 2,000 dimensions; halfvec allows up to 4,000.

The application supplies query_embedding with exactly the configured number of dimensions. Database stores and searches embeddings; it does not generate them from source text. In the REST examples, query-embedding.json contains one JSON array with exactly 1,536 numeric values for this example schema.

(
    client.database
    .from_("documents")
    .vector_search(
        "embedding",
        query_embedding,
        topk=10,
        metric="cosine",
    )
    .sort("id", "asc")
    .page(1)
    .page_size(10)
    .execute()
)

Pure vector rows include _vector_score, the raw pgvector comparison value, and _similarity_score, a derived higher-is-better value. The transformation depends on the selected metric:

Metric_vector_score_similarity_score
cosineCosine distance; lower ranks first1 - distance, in the range [-1, 1]
l2Euclidean distance; lower ranks first1 / (1 + distance), in the range (0, 1]
ipNegated inner product; lower ranks firstInner product (-distance), which is unbounded

These values are ranking signals, not probabilities, and are not comparable across different metrics. The id ordering in the example breaks equal-score ties without replacing vector rank.

Hybrid search runs vector and full-text retrieval and combines their ranks with reciprocal-rank fusion. It requires both a query embedding and a keyword query.

(
    client.database
    .from_("documents")
    .vector_search(
        "embedding",
        query_embedding,
        topk=20,
        metric="cosine",
    )
    .search("release planning")
    .hybrid(strategy="rrf", alpha=0.5)
    .sort("id", "asc")
    .page(1)
    .page_size(10)
    .execute()
)

Only rrf is implemented. alpha=0 uses only the full-text contribution; alpha=1 uses only the vector contribution. Hybrid rows include _hybrid_score, _vector_score, and _fts_score. The two component scores are reciprocal-rank terms for their respective result lists, or zero when that leg does not contribute. _hybrid_score applies alpha to the vector term and 1 - alpha to the full-text term. None of these hybrid values is a raw vector distance, text rank, or probability. The id ordering breaks equal fused-score ties.

Query controls​

ControlBehavior
_topkDefines the candidate window and defaults to 10; keep it bounded for the query and page size
_vector_metricMust match the field's cosine, l2, or ip definition
_vector_thresholdApplies a metric-dependent cutoff
_vector_ef_searchSets HNSW search breadth; larger values trade query work for recall
_hybrid_alphaBalances the vector and full-text contributions from 0 to 1

Pagination slices the top-k candidate window. An offset at or beyond _topk is rejected. Secondary ordering can break equal-score ties but does not replace score order.

Embedding fields are omitted from default query results. Request an embedding field explicitly with field selection only when the application needs it. The generated search_vector field is managed by Database and is never returned. Validate the embedding dimension before the request, avoid logging embeddings that may encode sensitive content, and measure latency and relevance with application data before a launch decision.

Continue with query field selection or index planning.