You Probably Don't Need a Vector Database. Here's How to Do AI in PostgreSQL or SurrealDB Instead.

Let's be honest: the "modern" AI stack has become a tangled mess. You're told you need your primary database, a separate specialized vector database for similarity search, and a third-party API or a dedicated inference engine just to generate embeddings. This isn't just complex; it's an expensive, slow, and brittle data pipeline.

But what if most of it is an unnecessary abstraction? What if the hype around dedicated vector databases is just that—hype?

This article is a practical, controversial guide to simplifying your AI stack. I'm going to show you how to bypass not only dedicated vector databases but also external inference engines by generating and searching embeddings directly within your database. This isn't a toy example; it's a production-ready approach that treats an embedding model (like GloVe or fastText) as just another piece of data. We'll explore two powerful blueprints for achieving this: one for the ever-reliable PostgreSQL and one for the modern multi-model SurrealDB.

It's time to ditch the expensive middleware and bring your AI logic back home to your data. All the code discussed is open-source and available in this GitHub repository.

The Foundation: DDL as a Blueprint for Simplicity

It all starts with the DDL. How we define our tables reveals how simple our architecture can be.

PostgreSQL: Turning a Workhorse into a Vector Engine

CREATE TABLE embedding (
    word TEXT PRIMARY KEY,          
    embedding vector              
);

CREATE INDEX idx_embedding_word ON embedding (word);

CREATE TABLE sample_content (
    id bigserial PRIMARY KEY,
    content TEXT,         
    embedding vector(50)               
);

CREATE INDEX ON sample_content USING hnsw (embedding vector_l2_ops);
        

Analysis:

• Challenging the Need for a New DB: With one extension (pgvector), the world's most trusted relational database becomes a powerful vector engine. This immediately questions the need to add a completely new database system to your stack just for vector search.
• Indexing is Key: A standard B-tree index on the word column is critical for fast token lookups, and the HNSW index is what enables the high-performance vector search. This is the core of what a "vector database" does, right inside Postgres.
• The Manual Step: The key limitation here is that generating the embedding is a manual process. You have to explicitly call a function in your application or use a trigger. It proves vector search is possible, but it doesn't fully eliminate the orchestration layer.

SurrealDB: Making In-Database AI Effortless

REMOVE TABLE IF EXISTS embedding_model;
DEFINE TABLE embedding_model TYPE NORMAL SCHEMAFULL;
DEFINE FIELD word ON embedding_model TYPE string;
DEFINE FIELD embedding ON embedding_model TYPE array<float>;

REMOVE TABLE IF EXISTS sample_content;
DEFINE TABLE sample_content TYPE NORMAL SCHEMAFULL;
DEFINE FIELD content ON sample_content TYPE string;
DEFINE FIELD embedding ON sample_content TYPE option<array<float>> DEFAULT ALWAYS fn::content_to_vector(content);


DEFINE INDEX  embedding_index_hnsw ON sample_content FIELDS embedding HNSW DIMENSION 50 M 32 EFC 300;

DEFINE ANALYZER IF NOT EXISTS large_name_analyzer TOKENIZERS class FILTERS lowercase, ascii, edgengram(2, 10);        

Analysis:

• The DEFAULT ALWAYS Game-Changer: This is where SurrealDB makes a compelling case for being the all-in-one solution. DEFAULT ALWAYS fn::content_to_vector(content) is a declaration that you are bypassing an external inference engine. The database itself is responsible for generating the embedding on write, automatically. This is a massive win for architectural simplicity and a direct challenge to the modularized AI stack.
• Native Power: Vector capabilities and text analyzers aren't add-ons; they are first-class citizens. This tight integration simplifies setup and can lead to significant performance gains.
• Record IDs as a Superpower: In SurrealDB, looking up a word's vector can be done via its lightning-fast Record ID (e.g., embedding_model:hello), bypassing the need for a traditional B-tree index on the word field and streamlining the lookup process.

The Engine Room: Bypassing the Inference Engine with SQL/SurrealQL

This is where the magic happens. By writing functions in our database, we are creating a lightweight, on-demand inference engine that lives right next to our data.

PostgreSQL: A Procedural, DIY Inference Engine

The plpgsql code requires a chain of functions to work, but the result is the same: text goes in, a vector comes out, with no external API call.

-- A key snippet from the main function, demo_get_sentence_vectors
-- 1. Try to find the word's vector in our model table.
SELECT embedding INTO word_embedding
FROM embedding
WHERE word = current_word;

-- 2. If found, use it. If not, handle the OOV case.
IF word_embedding IS NOT NULL THEN

    all_sentence_vectors := array_append(all_sentence_vectors, word_embedding);
ELSE
    -- Generate n-grams for the unknown word and average their vectors.

    oov_ngrams := demo_generate_edgengrams(current_word, 2, 10);
    -- ... (looping and averaging logic omitted for brevity)
END IF;
        

SurrealDB (functions.surql): A Declarative, Functional Inference Engine

SurrealDB's functional approach is far more concise and powerful, showing what's possible when AI is a native concept.

DEFINE FUNCTION fn::retrieve_vectors_for_text_with_oov($text:string)
{
    -- One statement to tokenize, look up vectors via Record ID, and handle misses.

    LET $all_vectors = (SELECT VALUE {'word':$this, 'embedding':type::thing("embedding_model",$this).embedding} FROM $text.lowercase().words());

    -- Separate found vectors from unfound words.
    LET $matched_word_vectors =  (SELECT VALUE embedding FROM $all_vectors WHERE embedding IS NOT NONE);

    LET $unmatched_words = (SELECT VALUE word FROM $all_vectors WHERE embedding IS NONE);

    -- For unfound words, use the built-in analyzer to find and average n-gram vectors.
    LET $umatched_word_vectors = $unmatched_words.map(
        |$word| fn::mean_vector((SELECT VALUE type::thing("embedding_model",$this).embedding FROM search::analyze("large_name_analyzer", $word.word)))
    );

    RETURN $matched_word_vectors.concat($umatched_word_vectors);
};
        

Beyond RAG: The Untapped Potential of Vector Search

When most people hear "vector search," they think of Retrieval-Augmented Generation (RAG) for chatbots. That's a powerful use case, but it's just the tip of the iceberg. Thinking of vectors solely for semantic search is like thinking of SQL solely for SELECT *. The real power lies in using vector similarity as a general-purpose tool for pattern matching and data analysis.

By training models on your own domain-specific data, you can unlock a new class of applications:

• Smarter Entity Retrieval: Train a model on your product catalog or internal documents. Now, a search for a partial or misspelled product name doesn't require complex full-text search logic; it's a single, blazing-fast vector query that understands the concept of your entities.
• Sophisticated Recommendation Engines: Represent users and items (products, articles, songs) as vectors. Finding "customers who bought this also liked..." becomes a simple nearest-neighbor search in the vector space.
• Anomaly & Fraud Detection: Most of your transactions or log entries will cluster together in the vector space. A fraudulent transaction or a critical system error will be a distant outlier, easily detectable by its vector distance from the norm.
• Data Deduplication & Clustering: Find duplicate customer records, group similar support tickets, or cluster products for analysis by finding items whose vectors are close together, even if their text representations are different.

Conclusion: Stop Buying Middleware, Start Using Your Database

The evidence is clear. Before you add another service, another pipeline, and another monthly bill to your stack, look at the incredible power sitting in the databases you can already use.

• PostgreSQL + pgvector proves that your trusted relational workhorse can be a capable vector database, eliminating the need for a separate system.
• SurrealDB goes a step further, making a powerful argument that a modern, multi-model database can seamlessly replace both the dedicated vector database and the external inference engine.

For a vast number of AI-powered applications, a dedicated vector database isn't just overkill—it's an unnecessary architectural and financial burden. The future of AI applications isn't about adding more boxes and arrows to our diagrams; it's about simplifying, integrating, and moving the logic closer to the data.

A Look Ahead: The Next Frontiers

Now, for some food for thought. While the patterns shown here are powerful, two major challenges lie on the horizon:

1. The Terabyte-Scale Challenge: At massive scale, in-memory indexes like HNSW become an operational and financial burden. The next frontier is applying these in-database generation patterns to analytical powerhouses like ClickHouse, DuckDB, or Apache Spark that are designed for distributed, disk-based processing.
2. The Binary Data Challenge: We've focused on text, but what about images, audio, or video? The next logical step is to build systems that can generate and search embeddings for binary data directly in the database. Imagine uploading an image and having the database automatically generate a vector with a function like fn::image_to_vector(image_blob), unlocking a whole new world of multi-modal search without external dependencies.

The real question isn't which vector database to choose, but whether you need one at all.

Ready to challenge the status quo and build a leaner AI stack? Let's connect. I specialize in finding pragmatic, powerful solutions that bypass the hype.