sqlite-vec Query Patterns

sqlite-vec Query Patterns

Table of Contents

• KNN Query Basics
• Distance Functions
• Metadata Filtering
• Partition Key Filtering
• Point Queries
• Full Table Scan
• Query Optimization

KNN Query Basics

Standard KNN Syntax

SELECT rowid, distance
FROM vec_items
WHERE embedding MATCH ?
  AND k = 10
ORDER BY distance;

Key components:

• WHERE embedding MATCH ? - Triggers KNN query on the embedding column
• AND k = 10 - Limit to 10 nearest neighbors
• ORDER BY distance - Sort by proximity (distance column auto-generated)

Using LIMIT (SQLite 3.41+)

SELECT rowid, distance
FROM vec_items
WHERE embedding MATCH ?
LIMIT 10;

LIMIT only works correctly on SQLite 3.41+. Use k = for older versions.

Python Example

from sqlite_vec import serialize_float32

query = [0.3, 0.3, 0.3, 0.3]

results = db.execute(
    """
    SELECT rowid, distance
    FROM vec_items
    WHERE embedding MATCH ?
      AND k = 5
    ORDER BY distance
    """,
    [serialize_float32(query)]
).fetchall()

for rowid, distance in results:
    print(f"rowid={rowid}, distance={distance}")

Joining with Source Tables

WITH knn_matches AS (
  SELECT document_id, distance
  FROM vec_documents
  WHERE contents_embedding MATCH ?
    AND k = 10
)
SELECT
  documents.id,
  documents.title,
  documents.contents,
  knn_matches.distance
FROM knn_matches
LEFT JOIN documents ON documents.id = knn_matches.document_id
ORDER BY knn_matches.distance;

Distance Functions

Distance Metric in Table Definition

CREATE VIRTUAL TABLE vec_cosine USING vec0(
  embedding float[768] distance_metric=cosine
);

-- KNN query uses cosine distance automatically
SELECT rowid, distance
FROM vec_cosine
WHERE embedding MATCH ?
  AND k = 10;

Supported metrics:

• l2 - Euclidean distance (default)
• cosine - Cosine distance
• hamming - Hamming distance (for bit vectors)

Manual Distance Calculation

For regular tables (not vec0):

-- L2 distance
SELECT
  id,
  contents,
  vec_distance_l2(contents_embedding, ?) as distance
FROM documents
ORDER BY distance
LIMIT 10;

-- Cosine distance
SELECT
  id,
  vec_distance_cosine(contents_embedding, ?) as distance
FROM documents
ORDER BY distance
LIMIT 10;

-- Hamming distance (bit vectors)
SELECT
  id,
  vec_distance_hamming(signature, ?) as distance
FROM documents
ORDER BY distance
LIMIT 10;

Metadata Filtering

Single Metadata Filter

SELECT rowid, genre, distance
FROM vec_movies
WHERE synopsis_embedding MATCH ?
  AND k = 10
  AND genre = 'scifi'
ORDER BY distance;

Multiple Metadata Filters

SELECT rowid, distance
FROM vec_movies
WHERE synopsis_embedding MATCH ?
  AND k = 5
  AND genre = 'scifi'
  AND num_reviews BETWEEN 100 AND 500
  AND mean_rating > 3.5
  AND contains_violence = false
ORDER BY distance;

Range Queries

-- Numeric ranges
WHERE rating BETWEEN 3.0 AND 5.0
WHERE year >= 2020
WHERE budget < 1000000

-- Text equality (no LIKE in KNN)
WHERE category = 'technology'
WHERE language = 'en'

Boolean Filters

WHERE is_published = true
WHERE has_images = false
WHERE is_archived != true

Partition Key Filtering

Single Partition Key

SELECT document_id, distance
FROM vec_documents
WHERE contents_embedding MATCH ?
  AND k = 20
  AND user_id = 123  -- Partition key filter
ORDER BY distance;

This query only searches vectors belonging to user 123, making it much faster.

Date Range Partition

SELECT article_id, distance
FROM vec_articles
WHERE headline_embedding MATCH ?
  AND k = 10
  AND published_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY distance;

Multiple Partition Keys

SELECT document_id, distance
FROM vec_multi_tenant
WHERE embedding MATCH ?
  AND k = 10
  AND organization_id = 456
  AND region = 'us-west'
  AND created_month = '2024-12'
ORDER BY distance;

IN Constraint (Advanced)

-- Partition key with IN clause
SELECT document_id, distance
FROM vec_documents
WHERE contents_embedding MATCH ?
  AND k = 10
  AND user_id IN (123, 456, 789)
ORDER BY distance;

Point Queries

Retrieve a single vector by rowid or primary key:

By rowid

SELECT rowid, embedding
FROM vec_items
WHERE rowid = 42;

By Primary Key

SELECT document_id, contents_embedding
FROM vec_documents
WHERE document_id = 123;

With Auxiliary Data

SELECT document_id, embedding, title, contents
FROM vec_documents
WHERE document_id = 123;

Full Table Scan

Query all vectors without KNN:

-- All vectors
SELECT rowid, embedding
FROM vec_items;

-- With metadata filter (no vector MATCH)
SELECT rowid, genre
FROM vec_movies
WHERE genre = 'scifi';

-- Count vectors
SELECT COUNT(*) FROM vec_items;

Query Optimization

Selecting Specific Columns

-- Only what you need
SELECT document_id, distance
FROM vec_documents
WHERE embedding MATCH ?
  AND k = 10;

-- With auxiliary data
SELECT document_id, title, url, distance
FROM vec_documents
WHERE embedding MATCH ?
  AND k = 10;

Using Indexes on Metadata

Metadata columns in vec0 are automatically indexed for KNN queries.

Batch Queries

# Query multiple vectors efficiently
queries = [
    [0.1, 0.2, 0.3, 0.4],
    [0.5, 0.6, 0.7, 0.8],
    [0.9, 1.0, 1.1, 1.2]
]

results = []
for query in queries:
    rows = db.execute(
        """
        SELECT rowid, distance
        FROM vec_items
        WHERE embedding MATCH ?
          AND k = 5
        """,
        [serialize_float32(query)]
    ).fetchall()
    results.append(rows)

Result Pagination

-- Not directly supported, use k parameter
SELECT rowid, distance
FROM vec_items
WHERE embedding MATCH ?
  AND k = 50  -- Get top 50, handle pagination in application
ORDER BY distance;

Query Performance Tips

1. Use partition keys for multi-tenant/filtered queries
2. Keep k value reasonable (10-100 for most use cases)
3. Filter with metadata columns when possible
4. Use appropriate distance metric for your embeddings
5. Ensure partition keys have 100+ vectors per unique value
6. Batch similar queries together
7. Use auxiliary columns for large data not needed in filtering

Complex Query Example

from sqlite_vec import serialize_float32

# Multi-condition filtered KNN query
query_embedding = get_embedding("semantic search query")

results = db.execute(
    """
    SELECT
      document_id,
      title,
      url,
      distance
    FROM vec_knowledge_base
    WHERE content_embedding MATCH ?
      AND k = 20
      AND organization_id = ?
      AND created_month = ?
      AND document_type IN ('article', 'blog')
      AND is_public = true
      AND language = 'en'
      AND word_count > 500
    ORDER BY distance
    """,
    [
        serialize_float32(query_embedding),
        123,  # organization_id
        '2024-12'  # created_month
    ]
).fetchall()

for doc_id, title, url, dist in results:
    print(f"{title}: {dist:.4f}")

Combining with Regular SQL

-- Subquery with KNN
SELECT
  d.id,
  d.title,
  COUNT(c.id) as comment_count,
  v.distance
FROM (
  SELECT document_id, distance
  FROM vec_documents
  WHERE embedding MATCH ?
    AND k = 10
) v
JOIN documents d ON d.id = v.document_id
LEFT JOIN comments c ON c.document_id = d.id
GROUP BY d.id, d.title, v.distance
ORDER BY v.distance;

Similarity Search with Threshold

-- Find all items within distance threshold
SELECT rowid, distance
FROM vec_items
WHERE embedding MATCH ?
  AND k = 1000  -- Large k to get more candidates
  AND distance < 0.5  -- Filter by distance threshold
ORDER BY distance;