Table of Contents
Build a semantic search engine that understands context using embeddings.
In this guide, you'll learn how to build a semantic search system using Ollama, PostgreSQL, pgvector, and pgai—all in under 10 minutes.
The whole system runs on your machine - no OpenAI API keys, no rate limits, no sending sensitive data to external services. Just raw search capability that you can plug into any existing PostgreSQL database.
You'll have a working system that can:
- Search through documents based on meaning, not just keywords
- Return contextually relevant results using the latest LLMs
- Scale to handle large document collections Run completely on your own infrastructure
Semantic Search Architecture Overview
The stack is simple but powerful. Ollama handles the language models, pgvector adds vector search to PostgreSQL, and pgai brings advanced AI capabilities. You can build everything from semantic search to RAG systems to AI assistants.
Here's a quick overview of each component in the architecture:
Ollama: An open-source tool that enables you to run and manage LLMs and vision language models (VLMs) locally on your computer or a cloud server, providing enhanced privacy and control over data.
pgvector: A PostgreSQL extension that adds support for storing, indexing, and querying high-dimensional vector embeddings.
pgai: A PostgreSQL extension that simplifies data storage and retrieval for RAG and other AI applications by automating the creation and management of embeddings, facilitating semantic search, and enabling the execution of LLM functions directly within SQL queries.
pgai Vectorizer: A feature of the pgai extension that automates the creation and synchronization of vector embeddings directly within the database without the need for external tools.
Prerequisites
Before you get started, ensure that you have a working installation of PostgreSQL with the pgvector, pgai, and vectorizer extensions. You’ll also need to run the vectorizer workers, which automates the embedding creation process. To help you set up quickly, we’ve provided an example docker-compose file to get you started.
Download the Docker Compose file
curl -O https://raw.githubusercontent.com/timescale/pgai/main/examples/docker_compose_pgai_ollama/docker-compose.yml
Start the Docker Compose file
docker compose up -d
That should set up a PostgreSQL database with pgai, pgvector, and pgvectorscale extensions. It will also set up Ollama, which you can now use to deploy LLMs and embedding models.
Download embedding model
We will use the nomic-embed-text model for embedding generation. Download it using the following command:
docker compose exec ollama ollama pull nomic-embed-text
Set up PopSQL
We will use PopSQL as our SQL client. Head over to PopSQL, sign up, and download the desktop client. Once installed, click on Manage Connections and create a new PostgreSQL connection. Select the connection type as ‘Direct’ to connect to your local PostgreSQL installation.
Enable PostgreSQL extensions
We now need to enable the pgai and pgvector extensions. You can do so with the following SQL commands:
CREATE EXTENSION IF NOT EXISTS vector CASCADE;
CREATE EXTENSION IF NOT EXISTS ai CASCADE;
Your setup is ready. Let’s proceed!
Steps to Build Semantic Search with Ollama
The steps to implement semantic search within PostgreSQL are relatively straightforward. First, we’ll load a dataset from Hugging Face and use pgai’s vectorizer function to automatically generate embeddings from the data using Ollama and the nomic-embed-text embedding model. Then, we’ll perform semantic search by converting the user query into vector embeddings with the ai.ollama_embed function. These embeddings will be used to calculate similarity metrics and fetch relevant data.
Let’s start by creating a table and loading the dataset.
Create table
We’ll create a simple movies table to store selected columns from the dataset. Here’s how:
CREATE TABLE IF NOT EXISTS movies (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
overview TEXT,
genres TEXT,
producer TEXT,
"cast" TEXT
);
Load dataset
This guide will use the Cohere/movies dataset from Hugging Face. Pgai provides a convenient utility, ai.load_dataset, which allows you to download any dataset from Hugging Face and populate a table. Let’s use this utility to download the Cohere/movies dataset.
SELECT ai.load_dataset(
name => 'Cohere/movies',
table_name => 'movies',
if_table_exists => 'append',
field_types => '{"title": "TEXT", "overview": "TEXT", "genres": "TEXT", "producer": "TEXT", "cast": "TEXT"}'::jsonb
);
This will load the data into the movies table.
Create vectorizer
Next, you can automate the embedding creation using the vectorizer tool provided by pgai. The vectorizer eliminates the need for any external tools to create embeddings. You can set it up as follows:
SELECT ai.create_vectorizer(
'public.movies'::regclass,
destination => 'movies_embeddings',
embedding => ai.embedding_ollama('nomic-embed-text', 768),
chunking => ai.chunking_recursive_character_text_splitter('overview')
);
We used the ai.embedding_ollama function along with the nomic-embed-text embedding model. The generated embeddings are stored in the movie_embeddings table. Additionally, the vectorizer tool automatically creates a convenient view that joins the movies table with its embeddings.
Now, we’re ready to perform semantic search on this data.
Semantic search
To perform semantic search, we’ll use the ai.ollama_embed function with the same embedding model (nomic-embed-text) to generate query embeddings. These query embeddings will then be used to perform a similarity search on the movie_embeddings_vectorized view, which was automatically created by the vectorizer. Here’s how:
WITH query_embedding AS (
-- Generate the query embedding
SELECT ai.ollama_embed('nomic-embed-text', 'Epic science fiction movies', host => 'http://ollama:11434') AS embedding
)
SELECT
m.title,
m.overview,
m.genres,
m.producer,
m.cast,
t.embedding <=> (SELECT embedding FROM query_embedding) AS distance
FROM movies_embeddings t
LEFT JOIN movies m ON t.id = m.id
ORDER BY distance
LIMIT 5;
In the semantic search query above, we used the Cosine distance algorithm (<=>) to calculate similarity. You can also use L2 distance or inner product, depending on the nature of your data. Here’s how they are represented:
-
<->: L2 (Euclidean) distance -
<#>: Inner product -
<=>: Cosine distance
Results
The above query will give you the following results when you export in JSON format using PopSQL:
[
{
"title": "Epic",
"overview": "A teenager finds herself transported to a deep forest setting where a battle between the forces of good and the forces of evil is taking place. She bands together with a rag-tag group characters in order to save their world -- and ours.",
"genres": "Animation, Adventure, Family, Fantasy",
"producer": "Blue Sky Studios, Twentieth Century Fox Animation",
"cast": "Josh Hutcherson as Nod (voice), Amanda Seyfried as Mary Katherine (M.K.) (voice), Colin Farrell as Ronin (voice), Jason Sudeikis as Bomba (voice), Aziz Ansari as Mub (voice), Beyoncé Knowles as Queen Tara (voice), Judah Friedlander as Taxi Driver (voice), Steven Tyler as Nim Galuu (voice), Pitbull as Bufo (voice), Blake Anderson as Dagda (voice), Christoph Waltz as Mandrake (voice), John DiMaggio as Pinecone Jinn (voice), Allison Bills as Dandelion Jinn (voice), Todd Cummings as Fruit Fly (Old) (voice), Troy Evans as Thistle Jinn (voice), Emma Kenney as Marigold Girl (voice), Jason Harris as (voice), Jim Conroy as Race Announcer / Additional Voices (voice), Jason Fricchione as Bufo's Goon (voice), Helen Hong as Thistle Lady (voice), Kelly Keaton as Berry Lady (voice), Kyle Kinane as Biker Dude (voice), Anthony Lumia as Fruit Fly (Young) (voice), Malikha Mallette as Jinn Mom (voice), Rosa Salazar as Roller Derby Girl (voice)",
"distance": 0.5153232235101693
},
{
"title": "Epic Movie",
"overview": "When Edward, Peter, Lucy and Susan each follow their own path, they end up finding themselves at Willy's Chocolate factory. Walking through a wardrobe, they discover the world of Gnarnia, which is ruled by the White Bitch. Meeting up with characters such as Harry Potter and Captain Jack Swallows, the newly reunited family must team up with Aslo, a wise-but-horny lion to stop the white bitch's army",
"genres": "Action, Adventure, Comedy",
"producer": "Twentieth Century Fox Film Corporation, Regency Enterprises, New Regency Pictures, Paul Schiff Productions",
"cast": "Kal Penn as Edward, Adam Campbell as Peter, Jennifer Coolidge as White Bitch, Jayma Mays as Lucy, Faune A. Chambers as Susan, Crispin Glover as Willy, Tony Cox as Bink, Héctor Jiménez as Mr. Tumnus, Darrell Hammond as Captain Jack Swallows, Carmen Electra as Mystique, Fred Willard as Aslo, David Carradine as Museum Curator, Kevin McDonald as Harry Potter, George Alvarez as Ron, Crista Flanagan as Hermoine, Jill Latiano as Singing Pirate Girl, Irina Voronina as Jogging Girl, Britten Kelley as Cribs Faun",
"distance": 0.5596413576816696
},
{
"title": "Superhero Movie",
"overview": "The team behind Scary Movie takes on the comic book genre in this tale of Rick Riker, a nerdy teen imbued with superpowers by a radioactive dragonfly. And because every hero needs a nemesis, enter Lou Landers, aka the villainously goofy Hourglass.",
"genres": "Action, Comedy, Science Fiction",
"producer": "Dimension Films",
"cast": "Drake Bell as Dragonfly, Sara Paxton as Jill Johnson, Leslie Nielsen as Uncle Albert Adams, Christopher McDonald as Lou Landers, Jeffrey Tambor as Dr. Whitby, Pamela Anderson as Invisible Girl, Kevin Hart as Trey, Marion Ross as Aunt Lucille, Ryan Hansen as Lance Landers, Keith David as The Chief of Police, Robert Joy as Dr. Hawking, Robert Hays as Blaine Riker, Nicole Sullivan as Julia Riker, Sam Cohen as Young Rick, Tracy Morgan as Professor Xavier, Brent Spiner as Dr. Strom",
"distance": 0.5697590708732605
},
{
"title": "Titan A.E.",
"overview": "A young man finds out that he holds the key to restoring hope and ensuring survival for the human race, while an alien species called the Dredge are bent on mankind's destruction.",
"genres": "Animation, Action, Science Fiction, Family, Adventure",
"producer": "Twentieth Century Fox Film Corporation, David Kirschner Productions, Fox Animation Studios",
"cast": "Matt Damon as Cale Tucker, Bill Pullman as Captain Korso, Drew Barrymore as Akima, John Leguizamo as Gune, Nathan Lane as Preed, Janeane Garofalo as Stith, Ron Perlman as Prof. Sam Tucker, Alex D. Linz as Young Cale",
"distance": 0.5731358528137207
},
{
"title": "The Book of Eli",
"overview": "A post-apocalyptic tale, in which a lone man fights his way across America in order to protect a sacred book that holds the secrets to saving humankind.",
"genres": "Action, Thriller, Science Fiction",
"producer": "Alcon Entertainment, Silver Pictures",
"cast": "Denzel Washington as Eli, Gary Oldman as Carnegie, Mila Kunis as Solara, Ray Stevenson as Redridge, Jennifer Beals as Claudia, Tom Waits as Engineer, Michael Gambon as George, Frances de la Tour as Martha, Lateef Crowder as Highjacker #3, Chris Browning as Highjacker Leader, Joe Pingue as Hoyt, Evan Jones as Martz, Lora Martinez-Cunningham as Young Woman Highjacker, Malcolm McDowell as Lombardi, Mike McCarty as Sniper, Richard Cetrone as Hijacker, Don Thai Theerathada as Hijacker, Thom Williams as Hijacker, Scott Wilder as Middle-Aged Man, Heidi Pascoe as Middle-Aged Woman, Jennifer Caputo as Biker, Eddie Perez as Biker, Spencer Sano as Biker / Town Thug, Mike Gunther as Sniper, John Koyama as Sniper, Scott Michael Morgan as Construction Thug Leader, Sala Baker as Construction Thug, Arron Shiver as Bartender, Justin Tade as Town Doctor, Mike Seal as Door Guard, Edward A. Duran as Town Thug, David Wald as Town Thug, Jermaine Washington as Town Thug, Clay Donahue Fontenot as Convoy Thug, Al Goto as Convoy Thug, Brad Martin as Convoy Thug, Tim Rigby as Convoy Thug, Luis Bordonada as Carnegie Gunman #1, Angelique Midthunder as Gatling Gun Gunner, Todd Schneider as Caddy Driver, Darrin Prescott as Suburban Driver, Laurence Chavez as Ice Cream Truck Driver, Brian Neal Lucero as Alcatraz Guard, David Midthunder as Alcatraz Soldier, Karin Silvestri as Biker",
"distance": 0.5773254679039392
}
]
That’s it! Pgai and pgai Vectorizer enabled us to create semantic search directly within PostgreSQL using models deployed via Ollama. There was no need to rely on any external libraries or tools.
Next Steps
Want to start building? Sign up for Timescale Cloud (you can try it for free for 30 days), or learn more about pgai, pgai Vectorizer, or Ollama , and start creating semantic search applications today.
Top comments (0)