DEV Community

Cover image for MyScaleDB: Why Vector Databases Need SQL (The 2025 Reality Check)
Pascal CESCATO
Pascal CESCATO

Posted on • Originally published at en.benchwiseunderflow.in

MyScaleDB: Why Vector Databases Need SQL (The 2025 Reality Check)

#database #vectordatabase #sql #architecture

Context: I'm architecting a system analyzing 26M+ legal documents per year across 188,000 family law cases. After testing Qdrant, then pgvector, I discovered the real problem wasn't the vector search—it was the Metadata Hell. Here's why MyScaleDB solves it.

The "Vector Silo" Problem

We're in the "Hype Cycle" of Vector Databases. Everyone's rushing to deploy Pinecone, Qdrant, or Weaviate. Don't get me wrong—these are fantastic pieces of engineering. I've used them in production.

But as an architect designing systems for millions of complex documents (like legal case files with 26M+ records/year), I kept hitting the same architectural wall: The Metadata Hell.

In a real-world enterprise application, you never just ask:

"Give me documents semantically similar to 'breach of contract'."

You ask:

"Give me documents similar to 'breach of contract', BUT only from 2023, related to Family Law, where the Judge is 'Smith'."

With a pure vector DB, you're forced to do post-filtering (fetch 10k vectors, filter in Python → slow) or rely on custom DSLs that lack the expressive power of SQL JOINs.

We're re-inventing the wheel. We're trying to rebuild SQL filtering inside engines that weren't built for it.

1. The Great Vector Standoff: Why MyScaleDB?

Why choose MyScaleDB over the established giants? Here's the architectural breakdown based on TCO, scalability, and real-world operational simplicity.

vs. PostgreSQL + pgvector

The MVP Champion.

pgvector is amazing for starting out. It keeps your stack simple.

The Problem: PostgreSQL is a Row-Store designed for transactional integrity (ACID), not for scanning millions of vectors. Even with HNSW indexes, pgvector is resource-hungry (RAM) and struggles with heavy analytical queries. When you try to join a vector search with a heavy aggregate query on 100M rows, Postgres chokes.

My Experience: I've run pgvector on 5M vectors (768-dim). It works, but query latency degrades significantly beyond 10M vectors, especially with complex WHERE clauses. PostgreSQL isn't optimized for columnar scans.

vs. Qdrant / Pinecone / Weaviate

The Specialized Silos.

These are "Vector Native". They're fast.

The Problem: They create a Data Silo. You end up syncing your metadata between your SQL DB (YugabyteDB/Postgres) and your Vector DB. You deal with eventual consistency issues. And most importantly: JSON metadata filters are not optimized like SQL WHERE clauses. They scan JSON; SQL engines scan indexes.

My Experience: In the early prototype with Qdrant, I had to:

  1. Query Qdrant for semantic similarity
  2. Extract document IDs
  3. Query YugabyteDB for metadata (dates, parties, judges)
  4. Merge results in Python
  5. Apply final filters

Result: 3 round-trips, 400ms+ latency, eventual consistency headaches.

vs. OpenSearch / Elasticsearch (k-NN)

The Search Engines.

The Problem: They're built on Lucene. Fantastic for keyword search (BM25) but heavy on infrastructure (Java heap issues). Their vector implementation is often a "plugin" rather than a native core feature.

The MyScale Difference

MyScaleDB is not a "Vector DB" trying to add SQL. It is a SQL OLAP database that treats vectors as a first-class citizen. It solves the "Filter + Search" problem by using columnar storage for metadata (instant filtering) and a proprietary algorithm (MSTG) for vectors.

My Experience: Deployed MyScaleDB in Docker Compose stacks multiple times. It's surprisingly simple—single binary, ClickHouse-compatible clients, no Zookeeper/Kafka overhead. Resource usage is reasonable: ~8GB RAM for 10M vectors (768-dim) with metadata, vs 16GB+ for equivalent pgvector setup.

2. The Lineage: Standing on the Shoulders of a Giant (ClickHouse)

To understand why MyScaleDB is fast, you have to look at its DNA. MyScaleDB is built on top of ClickHouse.

ClickHouse is widely recognized as the fastest open-source OLAP (Online Analytical Processing) database on the planet. It uses:

  • Columnar Storage: It only reads the columns you ask for (great for metadata filtering)
  • Vectorized Execution: It processes data in blocks, not row-by-row
  • Compression: Massive disk savings

The History:

The MyScale team didn't start from scratch (dangerous for a database). They took the ClickHouse core and developed their own MSTG (Multi-Scale Tree Graph) algorithm, designed specifically for filtered vector search.

Why MSTG matters: Traditional HNSW algorithms suffer from the "connectivity problem"—when you apply heavy filters (e.g., "WHERE year=2023"), the graph's connectivity breaks down, degrading recall and speed. MSTG maintains high recall (98.5%) and performance even when filtering down to 1% of your dataset. This is MyScaleDB's algorithmic innovation—not just adapting existing vector indexes, but redesigning them for the SQL + vector use case.

Performance proof: On LAION 5M, MyScaleDB achieves 150 QPS at 98.5% recall. For comparison, at equivalent accuracy:

  • Qdrant (HNSW-based): 81 QPS (1.85x slower)
  • Pinecone s1: 9 QPS (16.7x slower)
  • Weaviate: 66 QPS (2.3x slower)

The ACID Trade-off (Important Note):

ClickHouse—and by extension MyScaleDB—is not ACID-compliant in the traditional sense. It's eventually consistent (updates are batched). This is fine for analytics and AI retrieval workloads (our vectors don't need transactions), but not suitable for transactional data (billing, user accounts).

For my use case: We use YugabyteDB for ACID transactions (documents, billing, audit trail) and MyScaleDB for vector search. Best of both worlds.

3. Benchmark Reality Check: The Numbers Don't Lie

Official MyScaleDB Benchmarks (LAION 5M Dataset)

MyScaleDB's official benchmarks on the LAION 5M dataset (5 million 768-dimensional vectors) demonstrate significant performance advantages. At 98.5% recall accuracy, MyScaleDB achieves over 150 QPS with 25.8ms average latency, while Pinecone s1 reaches only 9 QPS with over 400ms latency. Qdrant achieves 81 QPS at around 100ms, while Weaviate and Zilliz Cloud both deliver approximately 65 QPS at 60ms latency.

Database QPS Avg Latency Ingestion Time Monthly Cost Cost/100 QPS
MyScaleDB 150 25.8 ms 30 min $120* $80*
Qdrant (4c32g) 81 ~100 ms 145 min $273 $337
Pinecone s1 9 >400 ms 53 min $69** $767
Weaviate 66 ~60 ms 72 min $690 $1,045
Zilliz Cloud (1CU) 63 ~60 ms 113 min $186 $295

*Cloud Version - Selfhosted: Server costs
**Base GCP price; actual production cost higher

MyScaleDB demonstrates 3.6x better cost-performance ratio compared to other top-performing specialized vector databases at equivalent accuracy levels. For data ingestion, MyScaleDB completes indexing of 5 million vectors in approximately 30 minutes, significantly faster than competitors: Pinecone s1 (53 minutes), Weaviate (72 minutes), Zilliz Cloud (113 minutes), and Qdrant (145 minutes).

Sources: MyScaleDB Official Benchmarks (open-source, continuously updated)

For production scale (15M vectors at production scale):

  • 3x fewer servers than Qdrant (150 QPS vs 81 QPS per pod)
  • 16x fewer than Pinecone s1 (150 QPS vs 9 QPS)
  • Ingestion advantage: 90 minutes vs 435 minutes (Qdrant) for 15M vectors
  • TCO saving: €45-60K/year on infrastructure + operational efficiency

Independent Validation: Specialized vs SQL-Based Vector DBs

The performance gap between specialized vector databases and SQL-based approaches is narrowing. MyScaleDB demonstrates that a SQL OLAP database with native vector support can outperform specialized vector databases by integrating algorithmic innovations (MSTG vs traditional HNSW/IVF) with ClickHouse's columnar architecture.

Key architectural difference:

  • Specialized vector DBs (Pinecone, Qdrant, Weaviate): JSON metadata filters scan documents
  • MyScaleDB: SQL WHERE clauses use columnar indexes (microsecond-level filtering before vector search)

Real-world impact: For queries like "Find similar documents WHERE year=2023 AND judge='Smith'", MyScaleDB filters 10M documents to 2K candidates in <5ms (columnar scan), then performs vector search on 2K vectors. Specialized DBs either post-filter 10K vectors in application code (slow) or use less-optimized metadata filters.

Caveat: These numbers vary by hardware, configuration, and query patterns. For my specific use case, I validated filtered search performance (the "metadata hell" problem) where MyScaleDB's SQL filters excel. Benchmark code is publicly available and continuously updated.

4. Talk is Cheap: The 5-Minute Implementation

As an architect, I don't trust whitepapers. I trust docker run. Here's the minimal stack to prove the "SQL + Vector" power.

A. The Stack (Docker Compose)

No complex setup. It's a single binary.

# docker-compose.yml
services:
  myscaledb:
    image: myscale/myscaledb:latest
    container_name: myscaledb
    ports:
      - "8123:8123" # HTTP Interface
      - "9000:9000" # Native Client
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    volumes:
      - ./myscale_data:/var/lib/clickhouse
Enter fullscreen mode Exit fullscreen mode

Run it:

docker-compose up -d
Enter fullscreen mode Exit fullscreen mode

My Experience: Startup time ~10 seconds. No Kafka/Zookeeper dependencies. ClickHouse clients (DBeaver, Python clickhouse-connect) work out of the box.

B. The Schema (It's Just SQL!)

Connect using any ClickHouse client. Notice the VECTOR index—this is the key.

-- Create a table for Legal Documents
CREATE TABLE legal_docs (
    id UInt64,
    case_id String,
    year UInt32,
    judge_name String,
    content String,
    embedding Array(Float32), -- The Vector Column (768-dim)
    CONSTRAINT vec_idx VECTOR INDEX embedding TYPE MSTG('metric_type=Cosine')
) ENGINE = MergeTree()
ORDER BY (year, id); -- Columnar ordering for fast filtering
Enter fullscreen mode Exit fullscreen mode

Key Points:

  • VECTOR INDEX ... TYPE MSTG: This creates the multi-scale tree graph index
  • ORDER BY (year, id): ClickHouse uses this for columnar compression and fast range scans
  • No manual sharding configuration needed (MyScaleDB handles it)

C. The Python Implementation (WITH Actual Vector Search)

Let's insert dummy data and run a Complex Filtered Vector Search using the index.

import clickhouse_connect
import numpy as np

# 1. Connect (Standard ClickHouse Driver!)
client = clickhouse_connect.get_client(
    host='localhost', 
    port=8123, 
    username='default', 
    password=''
)

# 2. Generate realistic embeddings (768-dim for sentence-transformers)
def generate_embedding():
    return np.random.rand(768).tolist()

# 3. Insert Data (Batch insert is native and fast)
data = [
    [1, 'CASE-2023-001', 2023, 'Judge Smith', 'Divorce settlement agreement...', generate_embedding()],
    [2, 'CASE-2022-999', 2022, 'Judge Jones', 'Criminal liability report...', generate_embedding()],
    [3, 'CASE-2023-002', 2023, 'Judge Smith', 'Custody battle timeline...', generate_embedding()],
    [4, 'CASE-2023-003', 2023, 'Judge Brown', 'Property division dispute...', generate_embedding()],
    [5, 'CASE-2024-001', 2024, 'Judge Smith', 'Child support modification...', generate_embedding()],
]

client.insert(
    'legal_docs', 
    data, 
    column_names=['id', 'case_id', 'year', 'judge_name', 'content', 'embedding']
)

# 4. The "Killer Query" - ACTUAL VECTOR SEARCH with SQL Filters
# This uses the MSTG index, not just distance() calculation!
query_vector = generate_embedding()

sql = f"""
SELECT 
    id, 
    case_id, 
    judge_name,
    content, 
    distance(embedding, {query_vector}) as dist
FROM legal_docs
WHERE 
    year >= 2023                    -- SQL filter (columnar scan, microseconds)
    AND judge_name = 'Judge Smith'  -- Another SQL filter
ORDER BY dist ASC                   -- Vector similarity via MSTG index
LIMIT 5
"""

result = client.query(sql)
for row in result.result_rows:
    print(f"Case {row[1]}: {row[3][:50]}... (distance: {row[4]:.4f})")
Enter fullscreen mode Exit fullscreen mode

Why This Works:

  1. SQL filters execute first (columnar scan on year and judge_name)—this is blazing fast on ClickHouse (microseconds for millions of rows)
  2. MSTG index activates on the filtered subset (only ~2 documents match filters)
  3. Vector similarity computed only on relevant candidates
  4. Total latency: Sub-10ms even on 10M documents

Compare to Qdrant: You'd need to either:

  • Post-filter 10K vectors in Python (slow)
  • OR use Qdrant's filter syntax (not optimized like SQL indexes)

D. Real-World Production Query

Here's what I actually query in production:

-- Find expert testimonies semantically similar to a reference,
-- BUT only where the expert and opposing counsel worked together
-- in the same law firm between 2015-2020 (proximity detection)

WITH expert_history AS (
    SELECT expert_id, law_firm_id, start_date, end_date
    FROM employment_history
    WHERE profession = 'Expert'
),
counsel_history AS (
    SELECT lawyer_id, law_firm_id, start_date, end_date
    FROM employment_history
    WHERE profession = 'Lawyer'
)
SELECT 
    t.id,
    t.case_id,
    t.expert_name,
    distance(t.embedding, {query_vector}) as similarity,
    eh.law_firm_id as shared_firm
FROM testimonies t
JOIN expert_history eh ON t.expert_id = eh.expert_id
JOIN counsel_history ch ON eh.law_firm_id = ch.law_firm_id
WHERE 
    t.year BETWEEN 2023 AND 2024
    AND eh.start_date <= ch.end_date
    AND ch.start_date <= eh.end_date  -- Date overlap (worked together)
    AND DATEDIFF('year', eh.end_date, CURRENT_DATE()) <= 5  -- Within 5 years
ORDER BY similarity ASC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

This is impossible in pure vector DBs. You'd need:

  1. Fetch all testimonies from Qdrant
  2. Query SQL DB for employment overlaps
  3. Merge in Python
  4. Apply vector similarity

With MyScaleDB: One query. 50ms. Done.

5. Operational Simplicity (The Hidden Cost)

What I Learned Deploying MyScaleDB

Setup Complexity: ★★★★☆ (4/5 - Easy)

  • Single Docker image
  • ClickHouse-compatible clients (Python, DBeaver, CLI)
  • No Kafka/Zookeeper/Consul dependencies

Resource Usage: ★★★★☆ (4/5 - Reasonable)

  • 10M vectors (768-dim) + metadata: ~8GB RAM
  • Columnar compression: 3-4x better than row-store
  • CPU: Efficient (vectorized execution)

Operational Overhead: ★★★☆☆ (3/5 - Moderate)

  • ClickHouse expertise helpful (but not mandatory)
  • Monitoring: Standard Prometheus exporters
  • Backups: ClickHouse native snapshots

Compared to:

  • pgvector: Simpler (Postgres is Postgres), but 2x RAM usage
  • Qdrant: Similar complexity, but separate metadata sync overhead
  • Elasticsearch: Higher complexity (Java heap tuning, cluster management)

6. The ACID Elephant in the Room

MyScaleDB (ClickHouse) is NOT ACID-compliant. Writes are eventually consistent (batched inserts).

Why This Matters:
❌ Don't use MyScaleDB for transactional data (user accounts, billing, audit logs)
✅ Perfect for analytics, AI retrieval, time-series data

For production systems: We use YugabyteDB (PostgreSQL-compatible, ACID): Documents, billing, audit trail

  • MyScaleDB: Vector embeddings, analytical queries
  • Virtuoso (SPARQL): Semantic graph, ontology, logical reasoning

If ClickHouse were ACID, we could've used MyScaleDB for everything. Alas, physics: you can't have OLAP speed + ACID guarantees in the same engine (yet).

7. When NOT to Use MyScaleDB

Honesty matters. MyScaleDB isn't always the answer:

Use PostgreSQL + pgvector if:

✅ You have <5M vectors
✅ Your team knows Postgres inside-out
✅ You need ACID transactions on the same data
✅ Your queries are simple (no complex joins)

Use Qdrant/Pinecone if:

✅ You don't have SQL expertise on your team
✅ Your metadata is minimal (just a few tags)
✅ You want a fully managed SaaS (no ops)
✅ Your budget allows for premium pricing

Use MyScaleDB if:

✅ You have >10M vectors with complex metadata
✅ You need SQL JOINs + vector search in one query
✅ You want open-source with reasonable ops overhead
✅ You have ClickHouse experience (or willing to learn)

Conclusion: Convergence is Key

We spent the last 5 years unbundling the stack. Now we're remembering why SQL won in the first place.

For production-grade AI applications, we don't need "just a vector store." We need a database that understands that vectors are just another data type, not a separate universe.

MyScaleDB brings the power of ClickHouse (OLAP speed) to the world of AI. If you're building a scalable, complex retrieval system with heavy filtering, stop managing silos. Use SQL.

For my project, MyScaleDB solved the "Metadata Hell" where Qdrant failed. For your use case, benchmark yourself—but know that the future of vector databases looks a lot like SQL databases with native vector support.

References

About the Author: I'm architecting a legal document analysis system handling 26M+ documents/year. Previously explored Qdrant, pgvector, and settled on MyScaleDB for vector search + YugabyteDB for ACID + Virtuoso for semantic reasoning.

📬 Want essays like this in your inbox?

I just launched a newsletter about thinking clearly in tech — no spam, no fluff.

Subscribe here: https://buttondown.com/efficientlaziness

Efficient Laziness — Think once, well, and move forward.

Top comments (1)

Collapse
 
gnomeman4201 profile image
GnomeMan4201 •

In threat-intelligence work, vectors aren’t just about semantic similarity...they’re behavioral fingerprints. I use them for clustering suspicious accounts, detecting sock-puppet networks, and spotting coordinated activity. When I’m hunting credential-stuffing campaigns or bot-driven follower spikes, I need to correlate embedding clusters with login velocity, IP-geolocation deltas, and account-creation timestamps all in a single query.

Pure vector search can’t express those constraints. SQL becomes essential when you need to join those embeddings with metadata like timestamps, velocity patterns, device fingerprints, and graph relationships. That’s the only way to separate legitimate user behavior from coordinated adversarial actions at scale.