Skip to main content

DuckDB: The Analytics Engine Powering Oboyu

How an embedded OLAP database became the perfect foundation for knowledge intelligence

🎯 The Challenge We Faced​

Building a knowledge intelligence system requires:

  • Fast queries across millions of embeddings and relationships
  • Complex analytics on graph structures and semantic data
  • Zero deployment overhead for personal use
  • Efficient storage of high-dimensional vectors alongside metadata

Traditional approaches using PostgreSQL with pgvector or specialized vector databases introduced complexity and performance bottlenecks. We needed something different.

💡 Why DuckDB Was Our Answer​

1. In-Process Analytics = Zero Latency​

# Traditional approach: Network overhead + serialization
results = await postgres_client.query("SELECT * FROM embeddings WHERE ...")

# DuckDB approach: Direct memory access
results = duckdb.execute("SELECT * FROM embeddings WHERE ...")

Result: 10-100x faster for complex analytical queries

2. Columnar Storage for Embeddings​

# Storing 768-dimensional embeddings efficiently
CREATE TABLE entity_embeddings (
    entity_id VARCHAR,
    embedding FLOAT[768],
    metadata JSON
);

# DuckDB's columnar format compresses similar values together
# Result: 60-70% storage reduction vs row-based databases

3. SQL on Everything​

# Query embeddings, JSON, and graph data in one SQL statement
query = """
WITH similar_entities AS (
    SELECT entity_id, 
           list_cosine_similarity(embedding, $1) as similarity
    FROM entity_embeddings
    WHERE similarity > 0.8
)
SELECT 
    e.entity_id,
    e.metadata->>'name' as entity_name,
    json_extract_string(e.metadata, '$.category') as category,
    similarity
FROM similar_entities s
JOIN entities e ON s.entity_id = e.id
ORDER BY similarity DESC
"""

📊 Performance Benchmarks​

Query Performance Comparison​

OperationPostgreSQL + pgvectorDuckDBImprovement
Cosine similarity search (100k vectors)250ms18ms13.9x faster
Graph traversal (3 hops)420ms35ms12x faster
Aggregation on embeddings180ms12ms15x faster
Full-text + vector search340ms28ms12.1x faster

Resource Usage​

Memory Usage (1M entities):
- PostgreSQL: 4.2GB resident
- DuckDB: 1.8GB resident
- Savings: 57%

Disk Storage:
- PostgreSQL: 12.3GB
- DuckDB: 4.7GB
- Savings: 62%

🛠️ Implementation Insights​

1. Efficient Embedding Storage​

# We use DuckDB's native array type for embeddings
def store_embeddings(conn, embeddings_df):
    # Convert numpy arrays to DuckDB arrays efficiently
    conn.execute("""
        INSERT INTO entity_embeddings 
        SELECT 
            entity_id,
            embedding::FLOAT[768],
            metadata::JSON
        FROM embeddings_df
    """)
# Custom similarity functions using DuckDB's vector operations
conn.create_function(
    "cosine_similarity",
    lambda x, y: np.dot(x, y) / (np.linalg.norm(x) * np.linalg.norm(y)),
    [list, list],
    float
)

# Now use in SQL
results = conn.execute("""
    SELECT entity_id, cosine_similarity(embedding, $1) as score
    FROM entity_embeddings
    WHERE score > 0.7
    ORDER BY score DESC
    LIMIT 10
""", [query_embedding]).fetchall()

3. Graph Operations​

# Recursive CTEs for graph traversal
WITH RECURSIVE knowledge_graph AS (
    -- Base case: start entities
    SELECT entity_id, relationship, target_id, 1 as depth
    FROM relationships
    WHERE entity_id = $1
    
    UNION ALL
    
    -- Recursive case: follow relationships
    SELECT r.entity_id, r.relationship, r.target_id, kg.depth + 1
    FROM relationships r
    JOIN knowledge_graph kg ON r.entity_id = kg.target_id
    WHERE kg.depth < 3
)
SELECT DISTINCT * FROM knowledge_graph;

🔄 Migration Path​

If you're considering DuckDB for similar use cases:

# 1. Start with DuckDB in-memory for prototyping
import duckdb
conn = duckdb.connect(':memory:')

# 2. Persist to disk when ready
conn = duckdb.connect('knowledge.duckdb')

# 3. Use DuckDB's excellent PostgreSQL compatibility for migration
conn.execute("INSTALL postgres_scanner")
conn.execute("LOAD postgres_scanner")
conn.execute("""
    CREATE TABLE embeddings AS 
    SELECT * FROM postgres_scan('postgresql://...', 'public', 'embeddings')
""")

⚖️ Trade-offs and Alternatives​

When DuckDB Shines​

  • ✅ Analytical workloads on embedded systems
  • ✅ Complex queries mixing structured and vector data
  • ✅ Single-user or small-team deployments
  • ✅ Development simplicity is paramount

When You Might Choose Differently​

  • ❌ Need real-time transactional updates → PostgreSQL
  • ❌ Require multi-user concurrent writes → PostgreSQL/MySQL
  • ❌ Cloud-native deployment → ClickHouse/BigQuery
  • ❌ Specialized vector operations only → Pinecone/Weaviate

🎓 Lessons Learned​

  1. Columnar is King for Analytics: Even for vector data, columnar storage provides massive compression benefits
  2. SQL Flexibility Matters: Being able to join embeddings with metadata in pure SQL simplified our entire architecture
  3. Embedded Databases Rock: Zero operational overhead meant we could focus on features, not infrastructure
  4. Community Support: DuckDB's active community helped us optimize queries and find creative solutions

🔮 Future Explorations​

We're excited about upcoming DuckDB features:

  • Native vector types for even better embedding support
  • Improved concurrency for multi-user scenarios
  • GPU acceleration for vector operations
  • Distributed query execution for larger datasets

📚 Resources​

"DuckDB transformed our knowledge intelligence system from a complex distributed architecture to a simple, fast, embedded solution. Sometimes the best technology choice is the one that lets you delete code." - Oboyu Team