DuckDB for AI: Vector Search, ML Pipelines, and RAG Implementation

Introduction

DuckDB’s support for vector similarity search through the vss extension enables AI applications directly within your analytical workflows. Combined with its excellent Python integration, DuckDB becomes a powerful tool for building ML pipelines, feature engineering, and retrieval-augmented generation (RAG) systems.

This comprehensive guide explores how to leverage DuckDB for AI applications, from embedding storage to production ML pipelines.

The article is organized as a progression that mirrors a real AI workflow. It opens with vector similarity search, the foundation on which everything else builds: you store embeddings in DuckDB, index them with HNSW, and query them with fast approximate nearest- neighbor search. From there we assemble a complete retrieval-augmented generation pipeline that connects those vectors to an LLM, then broaden the scope to classical machine learning — feature engineering, train/test splits, cross-validation, and batch prediction — all executed with SQL inside DuckDB. The closing section covers the tuning and operational practices that make these patterns reliable in production.

Vector Similarity Search

Setting Up VSS Extension

The vector similarity search (vss) extension is what turns DuckDB from an analytical database into a vector store. Like most DuckDB extensions, it is installed and loaded at the session level — the two commands below fetch the extension from the official registry and activate it in the current connection. Installing and loading are deliberately separate steps because the same INSTALL can be shared across databases and machines while LOAD is scoped to a connection. The verification query at the end confirms the extension is present by inspecting the registry of available extensions.

-- Install and load vector search extension
INSTALL vss;
LOAD vss;

-- Check vss is available
SELECT * FROM duckdb_extensions() WHERE extension_name = 'vss';

The extension needs no external server and no special runtime, which is the core appeal of DuckDB for AI workloads: vectors live in the same database as the rest of your analytical data, and you query them with the same engine. Once the extension is loaded, vector columns are declared as FLOAT[n] arrays, which are stored efficiently and supported by the full SQL type system. The next section shows how to define those columns and build the index that makes search fast.

Creating Vector Tables

The schema below declares a table for document embeddings and the index that accelerates search over it. The key type is FLOAT[384], a fixed-length array that matches the embedding dimensionality of the MiniLM sentence-transformer model used throughout this guide. The id primary key is important for joining results back to source documents, and document_id provides the foreign relationship to the documents table we create later in the RAG section. The dimension is baked into the type, so storing a vector of the wrong length fails at insertion time rather than silently corrupting later queries.

The second statement builds an HNSW (hierarchical navigable small world) graph index over the embedding column. HNSW is the default index for DuckDB vector search because it offers an excellent balance of build time, query speed, and recall. The two tuning parameters are meaningful: ef_construction controls how thoroughly the graph is built at insert time (higher values give better recall at the cost of slower builds), while ef_search controls how thoroughly the graph is searched per query (higher values improve recall but increase latency). Starting values of two hundred and fifty, as shown, are sensible for most workloads.

-- Create table with vector column
CREATE TABLE document_embeddings (
    id INTEGER PRIMARY KEY,
    document_id INTEGER,
    text_content TEXT,
    embedding FLOAT[384]  -- 384 dimensions for MiniLM
);

-- Create HNSW index for fast search
CREATE INDEX idx_embedding ON document_embeddings 
USING HNSW (embedding)
WITH (ef_construction = 200, ef_search = 50);

The relationship between dimension and model is worth internalizing: the embedding dimension is a property of the model you choose, not of DuckDB, and you must declare it to match. The MiniLM-L6 model used here produces 384-dimensional vectors, while OpenAI’s text-embedding- ada-002 produces 1536 and BGE models commonly use 768. Changing models later therefore means changing your table schema, so it pays to pick an embedding model before you design the storage layer. The next section demonstrates the full insert flow from Python.

Inserting Vectors

With the table and index in place, the Python block below shows the complete flow of generating embeddings with sentence-transformers and writing them into DuckDB. The pattern is straightforward: connect to a DuckDB file, load the vss extension on that connection, create the schema if it does not exist, then loop over documents and insert each one with a parameterized query. Parameterization matters here — values are bound with question-mark placeholders rather than interpolated into SQL strings, which prevents SQL injection and lets the engine reuse the prepared statement across inserts.

Two details in this block are easy to miss but important. First, the SentenceTransformer.encode() method returns a NumPy array, which must be converted to a plain Python list with .tolist() before insertion because DuckDB’s Python driver cannot bind raw NumPy arrays directly. Second, note that the index is created before any data is inserted; HNSW is an insertable index, so this ordering is valid and means every row is indexed as it lands. For bulk loading you can insert a large batch first and build the index afterward, which is often faster.

import duckdb
import numpy as np
from sentence_transformers import SentenceTransformer

# Connect to DuckDB
con = duckdb.connect('vectors.db')
con.execute("INSTALL vss")
con.execute("LOAD vss")

# Create table
con.execute("""
    CREATE TABLE IF NOT EXISTS embeddings (
        id INTEGER,
        text TEXT,
        embedding FLOAT[384]
    )
""")

# Create index
con.execute("""
    CREATE INDEX IF NOT EXISTS idx_embedding 
    ON embeddings USING HNSW (embedding)
""")

# Generate embeddings using sentence-transformers
model = SentenceTransformer('all-MiniLM-L6-v2')

documents = [
    (1, "Python is a high-level programming language"),
    (2, "Machine learning is a subset of AI"),
    (3, "Data science combines statistics and programming"),
    (4, "Neural networks are inspired by biological brains"),
    (5, "Natural language processing deals with text data")
]

for doc_id, text in documents:
    embedding = model.encode(text)
    # Convert numpy array to list
    embedding_list = embedding.tolist()
    con.execute(
        "INSERT INTO embeddings VALUES (?, ?, ?)",
        (doc_id, text, embedding_list)
    )

print("Inserted embeddings")

The result of this flow is a queryable corpus: every document has a text representation and a dense vector, and the HNSW index is ready to accelerate search. The choice of embedding model determines the semantic quality of retrieval, and the same model must be used at query time as at insert time, or the distance computations will be meaningless. Having populated the table, the next section shows the SQL patterns for actually searching it.

Vector Similarity Search

Searching is where the vss extension shines, because it exposes vector operations as plain SQL functions. The first query uses array_cosine_distance to rank documents by cosine similarity, which is the default and usually the best choice for text embeddings because it measures direction rather than magnitude and is insensitive to how the embedding model scales its outputs. The second query swaps in array_distance for Euclidean distance, which emphasizes magnitude and may be preferred when vectors are normalized or when magnitude carries meaning in the domain. Both functions order results ascending and return the nearest neighbors via LIMIT.

The third query adds a filtering condition to the same distance function, restricting results to documents within a distance threshold. This pattern — nearest-neighbor search combined with a metric predicate — is common in real retrieval systems because it lets you enforce a minimum relevance bar, so queries that have no close match return an empty set instead of a best-effort garbage match. Note that the placeholder [0.1, 0.2, ...] in the examples must be replaced with a full vector of the declared dimension; in practice it is usually bound as a parameter from Python.

-- Cosine distance search
SELECT 
    id,
    text,
    array_cosine_distance(embedding, [0.1, 0.2, ...]) as distance
FROM embeddings
ORDER BY distance
LIMIT 5;

-- Euclidean distance
SELECT 
    id,
    text,
    array_distance(embedding, [0.1, 0.2, ...]) as distance
FROM embeddings
ORDER BY distance
LIMIT 5;

-- Filtered search
SELECT 
    id,
    text,
    array_cosine_distance(embedding, [0.1, 0.2, ...]) as distance
FROM embeddings
WHERE array_cosine_distance(embedding, [0.1, 0.2, ...]) < 0.5
ORDER BY distance
LIMIT 5;

Because distance predicates are just SQL expressions, you can compose them with any other filter — metadata columns, joins to document tables, even time-based conditions — giving you a hybrid retrieval engine that does both structured filtering and semantic search in one query. This compositional power is the main advantage DuckDB has over purpose-built vector databases for analytical workloads: you never need to move data between a vector store and a data warehouse. The next section builds on these primitives to assemble a complete RAG pipeline.

Building RAG Pipelines

RAG Architecture with DuckDB

Retrieval-augmented generation combines a retrieval step with an LLM: relevant documents are fetched first, then fed to the model as context so it can answer with grounded, up-to-date information instead of relying only on its training data. The class below wires that pipeline together with DuckDB as the vector store. It manages two tables — one for document metadata and one for text chunks with their embeddings — and provides three operations that mirror the RAG lifecycle: ingest_document to split, embed, and store content; retrieve to find the most relevant chunks for a question; and answer to hand those chunks to an LLM and generate a response.

The design embeds several good decisions. Chunking is done with a simple fixed-size window in the ingest step, and each chunk stores its index so the original document order can be reconstructed. Retrieval returns chunks together with their distance scores, which are used in the answer step to build a context budget — the code accumulates chunks only until a max_context limit is reached, prioritizing the most relevant material. This keeps the prompt compact and reduces cost, since LLM pricing is typically per token.

import duckdb
import numpy as np
from sentence_transformers import SentenceTransformer
import openai

class DuckDBRAG:
    """RAG pipeline using DuckDB for vector storage."""
    
    def __init__(self, db_path='rag.db'):
        self.con = duckdb.connect(db_path)
        self.con.execute("INSTALL vss")
        self.con.execute("LOAD vss")
        self._init_schema()
        self.model = SentenceTransformer('all-MiniLM-L6-v2')
    
    def _init_schema(self):
        """Initialize database schema."""
        self.con.execute("""
            CREATE TABLE IF NOT EXISTS chunks (
                id INTEGER PRIMARY KEY,
                document_id INTEGER,
                chunk_text TEXT,
                chunk_index INTEGER,
                embedding FLOAT[384]
            )
        """)
        
        self.con.execute("""
            CREATE TABLE IF NOT EXISTS documents (
                id INTEGER PRIMARY KEY,
                title TEXT,
                source TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Create HNSW index
        self.con.execute("""
            CREATE INDEX IF NOT EXISTS idx_chunks_embedding 
            ON chunks USING HNSW (embedding)
        """)
    
    def ingest_document(self, document_id, title, source, text, chunk_size=500):
        """Ingest document with chunking and embeddings."""
        # Split into chunks
        chunks = [text[i:i+chunk_size] 
                  for i in range(0, len(text), chunk_size)]
        
        # Insert document metadata
        self.con.execute(
            "INSERT INTO documents (id, title, source) VALUES (?, ?, ?)",
            (document_id, title, source)
        )
        
        # Generate embeddings and insert chunks
        for i, chunk in enumerate(chunks):
            embedding = self.model.encode(chunk)
            self.con.execute("""
                INSERT INTO chunks (document_id, chunk_text, chunk_index, embedding)
                VALUES (?, ?, ?, ?)
            """, (document_id, chunk, i, embedding.tolist()))
        
        self.con.commit()
        return len(chunks)
    
    def retrieve(self, query, top_k=5):
        """Retrieve relevant chunks for query."""
        query_embedding = self.model.encode(query)
        
        results = self.con.execute("""
            SELECT 
                chunk_text,
                document_id,
                array_cosine_distance(embedding, ?) as distance
            FROM chunks
            ORDER BY distance
            LIMIT ?
        """, (query_embedding.tolist(), top_k)).fetchall()
        
        return results
    
    def answer(self, question, max_context=2000):
        """Answer question using RAG."""
        # Retrieve relevant chunks
        chunks = self.retrieve(question, top_k=10)
        
        # Build context
        context = ""
        for chunk, doc_id, distance in chunks:
            if len(context) + len(chunk) > max_context:
                break
            context += chunk + "\n\n"
        
        # Generate answer using LLM
        prompt = f"""Based on the following context, answer the question.

Context:
{context}

Question: {question}

Answer:"""
        
        response = openai.ChatCompletion.create(
            model="gpt-4",
            messages=[{"role": "user", "content": prompt}],
            temperature=0
        )
        
        return response.choices[0].message.content
    
    def close(self):
        """Close connection."""
        self.con.close()

# Usage
rag = DuckDBRAG('knowledge.db')

# Ingest documents
with open('article.txt') as f:
    rag.ingest_document(1, 'AI Article', 'article.txt', f.read())

# Ask questions
answer = rag.answer("What is machine learning?")
print(answer)

rag.close()

The pipeline as written is deliberately minimal, but it maps cleanly onto production systems. The retrieve method could easily add a metadata filter — for example, restricting results to a given document or time range — and the answer method could be extended with prompts that instruct the model to cite sources or say when the context is insufficient. The embedding model and the OpenAI client are both swappable, which makes this class a reasonable starting template rather than a fixed implementation. The key architectural takeaway is that DuckDB sits at the center, unifying storage, indexing, and querying for the whole pipeline.

Feature Engineering

ML Feature Creation

Feature engineering is where DuckDB’s analytical heritage pays off, because SQL is an exceptionally concise language for aggregations that would take many lines of pandas. The FeatureEngineer class below demonstrates the pattern: register a pandas DataFrame as a DuckDB view, run SQL that computes features, and materialize the result back into a DataFrame for the model. The key mechanism is the CREATE OR REPLACE VIEW statement, which exposes an in-memory DataFrame to the SQL engine without copying it into a persistent table — DuckDB can query the data directly from the Arrow or pandas buffer.

The first method, create_user_features, builds a user-level feature row from an event log. It computes classic behavioral aggregates: total events, distinct session count, average duration, first and last event timestamps, purchase and add-to-cart counts, average purchase amount, and the number of active days. Notice how much work is compressed into a single GROUP BY user_id — the same query in pandas would require multiple groupby passes and merges. This is the primary reason data scientists reach for DuckDB when event tables grow into millions of rows.

import duckdb
import numpy as np

class FeatureEngineer:
    """Feature engineering with DuckDB."""
    
    def __init__(self, db_path='features.db'):
        self.con = duckdb.connect(db_path)
    
    def create_user_features(self, events_df):
        """Create user-level features from events."""
        # Register DataFrame as view
        self.con.execute("CREATE OR REPLACE VIEW events AS SELECT * FROM events_df")
        
        # Create features
        self.con.execute("""
            CREATE TABLE user_features AS
            SELECT 
                user_id,
                COUNT(*) as total_events,
                COUNT(DISTINCT session_id) as unique_sessions,
                AVG(duration) as avg_duration,
                MIN(event_time) as first_event,
                MAX(event_time) as last_event,
                SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) as purchase_count,
                SUM(CASE WHEN action = 'add_to_cart' THEN 1 ELSE 0 END) as cart_adds,
                AVG(CASE WHEN action = 'purchase' THEN amount ELSE NULL END) as avg_purchase_amount,
                DATEDIFF('day', MIN(event_time), MAX(event_time)) as active_days
            FROM events
            GROUP BY user_id
        """)
        
        return self.con.execute("SELECT * FROM user_features").df()
    
    def create_time_features(self):
        """Create time-based features."""
        self.con.execute("""
            CREATE TABLE time_features AS
            SELECT 
                user_id,
                EXTRACT(HOUR FROM event_time) as hour_of_day,
                EXTRACT(DOW FROM event_time) as day_of_week,
                EXTRACT(MONTH FROM event_time) as month,
                COUNT(*) as event_count
            FROM events
            GROUP BY 1, 2, 3, 4
        """)
    
    def create_aggregation_features(self):
        """Create aggregation features."""
        self.con.execute("""
            CREATE TABLE agg_features AS
            SELECT 
                user_id,
                -- Rolling aggregations
                COUNT(*) OVER (
                    PARTITION BY user_id 
                    ORDER BY event_time 
                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
                ) as rolling_7day_events,
                
                AVG(amount) OVER (
                    PARTITION BY user_id 
                    ORDER BY event_time 
                    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
                ) as rolling_30day_avg_amount,
                
                -- Running total
                SUM(amount) OVER (
                    PARTITION BY user_id 
                    ORDER BY event_time
                ) as running_total,
                
                -- Lead/lag
                LAG(amount, 1) OVER (
                    PARTITION BY user_id 
                    ORDER BY event_time
                ) as prev_amount,
                
                LEAD(amount, 1) OVER (
                    PARTITION BY user_id 
                    ORDER BY event_time
                ) as next_amount
            FROM events
            WHERE amount IS NOT NULL
        """)

# Usage
engineer = FeatureEngineer('ml_features.db')

# Create features from DataFrame
features = engineer.create_user_features(events_df)

# Export to ML pipeline
X = features.drop('user_id', axis=1).values
y = features['target'].values

The remaining two methods extend the pattern along different axes. create_time_features pivots the events into time buckets — hour of day, day of week, and month — which are the raw ingredients for seasonality features in recommender and demand-forecasting models. create_aggregation_features uses window functions to compute rolling statistics: a seven- day event count, a thirty-day average spend, a running total, and lead/lag values for the previous and next purchase amounts. Window functions are the crucial tool here, because they compute these sequences in one pass without self-joins, and they are exactly what distinguishes a real feature pipeline from a toy script.

Model Evaluation

Query-Level Features

DuckDB is equally useful on the evaluation side of the ML lifecycle, where it can act as a lightweight feature store for model inputs. The ModelFeatures class below demonstrates this role: a table named query_features holds a row per query, populated with descriptive statistics such as query length, number of filters, presence of joins or aggregations, and a hand-built complexity score. Models that predict query runtime or resource usage can then be served by fetching these features on demand. The first method returns features for a single query using a parameterized lookup; the second fetches many queries in one round trip.

import duckdb

class ModelFeatures:
    """Generate features for model evaluation."""
    
    def __init__(self, db_path='model_features.db'):
        self.con = duckdb.connect(db_path)
    
    def get_prediction_features(self, query):
        """Get features for a specific query."""
        result = self.con.execute("""
            SELECT 
                query_length,
                num_filters,
                has_join,
                has_aggregation,
                num_ctes,
                complexity_score
            FROM query_features
            WHERE query = ?
        """, [query]).fetchone()
        
        return result
    
    def batch_features(self, queries):
        """Get features for multiple queries."""
        placeholders = ','.join(['?' for _ in queries])
        results = self.con.execute(f"""
            SELECT 
                query,
                query_length,
                num_filters,
                has_join,
                has_aggregation,
                complexity_score
            FROM query_features
            WHERE query IN ({placeholders})
        """, queries).fetchall()
        
        return {r[0]: r[1:] for r in results}

# Usage
feature_store = ModelFeatures('ml.db')
features = feature_store.get_prediction_features(
    "SELECT * FROM users WHERE age > 25"
)

The batch_features method is the more production-relevant of the two, because it constructs a parameterized IN clause by joining as many placeholders as there are queries. Passing the queries as bound parameters rather than embedding them in SQL is important for both correctness and performance: it avoids escaping bugs and lets the planner cache the prepared statement. Returning the results as a dictionary keyed by query string makes the store trivially usable as a lookup table inside a prediction service. This pattern generalizes to any feature that can be expressed as a SQL query over your analytical tables.

Data Preparation for ML

Train/Test Split

Before a model can be trained, the data must be split into training and held-out evaluation sets, and the snippet below shows two complementary approaches with DuckDB. The first path extracts rows into Python and uses scikit-learn’s train_test_split for a reproducible, stratified-capable split with a fixed random seed. The second path performs the split entirely inside SQL using a random predicate: each row is assigned to the training table with eighty percent probability and to the test table with the remaining twenty percent. The Python-based approach is preferable when you need reproducibility or class balance; the SQL approach is faster for very large tables because no data leaves the engine.

import duckdb
from sklearn.model_selection import train_test_split

con = duckdb.connect('ml.db')

# Get all data
data = con.execute("""
    SELECT 
        feature1,
        feature2,
        feature3,
        target
    FROM ml_table
""").fetchall()

X = [row[:-1] for row in data]
y = [row[-1] for row in data]

# Split
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

# Create train/test tables
con.execute("CREATE TABLE train_data AS SELECT * FROM ml_table WHERE RANDOM() < 0.8")
con.execute("CREATE TABLE test_data AS SELECT * FROM ml_table WHERE RANDOM() >= 0.8")

A caution about the SQL approach: because RANDOM() is evaluated independently for each row, the two statements above do not partition the data into complementary sets — a row can, in principle, be picked by neither or both. For a truly complementary split you would add a row number and assign by hash or modular arithmetic. The trade-off is worth knowing even if the probabilistic version is good enough for exploratory work, and it is exactly the kind of subtlety that separates a working demo from a trustworthy evaluation pipeline. For rigorous modeling, prefer the scikit-learn split shown first.

Cross-Validation

Cross-validation gives a more honest estimate of model quality than a single split by training and evaluating the model multiple times on different slices of the data. The block below integrates scikit-learn’s KFold with DuckDB: the data is read once into a DataFrame, the fold indices are generated in Python, and each fold’s training and validation slices are produced from the same array indices. The example loops over five shuffled folds and shows where each fold’s score would be recorded. In a complete implementation you would also persist the per-fold metrics back into DuckDB — writing them to a cv_results table — so the full evaluation history lives in one place.

import duckdb
import numpy as np
from sklearn.model_selection import KFold

con = duckdb.connect('ml.db')

# Get data
data = con.execute("SELECT * FROM ml_table").df()
X = data.drop('target', axis=1).values
y = data['target'].values

# K-Fold cross-validation
kf = KFold(n_splits=5, shuffle=True, random_state=42)

scores = []
for fold, (train_idx, val_idx) in enumerate(kf.split(X)):
    X_train, X_val = X[train_idx], X[val_idx]
    y_train, y_val = y[train_idx], y[val_idx]
    
    # Train model (example with simple model)
    # model.fit(X_train, y_train)
    # score = model.score(X_val, y_val)
    # scores.append(score)
    
    # Store fold results in DuckDB
    con.execute("""
        INSERT INTO cv_results VALUES (?, ?)
    """, (fold, np.mean(scores)))

print(f"Mean CV Score: {np.mean(scores):.4f}")

Two integration details stand out. First, the fold management stays in Python while the data itself stays in DuckDB, which keeps the pattern simple without sacrificing the ability to scale by pushing work back into SQL when the dataset grows. Second, writing results back to DuckDB means evaluation artifacts are queryable alongside the source data, enabling comparisons across experiments and model versions. With training and evaluation covered, the final implementation section shows how a trained model is put into production as a batch scoring service.

Production ML Integration

Batch Prediction

Many ML workloads are batch rather than interactive: periodically score a large set of rows and write the predictions back to the database. The BatchPredictor class below shows the canonical pattern. A serialized model is loaded from disk with joblib, features are read from a prediction_input table, and predictions are written back by updating each row. The flow is a loop because DuckDB’s Python API updates are statement-based; for very large tables you would instead predict in chunks and use bulk insertion, but the update-by- primary-key pattern keeps the example self-contained and correct for moderate sizes.

import duckdb
import joblib

class BatchPredictor:
    """Batch prediction using DuckDB."""
    
    def __init__(self, db_path, model_path):
        self.con = duckdb.connect(db_path)
        self.model = joblib.load(model_path)
    
    def predict(self):
        """Run batch prediction."""
        # Get features
        features = self.con.execute("""
            SELECT 
                feature1,
                feature2,
                feature3
            FROM prediction_input
        """).fetchall()
        
        # Predict
        predictions = self.model.predict(features)
        
        # Store predictions
        for i, pred in enumerate(predictions):
            self.con.execute("""
                UPDATE prediction_input
                SET prediction = ?
                WHERE rowid = ?
            """, (pred, i + 1))
        
        self.con.commit()
        return predictions
    
    def predict_proba(self):
        """Get prediction probabilities."""
        features = self.con.execute("SELECT * FROM prediction_input").fetchall()
        probas = self.model.predict_proba(features)
        
        # Store probabilities
        for i, proba in enumerate(probas):
            self.con.execute("""
                UPDATE prediction_input
                SET prob_class_0 = ?, prob_class_1 = ?
                WHERE rowid = ?
            """, (proba[0], proba[1], i + 1))

# Usage
predictor = BatchPredictor('predictions.db', 'model.joblib')
predictions = predictor.predict()

The second method, predict_proba, demonstrates the same loop for probabilistic output, storing each class probability in its own column so downstream consumers can apply decision thresholds without reloading the model. This separation of concerns — the model owns scoring, DuckDB owns storage and retrieval — is the essence of using DuckDB as the integration point in an ML platform. Prediction inputs and outputs coexist in the same database as the features that produced them, which makes auditing, retraining, and reproducibility dramatically simpler than when scores are written to CSV files.

Best Practices

Vector Search Optimization

The final section distills the operational lessons from the rest of the article into a compact set of practices. For vector search, the most consequential choice is embedding dimensionality, because it is a schema decision that is painful to change later. The comment block records the dimensions of the three most common model families — MiniLM at 384, OpenAI’s ada-002 at 1536, and BGE at 768 — so you can make the choice deliberately rather than by accident. Lower dimensions mean smaller indexes and faster scans but generally lower retrieval quality, while higher dimensions capture more nuance at the cost of storage and latency.

-- Choose appropriate dimensions
-- MiniLM: 384
-- ada-002: 1536
-- BGE: 768

-- Index tuning
CREATE INDEX idx_embedding ON table USING HNSW (embedding)
WITH (ef_construction = 200, ef_search = 50);

The HNSW tuning parameters follow the same logic you saw in the index-creation section: ef_construction trades build time for graph quality, and ef_search trades per-query latency for recall. A good workflow is to tune ef_search first on a held-out set of queries, measuring recall against an exact scan, and only raise ef_construction if the graph itself is the bottleneck. Remember that these parameters only affect the approximate search path — if you ever need exact answers, a brute-force distance scan with the same functions remains available.

ML Pipeline Tips

The last block collects the general practices that keep ML pipelines built on DuckDB fast and correct. Using the right data types — integers for categoricals and doubles for continuous values — avoids silent precision loss and keeps joins and aggregations efficient, since DuckDB chooses algorithms based on column types. The second practice, chunked processing, addresses the interaction between DuckDB and Python memory: rather than pulling an entire result set into memory at once, iterate over the data in fixed-size batches so peak memory stays bounded no matter how large the underlying table grows.

# Use appropriate data types
# INTEGER for categorical
# DOUBLE for continuous

# Batch processing
# Process in chunks to avoid memory issues
chunk_size = 100000
for i in range(0, len(data), chunk_size):
    chunk = data[i:i+chunk_size]
    process(chunk)

Chunking is especially relevant in AI workflows because the same data is often processed twice: once inside DuckDB for feature computation, and once in Python for model inference. Bounding the transfer between the two keeps both memory profiles predictable. Taken together, the practices in this section — deliberate embedding dimensionality, tuned HNSW parameters, disciplined typing, and chunked transfer — are the difference between a working notebook and a pipeline that stays reliable as data grows. They are the habits to carry forward into production DuckDB AI systems.

Resources

• DuckDB vss Extension
• Sentence Transformers
• OpenAI API

Conclusion

DuckDB provides excellent support for AI applications through its vector search capabilities and seamless Python integration. From RAG pipelines to ML feature engineering, DuckDB offers a unified platform for analytics and AI workflows.

In the next article, we’ll explore real-world DuckDB use cases, including production patterns and implementation strategies.